Based on getting a query result field's table Oid and column number from PQftable() and PQftablecol(), what's the most efficient path to getting the schema name, table name, and column name for each field? (This is in the context of a tool running an arbitrary user query.)
For example:
SELECT 1, table1.foo AS colAlias, table2.bar FROM table1, table2
... will have different origins for each column, and I'm looking for
[Field 0]: no schema or table [Field 1]: schema, table1, foo [Field 2]: schema, table2, bar
One strategy would be to fetch, for each unique table oid in the result set, the result of the following query:
SELECT pg_namespace.nspname AS schema_name, pg_class.relname AS table_name, pg_attribute.attname AS column_name
FROM
pg_attribute,
pg_namespace JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
WHERE
pg_attribute.attrelid = %u
AND pg_attribute.attnum > 0
AND NOT pg_attribute.attisdropped
AND tbl.oid = pg_attribute.attrelid
ORDER BY pg_attribute.attnum
This just gets a list of all columns in the table, as [schema_name, table_name, column_name]. Then as I loop through each field in the original user-query, I can simply look in these column lists, and find the [schema_name, table_name, column_name] for that particular field.
Yes, it may be "inefficient" in terms of transmission of duplicate bytes for the schema and table names, and yes it may be inefficient in terms of fetching more columns than is necessarily used in the result, but it's likely on average quicker to send more bytes than it is to make multiple queries per field in the result set.
I don't see any magical API functions to optionally get the server to just send this info back when the query is executed, so is there a clearly more efficient route than (roughly) what I'm doing above?
