Re: Getting table metadata

On Wed, Jan 19, 2005 at 12:14:57AM -0500, Ken Tozier wrote:

> I recently stumbled upon the system catalog functions here
> "http://www.postgresql.org/docs/7.4/static/catalogs.html" and see that
> it's easy to get a list of all databases and relatively easy to get a
> list of tables, but there doesn't seem to be any built in method for
> retrieving a table definition.

See also "The Information Schema" if you're using 7.4 or later.

> The best I could come up with would be to do a select something
> like this:
>
> SELECT * FROM pg_attribute WHERE attrelid=<the table oid>;
>
> The problem I'm running into however, is that given a table name, there
> doesn't seem to be any way to get the table oid.

See "Object Identifier Types" in the "Data Types" chapter.

SELECT * FROM pg_attribute WHERE attrelid = 'tablename'::regclass;

> Is there some function or query that does this? Better yet, is there
> an easier way to get at this metadata?

If you run "psql -E" you can see the queries that psql makes when
you issue commands like "\d tablename". As you can see, it takes
a lot of information from the system catalogs to generate a description
of a table. The Information Schema abstracts these queries through
views, so querying them might be the easiest way if they provide
what you need. See in particular information_schema.columns.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/