Обсуждение: Query to get column-names in table via PG tables?

Hi all,

I am looking for expertise on how to program the equivalent to this
query, but using the pg_catalog tables, which I understand have fewer
security restrictions than information_schema in some cases:

SELECT column_name
FROM information_schema.columns
WHERE table_catalog=? AND table_schema=? AND table_name=?
ORDER BY ordinal_position

I need this to lookup the column names and their ordinal position for a
given table (implementing a driver call).

Thanks in advance,
Ken

Ken Johanson wrote:
> Hi all,
>
> I am looking for expertise on how to program the equivalent to this
> query, but using the pg_catalog tables, which I understand have fewer
> security restrictions than information_schema in some cases:
>
> SELECT column_name
> FROM information_schema.columns
> WHERE table_catalog=? AND table_schema=? AND table_name=?
> ORDER BY ordinal_position
>
> I need this to lookup the column names and their ordinal position for a
> given table (implementing a driver call).

Just curious... but why is ordinal position important here?

Joshua D. Drake

>
> Thanks in advance,
> Ken
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

>> I am looking for expertise on how to program the equivalent to this
>> query, but using the pg_catalog tables, which I understand have fewer
>> security restrictions than information_schema in some cases:
>>
>> SELECT column_name
>> FROM information_schema.columns
>> WHERE table_catalog=? AND table_schema=? AND table_name=?
>> ORDER BY ordinal_position
>>
>> I need this to lookup the column names and their ordinal position for
>> a given table (implementing a driver call).
>
> Just curious... but why is ordinal position important here?
>
>


Because the API spec (JDBC) for the driver supports an argument of
column indexes (int array) which are the table's natural position. This
is to specify which columns' auto-generated keys to return.

http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Statement.html#executeUpdate(java.lang.String,%20int[])

So in this case I must pre-fetch the column names from the indexes, and
append a RETURNING clause. Inefficient but the only strategy I know of.

I wont argue if this API is somewhat dubious in ordinary applications,
but the interface requires it be implemented anyway.

Ken

On Jan 10, 2008 1:37 AM, Ken Johanson <pg-user@kensystem.com> wrote:
> Hi all,
>
> I am looking for expertise on how to program the equivalent to this
> query, but using the pg_catalog tables, which I understand have fewer
> security restrictions than information_schema in some cases:
>
> SELECT column_name
> FROM information_schema.columns
> WHERE table_catalog=? AND table_schema=? AND table_name=?
> ORDER BY ordinal_position

Do what psql does...launch it with psql -E, and it will echo any
internal queries it makes back to you.  Do \d on a couple of tables
and you should see what is going on.

merlin

>> I am looking for expertise on how to program the equivalent to this
>> query, but using the pg_catalog tables, which I understand have fewer
>> security restrictions than information_schema in some cases:
>>
>> SELECT column_name
>> FROM information_schema.columns
>> WHERE table_catalog=? AND table_schema=? AND table_name=?
>> ORDER BY ordinal_position
>
> Do what psql does...launch it with psql -E, and it will echo any
> internal queries it makes back to you.  Do \d on a couple of tables
> and you should see what is going on.
>

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?).

Thank you,
Ken

Ken Johanson wrote:
>>> I am looking for expertise on how to program the equivalent to this
>>> query, but using the pg_catalog tables, which I understand have fewer
>>> security restrictions than information_schema in some cases:
>>>
>>> SELECT column_name
>>> FROM information_schema.columns
>>> WHERE table_catalog=? AND table_schema=? AND table_name=?
>>> ORDER BY ordinal_position
>>
>> Do what psql does...launch it with psql -E, and it will echo any
>> internal queries it makes back to you.  Do \d on a couple of tables
>> and you should see what is going on.
>>
>
> 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?).

Ah driver you say? For which language? Will it be coded in C?
If you want to do it most reliable and do not want to code
for every PG version and also do not want to use information_schema
(why btw?) you can also resort to just

SELECT * FROM schema.table WHERE false;

and then inspect the cursor for column names and datatypes.

Regards
Tino

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

Tino Wildenhain wrote:
> Ken Johanson wrote:
>>>> I am looking for expertise on how to program the equivalent to this
>>>> query, but using the pg_catalog tables, which I understand have fewer
>>>> security restrictions than information_schema in some cases:
>>>>
>>>> SELECT column_name
>>>> FROM information_schema.columns
>>>> WHERE table_catalog=? AND table_schema=? AND table_name=?
>>>> ORDER BY ordinal_position
>>>
trim
>>
>> 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?).
>
> Ah driver you say? For which language? Will it be coded in C?

This case happens to be the JDBC driver; it's:
Statement.executeUpdate(sql, int[] columnINdexes) method..

> If you want to do it most reliable and do not want to code
> for every PG version and also do not want to use information_schema
> (why btw?) you can also resort to just
>
> SELECT * FROM schema.table WHERE false;
>
> and then inspect the cursor for column names and datatypes.

I think you may be right. This is the obvious and elegantly-simple
answer (ashamed that I missed it). It does all I need at this point: to
get the column names for the given indexes. Unfort though I suspect it
is one extra trip to the server but I was resigned to that anyway
(though hoped for a way to do via RETURNING).

I was avoiding using information_schema for being told it wil have
security restrictions that pg_* wont; but the 'SELECT * FROM .. WHERE
false' method overcomes those also (of course :-).

Thank you Tino,
Ken

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

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