Proposal: IMPORT FOREIGN SCHEMA statement.

Hello,

The SQL-MED specification defines the IMPORT FOREIGN SCHEMA statement.

This adds discoverability to foreign servers. The structure of the statement
as I understand it is simple enough:

IMPORT FOREIGN SCHEMA remote_schema FROM SERVER some_server [ (LIMIT TO | 
EXCEPT) table_list ] INTO local_schema.

Is anyone working on this? I found a reference to this from 2010 in the 
archive, stating that work should be focused on core functionality, but 
nothing more recent.

This would be very useful for postgres_fdw and other RDBMS-backed fdws, but I 
think even file_fdw could benefit from it if it was able to create a foreign 
table for every csv-with-header file in a directory.

I can see a simple API working for that.  A new function would be added to the 
fdw routine, which is responsible for crafting CreateForeignTableStmt. It 
could have the following signature:

typedef List *(*ImportForeignSchema_function) (ForeignServer *server, 
ImportForeignSchemaStmt * parsetree);


I experimented with this idea, and came up with the attached two patches: one 
for the core, and the other for actually implementing the API in postgres_fdw. 

Maybe those can serve as a proof-of-concept for discussing the design?


-- 
Ronan Dunklau
http://dalibo.com - http://dalibo.org