Re: plpgsql Question..

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.

--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.