Re: Are there commands to enquire about table structure?

"Ben" <reply(at)to-the-newsgroup(dot)com> writes:
> Using Doug's pointer, I came up with this for 7.3...

> SELECT
> a.relname,b.attname,c.typname,b.attlen,b.atttypmod
> FROM
> pg_class AS a
> LEFT OUTER JOIN pg_attribute AS b ON (b.attrelid = a.oid)
> LEFT OUTER JOIN pg_type AS c ON (b.atttypid = c.oid)
> WHERE
> c.typname ILIKE '%XX%'
> AND b.attname ILIKE '%YY%'
> AND a.relname ILIKE '%ZZ%'
> AND b.attisdropped=false
> ORDER BY
> a.relname,b.attname

> What I've not figured out yet is how this relates to a particular
> database; if a table and field match in two databases, you'll see them
> both, which (probably) isn't what you'd want.

No, you won't, because each database has its own copy of pg_class et al.
Tables that are in other databases simply won't be in the copy of the
catalogs that you are looking at.

It is true that this query will produce multiple hits if you have
similarly named tables in different schemas of one database. To deal
with that, you probably want to extend the thing to join against
pg_namespace and show the schema name.

regards, tom lane