Skip site navigation (1) Skip section navigation (2)

Re: reading table metadata from pl/pgsql in 8.2 under FreeBSD

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "David Monarchi" <david(dot)e(dot)monarchi(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: reading table metadata from pl/pgsql in 8.2 under FreeBSD
Date: 2007-05-22 23:39:29
Message-ID: 11705.1179877169@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
"David Monarchi" <david(dot)e(dot)monarchi(at)gmail(dot)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

In response to

pgsql-novice by date

Next:From: Derrick BettsDate: 2007-05-25 01:06:19
Subject: Re: Multiple row update with trigger
Previous:From: David MonarchiDate: 2007-05-22 20:20:58
Subject: reading table metadata from pl/pgsql in 8.2 under FreeBSD

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group