From: mallah@trade-india.com
To: rbt@rbt.ca
Cc: pgsql-sql@postgresql.org
Subject: Re: plpgsql Question..
Date: Tue, 29 Apr 2003 05:44:43 +0530 (IST)
Message-ID: <1044.219.65.252.22.1051575283.squirrel@mail.trade-india.com> (raw)
In-Reply-To: <1051537604.33234.5.camel@jester>
References: <200304281742.24167.mallah@trade-india.com>
<1051534844.51984.8.camel@jester>
<200304281901.42113.mallah@trade-india.com>
<1051537604.33234.5.camel@jester>
> Why select at all? Just update them both with the below?
>
> UPDATE desttable SET <field> = coalesce(<field>, (SELECT <field> FROM sourcetable)), <field2> =
> coalesce(<field2>, (SELECT <field2> FROM sourcetable));
>
> This will update field to be the old value of field, if one existed, or the value from the
> sourcetable if it was null.
>
> If speed is an issue, look into an UPDATE with the FROM clause to do a join rather than several
> sub-selects.
Oops Sorry ,
This is indeed a solution i missed to read the last two lines while
posting the previous reply. Thanks a lot i will try and get back.
Regds
Mallah.
>
>
> Build the above query by passing the function a list of fields via get_columns() as used below.
>
> On Mon, 2003-04-28 at 09:31, Rajesh Kumar Mallah wrote:
>> Hi,
>>
>> I wanted to "merge" two records in a table
>> having lots of feilds i dont want to modify
>> the procedure every time a add a new record.
>>
>> so i pass the function two primary keys to be
>> merged (source,dest) , the function
>>
>>
>> 1. shud iterate the list of feilds in that tables .
>> 2. For each feild compare the two values in the rows and pick the not
>> null one from either of the two.
>>
>> 3. update dest row with the not null values derieved from source if
>> dest were null.
>>
>>
>>
>> the following is my unsuccessful attempt to this
>> requirement.
>>
>>
>>
>> CREATE OR REPLACE FUNCTION general.merge_profiles (integer,integer) RETURNS text AS '
>>
>> DECLARE
>> source alias for $1;
>> dest alias for $2;
>> source_record RECORD;
>> dest_record RECORD;
>> r RECORD;
>> upd_stmt text;
>>
>>
>> BEGIN
>>
>> SELECT INTO source_record * from general.profile_master where profile_id=source;
>>
>> IF NOT FOUND THEN
>> RAISE EXCEPTION '' profile % not found '' , source;
>> END IF;
>>
>> SELECT INTO dest_record * from general.profile_master where profile_id=dest;
>>
>> IF NOT FOUND THEN
>> RAISE EXCEPTION '' profile % not found '' , dest;
>> END IF;
>>
>> upd_stmt := '' UPDATE profile_master SET '' ;
>>
>> FOR r IN SELECT get_columns as colname from utils.get_columns(''profile_master'' ,
>> ''general'')
>> where get_columns not in (''profile_id'') LOOP
>> upd_stmt := upd_stmt || r.colname ||
>> ''= COALESCE (source_record.'' || r.colname || '', dest_record.'' || r.colname
>> || '') , '' ;
>>
>> END LOOP ;
>>
>> upd_stmt := rtrim (upd_stmt , '', '');
>>
>> upd_stmt := upd_stmt || '' WHERE profile_id = '' || dest || '';'' ;
>>
>> -- PERFORM upd_stmt;
>> RAISE INFO ''%'' , upd_stmt;
>>
>> RETURN ''OK'';
>> END;
>>
>> ' LANGUAGE 'plpgsql' ;
>>
>>
>>
>>
>> On Monday 28 Apr 2003 6:30 pm, you wrote:
>> > On Mon, 2003-04-28 at 08:12, Rajesh Kumar Mallah wrote:
>> > > is it possible to access a feild in a RECORD type
>> > > variable where the feild name is variable.
>> > >
>> > > eg say r is a record having feild name , email , salary
>> > >
>> > > r.name is 'foo'
>> > > r.email is 'bar@foo.com'
>> > > r.salary is 1000
>> > >
>> > > suppose feild_name iterates via a FOR LOOP
>> > > through values (name , email , salary)
>> > >
>> > > is it possible to access that feild in record r
>> > > inside the loop ?
>> >
>> > Not that I'm aware of. What you can do it build the query to SELECT <variable> FROM table.
>> >
>> > Another alternative is to switch languages. A perl, tcl, etc. based function would be
>> > capable of doing what you want.
> --
> Rod Taylor <rbt@rbt.ca>
>
> PGP Key: http://www.rbt.ca/rbtpub.asc
-----------------------------------------
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
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: mallah@trade-india.com, rbt@rbt.ca
Subject: Re: plpgsql Question..
In-Reply-To: <1044.219.65.252.22.1051575283.squirrel@mail.trade-india.com>
* 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