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
To demonstrate the coolness of this new feature, we shall demonstrate, with an exercise we did earlier, but instead of using our make shift
We will perform the same feat with
IMPORT FOREIGN SCHEMA.
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;
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');
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;
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;
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)