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.

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