PostgreSQL 9.5RC1 got released recently, and as with PostgreSQL 9.5beta2, the FDW API changed just enough so that the ogr_fdw I compiled for PostgreSQL 9.5beta2 no longer worked for PostgreSQL 9.5RC1. While patching up ogr_fdw to make it work with PostgreSQL 9.5RC1, I took a study of postgres_fdw to see how much effort it would be to implement this new PostgreSQL 9.5 Import Schema functionality for my favorite fdw ogr_fdw. Took me about a day's work, and if I was more experienced, it would have been probably only an hour to graft the logic from postgres_fdw and the ogr_fdw_info that Paul Ramsey had already done, to achieve Import Foreign Schema nirvana. Here's hoping my ogr_fdw patch gets accepted in some shape or form in time for PostgreSQL 9.5 release and in time to package for Windows PostGIS 2.2 Bundle for PostgreSQL 9.5.

I tested my patch on a couple of data sources and for the most part, I think it works pretty well and covers Import All data, LIMIT/EXCEPT, some IMPORT FOREIGN SCHEMA options for name preservation of table and column, and import partial via schema. You can look at the revised readme here. For the remote schema part, I took a bit of liberty in using that as a layer prefix filter instead of a true schema, since few OGR data sources support the concept of schemas, and some even have nested schemas like WFS sources which might have layer names like state.city.gov.tax.Parcels. So I figured using the remote schema to mean table prefix, would be way more useful.

A couple of caveats

• The remote schema ogr_all is a catchall to say don't apply any layer prefix check.
• The import schema implementation I have, launders table and column names to agree with the behavior of the ogr_fdw_info command-line tool. However I did define IMPORT FOREIGN SCHEMA statement options - launder_table_names, launder_column_names to turn this behavior off.
• For LIMIT and EXCEPT clauses are always based on the resulting table names. Since ogr_fdw launders the layer names by default to mash into more kosher table names, this means your LIMIT / EXCEPT may not be what you are expecting.

To demonstrate the coolness of this new feature, we shall demonstrate, with an exercise we did earlier, but instead of using our make shift ogr_fdw_sql_table function, We will perform the same feat with IMPORT FOREIGN SCHEMA.

Import tables from Spreadsheet Workbook

We performed this exercise earlier in Import Foreign Schema hack with OGR_FDW and reading LibreOffice calc workbooks and now we shall do something similar by importing 2 tables IMPORT FOREIGN SCHEMA.

CREATE SERVER svr_budget2015
  FOREIGN DATA WRAPPER ogr_fdw
  OPTIONS (
    datasource 'C:/fdw_data/Budget2015.ods',
    format 'ODS' );

CREATE SCHEMA IF NOT EXISTS budget2015;

IMPORT FOREIGN SCHEMA ogr_all 
    LIMIT TO(dashboard, debt_analysis)
    FROM SERVER svr_budget2015 INTO budget2015;

Preserving case and other unkosher things

If you wanted to preserve the original table names and column names, as mentioned, it's the resulting table names that matter. So this means when using the LIMIT to clause, you would need to use the remote datasource's name and since it has mixed case, it needs to be quoted to be treated correctly.

IMPORT FOREIGN SCHEMA ogr_all 
    LIMIT TO("Dashboard", "Debt Analysis")
    FROM SERVER svr_budget2015 INTO budget2015
    OPTIONS(launder_table_names 'false', launder_column_names 'false');

Bringing tables in by prefix

To use the schema feature, since most data sources don't have schema feature, we use that to mean the start of a layer name. Note that in this case, you have to match the casing etc of the layer, not the final table. So we would do the following to bring in all tables that start with Form.

IMPORT FOREIGN SCHEMA "Form"
    FROM SERVER svr_budget2015 INTO budget2015;

Let's see what we have

To check what tables we have now we do this:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'budget2015'
ORDER BY table_name;

Which outputs:

         table_name
----------------------------
 dashboard
 Dashboard
 Debt Analysis
 debt_analysis
 form_2___equity
 form_3___income
 form_4___lump_sum_planning
 form_5___monthly_cash_flow
 form_7___allocation
 form_8___spending_details
 form_9___work_expenses
(11 rows)