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 @@ -3069,8 +3069,9 @@ ANALYZE measurement; For additional information, see , , - , and - . + , + , and + . diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml index 6b5c8b7..4e4b2b1 100644 --- a/doc/src/sgml/fdwhandler.sgml +++ b/doc/src/sgml/fdwhandler.sgml @@ -94,7 +94,24 @@ src/include/foreign/fdwapi.h, which see for additional details. - + + FDW Routines For Using Foreign Catalogs + +static List * +postgresImportForeignSchema(ForeignServer *server, + ImportForeignSchemaStmt * parsetree) + + + Import the tables defined in a remote "schema" in a local schema by name. + This is called when issuing an IMPORT FOREIGN SCHEMA + command. + The server is the foreign server used in + the command, the parsetree is the parsed representation of the + command. + The routine must return a List of CreateForeignTableStmt. + Those statements are then executed to create the new tables in the schema. + + FDW Routines For Scanning Foreign Tables diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index 1b0962c..aa95d71 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -132,6 +132,7 @@ Complete list of usable sgml source files in this directory. + 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 0000000..ec3a188 --- /dev/null +++ b/doc/src/sgml/ref/import_foreign_schema.sgml @@ -0,0 +1,90 @@ + + + + + IMPORT FOREIGN SCHEMA + + + + IMPORT FOREIGN SCHEMA + 7 + SQL - Language Statements + + + + IMPORT FOREIGN SCHEMA + import tables from a foreign schema + + + + +IMPORT FOREIGN SCHEMA schema_name +FROM SERVER server_name +[ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ] +INTO local_schema + + + + + Description + + IMPORT FOREIGN SCHEMA imports tables from a remote schema, using a server, + in a local schema in the current database. The tables will be owned by the user issuing the + command. + The tables are imported with the correct options and columns definitions. + The list of tables to import can optionnaly be limited to an explicitly defined subset (LIMIT TO clause) + or by excluding some tables (EXCEPT clause). + + + To be able to import foreign tables in a local schema, you must have USAGE + privilege on the foreign server, as well as CREATE usage on the local schema. + + + + Parameters + + + schema_name + + + The remote schema to import. + The specific meaning of a remote schema depends on the Foreign Data Wrapper implementation. + + + + + + + server_name + + + The server to import from. + + + + + + + local_schema + + + The schema in which the imported foreign tables will be created. + + + + + + + table_name + + + A list of table names that should either be excluded from the import process (EXCEPT clause) or that should be the only ones to be imported (LIMIT TO clause). + + + + + + diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index a6575f5..dbe1ecb 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -160,6 +160,7 @@ &fetch; &grant; &insert; + &importForeignSchema; &listen; &load; &lock; diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c index 96f926c..61398e6 100644 --- a/src/backend/commands/event_trigger.c +++ b/src/backend/commands/event_trigger.c @@ -250,7 +250,8 @@ check_ddl_tag(const char *tag) 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, "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..28f43a7 100644 --- a/src/backend/commands/foreigncmds.c +++ b/src/backend/commands/foreigncmds.c @@ -27,7 +27,9 @@ #include "catalog/pg_type.h" #include "catalog/pg_user_mapping.h" #include "commands/defrem.h" +#include "commands/tablecmds.h" #include "foreign/foreign.h" +#include "foreign/fdwapi.h" #include "miscadmin.h" #include "parser/parse_func.h" #include "utils/acl.h" @@ -1427,3 +1429,55 @@ CreateForeignTable(CreateForeignTableStmt *stmt, Oid relid) heap_close(ftrel, RowExclusiveLock); } + +/* + * Import a foreign schema + */ +void +ImportForeignSchema(ImportForeignSchemaStmt * stmt) +{ + Oid ownerId; + ForeignDataWrapper *fdw; + ForeignServer *server; + FdwRoutine *fdw_routine; + AclResult aclresult; + List *table_list = NULL; + ListCell *lc; + char * local_schema = strdup(stmt->local_schema); + + ownerId = GetUserId(); + server = GetForeignServerByName(stmt->servername, false); + aclresult = pg_foreign_server_aclcheck(server->serverid, ownerId, ACL_USAGE); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, ACL_KIND_FOREIGN_SERVER, server->servername); + + /* Check the permissions on the schema */ + LookupCreationNamespace(local_schema); + + 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("This FDW does not support schema importation"))); + } + table_list = fdw_routine->ImportForeignSchema(server, stmt); + foreach(lc, table_list) + { + CreateForeignTableStmt *create_stmt = lfirst(lc); + Oid relOid = DefineRelation((CreateStmt *) create_stmt, + RELKIND_FOREIGN_TABLE, + InvalidOid); + /* Override whatever the fdw set for the schema */ + create_stmt->base.relation->schemaname = local_schema; + CreateForeignTable(create_stmt, relOid); + } +} diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 7b9895d..aa10f1d 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -235,8 +235,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); DropAssertStmt DropTrigStmt DropRuleStmt DropCastStmt DropRoleStmt DropUserStmt DropdbStmt DropTableSpaceStmt DropFdwStmt DropForeignServerStmt DropUserMappingStmt ExplainStmt FetchStmt - GrantStmt GrantRoleStmt IndexStmt InsertStmt ListenStmt LoadStmt - LockStmt NotifyStmt ExplainableStmt PreparableStmt + GrantStmt GrantRoleStmt IndexStmt ImportForeignSchemaStmt InsertStmt + ListenStmt LoadStmt LockStmt NotifyStmt ExplainableStmt PreparableStmt CreateFunctionStmt AlterFunctionStmt ReindexStmt RemoveAggrStmt RemoveFuncStmt RemoveOperStmt RenameStmt RevokeStmt RevokeRoleStmt RuleActionStmt RuleActionStmtOrEmpty RuleStmt @@ -319,6 +319,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type defacl_privilege_target %type DefACLOption %type DefACLOptionList +%type OptImportForeignSchemaRestriction +%type ImportForeignSchemaRestrictionType %type stmtblock stmtmulti OptTableElementList TableElementList OptInherit definition @@ -552,7 +554,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); HANDLER HAVING HEADER_P HOLD HOUR_P - IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P + IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P IMPORT 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 @@ -798,6 +800,7 @@ stmt : | FetchStmt | GrantStmt | GrantRoleStmt + | ImportForeignSchemaStmt | IndexStmt | InsertStmt | ListenStmt @@ -1287,6 +1290,40 @@ schema_stmt: ; +/* + * IMPORT FOREIGN SCHEMA statement + */ +ImportForeignSchemaStmt: + IMPORT FOREIGN SCHEMA name FROM SERVER name + OptImportForeignSchemaRestriction INTO name { + ImportForeignSchemaStmt *n = makeNode(ImportForeignSchemaStmt); + n->remote_schema = $4; + n->servername = $7; + n->restriction = (ImportForeignSchemaRestriction * ) $8; + n->local_schema = $10; + $$ = (Node *) n; + }; + +ImportForeignSchemaRestrictionType: + LIMIT TO { $$ = IMPORT_LIMIT_TO; } + | EXCEPT { $$ = IMPORT_EXCEPT; }; + +OptImportForeignSchemaRestriction: + ImportForeignSchemaRestrictionType '(' relation_expr_list ')' { + ImportForeignSchemaRestriction * restriction = makeNode(ImportForeignSchemaRestriction); + restriction->restriction_type = $1; + restriction->table_list = $3; + $$ = (Node *) restriction; + } + | /*EMPTY*/ { + ImportForeignSchemaRestriction * restriction = makeNode(ImportForeignSchemaRestriction); + restriction->restriction_type = IMPORT_ALL; + restriction->table_list = NULL; + $$ = (Node *) restriction; + }; + + + /***************************************************************************** * * Set PG internal variable @@ -2706,6 +2743,7 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')' } ; + /* * Redundancy here is needed to avoid shift/reduce conflicts, * since TEMP is not a reserved word. See also OptTempTableName. @@ -12894,6 +12932,7 @@ unreserved_keyword: | IMMEDIATE | IMMUTABLE | IMPLICIT_P + | IMPORT | INCLUDING | INCREMENT | INDEX diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 3423898..41c5889 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -202,6 +202,7 @@ check_xact_readonly(Node *parsetree) case T_AlterTableSpaceOptionsStmt: case T_AlterTableSpaceMoveStmt: case T_CreateForeignTableStmt: + case T_ImportForeignSchemaStmt: case T_SecLabelStmt: PreventCommandIfReadOnly(CreateCommandTag(parsetree)); break; @@ -1316,6 +1317,10 @@ ProcessUtilitySlow(Node *parsetree, ExecAlterDefaultPrivilegesStmt((AlterDefaultPrivilegesStmt *) parsetree); break; + case T_ImportForeignSchemaStmt: + ImportForeignSchema((ImportForeignSchemaStmt *) parsetree); + break; + default: elog(ERROR, "unrecognized node type: %d", (int) nodeTag(parsetree)); @@ -1853,6 +1858,10 @@ CreateCommandTag(Node *parsetree) tag = "CREATE FOREIGN TABLE"; break; + case T_ImportForeignSchemaStmt: + tag = "IMPORT FOREIGN SCHEMA"; + break; + case T_DropStmt: switch (((DropStmt *) parsetree)->removeType) { @@ -2518,6 +2527,7 @@ GetCommandLogLevel(Node *parsetree) case T_CreateUserMappingStmt: case T_AlterUserMappingStmt: case T_DropUserMappingStmt: + case T_ImportForeignSchemaStmt: lev = LOGSTMT_DDL; break; diff --git a/src/include/catalog/pg_type.h b/src/include/catalog/pg_type.h index a8abc0f..cbb81ba 100644 --- a/src/include/catalog/pg_type.h +++ b/src/include/catalog/pg_type.h @@ -442,9 +442,11 @@ DESCR("network IP address/netmask, network address"); /* OIDS 1000 - 1099 */ DATA(insert OID = 1000 ( _bool PGNSP PGUID -1 f b A f t \054 0 16 0 array_in array_out array_recv array_send - - array_typanalyze i x f 0 -1 0 0 _null_ _null_ _null_ )); +#define BOOLARRAYOID 1000 DATA(insert OID = 1001 ( _bytea PGNSP PGUID -1 f b A f t \054 0 17 0 array_in array_out array_recv array_send - - array_typanalyze i x f 0 -1 0 0 _null_ _null_ _null_ )); DATA(insert OID = 1002 ( _char PGNSP PGUID -1 f b A f t \054 0 18 0 array_in array_out array_recv array_send - - array_typanalyze i x f 0 -1 0 0 _null_ _null_ _null_ )); DATA(insert OID = 1003 ( _name PGNSP PGUID -1 f b A f t \054 0 19 0 array_in array_out array_recv array_send - - array_typanalyze i x f 0 -1 0 0 _null_ _null_ _null_ )); +#define NAMEARRAYOID 1003 DATA(insert OID = 1005 ( _int2 PGNSP PGUID -1 f b A f t \054 0 21 0 array_in array_out array_recv array_send - - array_typanalyze i x f 0 -1 0 0 _null_ _null_ _null_ )); #define INT2ARRAYOID 1005 DATA(insert OID = 1006 ( _int2vector PGNSP PGUID -1 f b A f t \054 0 22 0 array_in array_out array_recv array_send - - array_typanalyze i x f 0 -1 0 0 _null_ _null_ _null_ )); diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h index 5ec9374..ea7967f 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -124,6 +124,7 @@ extern Oid AlterUserMapping(AlterUserMappingStmt *stmt); 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..adc9569 100644 --- a/src/include/foreign/fdwapi.h +++ b/src/include/foreign/fdwapi.h @@ -14,6 +14,7 @@ #include "nodes/execnodes.h" #include "nodes/relation.h" +#include "foreign/foreign.h" /* To avoid including explain.h here, reference ExplainState thus: */ struct ExplainState; @@ -100,6 +101,10 @@ typedef bool (*AnalyzeForeignTable_function) (Relation relation, AcquireSampleRowsFunc *func, BlockNumber *totalpages); +typedef List *(*ImportForeignSchema_function) (ForeignServer *server, + ImportForeignSchemaStmt * parsetree); + + /* * FdwRoutine is the struct returned by a foreign-data wrapper's handler * function. It provides pointers to the callback functions needed by the @@ -144,6 +149,9 @@ typedef struct FdwRoutine /* Support functions for ANALYZE */ AnalyzeForeignTable_function AnalyzeForeignTable; + + /* Support functions for IMPORT FOREIGN SCHEMA */ + ImportForeignSchema_function ImportForeignSchema; } FdwRoutine; diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index bc58e16..38977ac 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -365,6 +365,7 @@ typedef enum NodeTag T_RefreshMatViewStmt, T_ReplicaIdentityStmt, T_AlterSystemStmt, + T_ImportForeignSchemaStmt, /* * TAGS FOR PARSE TREE NODES (parsenodes.h) @@ -406,6 +407,8 @@ typedef enum NodeTag T_XmlSerialize, T_WithClause, T_CommonTableExpr, + T_ImportForeignSchemaRestriction, + T_ImportForeignSchemaRestrictionType, /* * TAGS FOR REPLICATION GRAMMAR PARSE NODES (replnodes.h) diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 7e560a1..e08d7a7 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1249,6 +1249,34 @@ typedef enum DropBehavior } DropBehavior; /* ---------------------- + * Import Foreign Schema statement + * ---------------------- + */ + +typedef enum ImportForeignSchemaRestrictionType +{ + IMPORT_ALL, + IMPORT_LIMIT_TO, + IMPORT_EXCEPT +} ImportForeignSchemaRestrictionType; + +typedef struct ImportForeignSchemaRestriction +{ + ImportForeignSchemaRestrictionType restriction_type; + List *table_list; +} ImportForeignSchemaRestriction; + +typedef struct ImportForeignSchemaStmt +{ + NodeTag type; + char *remote_schema; + char *servername; + char *local_schema; + ImportForeignSchemaRestriction * restriction; + List *table_names; +} ImportForeignSchemaStmt; + +/* ---------------------- * Alter Table * ---------------------- */ diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 61fae22..74a94e4 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -184,6 +184,7 @@ PG_KEYWORD("ilike", ILIKE, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("immediate", IMMEDIATE, UNRESERVED_KEYWORD) PG_KEYWORD("immutable", IMMUTABLE, UNRESERVED_KEYWORD) PG_KEYWORD("implicit", IMPLICIT_P, UNRESERVED_KEYWORD) +PG_KEYWORD("import", IMPORT, 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..8c6e056 100644 --- a/src/test/regress/expected/foreign_data.out +++ b/src/test/regress/expected/foreign_data.out @@ -1193,6 +1193,13 @@ DROP TRIGGER trigtest_before_row ON foreign_schema.foreign_table_1; 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 FROM SERVER s9 LIMIT TO (t1) INTO public; --ERROR +ERROR: foreign-data wrapper "foo" has no handler +IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 EXCEPT (t1) INTO public; -- 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..55b102b 100644 --- a/src/test/regress/sql/foreign_data.sql +++ b/src/test/regress/sql/foreign_data.sql @@ -514,6 +514,13 @@ 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 +IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 LIMIT TO (t1) INTO public; --ERROR +IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 EXCEPT (t1) INTO public; -- ERROR + + -- DROP FOREIGN TABLE DROP FOREIGN TABLE no_table; -- ERROR DROP FOREIGN TABLE IF EXISTS no_table;