Re: IMPORT FOREIGN SCHEMA statement

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> writes:
> > Oracle follows the SQL standard in folding table names to upper case.
> > So this would normally result in a lot of PostgreSQL foreign tables
> > with upper case names, which would be odd and unpleasant.
>
> > I cannot see a way out of that, but I thought I should mention it.
>
> It seems like it would often be desirable for the Oracle FDW to smash
> all-upper-case names to all-lower-case while importing, so that no quoting
> is needed on either side. I doubt though that this is so desirable that
> it should happen unconditionally.

The oracle FDW would simply need a foreign-server level option which
says "smash everything to lowercase on import".

> 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.)

I can see value in having specific mappings defined at the foreign
server level for what IMPORT does by default, but as IMPORT is intended
to be a bulk process, I don't see the value in trying to teach it how to
do a specific mapping for a specific table or column inside the schema.
You run the IMPORT for the entire schema and then go fix up any special
cases or things you wanted differently.

That said, I'm not against having a way to pass to IMPORT a similar
key/value pair set which we already support for the FDW options on
tables, columns, etc, by way of safe-guarding any potential use case for
such.

Thanks,

Stephen