Re: IMPORT FOREIGN SCHEMA statement

Stephen Frost wrote:
> * 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".

That's not the same thing though -- consider what happens to the quoting
needs for names with mixed case. If you change mixed case to
all-lowercase, references to such objects using quotes in the
application code would fail because the name is now all-lowercase in
Postgres. A tri-valued enum could do the trick:
lowercase_names={wholly_uppercase_only, all, none}
with the first one being the most sensible and default.

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

Yes, it seems better to offer the ability to create transactional
scripts around IMPORT (so it must be able to run in a transaction block
-- IMPORT first, then do a bunch of ALTERs), than complicating IMPORT
itself infinitely to support hundreds of possible options.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services