Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id B6F982E2E8A for ; Tue, 11 Dec 2007 22:34:58 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 08090-05-3 for ; Tue, 11 Dec 2007 22:34:53 -0400 (AST) Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.240]) by postgresql.org (Postfix) with ESMTP id CAABE2E14BF for ; Tue, 11 Dec 2007 22:34:53 -0400 (AST) Received: by an-out-0708.google.com with SMTP id c36so16229ana.33 for ; Tue, 11 Dec 2007 18:34:52 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=eoVL8J/alRBmr88g8/PgwrAyXfzA5H1XkY0kNFiO2WM=; b=B9AAl4+cvfHs51n9fPjgalOJLA+CMFqT/WA0wqY4tfxGU7GSD6L9v+KuHZ7WdDj8vaHMGQsDOPj5yaVVkgbQdetiICv9Hf0jVlwWLM9zHrQ6EVg4qB0/Jqy9DlhqBV2wiumeeR9R0+B0l38hx9H2mXP3SzhXa50ksDHsuTe9p6I= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=dGwVCKauvkV2BXwAcT/w7h1Kh78UPoaMVQQImwn1LJohdIWdGwFNBPWxjE7bQLUtX/Up/JvsSzNrAQ9j0RqEeoukIh/g8m/dE5dleDKCbGfVMnOx/GKTjmuGZ2mzoZCahoz2ByktZB3oDhmnSB6dsb1GVszT/7tEDiOAS9I6BCA= Received: by 10.100.251.5 with SMTP id y5mr338038anh.12.1197426892465; Tue, 11 Dec 2007 18:34:52 -0800 (PST) Received: by 10.90.79.20 with HTTP; Tue, 11 Dec 2007 18:34:52 -0800 (PST) Message-ID: <493343bd0712111834o14b436f5maacba22a76993776@mail.gmail.com> Date: Wed, 12 Dec 2007 22:04:52 +1930 From: "=?ISO-8859-1?Q?Elvis_Henr=EDquez?=" To: "Allan Kamau" Subject: Re: (Pl/SQL) Obtaining field names from record 'variable' Cc: pgsql-admin@postgresql.org In-Reply-To: <248682.35322.qm@web53508.mail.re2.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1848_24109331.1197426892454" References: <248682.35322.qm@web53508.mail.re2.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.87 tagged_above=0 required=5 tests=AWL=1.277, BAYES_00=-2.599, HTML_MESSAGE=0.001, INVALID_DATE_TZ_ABSURD=2.191 X-Spam-Level: X-Archive-Number: 200712/56 X-Sequence-Number: 27573 ------=_Part_1848_24109331.1197426892454 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi. You could 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 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 all the text functions available, so you can split the field names, join them, etc. I have done some similar queries. It's pretty simple. Hope it helped. Elvis. On Dec 12, 2007 7:37 AM, Allan Kamau wrote: > Hi, > I am writing a PL/SQL script 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. > For example my table is listed below (briefly) > > create table my_table > ( > id int not null-- default nextval(('my_table_seq'::text)::regclass) > ,some_explanation varchar(20) not null > ,D30 varchar(100) null > ,W32 varchar(100) null > ,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 field. > > > > Allan. > > > > > > ____________________________________________________________________________________ > Be a better friend, newshound, and > know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > ------=_Part_1848_24109331.1197426892454 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Hi. You could 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 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 all the text functions available, so you can split the field names, join them, etc.

I have done some similar queries. It's pretty simple.

Hope it helped.

Elvis.

On Dec 12, 2007 7:37 AM, Allan Kamau < kamauallan@yahoo.com > wrote:
Hi,
I am writing a PL/SQL script 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.
For example my table is listed below (briefly)

create table my_table
(
id int not null-- default nextval(('my_table_seq'::text)::regclass)
,some_explanation varchar(20) not null
,D30 varchar(100) null
,W32 varchar(100) null
,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 field.


Allan.




     ____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile.  Try it now.   http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ


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

------=_Part_1848_24109331.1197426892454--