X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 3DB3B475B47
	for <pgsql-sql@postgresql.org>; Mon, 28 Apr 2003 20:16:19 -0400 (EDT)
Received: from www.trade-india.com (ns1.trade-india.com [66.234.10.14])
	by postgresql.org (Postfix) with SMTP id CADC1475ADE
	for <pgsql-sql@postgresql.org>; Mon, 28 Apr 2003 20:16:17 -0400 (EDT)
Received: (qmail 3987 invoked by uid 99); 29 Apr 2003 00:14:43 -0000
Received: from 219.65.252.22 (SquirrelMail authenticated user mallah)
	by mail.trade-india.com with HTTP;
	Tue, 29 Apr 2003 05:44:43 +0530 (IST)
Message-ID: <1044.219.65.252.22.1051575283.squirrel@mail.trade-india.com>
Date: Tue, 29 Apr 2003 05:44:43 +0530 (IST)
Subject: Re: plpgsql  Question..
From: <mallah@trade-india.com>
To: <rbt@rbt.ca>
In-Reply-To: <1051537604.33234.5.camel@jester>
References: <200304281742.24167.mallah@trade-india.com>
	<1051534844.51984.8.camel@jester>
	<200304281901.42113.mallah@trade-india.com>
	<1051537604.33234.5.camel@jester>
X-Priority: 3
Importance: Normal
X-MSMail-Priority: Normal
Cc: <pgsql-sql@postgresql.org>
X-Mailer: SquirrelMail (version 1.2.6)
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
X-Spam-Status: No, hits=-32.0 required=5.0
	tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,MISSING_MIMEOLE,
	MISSING_OUTLOOK_NAME,NO_REAL_NAME,QUOTED_EMAIL_TEXT,
	QUOTE_TWICE_1,REFERENCES,REPLY_WITH_QUOTES
	autolearn=ham	version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200304/421
X-Sequence-Number: 13122

> Why select at all?  Just update them both with the below?
>
> UPDATE desttable SET <field> =3D coalesce(<field>, (SELECT <field> FROM s=
ourcetable)), <field2> =3D
> coalesce(<field2>, (SELECT <field2> FROM sourcetable));
>
> This will update field to be the old value of field, if one existed, or t=
he value from the
> sourcetable if it was null.
>
> If speed is an issue, look into an UPDATE with the FROM clause to do a jo=
in rather than several
> sub-selects.


Oops Sorry ,

This is indeed a solution  i missed to read the last two lines while
posting the previous reply. Thanks a lot i will try and get back.

Regds
Mallah.



>
>
> Build the above query by passing the function a list of fields via get_co=
lumns() 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) RET=
URNS 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 pr=
ofile_id=3Dsource;
>>
>>         IF NOT FOUND THEN
>>                 RAISE EXCEPTION '' profile % not found '' , source;
>>         END IF;
>>
>>         SELECT INTO dest_record   * from general.profile_master where pr=
ofile_id=3Ddest;
>>
>>         IF NOT FOUND THEN
>>                 RAISE EXCEPTION '' profile % not found '' , dest;
>>         END IF;
>>
>>         upd_stmt :=3D '' 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 :=3D upd_stmt || r.colname ||
>>                 ''=3D COALESCE (source_record.'' || r.colname || '', des=
t_record.'' || r.colname
>>                 || '') , '' ;
>>
>>         END LOOP ;
>>
>>         upd_stmt :=3D rtrim (upd_stmt , '', '');
>>
>>         upd_stmt :=3D upd_stmt || '' WHERE profile_id =3D '' || 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 fu=
nction would be
>> > capable of doing what you want.
> --
> Rod Taylor <rbt@rbt.ca>
>
> PGP Key: http://www.rbt.ca/rbtpub.asc



-----------------------------------------
Get your free web based email at trade-india.com.
   "India's Leading B2B eMarketplace.!"
http://www.trade-india.com/