Thread: 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@wolff.to]
Sent: Tuesday, August 21, 2001 7:23 AM
To: Oberpriller, Wade D.
Cc: 'pgsql-general@postgresql.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@nsc-msg01.network.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@postgresql.org