On 10th of July, Tom Lane committed patch:

Implement IMPORT FOREIGN SCHEMA.
 
This command provides an automated way to create foreign table definitions
that match remote tables, thereby reducing tedium and chances for error.
In this patch, we provide the necessary core-server infrastructure and
implement the feature fully in the postgres_fdw foreign-data wrapper.
Other wrappers will throw a "feature not supported" error until/unless
they are updated.
 
Ronan Dunklau and Michael Paquier, additional work by me

This is pretty cool.

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.