Re: Proposal: IMPORT FOREIGN SCHEMA statement.

On Fri, Feb 21, 2014 at 4:01 PM, Ronan Dunklau <ronan(dot)dunklau(at)dalibo(dot)com>wrote:

> 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?
>

I havent had a look at the patch yet since I dont have a nice editor right
now, but how do you handle inter operability between datatypes?
Specifically, how do you handle those datatypes which have a different name
from the PostgreSQL name for them and/or are stored in a different manner?

Regards,

Atri