Re: Query to get column-names in table via PG tables?

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, thought you;d be interested in this. The guys (Tom and Kris) on
the jdbc list suggested I use:
    SELECT 'database.schema.table'::regclass::oid;
to get the table's OID. So I wont need to (less directly) search for
catalog and schema and tablename in information schema.

I'll just be using the pg_ tables passing the OID. It reduces my
learning curve hopefully.

-Ken

Best,
Ken