Re: Dynamically discovering field names in PLPGSQL queries

On Mon, Aug 20, 2001 at 04:44:13PM -0500, Oberpriller, Wade D. 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?

to see the inner workings of postgresql, try
    psql -E
from which you can learn buckets and heaps.

to wit:

$ psql -E db -c '\d cust'

********* QUERY *********
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
FROM pg_class WHERE relname='cust'
*************************

********* QUERY *********
SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull, a.atthasdef, a.attnum
FROM pg_class c, pg_attribute a
WHERE c.relname = 'cust'
  AND a.attnum > 0 AND a.attrelid = c.oid
ORDER BY a.attnum
*************************

********* QUERY *********
SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c
WHERE c.relname = 'cust' AND c.oid = d.adrelid AND d.adnum = 1
*************************

********* QUERY *********
SELECT c2.relname
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'cust' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY c2.relname
*************************

                                       Table "cust"
 Attribute |         Type          |                       Modifier
-----------+-----------------------+------------------------------------------------------
 cust_id   | integer               | not null default nextval('"cust_cust_id_seq"'::text)
 name      | character varying(60) | not null
 login     | character varying(12) | not null
 acct      | character varying(30) | not null
 passwd    | character varying(25) | not null
Indices: cust_cust_id_key,
         cust_pkey


one of those four internal psql-generated wonders gives us what
we wanted--

db=# SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull, a.atthasdef, a.attnum
db=> FROM pg_class c, pg_attribute a
db=> WHERE c.relname = 'cust'
db=>   AND a.attnum > 0 AND a.attrelid = c.oid
db=> ORDER BY a.attnum;

 attname |      format_type      | attnotnull | atthasdef | attnum
---------+-----------------------+------------+-----------+--------
 cust_id | integer               | t          | t         |      1
 name    | character varying(60) | t          | f         |      2
 login   | character varying(12) | t          | f         |      3
 acct    | character varying(30) | t          | f         |      4
 passwd  | character varying(25) | t          | f         |      5
(5 rows)



--
Khan said that revenge is a dish best served cold. I think
sometimes it's best served hot, chunky, and foaming.
    - P.J.Lee ('79-'80)

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!