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

3 files changed, 158 insertions, 11 deletions

diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index eb7716cd84c..abbe1bb45a3 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1131,6 +1131,10 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
  * process at this point, add the TableLikeClause to cxt->likeclauses, which
  * will cause utility.c to call expandTableLikeClause() after the new
  * table has been created.
+ *
+ * Some options are ignored.  For example, as foreign tables have no storage,
+ * these INCLUDING options have no effect: STORAGE, COMPRESSION, IDENTITY
+ * and INDEXES.  Similarly, INCLUDING INDEXES is ignored from a view.
  */
 static void
 transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_clause)
@@ -1145,12 +1149,6 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
 	setup_parser_errposition_callback(&pcbstate, cxt->pstate,
 									  table_like_clause->relation->location);
 
-	/* we could support LIKE in many cases, but worry about it another day */
-	if (cxt->isforeign)
-		ereport(ERROR,
-				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-				 errmsg("LIKE is not supported for creating foreign tables")));
-
 	/* Open the relation referenced by the LIKE clause */
 	relation = relation_openrv(table_like_clause->relation, AccessShareLock);
 
@@ -1231,7 +1229,8 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
 		 * Copy identity if requested
 		 */
 		if (attribute->attidentity &&
-			(table_like_clause->options & CREATE_TABLE_LIKE_IDENTITY))
+			(table_like_clause->options & CREATE_TABLE_LIKE_IDENTITY) &&
+			!cxt->isforeign)
 		{
 			Oid			seq_relid;
 			List	   *seq_options;
@@ -1250,14 +1249,16 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
 		}
 
 		/* Likewise, copy storage if requested */
-		if (table_like_clause->options & CREATE_TABLE_LIKE_STORAGE)
+		if ((table_like_clause->options & CREATE_TABLE_LIKE_STORAGE) &&
+			!cxt->isforeign)
 			def->storage = attribute->attstorage;
 		else
 			def->storage = 0;
 
 		/* Likewise, copy compression if requested */
-		if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0
-			&& CompressionMethodIsValid(attribute->attcompression))
+		if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0 &&
+			CompressionMethodIsValid(attribute->attcompression) &&
+			!cxt->isforeign)
 			def->compression =
 				pstrdup(GetCompressionMethodName(attribute->attcompression));
 		else
@@ -1536,7 +1537,8 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
 	 * Process indexes if required.
 	 */
 	if ((table_like_clause->options & CREATE_TABLE_LIKE_INDEXES) &&
-		relation->rd_rel->relhasindex)
+		relation->rd_rel->relhasindex &&
+		childrel->rd_rel->relkind != RELKIND_FOREIGN_TABLE)
 	{
 		List	   *parent_indexes;
 		ListCell   *l;
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index 2cebe382432..bf34289e984 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -566,3 +566,106 @@ DROP TYPE ctlty1;
 DROP VIEW ctlv1;
 DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
 NOTICE:  table "ctlt10" does not exist, skipping
+--
+-- CREATE FOREIGN TABLE LIKE
+--
+CREATE FOREIGN DATA WRAPPER ctl_dummy;
+CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy;
+CREATE TABLE ctl_table(a int PRIMARY KEY,
+  b varchar COMPRESSION pglz,
+  c int GENERATED ALWAYS AS (a * 2) STORED,
+  d bigint GENERATED ALWAYS AS IDENTITY,
+  e int DEFAULT 1);
+CREATE INDEX ctl_table_a_key ON ctl_table(a);
+COMMENT ON COLUMN ctl_table.b IS 'Column b';
+CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table;
+ALTER TABLE ctl_table ADD CONSTRAINT foo CHECK (b = 'text');
+ALTER TABLE ctl_table ALTER COLUMN b SET STORAGE MAIN;
+\d+ ctl_table
+                                                   Table "public.ctl_table"
+ Column |       Type        | Collation | Nullable |              Default               | Storage | Stats target | Description 
+--------+-------------------+-----------+----------+------------------------------------+---------+--------------+-------------
+ a      | integer           |           | not null |                                    | plain   |              | 
+ b      | character varying |           |          |                                    | main    |              | Column b
+ c      | integer           |           |          | generated always as (a * 2) stored | plain   |              | 
+ d      | bigint            |           | not null | generated always as identity       | plain   |              | 
+ e      | integer           |           |          | 1                                  | plain   |              | 
+Indexes:
+    "ctl_table_pkey" PRIMARY KEY, btree (a)
+    "ctl_table_a_key" btree (a)
+Check constraints:
+    "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+    "public.ctl_table_stat" ON a, b FROM ctl_table
+Not-null constraints:
+    "ctl_table_a_not_null" NOT NULL "a"
+    "ctl_table_d_not_null" NOT NULL "d"
+
+-- Test EXCLUDING ALL
+CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table EXCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table1
+                                     Foreign table "public.ctl_foreign_table1"
+ Column |       Type        | Collation | Nullable | Default | FDW options | Storage  | Stats target | Description 
+--------+-------------------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a      | integer           |           | not null |         |             | plain    |              | 
+ b      | character varying |           |          |         |             | extended |              | 
+ c      | integer           |           |          |         |             | plain    |              | 
+ d      | bigint            |           | not null |         |             | plain    |              | 
+ e      | integer           |           |          |         |             | plain    |              | 
+Not-null constraints:
+    "ctl_table_a_not_null" NOT NULL "a"
+    "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+-- \d+ does not report the value of attcompression for a foreign table, so
+-- check separately.
+SELECT attname, attcompression FROM pg_attribute
+  WHERE attrelid = 'ctl_foreign_table1'::regclass and attnum > 0 ORDER BY attnum;
+ attname | attcompression 
+---------+----------------
+ a       | 
+ b       | 
+ c       | 
+ d       | 
+ e       | 
+(5 rows)
+
+-- Test INCLUDING ALL
+-- INDEXES, IDENTITY, COMPRESSION, STORAGE are not copied.
+CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table2
+                                                  Foreign table "public.ctl_foreign_table2"
+ Column |       Type        | Collation | Nullable |              Default               | FDW options | Storage  | Stats target | Description 
+--------+-------------------+-----------+----------+------------------------------------+-------------+----------+--------------+-------------
+ a      | integer           |           | not null |                                    |             | plain    |              | 
+ b      | character varying |           |          |                                    |             | extended |              | Column b
+ c      | integer           |           |          | generated always as (a * 2) stored |             | plain    |              | 
+ d      | bigint            |           | not null |                                    |             | plain    |              | 
+ e      | integer           |           |          | 1                                  |             | plain    |              | 
+Check constraints:
+    "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+    "public.ctl_foreign_table2_a_b_stat" ON a, b FROM ctl_foreign_table2
+Not-null constraints:
+    "ctl_table_a_not_null" NOT NULL "a"
+    "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+-- \d+ does not report the value of attcompression for a foreign table, so
+-- check separately.
+SELECT attname, attcompression FROM pg_attribute
+  WHERE attrelid = 'ctl_foreign_table2'::regclass and attnum > 0 ORDER BY attnum;
+ attname | attcompression 
+---------+----------------
+ a       | 
+ b       | 
+ c       | 
+ d       | 
+ e       | 
+(5 rows)
+
+DROP TABLE ctl_table;
+DROP FOREIGN TABLE ctl_foreign_table1;
+DROP FOREIGN TABLE ctl_foreign_table2;
+DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;
+NOTICE:  drop cascades to server ctl_s0
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index 63a60303659..6e21722aaeb 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -225,3 +225,45 @@ DROP SEQUENCE ctlseq1;
 DROP TYPE ctlty1;
 DROP VIEW ctlv1;
 DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
+
+--
+-- CREATE FOREIGN TABLE LIKE
+--
+CREATE FOREIGN DATA WRAPPER ctl_dummy;
+CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy;
+
+CREATE TABLE ctl_table(a int PRIMARY KEY,
+  b varchar COMPRESSION pglz,
+  c int GENERATED ALWAYS AS (a * 2) STORED,
+  d bigint GENERATED ALWAYS AS IDENTITY,
+  e int DEFAULT 1);
+
+CREATE INDEX ctl_table_a_key ON ctl_table(a);
+COMMENT ON COLUMN ctl_table.b IS 'Column b';
+CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table;
+ALTER TABLE ctl_table ADD CONSTRAINT foo CHECK (b = 'text');
+ALTER TABLE ctl_table ALTER COLUMN b SET STORAGE MAIN;
+
+\d+ ctl_table
+
+-- Test EXCLUDING ALL
+CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table EXCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table1
+-- \d+ does not report the value of attcompression for a foreign table, so
+-- check separately.
+SELECT attname, attcompression FROM pg_attribute
+  WHERE attrelid = 'ctl_foreign_table1'::regclass and attnum > 0 ORDER BY attnum;
+
+-- Test INCLUDING ALL
+-- INDEXES, IDENTITY, COMPRESSION, STORAGE are not copied.
+CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table2
+-- \d+ does not report the value of attcompression for a foreign table, so
+-- check separately.
+SELECT attname, attcompression FROM pg_attribute
+  WHERE attrelid = 'ctl_foreign_table2'::regclass and attnum > 0 ORDER BY attnum;
+
+DROP TABLE ctl_table;
+DROP FOREIGN TABLE ctl_foreign_table1;
+DROP FOREIGN TABLE ctl_foreign_table2;
+DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;