Dear all,
We stumbled upon a few cases in which retrieving information from the
foreign server may turn pretty useful before creating any foreign
table, especially info related to the catalog. E.g: a list of schemas
or tables the user has access to.
I thought of using dblink for it, but that requires duplication of
server and user mapping details and it adds its own management of
connections.
Then I thought a better approach may be a mix of both: a function to
issue arbitrary queries to the foreign server reusing all the details
encapsulated in the server and user mapping. It would use the same
pool of connections.
E.g:
CREATE FUNCTION postgres_fdw_query(server name, sql text)
RETURNS SETOF record
SELECT * FROM postgres_fdw_query('foreign_server', $$SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name$$
) AS schemas(table_name text, table_type text);
Find attached a patch with a working PoC (with some code from
dblink). It is not meant to be perfect yet.
Is this something you may be interested in having as part of
postgres_fdw? Thoughts?
Thanks
-Rafa de la Torre