Re: select into - Mailing list pgsql-sql

<br />Hi Adrian,<br /><br />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
columnname 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?<br /><br />here is my code so far
<br/><br />    sql_str1 = 'select * from ' || svc_tbl_name || ' where uid = ' || sub_id ;<br />    <br />    for
svc_data_recin execute sql_str1 loop<br />    end loop;<br /><br />    -- get service_user table's column names<br
/>   for col_name in select column_name <br />            from information_schema.columns <br />            where
table_name~svc_tbl_nameloop<br />        <br />            raise notice 'Column name:%', col_name.column_name;<br
/>           raise notice 'Value: %', svc_data_rec[col_name.column_name];<br />    <br />    end loop;<br /><br />Thank
you,<br/><br />Mustafa ...<br /><br /><br /><b><i>Adrian Klaver <aklaver@comcast.net></i></b> wrote:<blockquote
class="replbq"style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"> On Thursday 23
November2006 10:54 pm, Mulham freshcode wrote:<br />> Hi Tom,<br />><br />> Thanks for the help. Am using
version8.0 and it seems like RECORD is not<br />> that dynamic still. I tried with the FOR ... IN EXECUTE ... LOOP
andit<br />> does the trick. But am still finding it hard to move forward with this. I<br />> have the name of
tablefield in a varchar variable that i got from<br />> information_schema.columns and I have the records variable
thatstores the <br />> contains the data from that table. Usually I'd do something like<br />> data_rec.col_name
toextract the data from the record but now I don't know<br />> the name per se. how can i say something like
data_rec[col_name]where<br />> col_name is a variable that has the actual column name. I found no examples<br />>
inthe docs that explain this. Can it be done in version 8.0.1?<br />><br />> I find variable substitution kind of
confusing.I mean why is there no way<br />> of saying explicitly replace this variable with its content before<br
/>>executing the statement?<br />><br />> Sorry for the long question,<br />> and thanks again for the
help<br/>><br />> Mustafa...<br />><br />> Tom Lane  wrote: Mulham freshcode writes:<br />> > execute
sql_str1into svc_data_rec ;<br />> ><br />> > svc_data_rec is a RECORD, which is supposed to be dynamic.<br
/>><br/>> This should work --- in PG 8.1 or later. In older versions you'd have<br />> to fool around with a
FOR... IN EXECUTE ... loop.<br />><br />> regards, tom lane<br />><br />I am trying to sort this out. Are you
tryingto find the data for a single <br />field from each table, or for some set of fields?. If you are looking for <br
/>datafrom a single field couldn't you dispense with the RECORD variable and <br />just build a query of the form
SELECTcol_name FROM tbl_name. If you want to <br />go through a set of fields then it would involve some nested
loops.<br/><br />-- <br />Adrian Klaver <br />aklaver@comcast.net<br /></blockquote><br /><p><hr size="1" />Access over
1million songs - <a
href="http://pa.yahoo.com/*http://us.rd.yahoo.com/evt=36035/*http://music.yahoo.com/unlimited/">Yahoo!Music
Unlimited.</a>