Re: Clarification question

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
>