From: Rajesh Kumar Mallah <mallah@trade-india.com>
Organization: Infocom Network Limited
To: Rod Taylor <rbt@rbt.ca>
Subject: Re: plpgsql  Question..
Date: Mon, 28 Apr 2003 19:01:42 +0530
User-Agent: KMail/1.5
References: <200304281742.24167.mallah@trade-india.com>
	<1051534844.51984.8.camel@jester>
In-Reply-To: <1051534844.51984.8.camel@jester>
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200304281901.42113.mallah@trade-india.com>

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.

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