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