Re: plpgsql Question..

> 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(at)foo(dot)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(at)rbt(dot)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/