Thread: plpgsql Question..
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 ? Regds mallah. -- 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.
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. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Thanks rod , i am looking into the possibilities. Regds mallah. On Monday 28 Apr 2003 6:30 pm, Rod Taylor 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.
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'')
whereget_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.
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));
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.
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
> 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));
>
> 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.
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_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.
> --
> 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/
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.
>