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

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