aboutsummaryrefslogtreecommitdiff
path: root/doc/src
diff options
context:
space:
mode:
Diffstat (limited to 'doc/src')
-rw-r--r--doc/src/sgml/ddl.sgml5
-rw-r--r--doc/src/sgml/event-trigger.sgml6
-rw-r--r--doc/src/sgml/fdwhandler.sgml60
-rw-r--r--doc/src/sgml/postgres-fdw.sgml75
-rw-r--r--doc/src/sgml/ref/allfiles.sgml1
-rw-r--r--doc/src/sgml/ref/create_foreign_table.sgml1
-rw-r--r--doc/src/sgml/ref/import_foreign_schema.sgml168
-rw-r--r--doc/src/sgml/reference.sgml1
8 files changed, 312 insertions, 5 deletions
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
<xref linkend="sql-createforeigndatawrapper">,
<xref linkend="sql-createserver">,
- <xref linkend="sql-createusermapping">, and
- <xref linkend="sql-createforeigntable">.
+ <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 e5b9e661853..3db8ef1a132 100644
--- a/doc/src/sgml/event-trigger.sgml
+++ b/doc/src/sgml/event-trigger.sgml
@@ -604,6 +604,12 @@
<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 6b5c8b7e97b..5fd8d6fbbe9 100644
--- a/doc/src/sgml/fdwhandler.sgml
+++ b/doc/src/sgml/fdwhandler.sgml
@@ -696,6 +696,66 @@ AcquireSampleRowsFunc (Relation relation, int elevel,
</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 <xref linkend="sql-importforeignschema">, 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 <xref linkend="sql-createforeigntable"> 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 e6f6e205815..43adb61455d 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -49,7 +49,8 @@
</listitem>
<listitem>
<para>
- Create a foreign table, using <xref linkend="sql-createforeigntable">,
+ 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
@@ -99,7 +100,7 @@
<listitem>
<para>
<literal>user</literal> and <literal>password</literal> (specify these
- for a user mapping, instead)
+ in a user mapping, instead)
</para>
</listitem>
<listitem>
@@ -291,6 +292,72 @@
</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_collate</literal></term>
+ <listitem>
+ <para>
+ This option controls whether column <literal>COLLATE</> options
+ are included in the definitions of foreign tables imported
+ from a foreign server. The default is <literal>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.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>import_default</literal></term>
+ <listitem>
+ <para>
+ This option controls whether column <literal>DEFAULT</> expressions
+ are included in the definitions of foreign tables imported
+ from a foreign server. The default is <literal>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; <function>nextval()</> is a common source of problems.
+ The <command>IMPORT</> will fail altogether if an imported default
+ expression uses a function or operator that does not exist locally.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>import_not_null</literal></term>
+ <listitem>
+ <para>
+ This option controls whether column <literal>NOT NULL</>
+ constraints are included in the definitions of foreign tables imported
+ from a foreign server. The default is <literal>true</>.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ <para>
+ Note that constraints other than <literal>NOT NULL</> will never be
+ imported from the remote tables, since <productname>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.
+ </para>
+ </sect3>
</sect2>
<sect2>
@@ -422,7 +489,7 @@ CREATE USER MAPPING FOR local_user
<programlisting>
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 <literal>column_name</>
options to the individual columns to show how they are named in the remote
table.
+ In many cases, use of <xref linkend="sql-importforeignschema"> is
+ preferable to constructing foreign table definitions manually.
</para>
</sect2>
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.
<!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 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;
<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
new file mode 100644
index 00000000000..bdcc26558ac
--- /dev/null
+++ b/doc/src/sgml/ref/import_foreign_schema.sgml
@@ -0,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 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;
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-30 20:33:48 +0000