From: | "Ben" <reply(at)to-the-newsgroup(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Are there commands to enquire about table structure? |
Date: | 2004-02-02 23:32:42 |
Message-ID: | 5ddfc450be0edc9599b40a709edf577e@news.teranews.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Gaetano Mendola | 2004-02-03 00:31:34 | Before ship 7.4.2 |
Previous Message | Doug McNaught | 2004-02-02 22:45:35 | Re: Are there commands to enquire about table structure? |