From: | Ken Johanson <pg-user(at)kensystem(dot)com> |
---|---|
To: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Query to get column-names in table via PG tables? |
Date: | 2008-01-16 07:44:09 |
Message-ID: | 478DB5C9.1040402@kensystem.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Merlin Moncure wrote:
> On Jan 15, 2008 1:15 AM, Ken Johanson <pg-user(at)kensystem(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Ken Johanson | 2008-01-16 08:12:13 | Re: Patch for Statement.getGeneratedKeys() |
Previous Message | Tom Lane | 2008-01-16 07:38:37 | Re: Patch for Statement.getGeneratedKeys() |