From: Greg Patnude <gpatnude@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: best way to swap two records (computer details)
Date: Fri, 18 Mar 2005 08:32:25 -0800
Message-ID: <d1evm7$2g5i$1@news.hub.org> (raw)
References: <200503181325.23293.gary.stainburn@ringways.co.uk>
How about a user defined function ???
CREATE OR REPLACE FUNCTION harwareupdate(integer, integer) RETURNS BOOLEAN
AS '
update pieces set p_name = \'LSALES1\', p_location = \'Mike Haley\',
p_site = \'L\' where p_id = $1;
update pieces set p_name = \'SPARE\', p_location = \'spare\', p_site =
\'L\'
where p_id = 2;
update pieces set p_owner = $1 where p_owner = $2 and p_type
in (select hwt_id from hw_types where hwt_cat in (
select hwc_id from hw_categories where hwc_hwg_id = 7
)
);
SELECT TRUE;
' LANGUAGE SQL;
Then all you need to do is:
SELECT * FROM updatehardware(724, 305);
"Gary Stainburn" <gary.stainburn@ringways.co.uk> wrote in message
news:200503181325.23293.gary.stainburn@ringways.co.uk...
> Hi folks.
>
> I have a table called pieces which contain every piece of hardware and
> software within my company.
>
> Each piece has an owner attribute which points to another piece which -
> funnily enough - owns it.
>
> For example records for CPU, motherboard, HDD, O/S, and applications
> will all be owned by a piece record representing a computer.
>
> I'm currently going through an upgrade process at the moment where I
> build a new PC, install all relevent software and use Documents and
> Settings Transfer Wizard to move a user onto the new PC before wiping
> and disposing the old PC.
>
> My question is what's the best way to swap settings between the two
> computer records and swap any software installed? Ideally I'd like it
> in the form of a function where I can pass the two p_id's and return a
> boolean reflecting success (true) or fail (false).
>
> Currently I do this manually with:
>
> update pieces set p_name = 'LSALES1', p_location = 'Mike Haley', p_site
> = 'L' where p_id = 724;
> update pieces set p_name = 'SPARE', p_location = 'spare', p_site = 'L'
> where p_id = 305;
>
> update pieces set p_owner = 724 where p_owner = 305 and p_type in (
> select hwt_id from hw_types where hwt_cat in (
> select hwc_id from hw_categories where hwc_hwg_id = 7));
>
> The hw_types and hw_categories select all O/S and application software.
> This doesn't put any software currently on 305 onto 724 which would be
> nice.
>
> (I'm not after someone to do my work for me, but a good starting point
> would be very much appreciated)
>
> --
> Gary Stainburn
>
> This email does not contain private or confidential material as it
> may be snooped on by interested government parties for unknown
> and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gpatnude@hotmail.com
Subject: Re: best way to swap two records (computer details)
In-Reply-To: <d1evm7$2g5i$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox