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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend