From 59efda3e50ca4de6a9d5aa4491464e22b6329b1e Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 10 Jul 2014 15:01:31 -0400 Subject: Implement IMPORT FOREIGN SCHEMA. This command provides an automated way to create foreign table definitions that match remote tables, thereby reducing tedium and chances for error. In this patch, we provide the necessary core-server infrastructure and implement the feature fully in the postgres_fdw foreign-data wrapper. Other wrappers will throw a "feature not supported" error until/unless they are updated. Ronan Dunklau and Michael Paquier, additional work by me --- doc/src/sgml/ddl.sgml | 5 +- doc/src/sgml/event-trigger.sgml | 6 + doc/src/sgml/fdwhandler.sgml | 60 ++++++++++ doc/src/sgml/postgres-fdw.sgml | 75 ++++++++++++- doc/src/sgml/ref/allfiles.sgml | 1 + doc/src/sgml/ref/create_foreign_table.sgml | 1 + doc/src/sgml/ref/import_foreign_schema.sgml | 168 ++++++++++++++++++++++++++++ doc/src/sgml/reference.sgml | 1 + 8 files changed, 312 insertions(+), 5 deletions(-) create mode 100644 doc/src/sgml/ref/import_foreign_schema.sgml (limited to 'doc/src') diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 8ace8bd3a25..3b7fff4846c 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/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml index e5b9e661853..3db8ef1a132 100644 --- a/doc/src/sgml/event-trigger.sgml +++ b/doc/src/sgml/event-trigger.sgml @@ -603,6 +603,12 @@ X X + + IMPORT FOREIGN SCHEMA + X + X + - + SELECT INTO X diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml index 6b5c8b7e97b..5fd8d6fbbe9 100644 --- a/doc/src/sgml/fdwhandler.sgml +++ b/doc/src/sgml/fdwhandler.sgml @@ -696,6 +696,66 @@ AcquireSampleRowsFunc (Relation relation, int elevel, + + FDW Routines For <command>IMPORT FOREIGN SCHEMA</> + + + +List * +ImportForeignSchema (ImportForeignSchemaStmt *stmt, Oid serverOid); + + + Obtain a list of foreign table creation commands. This function is + called when executing , 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. + These strings will be parsed and executed by the core server. + + + + Within the ImportForeignSchemaStmt struct, + remote_schema is the name of the remote schema from + which tables are to be imported. + list_type identifies how to filter table names: + FDW_IMPORT_SCHEMA_ALL means that all tables in the remote + schema should be imported (in this case table_list is + empty), FDW_IMPORT_SCHEMA_LIMIT_TO means to include only + tables listed in table_list, + and FDW_IMPORT_SCHEMA_EXCEPT means to exclude the tables + listed in table_list. + 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 + 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. + + + + The FDW may ignore the local_schema field of + the ImportForeignSchemaStmt, because the core server + will automatically insert that name into the parsed CREATE + FOREIGN TABLE commands. + + + + The FDW does not have to concern itself with implementing the filtering + specified by list_type and 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 IsImportableForeignTable() may be + useful to test whether a given foreign-table name will pass the filter. + + + + If the FDW does not support importing table definitions, the + ImportForeignSchema pointer can be set to NULL. + + + + diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml index e6f6e205815..43adb61455d 100644 --- a/doc/src/sgml/postgres-fdw.sgml +++ b/doc/src/sgml/postgres-fdw.sgml @@ -49,7 +49,8 @@ - Create a foreign table, using , + Create a foreign table, using + or , 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 @@ -99,7 +100,7 @@ user and password (specify these - for a user mapping, instead) + in a user mapping, instead) @@ -291,6 +292,72 @@ + + + Importing Options + + + postgres_fdw is able to import foreign table definitions + using . 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. + + + + Importing behavior can be customized with the following options + (given in the IMPORT FOREIGN SCHEMA command): + + + + + import_collate + + + This option controls whether column COLLATE options + are included in the definitions of foreign tables imported + from a foreign server. The default is true. You might + need to turn this off if the remote server has a different set of + collation names than the local server does, which is likely to be the + case if it's running on a different operating system. + + + + + import_default + + + This option controls whether column DEFAULT expressions + are included in the definitions of foreign tables imported + from a foreign server. The default is false. If you + enable this option, be wary of defaults that might get computed + differently on the local server than they would be on the remote + server; nextval() is a common source of problems. + The IMPORT will fail altogether if an imported default + expression uses a function or operator that does not exist locally. + + + + + import_not_null + + + This option controls whether column NOT NULL + constraints are included in the definitions of foreign tables imported + from a foreign server. The default is true. + + + + + + + Note that constraints other than NOT NULL will never be + imported from the remote tables, since PostgreSQL + does not support any other type of constraint on a foreign table. + Checking other types of constraints is always left to the remote server. + + @@ -422,7 +489,7 @@ CREATE USER MAPPING FOR local_user CREATE FOREIGN TABLE foreign_table ( - id serial NOT NULL, + id integer NOT NULL, data text ) SERVER foreign_server @@ -434,6 +501,8 @@ CREATE FOREIGN TABLE foreign_table ( Column names must match as well, unless you attach column_name options to the individual columns to show how they are named in the remote table. + In many cases, use of is + preferable to constructing foreign table definitions manually. diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index 1b0962c253d..b685e16a0fa 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -131,6 +131,7 @@ Complete list of usable sgml source files in this directory. + diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml index 4a8cf3889c2..46a20eff14f 100644 --- a/doc/src/sgml/ref/create_foreign_table.sgml +++ b/doc/src/sgml/ref/create_foreign_table.sgml @@ -231,6 +231,7 @@ SERVER film_server; + 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 00000000000..bdcc26558ac --- /dev/null +++ b/doc/src/sgml/ref/import_foreign_schema.sgml @@ -0,0 +1,168 @@ + + + + + IMPORT FOREIGN SCHEMA + + + + IMPORT FOREIGN SCHEMA + 7 + SQL - Language Statements + + + + IMPORT FOREIGN SCHEMA + import table definitions from a foreign server + + + + +IMPORT FOREIGN SCHEMA remote_schema +[ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ] +FROM SERVER server_name +INTO local_schema +[ OPTIONS ( option 'value' [, ... ] ) ] + + + + + Description + + + IMPORT FOREIGN SCHEMA 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. + + + + 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. + + + + To use IMPORT FOREIGN SCHEMA, the user must have + USAGE privilege on the foreign server, as well as + CREATE privilege on the target schema. + + + + + Parameters + + + + + remote_schema + + + The remote schema to import from. The specific meaning of a remote schema + depends on the foreign data wrapper in use. + + + + + + LIMIT TO ( table_name [, ...] ) + + + Import only foreign tables matching one of the given table names. + Other tables existing in the foreign schema will be ignored. + + + + + + EXCEPT ( table_name [, ...] ) + + + Exclude specified foreign tables from the import. All tables + existing in the foreign schema will be imported except the + ones listed here. + + + + + + server_name + + + The foreign server to import from. + + + + + + local_schema + + + The schema in which the imported foreign tables will be created. + + + + + + OPTIONS ( option 'value' [, ...] ) + + + Options to be used during the import. + The allowed option names and values are specific to each foreign + data wrapper. + + + + + + + + Examples + + + Import table definitions from a remote schema foreign_films + on server film_server, creating the foreign tables in + local schema films: + + +IMPORT FOREIGN SCHEMA foreign_films + FROM SERVER film_server INTO films; + + + + + As above, but import only the two tables actors and + directors (if they exist): + + +IMPORT FOREIGN SCHEMA foreign_films LIMIT TO (actors, directors) + FROM SERVER film_server INTO films; + + + + + + + Compatibility + + + The IMPORT FOREIGN SCHEMA command conforms to the + SQL standard, except that the OPTIONS + clause is a PostgreSQL extension. + + + + + + See Also + + + + + + + diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index a6575f52ac0..6ec126381c3 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -159,6 +159,7 @@ &explain; &fetch; &grant; + &importForeignSchema; &insert; &listen; &load; -- cgit v1.2.3