[Proposal] Arbitrary queries in postgres_fdw

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