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

>> 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