Re: Are there commands to enquire about table structure?

On Sat, 31 Jan 2004 23:41:54 -0500, Doug McNaught wrote:

> For 7.3, the info you need is available in the system catalogs, which
> have a somewhat hairier layout than the SQL-standard information_schema.

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

...you replace XX, YY and ZZ with a substring you want to find in the
field, and/or table, and/or type. The select will return all matching
fields in a reasonble fashion.

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.

Further pointers are welcome, otherwise if I figure it out myself, I'll
post the results.

--Ben