Re: IMPORT FOREIGN SCHEMA statement

>> Additionally, I don't really see how that would be useful in a general
case.
>> With an "in-core" defined meaning of type transformation, any FDW that
doesn't
>> fit exactly into the model would have a hard time. For example, what
happens
>> if an FDW is only ever capable of returning text ?

> That's actually the case where it's most important to have the feature
> all the way down to the column level.

I'm not sure configuration about specific columns from specific tables would
be that useful: if you already know the structure of the table, you can either
create it yourself, or run an alter column statement just after creating it.

Alternativeley, with the arbitrary options clause proposed by Tom and detailed
below, one could use the LIMIT TO / EXCEPT clauses to fine-tune what options
should apply.

> That's why I suggested doing it via CREATE/ALTER with JSONB or similar
> containing the details rather than inventing new SQL grammar, an
> option I included only to dismiss it.

Hum, adding a simple TYPE MAPPING is already inventing new SQL grammar, but
its less invasive.

> Between this and the type-mapping questions, it seems likely that
> we're going to need a way for IMPORT FOREIGN SCHEMA to accept
> user-supplied control options, which would in general be specific
> to the FDW being used. (Another thing the SQL committee failed to
> think about.)

So, without extending the syntax too much, we could add options:

IMPORT FOREIGN SCHEMA remote_schema FROM SERVER server_name INTO local_schema
[ { LIMIT TO | EXCEPT } (table_name [, ...])]
[ OPTIONS (option_list) ]

This option list could then contain fdw-specific options, including type
mapping.

Or we could add a specific clause:

IMPORT FOREIGN SCHEMA remote_schema FROM SERVER server_name INTO local_schema
[ { LIMIT TO | EXCEPT } (table_name [, ...])]
[ OPTIONS (option_list) ]
[ TYPE MAPPING some mapping_definition ]

With mapping_definition being either a tuple, or well-defined jsonb format
common accross FDWs.

A third solution, which I don't like but doesn't modify the SQL grammar, would
be to encode the options in the remote_schema name.

Would one of those solutions be acceptable ?

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