Re: IMPORT FOREIGN SCHEMA return create foreign table commands are those further filtered in LIMIT and EXCEPT cases? - Mailing list pgsql-hackers

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
Sent: Thursday, December 31, 2015 3:36 PM
To: Paragon Corporation <lr@pcorp.us>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] IMPORT FOREIGN SCHEMA return create foreign table
commands are those further filtered in LIMIT and EXCEPT cases?

"Paragon Corporation" <lr@pcorp.us> writes:
>> I've implemented IMPORT FOREIGN SCHEMA support for an fdw called 
>>  ogr_fdw against the PostgreSQL 9.5RC1 code base.
>> Code is here:  https://github.com/robe2/pgsql-ogr-fdw
>> It works great except in the Case of LIMIT TO  clause  (and possible 
>> EXCEPT though I have to retest EXCEPT to see if there is a case it
doesn't work).
>> In LIMIT case sometimes works and it sometimes doesn't and when it 
>> doesn't no foreign tables are created.

> Case-folding issue, perhaps?  Are you taking care to double-quote the
table names in the generated CREATE FOREIGN TABLE statements?  Because if
you don't, they'll be smashed to lower case and then won't match the quoted
table names in your > example.

>            regards, tom lane


Tom,

Yes using quote identifier etc. 

I just rewrote my logic to go by the resulting table name instead of the
remote data source name and that seems to do the trick.

So I now write:

IMPORT FOREIGN SCHEMA ogr_all    LIMIT TO(orders, products)   FROM SERVER northwind INTO northwind;

But with the laundering options off, I would do this:

IMPORT FOREIGN SCHEMA ogr_all    LIMIT TO("Orders", "Products")   FROM SERVER northwind INTO northwind
OPTIONS(launder_table_names
'false', launder_column_names 'false') ;

So I am guessing you guys are filtering the list so that it works for all
FDWs even if the FDW author doesn't directly handle LIMIT / EXCEPT clause?
But not sure where that is being done.

So instead of doing this check in my code (which is what I was doing)

Relname = layer_name    (where in my case layer_name is the remote
datasource which can be pretty crazy for spatial data like web service
layers )

I'm doing this

Relname = table_name 

In the case where laundering is on which is the default  table_name !=
layer_name.  In case where user chooses to preserve layer name then
table_name == layer_name

Thanks,
Regina