From: | Mulham freshcode <mulhamcode(at)yahoo(dot)com> |
---|---|
To: | aklaver(at)comcast(dot)net, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: select into |
Date: | 2006-11-25 04:17:43 |
Message-ID: | 20061125041743.60657.qmail@web90513.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi Adrian,
I have number of similar tables that have different number of fields (similar in functionality). An in my stored procedure am trying to select a row from one of these tables (that i don't know in advance, hence the use of record) and return the data in the form of a table that has column_name:value pairs. where column name is that from the original table. I have no problem finding the column names but I don't know how to say data[column_name] to get the corresponding value. Is there a way to do it in pgsql?
here is my code so far
sql_str1 = 'select * from ' || svc_tbl_name || ' where uid = ' || sub_id ;
for svc_data_rec in execute sql_str1 loop
end loop;
-- get service_user table's column names
for col_name in select column_name
from information_schema.columns
where table_name~svc_tbl_name loop
raise notice 'Column name:%', col_name.column_name;
raise notice 'Value: %', svc_data_rec[col_name.column_name];
end loop;
Thank you,
Mustafa ...
Adrian Klaver <aklaver(at)comcast(dot)net> wrote: On Thursday 23 November 2006 10:54 pm, Mulham freshcode wrote:
> Hi Tom,
>
> Thanks for the help. Am using version 8.0 and it seems like RECORD is not
> that dynamic still. I tried with the FOR ... IN EXECUTE ... LOOP and it
> does the trick. But am still finding it hard to move forward with this. I
> have the name of table field in a varchar variable that i got from
> information_schema.columns and I have the records variable that stores the
> contains the data from that table. Usually I'd do something like
> data_rec.col_name to extract the data from the record but now I don't know
> the name per se. how can i say something like data_rec[col_name] where
> col_name is a variable that has the actual column name. I found no examples
> in the docs that explain this. Can it be done in version 8.0.1?
>
> I find variable substitution kind of confusing. I mean why is there no way
> of saying explicitly replace this variable with its content before
> executing the statement?
>
> Sorry for the long question,
> and thanks again for the help
>
> Mustafa...
>
> Tom Lane wrote: Mulham freshcode writes:
> > execute sql_str1 into svc_data_rec ;
> >
> > svc_data_rec is a RECORD, which is supposed to be dynamic.
>
> This should work --- in PG 8.1 or later. In older versions you'd have
> to fool around with a FOR ... IN EXECUTE ... loop.
>
> regards, tom lane
>
I am trying to sort this out. Are you trying to find the data for a single
field from each table, or for some set of fields?. If you are looking for
data from a single field couldn't you dispense with the RECORD variable and
just build a query of the form SELECT col_name FROM tbl_name. If you want to
go through a set of fields then it would involve some nested loops.
--
Adrian Klaver
aklaver(at)comcast(dot)net
---------------------------------
Access over 1 million songs - Yahoo! Music Unlimited.
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2006-11-26 22:45:57 | Re: select into |
Previous Message | Tom Lane | 2006-11-24 16:02:00 | Re: select into |