Обсуждение: reading table metadata from pl/pgsql in 8.2 under FreeBSD

Hi -

My apologies in advance if this is in the archives/documentation.  I may not have used the correct search terms in trying to find the answer.

I've looked at the system tables, particularly pg_tables, pg_class, and pg_attribute.  In oder to access the attributes for a particular table using the pg_attribute table, I need the oid for the table, specifically the pg_class.oid. 

Looking at some tables, I believe that relfilenode is the the OID of the table.  The corresponding field (i.e., foreign key) in pg_attribute is attrelid.  So far so good.  My problem is that in the pg_class table I don't know how to determine which entries are tables.  And in the pg_attribute table I don't know how to determine which of the corresponding rows are attributes.  (My current rule of thumb to identify an attribute is that in pg_attribute, the defined columns have attnum > 0; all others have attnum <0. )

Thanks for any advice/suggestions.

david

"David Monarchi" <david.e.monarchi@gmail.com> writes:
> Looking at some tables, I believe that relfilenode is the the OID of the
> table.

No, OID is the OID of the table.

The clue I think you are missing is that OID is a "system" column, which
basically means that "SELECT *" doesn't show it.  But if you do "SELECT
oid,other-fields FROM pg_class" you'll see it.

relfilenode is coincidentally equal to OID in the initial state of a
pg_class entry, but it doesn't necessarily stay that way.  If you use
one where you should've used the other, your code will break eventually.

> My problem is that in the pg_class table I don't
> know how to determine which entries are tables.

See relkind.

> And in the pg_attribute
> table I don't know how to determine which of the corresponding rows are
> attributes.

All of them.  Depending on what you are doing, you might wish to ignore
system columns (attnum < 0) and/or dropped columns (attisdropped = true).

            regards, tom lane