IMPORT FOREIGN SCHEMA

Previously, in order to create a foreign table in PostgreSQL, you would need to define the table, referencing the destination columns and data types, and if you have a lot of tables, this can become tedious and error-prone, and when those tables change, you need to do it all over again...

 CREATE FOREIGN TABLE remote.customers ( id int NOT NULL, name text, company text, registered_date date, expiry_date date, active boolean, status text, account_level text) SERVER dest_server OPTIONS (schema_name 'public');  CREATE FOREIGN TABLE remote.purchases ( id int NOT NULL, purchase_time timestamptz, payment_time timestamptz, itemid int, volume int, invoice_sent boolean) SERVER dest_server OPTIONS (schema_name 'public'); 

As of PostgreSQL 9.5, you can import tables en masse:

 IMPORT FOREIGN SCHEMA public FROM SERVER dest_server INTO remote;

This would create foreign tables in the schema named "remote" for every table that appeared in the public schema on the foreign server labelled "dest_server".

You can also filter out any tables you don't wish:

 IMPORT FOREIGN SCHEMA public EXCEPT (reports, audit) FROM SERVER dest_server INTO remote;

Or limit it to just a specific set of tables:

 IMPORT FOREIGN SCHEMA public LIMIT TO (customers, purchases)

Example:

This command provides an automated way to create foreign table definitionsthat match remote tables, thereby reducing tedium and chances for error.In this patch, we provide the necessary core-server infrastructure andimplement the feature fully in the postgres_fdw foreign-data wrapper.Other wrappers will throw a "feature not supported" error until/unlessthey are updated.

I assume you know about Foreign Data Wrappers. If not, quick recap – it's a way to define tables that, upon access, will query in some way some external resource. Be it text file, or another postgres database, or search twitter.

While it is great, it required definition of each table separately. In a lot of cases it makes sense. But sometimes – you want to use fdw to link another database, and you might want to add all tables from this remote database so that it would be visible in your current one.

Of course – this could have been done with shell scripts, but now we have something nicer. It will require some support from FDW drivers (wrappers), and so far, only postgres_fdw understands it, but it's there, so let's see.

In my test Pg, I create 2 databases:

create database source;create database destination;

In source, I'll create some test tables:

$ create table t1 as select generate_series(1,10) as id;$ create table t2 as select generate_series(1,10) + 12 as id;$ create table t3 as select generate_series(1,10) + 45 as id;

Now, in destination database, I load the extension, and configure it:

$ create extension postgres_fdw ;$ create server src foreign data wrapper postgres_fdw options( dbname 'source' );$ create user mapping for depesz server src options ( user 'depesz' );

With this in place, I can create foreign tables:

$ create foreign table table_from_source (id int4) server src options (table_name 't1' );

And it will work:

$ select * from table_from_source ; id---- 1 2 3 4 5 6 7 8 9 10(10 rows)

But now, I can do better.

First, I create some schema, so that foreign tables will not mix with local:

create schema from_source;

And now, I can:

$ import foreign schema public from server src into from_source;

After which:

$ \dE *.*List of relations Schema| Name| Type | Owner -------------+-------------------+---------------+-------- from_source | t1| foreign table | depesz from_source | t2| foreign table | depesz from_source | t3| foreign table | depesz public | table_from_source | foreign table | depesz(4 rows)

All the foreign tables from database source got “copied".

Of course this is configurable – I can limit the tables to be linked to certain list, or even use “except" to get all tables with some exceptions.

Details, as always, in the documentation.

Great stuff. Should greatly simplify creation of foreign tables – even in case of single table – as it does schema discovery, so I wouldn't need to specify all columns any more. Cool.

PostgreSQL 9.5: IMPORT FOREIGN SCHEMA