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

From: Tino Wildenhain <tino(at)wildenhain(dot)de>
To: Ken Johanson <pg-user(at)kensystem(dot)com>
Cc: pgsql-general(at)postgresql(dot)org, Merlin Moncure <mmoncure(at)gmail(dot)com>
Subject: Re: Query to get column-names in table via PG tables?
Date: 2008-01-15 08:47:45
Message-ID: 478C7331.4060400@wildenhain.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Meskes 2008-01-15 09:05:25 Re: ECPG problem with 8.3
Previous Message Kris Jurka 2008-01-15 07:27:46 Re: Patch for Statement.getGeneratedKeys()