Re: IMPORT FOREIGN SCHEMA statement - Mailing list pgsql-hackers
| From | Tom Lane |
|---|---|
| Subject | Re: IMPORT FOREIGN SCHEMA statement |
| Date | |
| Msg-id | 16977.1404953543@sss.pgh.pa.us Whole thread Raw |
| In response to | Re: IMPORT FOREIGN SCHEMA statement (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: IMPORT FOREIGN SCHEMA statement
|
| List | pgsql-hackers |
I wrote:
> I've started looking at this patch. I wonder whether it's really such
> a great idea to expect the FDW to return a list of parsetrees for
> CREATE FOREIGN TABLE commands; that seems like a recipe for breakage
> anytime we change the parsetree representation, say add a field to
> ColumnDef. The alternative I'm thinking about is to have the FDW pass
> back a list of strings, which would be textual CREATE FOREIGN TABLE
> commands.
Here's a rather-heavily-editorialized draft patch that does it like that.
This patch takes the viewpoint I espoused nearby of allowing names in
the LIMIT TO clause that aren't present on the remote server. If we
decide we want to hew to the letter of the standard on that, I'd be
inclined to enforce this in the core code, not in individual FDWs as
the submitted patch did. (I didn't much like that implementation
anyway, since it didn't tell you which name it was unhappy about.)
regards, tom lane
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 322138d..2045774 100644
*** a/contrib/postgres_fdw/deparse.c
--- b/contrib/postgres_fdw/deparse.c
*************** static void deparseReturningList(StringI
*** 116,122 ****
static void deparseColumnRef(StringInfo buf, int varno, int varattno,
PlannerInfo *root);
static void deparseRelation(StringInfo buf, Relation rel);
- static void deparseStringLiteral(StringInfo buf, const char *val);
static void deparseExpr(Expr *expr, deparse_expr_cxt *context);
static void deparseVar(Var *node, deparse_expr_cxt *context);
static void deparseConst(Const *node, deparse_expr_cxt *context);
--- 116,121 ----
*************** deparseRelation(StringInfo buf, Relation
*** 1160,1166 ****
/*
* Append a SQL string literal representing "val" to buf.
*/
! static void
deparseStringLiteral(StringInfo buf, const char *val)
{
const char *valptr;
--- 1159,1165 ----
/*
* Append a SQL string literal representing "val" to buf.
*/
! void
deparseStringLiteral(StringInfo buf, const char *val)
{
const char *valptr;
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 2e49ee3..093bd65 100644
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
*************** NOTICE: NEW: (13,"test triggered !")
*** 2834,2836 ****
--- 2834,3010 ----
(0,27)
(1 row)
+ -- ===================================================================
+ -- test IMPORT FOREIGN SCHEMA
+ -- ===================================================================
+ CREATE SCHEMA import_source;
+ CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL);
+ CREATE TABLE import_source.t2 (c1 int, c2 varchar NULL, c3 text);
+ CREATE TYPE typ1 AS (m1 int, m2 varchar);
+ CREATE TABLE import_source.t3 (c1 int, c2 typ1);
+ CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
+ CREATE TABLE import_source."x 5" (c1 float8);
+ ALTER TABLE import_source."x 5" DROP COLUMN c1;
+ CREATE SCHEMA import_dest1;
+ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
+ \det+ import_dest1
+ List of foreign tables
+ Schema | Table | Server | FDW Options | Description
+ --------------+-------+----------+-------------------------------------------------+-------------
+ import_dest1 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
+ import_dest1 | t2 | loopback | (schema_name 'import_source', table_name 't2') |
+ import_dest1 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
+ import_dest1 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
+ import_dest1 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
+ (5 rows)
+
+ \d import_dest1.*
+ Foreign table "import_dest1.t1"
+ Column | Type | Modifiers | FDW Options
+ --------+-------------------+-----------+-------------
+ c1 | integer | |
+ c2 | character varying | not null |
+ Server: loopback
+ FDW Options: (schema_name 'import_source', table_name 't1')
+
+ Foreign table "import_dest1.t2"
+ Column | Type | Modifiers | FDW Options
+ --------+-------------------+-----------+-------------
+ c1 | integer | |
+ c2 | character varying | |
+ c3 | text | |
+ Server: loopback
+ FDW Options: (schema_name 'import_source', table_name 't2')
+
+ Foreign table "import_dest1.t3"
+ Column | Type | Modifiers | FDW Options
+ --------+---------+-----------+-------------
+ c1 | integer | |
+ c2 | typ1 | |
+ Server: loopback
+ FDW Options: (schema_name 'import_source', table_name 't3')
+
+ Foreign table "import_dest1.x 4"
+ Column | Type | Modifiers | FDW Options
+ --------+-----------------------+-----------+-------------
+ c1 | double precision | |
+ C 2 | text | |
+ c3 | character varying(42) | |
+ Server: loopback
+ FDW Options: (schema_name 'import_source', table_name 'x 4')
+
+ Foreign table "import_dest1.x 5"
+ Column | Type | Modifiers | FDW Options
+ --------+------+-----------+-------------
+ Server: loopback
+ FDW Options: (schema_name 'import_source', table_name 'x 5')
+
+ -- Options
+ CREATE SCHEMA import_dest2;
+ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
+ OPTIONS (import_not_null 'false');
+ \det+ import_dest2
+ List of foreign tables
+ Schema | Table | Server | FDW Options | Description
+ --------------+-------+----------+-------------------------------------------------+-------------
+ import_dest2 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
+ import_dest2 | t2 | loopback | (schema_name 'import_source', table_name 't2') |
+ import_dest2 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
+ import_dest2 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
+ import_dest2 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
+ (5 rows)
+
+ \d import_dest2.*
+ Foreign table "import_dest2.t1"
+ Column | Type | Modifiers | FDW Options
+ --------+-------------------+-----------+-------------
+ c1 | integer | |
+ c2 | character varying | |
+ Server: loopback
+ FDW Options: (schema_name 'import_source', table_name 't1')
+
+ Foreign table "import_dest2.t2"
+ Column | Type | Modifiers | FDW Options
+ --------+-------------------+-----------+-------------
+ c1 | integer | |
+ c2 | character varying | |
+ c3 | text | |
+ Server: loopback
+ FDW Options: (schema_name 'import_source', table_name 't2')
+
+ Foreign table "import_dest2.t3"
+ Column | Type | Modifiers | FDW Options
+ --------+---------+-----------+-------------
+ c1 | integer | |
+ c2 | typ1 | |
+ Server: loopback
+ FDW Options: (schema_name 'import_source', table_name 't3')
+
+ Foreign table "import_dest2.x 4"
+ Column | Type | Modifiers | FDW Options
+ --------+-----------------------+-----------+-------------
+ c1 | double precision | |
+ C 2 | text | |
+ c3 | character varying(42) | |
+ Server: loopback
+ FDW Options: (schema_name 'import_source', table_name 'x 4')
+
+ Foreign table "import_dest2.x 5"
+ Column | Type | Modifiers | FDW Options
+ --------+------+-----------+-------------
+ Server: loopback
+ FDW Options: (schema_name 'import_source', table_name 'x 5')
+
+ -- Check LIMIT TO and EXCEPT
+ CREATE SCHEMA import_dest3;
+ IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch)
+ FROM SERVER loopback INTO import_dest3;
+ \det+ import_dest3
+ List of foreign tables
+ Schema | Table | Server | FDW Options | Description
+ --------------+-------+----------+------------------------------------------------+-------------
+ import_dest3 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
+ (1 row)
+
+ IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch)
+ FROM SERVER loopback INTO import_dest3;
+ \det+ import_dest3
+ List of foreign tables
+ Schema | Table | Server | FDW Options | Description
+ --------------+-------+----------+-------------------------------------------------+-------------
+ import_dest3 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
+ import_dest3 | t2 | loopback | (schema_name 'import_source', table_name 't2') |
+ import_dest3 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
+ import_dest3 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
+ (4 rows)
+
+ -- Assorted error cases
+ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3;
+ ERROR: relation "t1" already exists
+ CONTEXT: importing foreign table "t1"
+ IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO import_dest3;
+ ERROR: schema "nonesuch" is not present on foreign server "loopback"
+ IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO notthere;
+ ERROR: schema "notthere" does not exist
+ IMPORT FOREIGN SCHEMA nonesuch FROM SERVER nowhere INTO notthere;
+ ERROR: server "nowhere" does not exist
+ -- Check case of a type present only on the remote server.
+ -- We can fake this by dropping the type locally in our transaction.
+ CREATE TYPE "Colors" AS ENUM ('red', 'green', 'blue');
+ CREATE TABLE import_source.t5 (c1 int, "Col" "Colors");
+ CREATE SCHEMA import_dest4;
+ BEGIN;
+ DROP TYPE "Colors" CASCADE;
+ NOTICE: drop cascades to table import_source.t5 column Col
+ IMPORT FOREIGN SCHEMA import_source LIMIT TO (t5)
+ FROM SERVER loopback INTO import_dest4; -- ERROR
+ ERROR: type "public.Colors" does not exist
+ LINE 3: "Col" public."Colors"
+ ^
+ QUERY: CREATE FOREIGN TABLE t5 (
+ c1 integer,
+ "Col" public."Colors"
+ ) SERVER loopback
+ OPTIONS (schema_name 'import_source', table_name 't5');
+ CONTEXT: importing foreign table "t5"
+ ROLLBACK;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 7dd43a9..d74fe64 100644
*** a/contrib/postgres_fdw/postgres_fdw.c
--- b/contrib/postgres_fdw/postgres_fdw.c
*************** static void postgresExplainForeignModify
*** 286,291 ****
--- 286,293 ----
static bool postgresAnalyzeForeignTable(Relation relation,
AcquireSampleRowsFunc *func,
BlockNumber *totalpages);
+ static List *postgresImportForeignSchema(ImportForeignSchemaStmt *stmt,
+ Oid serverOid);
/*
* Helper functions
*************** postgres_fdw_handler(PG_FUNCTION_ARGS)
*** 363,368 ****
--- 365,373 ----
/* Support functions for ANALYZE */
routine->AnalyzeForeignTable = postgresAnalyzeForeignTable;
+ /* Support functions for IMPORT FOREIGN SCHEMA */
+ routine->ImportForeignSchema = postgresImportForeignSchema;
+
PG_RETURN_POINTER(routine);
}
*************** analyze_row_processor(PGresult *res, int
*** 2565,2570 ****
--- 2570,2773 ----
}
/*
+ * Map a remote schema to a local one.
+ */
+ static List *
+ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
+ {
+ List *commands = NIL;
+ bool import_not_null = true;
+ ForeignServer *server;
+ UserMapping *mapping;
+ PGconn *conn;
+ StringInfoData buf;
+ PGresult *volatile res = NULL;
+ int numrows,
+ i;
+ ListCell *lc;
+
+ /* Parse statement options */
+ foreach(lc, stmt->options)
+ {
+ DefElem *def = (DefElem *) lfirst(lc);
+
+ if (strcmp(def->defname, "import_not_null") == 0)
+ import_not_null = defGetBoolean(def);
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_FDW_INVALID_OPTION_NAME),
+ errmsg("invalid option \"%s\"", def->defname)));
+ }
+
+ /*
+ * Get connection to the foreign server. Connection manager will
+ * establish new connection if necessary.
+ */
+ server = GetForeignServer(serverOid);
+ mapping = GetUserMapping(GetUserId(), server->serverid);
+ conn = GetConnection(server, mapping, false);
+
+ /* Create workspace for strings */
+ initStringInfo(&buf);
+
+ /* In what follows, do not risk leaking any PGresults. */
+ PG_TRY();
+ {
+ /* Check that the schema really exists */
+ appendStringInfoString(&buf, "SELECT 1 FROM pg_catalog.pg_namespace WHERE nspname = ");
+ deparseStringLiteral(&buf, stmt->remote_schema);
+
+ res = PQexec(conn, buf.data);
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ pgfdw_report_error(ERROR, res, conn, false, buf.data);
+
+ if (PQntuples(res) != 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_FDW_SCHEMA_NOT_FOUND),
+ errmsg("schema \"%s\" is not present on foreign server \"%s\"",
+ stmt->remote_schema, server->servername)));
+
+ PQclear(res);
+ res = NULL;
+ resetStringInfo(&buf);
+
+ /*
+ * Fetch all table data from this schema, possibly restricted by
+ * EXCEPT or LIMIT TO.
+ *
+ * Note: because we run the connection with search_path restricted to
+ * pg_catalog, the format_type() output will always include a schema
+ * name for types in other schemas, which is what we want.
+ *
+ * Note: in principle we'd like to include a COLLATE clause where
+ * appropriate, but because the remote server might have a different
+ * set of collations than we do, it's not easy to do that.
+ */
+ appendStringInfoString(&buf,
+ "SELECT relname, "
+ " attname, "
+ " format_type(atttypid, atttypmod), "
+ " attnotnull "
+ "FROM pg_catalog.pg_class c "
+ " JOIN pg_catalog.pg_namespace n ON "
+ " c.relnamespace = n.oid "
+ " LEFT JOIN pg_catalog.pg_attribute a ON "
+ " a.attrelid = c.oid AND a.attnum > 0 "
+ " AND NOT a.attisdropped "
+ "WHERE c.relkind IN ('r', 'v', 'f', 'm') "
+ " AND n.nspname = ");
+ deparseStringLiteral(&buf, stmt->remote_schema);
+
+ /* Apply restrictions for LIMIT TO and EXCEPT */
+ if (stmt->list_type == FDW_IMPORT_SCHEMA_LIMIT_TO ||
+ stmt->list_type == FDW_IMPORT_SCHEMA_EXCEPT)
+ {
+ bool first_item = true;
+
+ appendStringInfoString(&buf, " AND c.relname ");
+ if (stmt->list_type == FDW_IMPORT_SCHEMA_EXCEPT)
+ appendStringInfoString(&buf, "NOT ");
+ appendStringInfoString(&buf, "IN (");
+
+ /* Append list of table names within IN clause */
+ foreach(lc, stmt->table_list)
+ {
+ RangeVar *rv = (RangeVar *) lfirst(lc);
+
+ if (first_item)
+ first_item = false;
+ else
+ appendStringInfoString(&buf, ", ");
+ deparseStringLiteral(&buf, rv->relname);
+ }
+ appendStringInfoString(&buf, ")");
+ }
+
+ /* Append ORDER BY at the end of query to ensure output ordering */
+ appendStringInfo(&buf, " ORDER BY c.relname, a.attnum");
+
+ /* Fetch the data */
+ res = PQexec(conn, buf.data);
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ pgfdw_report_error(ERROR, res, conn, false, buf.data);
+
+ /* Process results */
+ numrows = PQntuples(res);
+ for (i = 0; i < numrows; i++)
+ {
+ char *tablename = PQgetvalue(res, i, 0);
+ bool first_item = true;
+
+ resetStringInfo(&buf);
+ appendStringInfo(&buf, "CREATE FOREIGN TABLE %s (\n",
+ quote_identifier(tablename));
+
+ /* Scan all rows for this table. */
+ do
+ {
+ char *attname = PQgetvalue(res, i, 1);
+ char *typename = PQgetvalue(res, i, 2);
+ char *attnotnull = PQgetvalue(res, i, 3);
+
+ /* If table has no columns, we'll see nulls here */
+ if (PQgetisnull(res, i, 1))
+ continue;
+
+ if (first_item)
+ first_item = false;
+ else
+ appendStringInfoString(&buf, ",\n");
+
+ appendStringInfo(&buf, " %s %s",
+ quote_identifier(attname),
+ typename);
+
+ /* Mark NOT NULL if necessary */
+ if (import_not_null && attnotnull[0] == 't')
+ appendStringInfoString(&buf, " NOT NULL");
+ }
+ while (++i < numrows &&
+ strcmp(PQgetvalue(res, i, 0), tablename) == 0);
+
+ /* Back up, we went one row too far */
+ i--;
+
+ /*
+ * Add server name and options. We specify remote schema and
+ * table name as options (the latter to ensure that renaming the
+ * foreign table doesn't break the association).
+ */
+ appendStringInfo(&buf, "\n) SERVER %s\nOPTIONS (",
+ quote_identifier(server->servername));
+
+ appendStringInfoString(&buf, "schema_name ");
+ deparseStringLiteral(&buf, stmt->remote_schema);
+ appendStringInfoString(&buf, ", table_name ");
+ deparseStringLiteral(&buf, tablename);
+
+ appendStringInfoString(&buf, ");");
+
+ commands = lappend(commands, pstrdup(buf.data));
+ }
+
+ /* Clean up */
+ PQclear(res);
+ res = NULL;
+ }
+ PG_CATCH();
+ {
+ if (res)
+ PQclear(res);
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+
+ ReleaseConnection(conn);
+
+ return commands;
+ }
+
+ /*
* Create a tuple from the specified row of the PGresult.
*
* rel is the local representation of the foreign table, attinmeta is
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index 8aa8f1a..94eadae 100644
*** a/contrib/postgres_fdw/postgres_fdw.h
--- b/contrib/postgres_fdw/postgres_fdw.h
*************** extern void deparseDeleteSql(StringInfo
*** 73,77 ****
--- 73,78 ----
extern void deparseAnalyzeSizeSql(StringInfo buf, Relation rel);
extern void deparseAnalyzeSql(StringInfo buf, Relation rel,
List **retrieved_attrs);
+ extern void deparseStringLiteral(StringInfo buf, const char *val);
#endif /* POSTGRES_FDW_H */
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 6187839..a6df288 100644
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
*************** UPDATE rem1 SET f2 = 'testo';
*** 609,611 ****
--- 609,663 ----
-- Test returning a system attribute
INSERT INTO rem1(f2) VALUES ('test') RETURNING ctid;
+
+ -- ===================================================================
+ -- test IMPORT FOREIGN SCHEMA
+ -- ===================================================================
+
+ CREATE SCHEMA import_source;
+ CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL);
+ CREATE TABLE import_source.t2 (c1 int, c2 varchar NULL, c3 text);
+ CREATE TYPE typ1 AS (m1 int, m2 varchar);
+ CREATE TABLE import_source.t3 (c1 int, c2 typ1);
+ CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
+ CREATE TABLE import_source."x 5" (c1 float8);
+ ALTER TABLE import_source."x 5" DROP COLUMN c1;
+
+ CREATE SCHEMA import_dest1;
+ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
+ \det+ import_dest1
+ \d import_dest1.*
+
+ -- Options
+ CREATE SCHEMA import_dest2;
+ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
+ OPTIONS (import_not_null 'false');
+ \det+ import_dest2
+ \d import_dest2.*
+
+ -- Check LIMIT TO and EXCEPT
+ CREATE SCHEMA import_dest3;
+ IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch)
+ FROM SERVER loopback INTO import_dest3;
+ \det+ import_dest3
+ IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch)
+ FROM SERVER loopback INTO import_dest3;
+ \det+ import_dest3
+
+ -- Assorted error cases
+ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3;
+ IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO import_dest3;
+ IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO notthere;
+ IMPORT FOREIGN SCHEMA nonesuch FROM SERVER nowhere INTO notthere;
+
+ -- Check case of a type present only on the remote server.
+ -- We can fake this by dropping the type locally in our transaction.
+ CREATE TYPE "Colors" AS ENUM ('red', 'green', 'blue');
+ CREATE TABLE import_source.t5 (c1 int, "Col" "Colors");
+
+ CREATE SCHEMA import_dest4;
+ BEGIN;
+ DROP TYPE "Colors" CASCADE;
+ IMPORT FOREIGN SCHEMA import_source LIMIT TO (t5)
+ FROM SERVER loopback INTO import_dest4; -- ERROR
+ ROLLBACK;
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 8ace8bd..3b7fff4 100644
*** a/doc/src/sgml/ddl.sgml
--- b/doc/src/sgml/ddl.sgml
*************** ANALYZE measurement;
*** 3069,3076 ****
For additional information, see
<xref linkend="sql-createforeigndatawrapper">,
<xref linkend="sql-createserver">,
! <xref linkend="sql-createusermapping">, and
! <xref linkend="sql-createforeigntable">.
</para>
</sect1>
--- 3069,3077 ----
For additional information, see
<xref linkend="sql-createforeigndatawrapper">,
<xref linkend="sql-createserver">,
! <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 e5b9e66..3db8ef1 100644
*** a/doc/src/sgml/event-trigger.sgml
--- b/doc/src/sgml/event-trigger.sgml
***************
*** 604,609 ****
--- 604,615 ----
<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 6b5c8b7..edfdcba 100644
*** a/doc/src/sgml/fdwhandler.sgml
--- b/doc/src/sgml/fdwhandler.sgml
*************** AcquireSampleRowsFunc (Relation relation
*** 696,701 ****
--- 696,761 ----
</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 <command>IMPORT FOREIGN SCHEMA</>, 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 <command>CREATE FOREIGN TABLE</> 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 e6f6e20..152a0e3 100644
*** a/doc/src/sgml/postgres-fdw.sgml
--- b/doc/src/sgml/postgres-fdw.sgml
***************
*** 49,55 ****
</listitem>
<listitem>
<para>
! Create a foreign table, using <xref linkend="sql-createforeigntable">,
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
--- 49,56 ----
</listitem>
<listitem>
<para>
! 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
***************
*** 291,296 ****
--- 292,333 ----
</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_not_null</literal></term>
+ <listitem>
+ <para>
+ This option controls whether <literal>NOT NULL</> column
+ constraints are included in the definitions of foreign tables imported
+ from a foreign server. The default is <literal>true</>.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ <para>
+ At present, column default expressions, collations, and constraints other
+ than <literal>NOT NULL</> will not be imported from the remote tables.
+ </para>
+ </sect3>
</sect2>
<sect2>
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index 1b0962c..b685e16 100644
*** a/doc/src/sgml/ref/allfiles.sgml
--- b/doc/src/sgml/ref/allfiles.sgml
*************** Complete list of usable sgml source file
*** 131,136 ****
--- 131,137 ----
<!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 4a8cf38..46a20ef 100644
*** a/doc/src/sgml/ref/create_foreign_table.sgml
--- b/doc/src/sgml/ref/create_foreign_table.sgml
*************** SERVER film_server;
*** 231,236 ****
--- 231,237 ----
<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
index ...bdcc265 .
*** a/doc/src/sgml/ref/import_foreign_schema.sgml
--- b/doc/src/sgml/ref/import_foreign_schema.sgml
***************
*** 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 a6575f5..6ec1263 100644
*** a/doc/src/sgml/reference.sgml
--- b/doc/src/sgml/reference.sgml
***************
*** 159,164 ****
--- 159,165 ----
&explain;
&fetch;
&grant;
+ &importForeignSchema;
&insert;
&listen;
&load;
diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c
index 110fe00..754264e 100644
*** a/src/backend/commands/event_trigger.c
--- b/src/backend/commands/event_trigger.c
*************** check_ddl_tag(const char *tag)
*** 250,256 ****
pg_strcasecmp(tag, "REFRESH MATERIALIZED VIEW") == 0 ||
pg_strcasecmp(tag, "ALTER DEFAULT PRIVILEGES") == 0 ||
pg_strcasecmp(tag, "ALTER LARGE OBJECT") == 0 ||
! pg_strcasecmp(tag, "DROP OWNED") == 0)
return EVENT_TRIGGER_COMMAND_TAG_OK;
/*
--- 250,257 ----
pg_strcasecmp(tag, "REFRESH MATERIALIZED VIEW") == 0 ||
pg_strcasecmp(tag, "ALTER DEFAULT PRIVILEGES") == 0 ||
pg_strcasecmp(tag, "ALTER LARGE OBJECT") == 0 ||
! pg_strcasecmp(tag, "DROP OWNED") == 0 ||
! pg_strcasecmp(tag, "IMPORT FOREIGN SCHEMA") == 0)
return EVENT_TRIGGER_COMMAND_TAG_OK;
/*
diff --git a/src/backend/commands/foreigncmds.c b/src/backend/commands/foreigncmds.c
index 8ab9c43..ab4ed6c 100644
*** a/src/backend/commands/foreigncmds.c
--- b/src/backend/commands/foreigncmds.c
***************
*** 15,22 ****
#include "access/heapam.h"
#include "access/htup_details.h"
- #include "access/xact.h"
#include "access/reloptions.h"
#include "catalog/dependency.h"
#include "catalog/indexing.h"
#include "catalog/objectaccess.h"
--- 15,22 ----
#include "access/heapam.h"
#include "access/htup_details.h"
#include "access/reloptions.h"
+ #include "access/xact.h"
#include "catalog/dependency.h"
#include "catalog/indexing.h"
#include "catalog/objectaccess.h"
***************
*** 27,35 ****
--- 27,37 ----
#include "catalog/pg_type.h"
#include "catalog/pg_user_mapping.h"
#include "commands/defrem.h"
+ #include "foreign/fdwapi.h"
#include "foreign/foreign.h"
#include "miscadmin.h"
#include "parser/parse_func.h"
+ #include "tcop/utility.h"
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
***************
*** 37,42 ****
--- 39,54 ----
#include "utils/syscache.h"
+ typedef struct
+ {
+ char *tablename;
+ char *cmd;
+ } import_error_callback_arg;
+
+ /* Internal functions */
+ static void import_error_callback(void *arg);
+
+
/*
* Convert a DefElem list to the text array format that is used in
* pg_foreign_data_wrapper, pg_foreign_server, pg_user_mapping, and
*************** CreateForeignTable(CreateForeignTableStm
*** 1427,1429 ****
--- 1439,1571 ----
heap_close(ftrel, RowExclusiveLock);
}
+
+ /*
+ * Import a foreign schema
+ */
+ void
+ ImportForeignSchema(ImportForeignSchemaStmt *stmt)
+ {
+ ForeignServer *server;
+ ForeignDataWrapper *fdw;
+ FdwRoutine *fdw_routine;
+ AclResult aclresult;
+ List *cmd_list;
+ ListCell *lc;
+
+ /* Check that the foreign server exists and that we have USAGE on it */
+ server = GetForeignServerByName(stmt->server_name, false);
+ aclresult = pg_foreign_server_aclcheck(server->serverid, GetUserId(), ACL_USAGE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, ACL_KIND_FOREIGN_SERVER, server->servername);
+
+ /* Check that the schema exists and we have CREATE permissions on it */
+ (void) LookupCreationNamespace(stmt->local_schema);
+
+ /* Get the FDW and check it supports IMPORT */
+ fdw = GetForeignDataWrapper(server->fdwid);
+ if (!OidIsValid(fdw->fdwhandler))
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("foreign-data wrapper \"%s\" has no handler",
+ fdw->fdwname)));
+ fdw_routine = GetFdwRoutine(fdw->fdwhandler);
+ if (fdw_routine->ImportForeignSchema == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FDW_NO_SCHEMAS),
+ errmsg("foreign-data wrapper \"%s\" does not support IMPORT FOREIGN SCHEMA",
+ fdw->fdwname)));
+
+ /* Call FDW to get a list of commands */
+ cmd_list = fdw_routine->ImportForeignSchema(stmt, server->serverid);
+
+ /* Parse and execute each command */
+ foreach(lc, cmd_list)
+ {
+ char *cmd = (char *) lfirst(lc);
+ import_error_callback_arg callback_arg;
+ ErrorContextCallback sqlerrcontext;
+ List *raw_parsetree_list;
+ ListCell *lc2;
+
+ /*
+ * Setup error traceback support for ereport(). This is so that any
+ * error in the generated SQL will be displayed nicely.
+ */
+ callback_arg.tablename = NULL; /* not known yet */
+ callback_arg.cmd = cmd;
+ sqlerrcontext.callback = import_error_callback;
+ sqlerrcontext.arg = (void *) &callback_arg;
+ sqlerrcontext.previous = error_context_stack;
+ error_context_stack = &sqlerrcontext;
+
+ /*
+ * Parse the SQL string into a list of raw parse trees.
+ */
+ raw_parsetree_list = pg_parse_query(cmd);
+
+ /*
+ * Process each parse tree (we allow the FDW to put more than one
+ * command per string, though this isn't really advised).
+ */
+ foreach(lc2, raw_parsetree_list)
+ {
+ CreateForeignTableStmt *cstmt = lfirst(lc2);
+
+ /*
+ * Because we only allow CreateForeignTableStmt, we can skip parse
+ * analysis, rewrite, and planning steps here.
+ */
+ if (!IsA(cstmt, CreateForeignTableStmt))
+ elog(ERROR,
+ "foreign-data wrapper \"%s\" returned incorrect statement type %d",
+ fdw->fdwname, (int) nodeTag(cstmt));
+
+ /* Ignore commands for tables excluded by filter options */
+ if (!IsImportableForeignTable(cstmt->base.relation->relname, stmt))
+ continue;
+
+ /* Enable reporting of current table's name on error */
+ callback_arg.tablename = cstmt->base.relation->relname;
+
+ /* Ensure creation schema is the one given in IMPORT statement */
+ cstmt->base.relation->schemaname = pstrdup(stmt->local_schema);
+
+ /* Execute statement */
+ ProcessUtility((Node *) cstmt,
+ cmd,
+ PROCESS_UTILITY_SUBCOMMAND, NULL,
+ None_Receiver, NULL);
+
+ /* Be sure to advance the command counter between subcommands */
+ CommandCounterIncrement();
+
+ callback_arg.tablename = NULL;
+ }
+
+ error_context_stack = sqlerrcontext.previous;
+ }
+ }
+
+ /*
+ * error context callback to let us supply the failing SQL statement's text
+ */
+ static void
+ import_error_callback(void *arg)
+ {
+ import_error_callback_arg *callback_arg = (import_error_callback_arg *) arg;
+ int syntaxerrposition;
+
+ /* If it's a syntax error, convert to internal syntax error report */
+ syntaxerrposition = geterrposition();
+ if (syntaxerrposition > 0)
+ {
+ errposition(0);
+ internalerrposition(syntaxerrposition);
+ internalerrquery(callback_arg->cmd);
+ }
+
+ if (callback_arg->tablename)
+ errcontext("importing foreign table \"%s\"",
+ callback_arg->tablename);
+ }
diff --git a/src/backend/foreign/foreign.c b/src/backend/foreign/foreign.c
index 6d548b7..4f5f6ae 100644
*** a/src/backend/foreign/foreign.c
--- b/src/backend/foreign/foreign.c
*************** GetFdwRoutineForRelation(Relation relati
*** 400,405 ****
--- 400,446 ----
/*
+ * IsImportableForeignTable - filter table names for IMPORT FOREIGN SCHEMA
+ *
+ * Returns TRUE if given table name should be imported according to the
+ * statement's import filter options.
+ */
+ bool
+ IsImportableForeignTable(const char *tablename,
+ ImportForeignSchemaStmt *stmt)
+ {
+ ListCell *lc;
+
+ switch (stmt->list_type)
+ {
+ case FDW_IMPORT_SCHEMA_ALL:
+ return true;
+
+ case FDW_IMPORT_SCHEMA_LIMIT_TO:
+ foreach(lc, stmt->table_list)
+ {
+ RangeVar *rv = (RangeVar *) lfirst(lc);
+
+ if (strcmp(tablename, rv->relname) == 0)
+ return true;
+ }
+ return false;
+
+ case FDW_IMPORT_SCHEMA_EXCEPT:
+ foreach(lc, stmt->table_list)
+ {
+ RangeVar *rv = (RangeVar *) lfirst(lc);
+
+ if (strcmp(tablename, rv->relname) == 0)
+ return false;
+ }
+ return true;
+ }
+ return false; /* shouldn't get here */
+ }
+
+
+ /*
* deflist_to_tuplestore - Helper function to convert DefElem list to
* tuplestore usable in SRF.
*/
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 8d3d5a7..3088578 100644
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
*************** _copyCreateForeignTableStmt(const Create
*** 3567,3572 ****
--- 3567,3587 ----
return newnode;
}
+ static ImportForeignSchemaStmt *
+ _copyImportForeignSchemaStmt(const ImportForeignSchemaStmt *from)
+ {
+ ImportForeignSchemaStmt *newnode = makeNode(ImportForeignSchemaStmt);
+
+ COPY_STRING_FIELD(server_name);
+ COPY_STRING_FIELD(remote_schema);
+ COPY_STRING_FIELD(local_schema);
+ COPY_SCALAR_FIELD(list_type);
+ COPY_NODE_FIELD(table_list);
+ COPY_NODE_FIELD(options);
+
+ return newnode;
+ }
+
static CreateTrigStmt *
_copyCreateTrigStmt(const CreateTrigStmt *from)
{
*************** copyObject(const void *from)
*** 4477,4482 ****
--- 4492,4500 ----
case T_CreateForeignTableStmt:
retval = _copyCreateForeignTableStmt(from);
break;
+ case T_ImportForeignSchemaStmt:
+ retval = _copyImportForeignSchemaStmt(from);
+ break;
case T_CreateTrigStmt:
retval = _copyCreateTrigStmt(from);
break;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index e7b49f6..1b07db6 100644
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
*************** _equalCreateForeignTableStmt(const Creat
*** 1769,1774 ****
--- 1769,1787 ----
}
static bool
+ _equalImportForeignSchemaStmt(const ImportForeignSchemaStmt *a, const ImportForeignSchemaStmt *b)
+ {
+ COMPARE_STRING_FIELD(server_name);
+ COMPARE_STRING_FIELD(remote_schema);
+ COMPARE_STRING_FIELD(local_schema);
+ COMPARE_SCALAR_FIELD(list_type);
+ COMPARE_NODE_FIELD(table_list);
+ COMPARE_NODE_FIELD(options);
+
+ return true;
+ }
+
+ static bool
_equalCreateTrigStmt(const CreateTrigStmt *a, const CreateTrigStmt *b)
{
COMPARE_STRING_FIELD(trigname);
*************** equal(const void *a, const void *b)
*** 2943,2948 ****
--- 2956,2964 ----
case T_CreateForeignTableStmt:
retval = _equalCreateForeignTableStmt(a, b);
break;
+ case T_ImportForeignSchemaStmt:
+ retval = _equalImportForeignSchemaStmt(a, b);
+ break;
case T_CreateTrigStmt:
retval = _equalCreateTrigStmt(a, b);
break;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index c182212..9573a9b 100644
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
*************** _outCreateForeignTableStmt(StringInfo st
*** 2012,2017 ****
--- 2012,2030 ----
}
static void
+ _outImportForeignSchemaStmt(StringInfo str, const ImportForeignSchemaStmt *node)
+ {
+ WRITE_NODE_TYPE("IMPORTFOREIGNSCHEMASTMT");
+
+ WRITE_STRING_FIELD(server_name);
+ WRITE_STRING_FIELD(remote_schema);
+ WRITE_STRING_FIELD(local_schema);
+ WRITE_ENUM_FIELD(list_type, ImportForeignSchemaType);
+ WRITE_NODE_FIELD(table_list);
+ WRITE_NODE_FIELD(options);
+ }
+
+ static void
_outIndexStmt(StringInfo str, const IndexStmt *node)
{
WRITE_NODE_TYPE("INDEXSTMT");
*************** _outNode(StringInfo str, const void *obj
*** 3119,3124 ****
--- 3132,3140 ----
case T_CreateForeignTableStmt:
_outCreateForeignTableStmt(str, obj);
break;
+ case T_ImportForeignSchemaStmt:
+ _outImportForeignSchemaStmt(str, obj);
+ break;
case T_IndexStmt:
_outIndexStmt(str, obj);
break;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ba7d091..a113809 100644
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
*************** typedef struct PrivTarget
*** 111,116 ****
--- 111,123 ----
List *objs;
} PrivTarget;
+ /* Private struct for the result of import_qualification production */
+ typedef struct ImportQual
+ {
+ ImportForeignSchemaType type;
+ List *table_names;
+ } ImportQual;
+
/* ConstraintAttributeSpec yields an integer bitmask of these flags: */
#define CAS_NOT_DEFERRABLE 0x01
#define CAS_DEFERRABLE 0x02
*************** static Node *makeRecursiveViewSelect(cha
*** 212,217 ****
--- 219,225 ----
ResTarget *target;
struct PrivTarget *privtarget;
AccessPriv *accesspriv;
+ struct ImportQual *importqual;
InsertStmt *istmt;
VariableSetStmt *vsetstmt;
}
*************** static Node *makeRecursiveViewSelect(cha
*** 238,245 ****
DropAssertStmt DropTrigStmt DropRuleStmt DropCastStmt DropRoleStmt
DropUserStmt DropdbStmt DropTableSpaceStmt DropFdwStmt
DropForeignServerStmt DropUserMappingStmt ExplainStmt FetchStmt
! GrantStmt GrantRoleStmt IndexStmt InsertStmt ListenStmt LoadStmt
! LockStmt NotifyStmt ExplainableStmt PreparableStmt
CreateFunctionStmt AlterFunctionStmt ReindexStmt RemoveAggrStmt
RemoveFuncStmt RemoveOperStmt RenameStmt RevokeStmt RevokeRoleStmt
RuleActionStmt RuleActionStmtOrEmpty RuleStmt
--- 246,253 ----
DropAssertStmt DropTrigStmt DropRuleStmt DropCastStmt DropRoleStmt
DropUserStmt DropdbStmt DropTableSpaceStmt DropFdwStmt
DropForeignServerStmt DropUserMappingStmt ExplainStmt FetchStmt
! GrantStmt GrantRoleStmt ImportForeignSchemaStmt IndexStmt InsertStmt
! ListenStmt LoadStmt LockStmt NotifyStmt ExplainableStmt PreparableStmt
CreateFunctionStmt AlterFunctionStmt ReindexStmt RemoveAggrStmt
RemoveFuncStmt RemoveOperStmt RenameStmt RevokeStmt RevokeRoleStmt
RuleActionStmt RuleActionStmtOrEmpty RuleStmt
*************** static Node *makeRecursiveViewSelect(cha
*** 322,327 ****
--- 330,337 ----
%type <ival> defacl_privilege_target
%type <defelt> DefACLOption
%type <list> DefACLOptionList
+ %type <ival> import_qualification_type
+ %type <importqual> import_qualification
%type <list> stmtblock stmtmulti
OptTableElementList TableElementList OptInherit definition
*************** static Node *makeRecursiveViewSelect(cha
*** 556,562 ****
HANDLER HAVING HEADER_P HOLD HOUR_P
! IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P
INCLUDING INCREMENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P
INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER
INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
--- 566,572 ----
HANDLER HAVING HEADER_P HOLD HOUR_P
! IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P
INCLUDING INCREMENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P
INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER
INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
*************** stmt :
*** 802,807 ****
--- 812,818 ----
| FetchStmt
| GrantStmt
| GrantRoleStmt
+ | ImportForeignSchemaStmt
| IndexStmt
| InsertStmt
| ListenStmt
*************** AlterForeignTableStmt:
*** 4275,4280 ****
--- 4286,4337 ----
/*****************************************************************************
*
* QUERY:
+ * IMPORT FOREIGN SCHEMA remote_schema
+ * [ { LIMIT TO | EXCEPT } ( table_list ) ]
+ * FROM SERVER server_name INTO local_schema [ OPTIONS (...) ]
+ *
+ ****************************************************************************/
+
+ ImportForeignSchemaStmt:
+ IMPORT_P FOREIGN SCHEMA name import_qualification
+ FROM SERVER name INTO name create_generic_options
+ {
+ ImportForeignSchemaStmt *n = makeNode(ImportForeignSchemaStmt);
+ n->server_name = $8;
+ n->remote_schema = $4;
+ n->local_schema = $10;
+ n->list_type = $5->type;
+ n->table_list = $5->table_names;
+ n->options = $11;
+ $$ = (Node *) n;
+ }
+ ;
+
+ import_qualification_type:
+ LIMIT TO { $$ = FDW_IMPORT_SCHEMA_LIMIT_TO; }
+ | EXCEPT { $$ = FDW_IMPORT_SCHEMA_EXCEPT; }
+ ;
+
+ import_qualification:
+ import_qualification_type '(' relation_expr_list ')'
+ {
+ ImportQual *n = (ImportQual *) palloc(sizeof(ImportQual));
+ n->type = $1;
+ n->table_names = $3;
+ $$ = n;
+ }
+ | /*EMPTY*/
+ {
+ ImportQual *n = (ImportQual *) palloc(sizeof(ImportQual));
+ n->type = FDW_IMPORT_SCHEMA_ALL;
+ n->table_names = NIL;
+ $$ = n;
+ }
+ ;
+
+ /*****************************************************************************
+ *
+ * QUERY:
* CREATE USER MAPPING FOR auth_ident SERVER name [OPTIONS]
*
*****************************************************************************/
*************** unreserved_keyword:
*** 12909,12914 ****
--- 12966,12972 ----
| IMMEDIATE
| IMMUTABLE
| IMPLICIT_P
+ | IMPORT_P
| INCLUDING
| INCREMENT
| INDEX
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 3423898..07e0b98 100644
*** a/src/backend/tcop/utility.c
--- b/src/backend/tcop/utility.c
*************** check_xact_readonly(Node *parsetree)
*** 202,207 ****
--- 202,208 ----
case T_AlterTableSpaceOptionsStmt:
case T_AlterTableSpaceMoveStmt:
case T_CreateForeignTableStmt:
+ case T_ImportForeignSchemaStmt:
case T_SecLabelStmt:
PreventCommandIfReadOnly(CreateCommandTag(parsetree));
break;
*************** ProcessUtilitySlow(Node *parsetree,
*** 1196,1201 ****
--- 1197,1206 ----
RemoveUserMapping((DropUserMappingStmt *) parsetree);
break;
+ case T_ImportForeignSchemaStmt:
+ ImportForeignSchema((ImportForeignSchemaStmt *) parsetree);
+ break;
+
case T_CompositeTypeStmt: /* CREATE TYPE (composite) */
{
CompositeTypeStmt *stmt = (CompositeTypeStmt *) parsetree;
*************** CreateCommandTag(Node *parsetree)
*** 1853,1858 ****
--- 1858,1867 ----
tag = "CREATE FOREIGN TABLE";
break;
+ case T_ImportForeignSchemaStmt:
+ tag = "IMPORT FOREIGN SCHEMA";
+ break;
+
case T_DropStmt:
switch (((DropStmt *) parsetree)->removeType)
{
*************** GetCommandLogLevel(Node *parsetree)
*** 2518,2523 ****
--- 2527,2533 ----
case T_CreateUserMappingStmt:
case T_AlterUserMappingStmt:
case T_DropUserMappingStmt:
+ case T_ImportForeignSchemaStmt:
lev = LOGSTMT_DDL;
break;
diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h
index 5ec9374..0ebdbc1 100644
*** a/src/include/commands/defrem.h
--- b/src/include/commands/defrem.h
*************** extern Oid AlterUserMapping(AlterUserMap
*** 124,129 ****
--- 124,130 ----
extern Oid RemoveUserMapping(DropUserMappingStmt *stmt);
extern void RemoveUserMappingById(Oid umId);
extern void CreateForeignTable(CreateForeignTableStmt *stmt, Oid relid);
+ extern void ImportForeignSchema(ImportForeignSchemaStmt *stmt);
extern Datum transformGenericOptions(Oid catalogId,
Datum oldOptions,
List *options,
diff --git a/src/include/foreign/fdwapi.h b/src/include/foreign/fdwapi.h
index 1b735da..dc0a7fc 100644
*** a/src/include/foreign/fdwapi.h
--- b/src/include/foreign/fdwapi.h
*************** typedef bool (*AnalyzeForeignTable_funct
*** 100,105 ****
--- 100,108 ----
AcquireSampleRowsFunc *func,
BlockNumber *totalpages);
+ typedef List *(*ImportForeignSchema_function) (ImportForeignSchemaStmt *stmt,
+ Oid serverOid);
+
/*
* FdwRoutine is the struct returned by a foreign-data wrapper's handler
* function. It provides pointers to the callback functions needed by the
*************** typedef struct FdwRoutine
*** 144,149 ****
--- 147,155 ----
/* Support functions for ANALYZE */
AnalyzeForeignTable_function AnalyzeForeignTable;
+
+ /* Support functions for IMPORT FOREIGN SCHEMA */
+ ImportForeignSchema_function ImportForeignSchema;
} FdwRoutine;
*************** typedef struct FdwRoutine
*** 151,155 ****
--- 157,163 ----
extern FdwRoutine *GetFdwRoutine(Oid fdwhandler);
extern FdwRoutine *GetFdwRoutineByRelId(Oid relid);
extern FdwRoutine *GetFdwRoutineForRelation(Relation relation, bool makecopy);
+ extern bool IsImportableForeignTable(const char *tablename,
+ ImportForeignSchemaStmt *stmt);
#endif /* FDWAPI_H */
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 7b0088f..067c768 100644
*** a/src/include/nodes/nodes.h
--- b/src/include/nodes/nodes.h
*************** typedef enum NodeTag
*** 357,362 ****
--- 357,363 ----
T_AlterTableSpaceMoveStmt,
T_SecLabelStmt,
T_CreateForeignTableStmt,
+ T_ImportForeignSchemaStmt,
T_CreateExtensionStmt,
T_AlterExtensionStmt,
T_AlterExtensionContentsStmt,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ff126eb..8364bef 100644
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
*************** typedef struct AlterForeignServerStmt
*** 1791,1797 ****
} AlterForeignServerStmt;
/* ----------------------
! * Create FOREIGN TABLE Statements
* ----------------------
*/
--- 1791,1797 ----
} AlterForeignServerStmt;
/* ----------------------
! * Create FOREIGN TABLE Statement
* ----------------------
*/
*************** typedef struct DropUserMappingStmt
*** 1832,1837 ****
--- 1832,1860 ----
} DropUserMappingStmt;
/* ----------------------
+ * Import Foreign Schema Statement
+ * ----------------------
+ */
+
+ typedef enum ImportForeignSchemaType
+ {
+ FDW_IMPORT_SCHEMA_ALL, /* all relations wanted */
+ FDW_IMPORT_SCHEMA_LIMIT_TO, /* include only listed tables in import */
+ FDW_IMPORT_SCHEMA_EXCEPT /* exclude listed tables from import */
+ } ImportForeignSchemaType;
+
+ typedef struct ImportForeignSchemaStmt
+ {
+ NodeTag type;
+ char *server_name; /* FDW server name */
+ char *remote_schema; /* remote schema name to query */
+ char *local_schema; /* local schema to create objects in */
+ ImportForeignSchemaType list_type; /* type of table list */
+ List *table_list; /* List of RangeVar */
+ List *options; /* list of options to pass to FDW */
+ } ImportForeignSchemaStmt;
+
+ /* ----------------------
* Create TRIGGER Statement
* ----------------------
*/
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 04e9810..b52e507 100644
*** a/src/include/parser/kwlist.h
--- b/src/include/parser/kwlist.h
*************** PG_KEYWORD("ilike", ILIKE, TYPE_FUNC_NAM
*** 184,189 ****
--- 184,190 ----
PG_KEYWORD("immediate", IMMEDIATE, UNRESERVED_KEYWORD)
PG_KEYWORD("immutable", IMMUTABLE, UNRESERVED_KEYWORD)
PG_KEYWORD("implicit", IMPLICIT_P, UNRESERVED_KEYWORD)
+ PG_KEYWORD("import", IMPORT_P, UNRESERVED_KEYWORD)
PG_KEYWORD("in", IN_P, RESERVED_KEYWORD)
PG_KEYWORD("including", INCLUDING, UNRESERVED_KEYWORD)
PG_KEYWORD("increment", INCREMENT, UNRESERVED_KEYWORD)
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index ff203b2..e4dedb0 100644
*** a/src/test/regress/expected/foreign_data.out
--- b/src/test/regress/expected/foreign_data.out
*************** DROP TRIGGER trigtest_before_row ON fore
*** 1193,1198 ****
--- 1193,1208 ----
DROP TRIGGER trigtest_after_stmt ON foreign_schema.foreign_table_1;
DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1;
DROP FUNCTION dummy_trigger();
+ -- IMPORT FOREIGN SCHEMA
+ IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 INTO public; -- ERROR
+ ERROR: foreign-data wrapper "foo" has no handler
+ IMPORT FOREIGN SCHEMA s1 LIMIT TO (t1) FROM SERVER s9 INTO public; --ERROR
+ ERROR: foreign-data wrapper "foo" has no handler
+ IMPORT FOREIGN SCHEMA s1 EXCEPT (t1) FROM SERVER s9 INTO public; -- ERROR
+ ERROR: foreign-data wrapper "foo" has no handler
+ IMPORT FOREIGN SCHEMA s1 EXCEPT (t1, t2) FROM SERVER s9 INTO public
+ OPTIONS (option1 'value1', option2 'value2'); -- ERROR
+ ERROR: foreign-data wrapper "foo" has no handler
-- DROP FOREIGN TABLE
DROP FOREIGN TABLE no_table; -- ERROR
ERROR: foreign table "no_table" does not exist
diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql
index 0f0869e..de9dbc8 100644
*** a/src/test/regress/sql/foreign_data.sql
--- b/src/test/regress/sql/foreign_data.sql
*************** DROP TRIGGER trigtest_after_row ON forei
*** 514,519 ****
--- 514,526 ----
DROP FUNCTION dummy_trigger();
+ -- IMPORT FOREIGN SCHEMA
+ IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 INTO public; -- ERROR
+ IMPORT FOREIGN SCHEMA s1 LIMIT TO (t1) FROM SERVER s9 INTO public; --ERROR
+ IMPORT FOREIGN SCHEMA s1 EXCEPT (t1) FROM SERVER s9 INTO public; -- ERROR
+ IMPORT FOREIGN SCHEMA s1 EXCEPT (t1, t2) FROM SERVER s9 INTO public
+ OPTIONS (option1 'value1', option2 'value2'); -- ERROR
+
-- DROP FOREIGN TABLE
DROP FOREIGN TABLE no_table; -- ERROR
DROP FOREIGN TABLE IF EXISTS no_table;
pgsql-hackers by date: