Clarification question

"Tim Barnard" <tbarnard@povn.com> writes:

Is the following select sufficient and correct for extracting the column
names of a table, excluding all system columns?

select attname from pg_attribute
where attrelid=
(select relfilenode from pg_class
where relname like <insert table name here>)
and attnum > 0

relfilenode is definitely the wrong thing; use pg_class.oid instead.
(Presently they are usually if not always equal, but the reason we
put in a relfilenode column is that we intend to make them different
someday soon.) Also I'd use a plain "=" not "like", if I know I am
looking for just one table. So

select attname from pg_attribute
where attrelid=
(select oid from pg_class
where relname = '<insert table name here>')
and attnum > 0

I want to be certain that no system columns are returned, only columns I've
created.

attnum > 0 is the right way to handle that.

BTW, it is likely that in 7.3 relname will not be a unique key for
pg_class anymore; you'll be needing to check which schema the table
is in, too. There's not much you can do about this now. Just be
aware that the system catalogs do tend to change over time.

regards, tom lane

Thanks Tom.

Tim

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Tim Barnard" <tbarnard@povn.com>
Cc: <pgsql-general@postgresql.org>
Sent: Saturday, January 19, 2002 3:22 PM
Subject: Re: [GENERAL] Clarification question

"Tim Barnard" <tbarnard@povn.com> writes:

Is the following select sufficient and correct for extracting the column
names of a table, excluding all system columns?

select attname from pg_attribute
where attrelid=
(select relfilenode from pg_class
where relname like <insert table name here>)
and attnum > 0

relfilenode is definitely the wrong thing; use pg_class.oid instead.
(Presently they are usually if not always equal, but the reason we
put in a relfilenode column is that we intend to make them different
someday soon.) Also I'd use a plain "=" not "like", if I know I am
looking for just one table. So

select attname from pg_attribute
where attrelid=
(select oid from pg_class
where relname = '<insert table name here>')
and attnum > 0

I want to be certain that no system columns are returned, only columns

I've

Thanks for the suggestion.

Tim Barnard
S.E. Mgr
Hartford Communications Corporation

----- Original Message -----
From: "Tommi M�kitalo" <t.maekitalo@epgmbh.de>
To: "Tim Barnard" <tbarnard@povn.com>
Sent: Sunday, January 20, 2002 10:53 AM
Subject: Re: [GENERAL] Clarification question

Just a note... In order to get your suggestion to work I had to alter it
slightly:

select attname
from pf_attribute join pg_class p1 on attrelid=p1.oid
where relname='<insert table name here>'
and attnum > 0

Without the alias I get a "Column reference oid is ambiguous" error.

Tim Barnard
S.E. Mgr
Hartford Communications Corporation

----- Original Message -----
From: "Tommi M�kitalo" <t.maekitalo@epgmbh.de>
To: "Tim Barnard" <tbarnard@povn.com>
Sent: Sunday, January 20, 2002 10:53 AM
Subject: Re: [GENERAL] Clarification question

Make that...

select attname
from pg_attribute join pg_class p1 on attrelid=p1.oid
where relname='<insert table name here>'
and attnum > 0

Sorry for the fat-fingering :-)

Tim Barnard
S.E. Mgr
Hartford Communications Corporation

----- Original Message -----
From: "Tommi M�kitalo" <t.maekitalo@epgmbh.de>
To: "Tim Barnard" <tbarnard@povn.com>
Sent: Sunday, January 20, 2002 10:53 AM
Subject: Re: [GENERAL] Clarification question