Re: plpgsql Question..

On 28 Apr 2003, Rod Taylor wrote:

> 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));

Hey i dont want to depend on the current list of feilds in the
table rite now. In that case i will have to keep updating
the function when the table structure is altered.

so i wanted to depend on the catalogs for getting list of
feilds.

is my thinking correct ?

regds
mallah.

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