On Jan 15, 2008 1:15 AM, Ken Johanson <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly