Merlin Moncure wrote:
On Jan 15, 2008 1:15 AM, Ken Johanson <pg-user@xxxxxxxxxxxxx> 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
I sincerely wish I had enough time to learn the servers internal schema
design, it seems immensely powerful (an understatement). Time (lack of)
lately leaves me always looking for the most direct path though, so
admittedly I was looking for someone else (a designer) to answer it. I
know that in my past attempts to improvise, I seem to miss(interpret) an
important where or on clause, and since I'm writing code for a PG driver
I'm hesitant to roll my own on this one. Tino's idea may do what I need
but if you have the time and expertise to translate that
information_schmea does I'd certainly apprciate that.
Thank you again,
Ken
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
