Add support for LIKE in CREATE FOREIGN TABLE

LIKE enables the creation of foreign tables based on the column
definitions, constraints and objects of the defined source relation(s).

This feature mirrors the behavior of CREATE TABLE LIKE, but ignores
the INCLUDING sub-options that do not make sense for foreign tables:
INDEXES, COMPRESSION, IDENTITY and STORAGE.  The supported sub-options
are COMMENTS, CONSTRAINTS, DEFAULTS, GENERATED and STATISTICS, mapping
with the clauses already supported by the command.

Note that the restriction with LIKE in CREATE FOREIGN TABLE was added in
a0c6dfeecfcc.

Author: Zhang Mingli
Reviewed-by: Álvaro Herrera, Sami Imseih, Michael Paquier
Discussion: https://postgr.es/m/42d3f855-2275-4361-a42a-826172ca2dc4@Spark

1 files changed, 113 insertions, 1 deletions

diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index e0b0e075c2c..d08834ac9d2 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -23,7 +23,8 @@ PostgreSQL documentation
 <synopsis>
 CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name</replaceable> ( [
   { <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ OPTIONS ( <replaceable class="parameter">option</replaceable> '<replaceable class="parameter">value</replaceable>' [, ... ] ) ] [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
-    | <replaceable>table_constraint</replaceable> }
+    | <replaceable>table_constraint</replaceable>
+    | LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ] }
     [, ... ]
 ] )
 [ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
@@ -57,6 +58,10 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name
    CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] }
 [ ENFORCED | NOT ENFORCED ]
 
+<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
+
+{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | STATISTICS | ALL }
+
 <phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
 
 IN ( <replaceable class="parameter">partition_bound_expr</replaceable> [, ...] ) |
@@ -192,6 +197,111 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
    </varlistentry>
 
    <varlistentry>
+    <term><literal>LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ]</literal></term>
+    <listitem>
+     <para>
+      The <literal>LIKE</literal> clause specifies a table from which
+      the new table automatically copies all column names, their data types,
+      and their not-null constraints.
+     </para>
+     <para>
+      Unlike <literal>INHERITS</literal>, the new table and original table
+      are completely decoupled after creation is complete.  Changes to the
+      original table will not be applied to the new table, and it is not
+      possible to include data of the new table in scans of the original
+      table.
+     </para>
+      <para>
+      Also unlike <literal>INHERITS</literal>, columns and
+      constraints copied by <literal>LIKE</literal> are not merged with similarly
+      named columns and constraints.
+      If the same name is specified explicitly or in another
+      <literal>LIKE</literal> clause, an error is signaled.
+     </para>
+     <para>
+      The optional <replaceable>like_option</replaceable> clauses specify
+      which additional properties of the original table to copy.  Specifying
+      <literal>INCLUDING</literal> copies the property, specifying
+      <literal>EXCLUDING</literal> omits the property.
+      <literal>EXCLUDING</literal> is the default.  If multiple specifications
+      are made for the same kind of object, the last one is used.  The
+      available options are:
+
+      <variablelist>
+       <varlistentry>
+        <term><literal>INCLUDING COMMENTS</literal></term>
+        <listitem>
+         <para>
+          Comments for the copied columns, constraints, and indexes will be
+          copied.  The default behavior is to exclude comments, resulting in
+          the copied columns and constraints in the new table having no
+          comments.
+         </para>
+        </listitem>
+       </varlistentry>
+
+       <varlistentry>
+        <term><literal>INCLUDING CONSTRAINTS</literal></term>
+        <listitem>
+         <para>
+          <literal>CHECK</literal> constraints will be copied.  No distinction
+          is made between column constraints and table constraints.  Not-null
+          constraints are always copied to the new table.
+         </para>
+        </listitem>
+       </varlistentry>
+
+       <varlistentry>
+        <term><literal>INCLUDING DEFAULTS</literal></term>
+        <listitem>
+         <para>
+          Default expressions for the copied column definitions will be
+          copied.  Otherwise, default expressions are not copied, resulting in
+          the copied columns in the new table having null defaults.  Note that
+          copying defaults that call database-modification functions, such as
+          <function>nextval</function>, may create a functional linkage
+          between the original and new tables.
+         </para>
+        </listitem>
+       </varlistentry>
+
+       <varlistentry>
+        <term><literal>INCLUDING GENERATED</literal></term>
+        <listitem>
+         <para>
+          Any generation expressions of copied column definitions will be
+          copied.  By default, new columns will be regular base columns.
+         </para>
+        </listitem>
+       </varlistentry>
+
+       <varlistentry>
+        <term><literal>INCLUDING STATISTICS</literal></term>
+        <listitem>
+         <para>
+          Extended statistics are copied to the new table.
+         </para>
+        </listitem>
+       </varlistentry>
+
+       <varlistentry>
+        <term><literal>INCLUDING ALL</literal></term>
+        <listitem>
+         <para>
+          <literal>INCLUDING ALL</literal> is an abbreviated form selecting
+          all the available individual options.  (It could be useful to write
+          individual <literal>EXCLUDING</literal> clauses after
+          <literal>INCLUDING ALL</literal> to select all but some specific
+          options.)
+         </para>
+        </listitem>
+       </varlistentry>
+      </variablelist>
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
     <term><literal>CONSTRAINT <replaceable class="parameter">constraint_name</replaceable></literal></term>
     <listitem>
      <para>
@@ -448,6 +558,8 @@ CREATE FOREIGN TABLE measurement_y2016m07
    The ability to specify column default values is also
    a <productname>PostgreSQL</productname> extension.  Table inheritance, in the form
    defined by <productname>PostgreSQL</productname>, is nonstandard.
+   The <literal>LIKE</literal> clause, as supported in this command, is
+   nonstandard.
   </para>
 
  </refsect1>