Re: Query to get column-names in table via PG tables? - Mailing list pgsql-general
From | Ken Johanson |
---|---|
Subject | Re: Query to get column-names in table via PG tables? |
Date | |
Msg-id | 478DB5C9.1040402@kensystem.com Whole thread Raw |
In response to | Re: Query to get column-names in table via PG tables? ("Merlin Moncure" <mmoncure@gmail.com>) |
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, 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
pgsql-general by date: