Re: Question about schema

dbmss(at)yahoo(dot)com writes:
> Can I use SQL to get info about a table
> which can be gotten by doing a
> \d [table name]
> under psql?

psql uses plain old SQL queries to extract the info it presents with \d;
it's not doing anything you couldn't do too. Read the source code for
psql (src/bin/psql/psql.c) to see what queries are used to produce the
various sorts of \d reports. For example, the particular case of
"\d table" is handled in the routine named tableDesc, and from looking
at that you can see that it constructs a primary query like this:

SELECT a.attnum, a.attname, t.typname, a.attlen,
a.atttypmod, a.attnotnull, a.atthasdef
FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relname = '<table name here>'
and a.attnum > 0
and a.attrelid = c.oid
and a.atttypid = t.oid
ORDER BY attnum

Looks like there are additional queries made to get the values of field
defaults and so forth.

All this stuff is kept in the system tables, and you can query those
tables with ordinary queries.

regards, tom lane