Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 4721D2E00AD for ; Wed, 12 Dec 2007 04:18:15 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 13000-08 for ; Wed, 12 Dec 2007 04:18:04 -0400 (AST) Received: from web53502.mail.re2.yahoo.com (web53502.mail.re2.yahoo.com [206.190.37.63]) by postgresql.org (Postfix) with SMTP id 4A9B12E009F for ; Wed, 12 Dec 2007 04:18:07 -0400 (AST) Received: (qmail 42097 invoked by uid 60001); 12 Dec 2007 08:18:05 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID; b=NBleuCrUIDOKbjAoOOIh4qaWy0FHc2Q9TtVGFQ62mvYCpE8y4Ea/HGWjUsPOmGxy9c1K/SycQQw46ZxS5AoYdxcPvu53PcqTen4e7MYcfkw9aG7i4OIoKj30Ht17CTjxxgSMGOpOOk8NIxEMBXrApr0iG8fdIWNtpjng8N7AY+M=; X-YMail-OSG: OZFPKWcVM1nDthevN3wURC2oxsT32vntO6KSl7ssgyUlaV1zyAYomChC1RAZx3I30N2xPaGpb6JO60U46jjkOtnn2x3KvEaV..hI1ww.bWrjx97oX1IfMtMdoWh2rw-- Received: from [196.14.186.131] by web53502.mail.re2.yahoo.com via HTTP; Wed, 12 Dec 2007 00:18:05 PST X-Mailer: YahooMailRC/818.31 YahooMailWebService/0.7.158.1 Date: Wed, 12 Dec 2007 00:18:05 -0800 (PST) From: Allan Kamau Subject: Re: (Pl/SQL) Obtaining field names from record 'variable' To: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-785433824-1197447485=:39716" Message-ID: <116540.39716.qm@web53502.mail.re2.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/63 X-Sequence-Number: 27580 --0-785433824-1197447485=:39716 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hi,Thank you for your reply Elvis, am still finding my feet on how to query= the system catalogs though and interested in knowing how to do this.=0ATod= ay I have another question, how do I query for a field value from the dynam= ically constructed field name of the returned record variable?=0ALet me exp= lain, lets say I have managed to obtain the field name 'D30' (from using th= e system catalogs or from reading field names I had previously placed in a= n array) into a varchar(20) variable called current_field, how can I get th= e fields value from my record variable called 'query_rec', and a simple que= ry_rec.current_field will not do?=0A=0AAllan.=0A=0A=0A----- Original Messag= e ----=0AFrom: Elvis Henr=EDquez =0ATo: Allan Ka= mau =0ACc: pgsql-admin@postgresql.org=0ASent: Wednesd= ay, December 12, 2007 8:34:52 PM=0ASubject: Re: [ADMIN] (Pl/SQL) Obtaining = field names from record 'variable'=0A=0A=0A=0AHi. You could do a query to t= he system catalogs (pg_attribute, for example) with your table name as the = search criteria and the field names as the data; loop through that data to = create your query in a string variable, and then EXECUTE it. While you are = "looping" through the results of the system catalog query, you can apply al= l the text functions available, so you can split the field names, join them= , etc.=0A=0A=0AI have done some similar queries. It's pretty simple. =0A=0A= Hope it helped.=0A=0AElvis.=0A=0AOn Dec 12, 2007 7:37 AM, Allan Kamau <=0Ak= amauallan@yahoo.com=0A> wrote:=0AHi,=0AI am writing a PL/SQL script to proc= ess data from a high dimensionality table where this table has most of it's= field names encapsulating data. I would like to automatically loop through= the fields of a RECORD variable (returned from a cursor) obtaining the fi= eld name and the field value.=0A=0AFor example my table is listed below (br= iefly)=0A=0Acreate table my_table=0A(=0Aid int not null-- default nextval((= 'my_table_seq'::text)::regclass)=0A,some_explanation varchar(20) not null= =0A,D30 varchar(100) null=0A=0A,W32 varchar(100) null=0A,C33 varchar(100) n= ull=0A,V7 varchar(100) null=0A,...=0A)=0A;=0A=0AThe field name W32 consists= of 'W' which is data I need and '32' which is data I also need along with = the actual record value contained in this field.=0A=0A=0A=0AAllan.=0A=0A=0A= =0A=0A _______________________________________________________________= _____________________=0ABe a better friend, newshound, and=0Aknow-it-all wi= th Yahoo! Mobile. Try it now. =0A=0Ahttp://mobile.yahoo.com/;_ylt=3DAhu06= i62sR8HDtDypao8Wcj9tAcJ=0A=0A=0A---------------------------(end of broadcas= t)---------------------------=0ATIP 1: if posting/reading through Usenet, p= lease send an appropriate=0A=0A=0A subscribe-nomail command to majord= omo@postgresql.org so that your=0A message can get through to the mai= ling list cleanly=0A=0A=0A=0A=0A=0A=0A=0A=0AHi,=0A=0A=0A Be a better f= riend, newshound, and =0Aknow-it-all with Yahoo! Mobile. Try it now.=0A=0A= =0A=0A=0A=0A _________________________________________________________= ___________________________=0ALooking for last minute shopping deals? =0AF= ind them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/= category.php?category=3Dshopping --0-785433824-1197447485=:39716 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
Hi,
Thank you for your reply= Elvis, am still finding my feet on how to query the system catalogs though= and interested in knowing how to do this.
Today I have another question= , how do I query for a field value from the dynamically constructed field n= ame of the returned record variable?
Let me explain, lets say I have man= aged to obtain the field name 'D30' (from using the system catalogs or from= reading field names I had  previously placed in an array) into a varchar(20) variable called current_field, how can I get the fields value = from my record variable called 'query_rec', and a simple query_rec.current_= field will not do?

Allan.


----- Original Message = ----
From: Elvis Henr=EDquez <henriquez.elvis@gmail.com>
To: Al= lan Kamau <kamauallan@yahoo.com>
Cc: pgsql-admin@postgresql.orgSent: Wednesday, December 12, 2007 8:34:52 PM
Subject: Re: [ADMIN] (Pl/= SQL) Obtaining field names from record 'variable'

=0A
Hi. You cou= ld do a query to the system catalogs (pg_attribute, for example) with your = table name as the search criteria and the field names as the data; loop thr= ough that data to create your query in a string variable, and then EXECUTE = it. While you are "looping" through the results of the system catalog query= , you can apply all the text functions available, so you can split the fiel= d names, join them, etc.=0A

I have done some similar queries. It's p= retty simple.

Hope it helped.

Elvis.

On Dec 12, 2007 7:37 AM, Allan Kamau <=0Akamauallan@yahoo.com=0A> wrote:
Hi,
I am writing a PL/SQL s= cript to process data from a high dimensionality table where this table has= most of it's field names encapsulating data. I would like to automatically= loop through the fields of a  RECORD variable (returned from a cursor= ) obtaining the field name and the field value.=0A
For example my table = is listed below (briefly)

create table my_table
(
id int not n= ull-- default nextval(('my_table_seq'::text)::regclass)
,some_explanatio= n varchar(20) not null
,D30 varchar(100) null=0A
,W32 varchar(100) nu= ll
,C33 varchar(100) null
,V7 varchar(100) null
,...
)
;
=
The field name W32 consists of 'W' which is data I need and '32' which = is data I also need along with the actual record value contained in this fi= eld.=0A


Allan.




     _________= ___________________________________________________________________________=
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. &= nbsp;Try it now.  =0A=0Ahttp://mobile= .yahoo.com/;_ylt=3DAhu06i62sR8HDtDypao8Wcj9tAcJ


------------= ---------------(end of broadcast)---------------------------
TIP 1: if p= osting/reading through Usenet, please send an appropriate
=0A=0A   =     subscribe-nomail command to majordomo@postgresql.org so that your
    =   message can get through to the mailing list cleanly
=

=0A=0A

Hi,

=0A
Be= a better friend, newshound, and =0Aknow-it-all with Yahoo! Mobile. Try it n= ow.


=0A
Be a better friend, = newshound, and =0Aknow-it-all with Yahoo! Mobile. Try it now. --0-785433824-1197447485=:39716--