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

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