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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
