Re: Query to get column-names in table via PG tables? - Mailing list pgsql-general

Merlin Moncure wrote:
> 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
>

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