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.

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!