On Sat, Feb 15, 2014 at 03:14:03PM +0100, Andres Freund wrote:
> On 2014-01-31 18:16:18 +0100, Vik Fearing wrote:
> > On 01/25/2014 06:25 AM, David Fetter wrote:
> > > Please find attached the next rev :)
> >
> > This version looks committable to me, so I am marking it as such.
>
> This doesn't contain a single regression test, I don't see how that's
> ok. Marking as waiting on author.
It now contains regression tests. Re-marking.
Cheers,
David.
--
David Fetter <da...@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index 38c6cf8..eab48fe 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -72,6 +72,20 @@ CREATE FOREIGN TABLE ft2 (
c8 user_enum
) SERVER loopback;
ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
+CREATE FOREIGN TABLE ft_tables (
+ LIKE information_schema.tables
+) SERVER loopback
+OPTIONS (
+ schema_name 'information_schema',
+ table_name 'tables'
+);
+CREATE FOREIGN TABLE ft_columns (
+ LIKE information_schema.columns
+) SERVER loopback
+OPTIONS (
+ schema_name 'information_schema',
+ table_name 'columns'
+);
-- ===================================================================
-- tests for validator
-- ===================================================================
@@ -113,12 +127,14 @@ ALTER FOREIGN TABLE ft2 OPTIONS (schema_name 'S 1',
table_name 'T 1');
ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (column_name 'C 1');
ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (column_name 'C 1');
\det+
- List of foreign tables
- Schema | Table | Server | FDW Options |
Description
---------+-------+----------+---------------------------------------+-------------
- public | ft1 | loopback | (schema_name 'S 1', table_name 'T 1') |
- public | ft2 | loopback | (schema_name 'S 1', table_name 'T 1') |
-(2 rows)
+ List of foreign tables
+ Schema | Table | Server | FDW Options
| Description
+--------+------------+----------+----------------------------------------------------------+-------------
+ public | ft1 | loopback | (schema_name 'S 1', table_name 'T 1')
|
+ public | ft2 | loopback | (schema_name 'S 1', table_name 'T 1')
|
+ public | ft_columns | loopback | (schema_name 'information_schema',
table_name 'columns') |
+ public | ft_tables | loopback | (schema_name 'information_schema',
table_name 'tables') |
+(4 rows)
-- Now we should be able to run ANALYZE.
-- To exercise multiple code paths, we use local stats on ft1
@@ -231,13 +247,25 @@ SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1'
AND t1.c7 >= '1';
101 | 1 | 00101 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 |
1 | 1 | foo
(1 row)
--- aggregate
+-- aggregates
SELECT COUNT(*) FROM ft1 t1;
count
-------
1000
(1 row)
+SELECT t.table_name, COUNT(*)
+FROM ft_tables t JOIN ft_columns c USING (table_catalog, table_schema,
table_name)
+WHERE t.table_type = 'FOREIGN TABLE'
+GROUP BY t.table_name;
+ table_name | count
+------------+-------
+ ft_columns | 44
+ ft2 | 8
+ ft_tables | 12
+ ft1 | 8
+(4 rows)
+
-- join two tables
SELECT t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1
OFFSET 100 LIMIT 10;
c1
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql
b/contrib/postgres_fdw/sql/postgres_fdw.sql
index ce8bb75..c5d2fde 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -81,6 +81,22 @@ CREATE FOREIGN TABLE ft2 (
) SERVER loopback;
ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
+CREATE FOREIGN TABLE ft_tables (
+ LIKE information_schema.tables
+) SERVER loopback
+OPTIONS (
+ schema_name 'information_schema',
+ table_name 'tables'
+);
+
+CREATE FOREIGN TABLE ft_columns (
+ LIKE information_schema.columns
+) SERVER loopback
+OPTIONS (
+ schema_name 'information_schema',
+ table_name 'columns'
+);
+
-- ===================================================================
-- tests for validator
-- ===================================================================
@@ -145,8 +161,12 @@ SELECT * FROM ft1 WHERE false;
-- with WHERE clause
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND
t1.c6 = '1' AND t1.c7 >= '1';
SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1';
--- aggregate
+-- aggregates
SELECT COUNT(*) FROM ft1 t1;
+SELECT t.table_name, COUNT(*)
+FROM ft_tables t JOIN ft_columns c USING (table_catalog, table_schema,
table_name)
+WHERE t.table_type = 'FOREIGN TABLE'
+GROUP BY t.table_name;
-- join two tables
SELECT t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1
OFFSET 100 LIMIT 10;
-- subquery
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml
b/doc/src/sgml/ref/create_foreign_table.sgml
index 1ef4b5e..375bd1a 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -20,6 +20,7 @@
<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> [ ... ] ]
+ | LIKE <replaceable>source_table</replaceable> [
<replaceable>like_option</replaceable> ... ] }
[, ... ]
] )
SERVER <replaceable class="parameter">server_name</replaceable>
@@ -31,6 +32,8 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable
class="PARAMETER">table_name
{ NOT NULL |
NULL |
DEFAULT <replaceable>default_expr</replaceable> }
+
+ <phrase> and <replaceable class="PARAMETER">like_option</replaceable> is the
same as for <xref linkend="SQL-CREATETABLE">.</phrase>
</synopsis>
</refsynopsisdiv>
@@ -114,6 +117,19 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable
class="PARAMETER">table_name
</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 foreign table automatically copies all column names and their
data types.
+ </para>
+ <para>
+ Inapplicable options like <literal>INCLUDING STORAGE</literal> are
ignored.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>NOT NULL</></term>
<listitem>
<para>
diff --git a/src/backend/parser/parse_utilcmd.c
b/src/backend/parser/parse_utilcmd.c
index eb07ca3..82c77eb 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -649,7 +649,7 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint
*constraint)
/*
* transformTableLikeClause
*
- * Change the LIKE <srctable> portion of a CREATE TABLE statement into
+ * Change the LIKE <srctable> portion of a CREATE [FOREIGN] TABLE statement
into
* column definitions which recreate the user defined column portions of
* <srctable>.
*/
@@ -668,12 +668,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")));
-
relation = relation_openrv(table_like_clause->relation,
AccessShareLock);
if (relation->rd_rel->relkind != RELKIND_RELATION &&
@@ -689,6 +683,12 @@ transformTableLikeClause(CreateStmtContext *cxt,
TableLikeClause *table_like_cla
cancel_parser_errposition_callback(&pcbstate);
/*
+ * For foreign tables, ignore all but applicable options.
+ */
+ if (cxt->isforeign)
+ table_like_clause->options &= CREATE_TABLE_LIKE_DEFAULTS |
CREATE_TABLE_LIKE_COMMENTS;
+
+ /*
* Check for privileges
*/
if (relation->rd_rel->relkind == RELKIND_COMPOSITE_TYPE)
diff --git a/src/test/regress/expected/foreign_data.out
b/src/test/regress/expected/foreign_data.out
index 60506e0..2df1c60 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -699,6 +699,13 @@ SELECT * FROM ft1;
-- ERROR
ERROR: foreign-data wrapper "dummy" has no handler
EXPLAIN SELECT * FROM ft1; -- ERROR
ERROR: foreign-data wrapper "dummy" has no handler
+CREATE FOREIGN TABLE foreign_schema.ft_columns (LIKE
information_schema.columns)
+ SERVER s0 OPTIONS (delimiter ',', quote '"');
+CREATE FOREIGN TABLE doesnt_exist_ft1 (LIKE doesnt_exist_lt1) -- ERROR
+ SERVER s0 OPTIONS (delimiter ',', quote '"');
+ERROR: relation "doesnt_exist_lt1" does not exist
+LINE 1: CREATE FOREIGN TABLE doesnt_exist_ft1 (LIKE doesnt_exist_lt1...
+ ^
-- ALTER FOREIGN TABLE
COMMENT ON FOREIGN TABLE ft1 IS 'foreign table';
COMMENT ON FOREIGN TABLE ft1 IS NULL;
@@ -919,7 +926,8 @@ SELECT * FROM information_schema.foreign_tables ORDER BY 1,
2, 3;
foreign_table_catalog | foreign_table_schema | foreign_table_name |
foreign_server_catalog | foreign_server_name
-----------------------+----------------------+--------------------+------------------------+---------------------
regression | foreign_schema | foreign_table_1 |
regression | s0
-(1 row)
+ regression | foreign_schema | ft_columns |
regression | s0
+(2 rows)
SELECT * FROM information_schema.foreign_table_options ORDER BY 1, 2, 3, 4;
foreign_table_catalog | foreign_table_schema | foreign_table_name |
option_name | option_value
@@ -927,7 +935,9 @@ SELECT * FROM information_schema.foreign_table_options
ORDER BY 1, 2, 3, 4;
regression | foreign_schema | foreign_table_1 | be quoted
| value
regression | foreign_schema | foreign_table_1 | escape
| @
regression | foreign_schema | foreign_table_1 | quote
| ~
-(3 rows)
+ regression | foreign_schema | ft_columns | delimiter
| ,
+ regression | foreign_schema | ft_columns | quote
| "
+(5 rows)
SET ROLE regress_test_role;
SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4;
@@ -1166,6 +1176,7 @@ NOTICE: foreign table "no_table" does not exist, skipping
DROP FOREIGN TABLE foreign_schema.foreign_table_1;
-- Cleanup
DROP SCHEMA foreign_schema CASCADE;
+NOTICE: drop cascades to foreign table foreign_schema.ft_columns
DROP ROLE regress_test_role; -- ERROR
ERROR: role "regress_test_role" cannot be dropped because some objects depend
on it
DETAIL: privileges for server s4
diff --git a/src/test/regress/sql/foreign_data.sql
b/src/test/regress/sql/foreign_data.sql
index f819eb1..54475d8 100644
--- a/src/test/regress/sql/foreign_data.sql
+++ b/src/test/regress/sql/foreign_data.sql
@@ -280,6 +280,10 @@ COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR
SELECT * FROM ft1; -- ERROR
EXPLAIN SELECT * FROM ft1; -- ERROR
+CREATE FOREIGN TABLE foreign_schema.ft_columns (LIKE
information_schema.columns)
+ SERVER s0 OPTIONS (delimiter ',', quote '"');
+CREATE FOREIGN TABLE doesnt_exist_ft1 (LIKE doesnt_exist_lt1) -- ERROR
+ SERVER s0 OPTIONS (delimiter ',', quote '"');
-- ALTER FOREIGN TABLE
COMMENT ON FOREIGN TABLE ft1 IS 'foreign table';
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
