pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
(Pl/SQL) Obtaining field names from record 'variable'
3+ messages / 2 participants
[nested] [flat]

* (Pl/SQL) Obtaining field names from record 'variable'
@ 2007-12-11 12:07  Allan Kamau <kamauallan@yahoo.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Allan Kamau @ 2007-12-11 12:07 UTC (permalink / raw)
  To: pgsql-admin

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 




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: (Pl/SQL) Obtaining field names from record 'variable'
@ 2007-12-12 02:34  Elvis Henríquez <henriquez.elvis@gmail.com>
  parent: Allan Kamau <kamauallan@yahoo.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Elvis Henríquez @ 2007-12-12 02:34 UTC (permalink / raw)
  To: Allan Kamau <kamauallan@yahoo.com>; +Cc: pgsql-admin

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
>

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: (Pl/SQL) Obtaining field names from record 'variable'
@ 2007-12-12 08:18  Allan Kamau <kamauallan@yahoo.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Allan Kamau @ 2007-12-12 08:18 UTC (permalink / raw)
  To: pgsql-admin

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 name of the returned record variable?
Let me explain, lets say I have managed 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íquez <henriquez.elvis@gmail.com>
To: Allan Kamau <kamauallan@yahoo.com>
Cc: pgsql-admin@postgresql.org
Sent: Wednesday, December 12, 2007 8:34:52 PM
Subject: Re: [ADMIN] (Pl/SQL) Obtaining field names from record 'variable'



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








Hi,


      Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.





      ____________________________________________________________________________________
Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  http://tools.search.yahoo.com/newsearch/category.php?category=shopping

^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2007-12-12 08:18 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-11 12:07 (Pl/SQL) Obtaining field names from record 'variable' Allan Kamau <kamauallan@yahoo.com>
2007-12-12 02:34 ` Elvis Henríquez <henriquez.elvis@gmail.com>
2007-12-12 08:18 Re: (Pl/SQL) Obtaining field names from record 'variable' Allan Kamau <kamauallan@yahoo.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox