On Jan 15, 2008 1:15 AM, Ken Johanson <pg-user@kensystem.com> wrote:
> The output of this is very verbose and broken into multiple queries
> making joins difficult for me to understand, I'm afraid; my current
> experience level likely will not reliably produce a single-query
> equivalent to the above.
>
> I have to again ask for designer expertise on this one. Also a factor is
> that since the query will be hard coded into a driver, knowledge of how
> to make it most durable across server versions would be a benefit
> (assuming the underlying tables change?).
One gotcha that I should have mentioned with querying system catalogs
is that they may change from version to version. That said, the query
you need should be fairly portable with small changes (I'm using 8.3
atm).
I think you have given up a little to easily. The system catalogs are
fully documented in the docs btw. Let's look at what psql outputs for
a typical table with \d:
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(queue)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
-- this query looks up the oid of the table you are asking for. you
probably are not interested in this.
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules,
relhasoids , reltablespace
FROM pg_catalog.pg_class WHERE oid = '155955'
-- psql checks for table properties of the table (the oid in this case
is 155955). you may not need this, in any event it should be clear
what it is doing.
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '155955' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
-- this is the 'column query'. it lists values from pg_attribute for
the table in column position order. note the table oid again
(155955). you can drop your own table oid here and get the exact
results psql gets.
Following are more queries that get information for indexes, rules
inheritance, etc. Unless you specifically are interested in those
things, you can ignore them.
It's not as hard as you think....the naming can trip you up as well as
the use of the hidden 'oid' column if you are not familiar with its
usage.
merlin