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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
