Re: plpgsql Question.. - Mailing list pgsql-sql

> 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/