Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 61E999FA280 for ; Sat, 25 Nov 2006 00:17:50 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 61830-09 for ; Sat, 25 Nov 2006 00:17:45 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from web90513.mail.mud.yahoo.com (web90513.mail.mud.yahoo.com [216.252.100.180]) by postgresql.org (Postfix) with SMTP id C4EE09FA17D for ; Sat, 25 Nov 2006 00:17:44 -0400 (AST) Received: (qmail 60659 invoked by uid 60001); 25 Nov 2006 04:17:43 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=PrjXu6CqqJaQF9ZU3bEXz+EVChjEZjkBJS0bZAC66aPXqetxrmMLFRU5Yes7mU1L7RR3ktahNwpmzABogSFywZrzYji7cp/0m/WPhYTqN/0t2Xq9v9xyBOzgO2jMi7M2yDLFnulg7G7hBWrP9vHvpGMd+hyimkVQQjZ8qYEvJwE= ; Message-ID: <20061125041743.60657.qmail@web90513.mail.mud.yahoo.com> Received: from [60.48.98.90] by web90513.mail.mud.yahoo.com via HTTP; Fri, 24 Nov 2006 20:17:43 PST Date: Fri, 24 Nov 2006 20:17:43 -0800 (PST) From: Mulham freshcode Subject: Re: select into To: aklaver@comcast.net, pgsql-sql@postgresql.org In-Reply-To: <200611240730.33837.aklaver@comcast.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-96012412-1164428263=:60452" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/250 X-Sequence-Number: 26902 --0-96012412-1164428263=:60452 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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 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@comcast.net --------------------------------- Access over 1 million songs - Yahoo! Music Unlimited. --0-96012412-1164428263=:60452 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
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@comcast.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@comcast.net

Access over 1 million songs - Yahoo! Music Unlimited. --0-96012412-1164428263=:60452--