FW: Re: Dynamically discovering field names in PLPGSQL queries

This worked fine to dynamically determine the layout of a table. How does
one get the values from the table?

For example:
DECLARE
REC1 RECORD;
REC2 RECORD;
ID INTEGER;
ID := 1;
FOR REC1 IN select attname from pg_attribute, pg_class where attrelid =
relfilenode and
attnum > 0 and relname = 'mytable' order by attnum LOOP
FOR REC2 IN EXECUTE 'select ' || quote_ident(REC1.attname) ||
' from mytable where (id = ID)' LOOP
???
??? How can one access the data within the REC2?
??? REC2.???
???
END LOOP;
END LOOP;

Thanks for any help,

Wade Oberpriller

-----Original Message-----
From: Bruno Wolff III [mailto:bruno(at)wolff(dot)to]
Sent: Tuesday, August 21, 2001 7:23 AM
To: Oberpriller, Wade D.
Cc: 'pgsql-general(at)postgresql(dot)org'
Subject: [GENERAL] Re: Dynamically discovering field names in PLPGSQL
queries

On Mon, Aug 20, 2001 at 04:44:13PM -0500,
"Oberpriller, Wade D." <oberpwd(at)nsc-msg01(dot)network(dot)com> wrote:
> Is there a way to discover the names of the fields in a record from a
SELECT
> statement in PLPGSQL?
>
> For example:
>
> SELECT INTO REC * FROM mytable;
>
> Is there a mechanism to determine what the "*" expanded to? or does a
> programmer always have to know the layout of the table apriori?

I think the following will work:
select attname from pg_attribute, pg_class where attrelid = relfilenode and
attnum > 0 and relname = 'mytable' order by attnum;

I tried this on one of my tables and it looked OK.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org