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

8 files changed, 312 insertions, 5 deletions

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 8ace8bd3a25..3b7fff4846c 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3069,8 +3069,9 @@ ANALYZE measurement;
     For additional information, see
     <xref linkend="sql-createforeigndatawrapper">,
     <xref linkend="sql-createserver">,
-    <xref linkend="sql-createusermapping">, and
-    <xref linkend="sql-createforeigntable">.
+    <xref linkend="sql-createusermapping">,
+    <xref linkend="sql-createforeigntable">, and
+    <xref linkend="sql-importforeignschema">.
    </para>
  </sect1>
 
diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml
index e5b9e661853..3db8ef1a132 100644
--- a/doc/src/sgml/event-trigger.sgml
+++ b/doc/src/sgml/event-trigger.sgml
@@ -604,6 +604,12 @@
         <entry align="center"><literal>X</literal></entry>
        </row>
        <row>
+        <entry align="left"><literal>IMPORT FOREIGN SCHEMA</literal></entry>
+        <entry align="center"><literal>X</literal></entry>
+        <entry align="center"><literal>X</literal></entry>
+        <entry align="center"><literal>-</literal></entry>
+       </row>
+       <row>
         <entry align="left"><literal>SELECT INTO</literal></entry>
         <entry align="center"><literal>X</literal></entry>
         <entry align="center"><literal>X</literal></entry>
diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml
index 6b5c8b7e97b..5fd8d6fbbe9 100644
--- a/doc/src/sgml/fdwhandler.sgml
+++ b/doc/src/sgml/fdwhandler.sgml
@@ -696,6 +696,66 @@ AcquireSampleRowsFunc (Relation relation, int elevel,
 
    </sect2>
 
+   <sect2 id="fdw-callbacks-import">
+    <title>FDW Routines For <command>IMPORT FOREIGN SCHEMA</></title>
+
+    <para>
+<programlisting>
+List *
+ImportForeignSchema (ImportForeignSchemaStmt *stmt, Oid serverOid);
+</programlisting>
+
+     Obtain a list of foreign table creation commands.  This function is
+     called when executing <xref linkend="sql-importforeignschema">, and is
+     passed the parse tree for that statement, as well as the OID of the
+     foreign server to use.  It should return a list of C strings, each of
+     which must contain a <xref linkend="sql-createforeigntable"> command.
+     These strings will be parsed and executed by the core server.
+    </para>
+
+    <para>
+     Within the <structname>ImportForeignSchemaStmt</> struct,
+     <structfield>remote_schema</> is the name of the remote schema from
+     which tables are to be imported.
+     <structfield>list_type</> identifies how to filter table names:
+     <literal>FDW_IMPORT_SCHEMA_ALL</> means that all tables in the remote
+     schema should be imported (in this case <structfield>table_list</> is
+     empty), <literal>FDW_IMPORT_SCHEMA_LIMIT_TO</> means to include only
+     tables listed in <structfield>table_list</>,
+     and <literal>FDW_IMPORT_SCHEMA_EXCEPT</> means to exclude the tables
+     listed in <structfield>table_list</>.
+     <structfield>options</> is a list of options used for the import process.
+     The meanings of the options are up to the FDW.
+     For example, an FDW could use an option to define whether the
+     <literal>NOT NULL</> attributes of columns should be imported.
+     These options need not have anything to do with those supported by the
+     FDW as database object options.
+    </para>
+
+    <para>
+     The FDW may ignore the <structfield>local_schema</> field of
+     the <structname>ImportForeignSchemaStmt</>, because the core server
+     will automatically insert that name into the parsed <command>CREATE
+     FOREIGN TABLE</> commands.
+    </para>
+
+    <para>
+     The FDW does not have to concern itself with implementing the filtering
+     specified by <structfield>list_type</> and <structfield>table_list</>,
+     either, as the core server will automatically skip any returned commands
+     for tables excluded according to those options.  However, it's often
+     useful to avoid the work of creating commands for excluded tables in the
+     first place.  The function <function>IsImportableForeignTable()</> may be
+     useful to test whether a given foreign-table name will pass the filter.
+    </para>
+
+    <para>
+     If the FDW does not support importing table definitions, the
+     <function>ImportForeignSchema</> pointer can be set to <literal>NULL</>.
+    </para>
+
+   </sect2>
+
    </sect1>
 
    <sect1 id="fdw-helpers">
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index e6f6e205815..43adb61455d 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -49,7 +49,8 @@
    </listitem>
    <listitem>
     <para>
-     Create a foreign table, using <xref linkend="sql-createforeigntable">,
+     Create a foreign table, using <xref linkend="sql-createforeigntable">
+     or <xref linkend="sql-importforeignschema">,
      for each remote table you want to access.  The columns of the foreign
      table must match the referenced remote table.  You can, however, use
      table and/or column names different from the remote table's, if you
@@ -99,7 +100,7 @@
      <listitem>
       <para>
        <literal>user</literal> and <literal>password</literal> (specify these
-       for a user mapping, instead)
+       in a user mapping, instead)
       </para>
      </listitem>
      <listitem>
@@ -291,6 +292,72 @@
 
    </variablelist>
   </sect3>
+
+  <sect3>
+   <title>Importing Options</title>
+
+   <para>
+    <filename>postgres_fdw</> is able to import foreign table definitions
+    using <xref linkend="sql-importforeignschema">.  This command creates
+    foreign table definitions on the local server that match tables or
+    views present on the remote server.  If the remote tables to be imported
+    have columns of user-defined data types, the local server must have types
+    of the same names.
+   </para>
+
+   <para>
+    Importing behavior can be customized with the following options
+    (given in the <command>IMPORT FOREIGN SCHEMA</> command):
+   </para>
+
+   <variablelist>
+    <varlistentry>
+     <term><literal>import_collate</literal></term>
+     <listitem>
+      <para>
+       This option controls whether column <literal>COLLATE</> options
+       are included in the definitions of foreign tables imported
+       from a foreign server. The default is <literal>true</>.  You might
+       need to turn this off if the remote server has a different set of
+       collation names than the local server does, which is likely to be the
+       case if it's running on a different operating system.
+      </para>
+     </listitem>
+    </varlistentry>
+    <varlistentry>
+     <term><literal>import_default</literal></term>
+     <listitem>
+      <para>
+       This option controls whether column <literal>DEFAULT</> expressions
+       are included in the definitions of foreign tables imported
+       from a foreign server. The default is <literal>false</>.  If you
+       enable this option, be wary of defaults that might get computed
+       differently on the local server than they would be on the remote
+       server; <function>nextval()</> is a common source of problems.
+       The <command>IMPORT</> will fail altogether if an imported default
+       expression uses a function or operator that does not exist locally.
+      </para>
+     </listitem>
+    </varlistentry>
+    <varlistentry>
+     <term><literal>import_not_null</literal></term>
+     <listitem>
+      <para>
+       This option controls whether column <literal>NOT NULL</>
+       constraints are included in the definitions of foreign tables imported
+       from a foreign server. The default is <literal>true</>.
+      </para>
+     </listitem>
+    </varlistentry>
+   </variablelist>
+
+   <para>
+    Note that constraints other than <literal>NOT NULL</> will never be
+    imported from the remote tables, since <productname>PostgreSQL</>
+    does not support any other type of constraint on a foreign table.
+    Checking other types of constraints is always left to the remote server.
+   </para>
+  </sect3>
  </sect2>
 
  <sect2>
@@ -422,7 +489,7 @@ CREATE USER MAPPING FOR local_user
 
 <programlisting>
 CREATE FOREIGN TABLE foreign_table (
-        id serial NOT NULL,
+        id integer NOT NULL,
         data text
 )
         SERVER foreign_server
@@ -434,6 +501,8 @@ CREATE FOREIGN TABLE foreign_table (
    Column names must match as well, unless you attach <literal>column_name</>
    options to the individual columns to show how they are named in the remote
    table.
+   In many cases, use of <xref linkend="sql-importforeignschema"> is
+   preferable to constructing foreign table definitions manually.
   </para>
  </sect2>
 
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index 1b0962c253d..b685e16a0fa 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -131,6 +131,7 @@ Complete list of usable sgml source files in this directory.
 <!ENTITY explain            SYSTEM "explain.sgml">
 <!ENTITY fetch              SYSTEM "fetch.sgml">
 <!ENTITY grant              SYSTEM "grant.sgml">
+<!ENTITY importForeignSchema SYSTEM "import_foreign_schema.sgml">
 <!ENTITY insert             SYSTEM "insert.sgml">
 <!ENTITY listen             SYSTEM "listen.sgml">
 <!ENTITY load               SYSTEM "load.sgml">
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 4a8cf3889c2..46a20eff14f 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -231,6 +231,7 @@ SERVER film_server;
    <member><xref linkend="sql-dropforeigntable"></member>
    <member><xref linkend="sql-createtable"></member>
    <member><xref linkend="sql-createserver"></member>
+   <member><xref linkend="sql-importforeignschema"></member>
   </simplelist>
  </refsect1>
 </refentry>
diff --git a/doc/src/sgml/ref/import_foreign_schema.sgml b/doc/src/sgml/ref/import_foreign_schema.sgml
new file mode 100644
index 00000000000..bdcc26558ac
--- /dev/null
+++ b/doc/src/sgml/ref/import_foreign_schema.sgml
@@ -0,0 +1,168 @@
+<!--
+doc/src/sgml/ref/import_foreign_schema.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="SQL-IMPORTFOREIGNSCHEMA">
+ <indexterm zone="sql-importforeignschema">
+  <primary>IMPORT FOREIGN SCHEMA</primary>
+ </indexterm>
+
+ <refmeta>
+  <refentrytitle>IMPORT FOREIGN SCHEMA</refentrytitle>
+  <manvolnum>7</manvolnum>
+  <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+  <refname>IMPORT FOREIGN SCHEMA</refname>
+  <refpurpose>import table definitions from a foreign server</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+IMPORT FOREIGN SCHEMA <replaceable class="PARAMETER">remote_schema</replaceable>
+[ { LIMIT TO | EXCEPT } ( <replaceable class="PARAMETER">table_name</replaceable> [, ...] ) ]
+FROM SERVER <replaceable class="PARAMETER">server_name</replaceable>
+INTO <replaceable class="PARAMETER">local_schema</replaceable>
+[ OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>' [, ... ] ) ]
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1 id="SQL-IMPORTFOREIGNSCHEMA-description">
+  <title>Description</title>
+
+  <para>
+   <command>IMPORT FOREIGN SCHEMA</command> creates foreign tables that
+   represent tables existing on a foreign server.  The new foreign tables
+   will be owned by the user issuing the command and are created with
+   the correct column definitions and options to match the remote tables.
+  </para>
+
+  <para>
+   By default, all tables and views existing in a particular schema on the
+   foreign server are imported.  Optionally, the list of tables can be limited
+   to a specified subset, or specific tables can be excluded.  The new foreign
+   tables are all created in the target schema, which must already exist.
+  </para>
+
+  <para>
+   To use <command>IMPORT FOREIGN SCHEMA</command>, the user must have
+   <literal>USAGE</literal> privilege on the foreign server, as well as
+   <literal>CREATE</literal> privilege on the target schema.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Parameters</title>
+
+  <variablelist>
+
+   <varlistentry>
+    <term><replaceable class="PARAMETER">remote_schema</replaceable></term>
+    <listitem>
+     <para>
+      The remote schema to import from. The specific meaning of a remote schema
+      depends on the foreign data wrapper in use.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><literal>LIMIT TO ( <replaceable class="PARAMETER">table_name</replaceable> [, ...] )</literal></term>
+    <listitem>
+     <para>
+      Import only foreign tables matching one of the given table names.
+      Other tables existing in the foreign schema will be ignored.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><literal>EXCEPT ( <replaceable class="PARAMETER">table_name</replaceable> [, ...] )</literal></term>
+    <listitem>
+     <para>
+      Exclude specified foreign tables from the import.  All tables
+      existing in the foreign schema will be imported except the
+      ones listed here.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="PARAMETER">server_name</replaceable></term>
+    <listitem>
+     <para>
+      The foreign server to import from.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="PARAMETER">local_schema</replaceable></term>
+    <listitem>
+     <para>
+      The schema in which the imported foreign tables will be created.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><literal>OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>' [, ...] )</literal></term>
+    <listitem>
+     <para>
+      Options to be used during the import.
+      The allowed option names and values are specific to each foreign
+      data wrapper.
+     </para>
+    </listitem>
+   </varlistentry>
+  </variablelist>
+ </refsect1>
+
+ <refsect1 id="SQL-IMPORTFOREIGNSCHEMA-examples">
+  <title>Examples</title>
+
+  <para>
+   Import table definitions from a remote schema <structname>foreign_films</>
+   on server <structname>film_server</>, creating the foreign tables in
+   local schema <structname>films</>:
+
+<programlisting>
+IMPORT FOREIGN SCHEMA foreign_films
+    FROM SERVER film_server INTO films;
+</programlisting>
+   </para>
+
+  <para>
+   As above, but import only the two tables <structname>actors</> and
+   <literal>directors</> (if they exist):
+
+<programlisting>
+IMPORT FOREIGN SCHEMA foreign_films LIMIT TO (actors, directors)
+    FROM SERVER film_server INTO films;
+</programlisting>
+   </para>
+
+ </refsect1>
+
+ <refsect1 id="SQL-IMPORTFOREIGNSCHEMA-compatibility">
+  <title>Compatibility</title>
+
+  <para>
+   The <command>IMPORT FOREIGN SCHEMA</command> command conforms to the
+   <acronym>SQL</acronym> standard, except that the <literal>OPTIONS</>
+   clause is a <productname>PostgreSQL</> extension.
+  </para>
+
+ </refsect1>
+
+ <refsect1>
+  <title>See Also</title>
+
+  <simplelist type="inline">
+   <member><xref linkend="sql-createforeigntable"></member>
+   <member><xref linkend="sql-createserver"></member>
+  </simplelist>
+ </refsect1>
+</refentry>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index a6575f52ac0..6ec126381c3 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -159,6 +159,7 @@
    &explain;
    &fetch;
    &grant;
+   &importForeignSchema;
    &insert;
    &listen;
    &load;