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
> > 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
>
