From 08cad9f93a15495ffa168f62fde885153fcb0885 Mon Sep 17 00:00:00 2001 From: Shigeru Hanada Date: Tue, 28 Sep 2010 18:04:18 +0900 Subject: [PATCH] Import foreign_table patch. --- doc/src/sgml/catalogs.sgml | 64 +- doc/src/sgml/information_schema.sgml | 131 ++++- doc/src/sgml/ref/allfiles.sgml | 3 + .../sgml/ref/alter_default_privileges.sgml | 4 +- doc/src/sgml/ref/alter_foreign_table.sgml | 556 ++++++++++++++++++ doc/src/sgml/ref/comment.sgml | 2 + doc/src/sgml/ref/create_foreign_table.sgml | 519 ++++++++++++++++ doc/src/sgml/ref/create_sequence.sgml | 2 +- doc/src/sgml/ref/create_table.sgml | 4 +- doc/src/sgml/ref/create_view.sgml | 2 +- doc/src/sgml/ref/drop_foreign_table.sgml | 123 ++++ doc/src/sgml/ref/pg_dump.sgml | 6 +- doc/src/sgml/ref/psql-ref.sgml | 22 +- doc/src/sgml/reference.sgml | 3 + src/backend/access/common/reloptions.c | 7 + src/backend/catalog/Makefile | 1 + src/backend/catalog/aclchk.c | 113 +++- src/backend/catalog/dependency.c | 10 + src/backend/catalog/heap.c | 34 +- src/backend/catalog/information_schema.sql | 73 ++- src/backend/catalog/objectaddress.c | 8 + src/backend/commands/alter.c | 3 + src/backend/commands/comment.c | 8 +- src/backend/commands/foreigncmds.c | 151 +++++ src/backend/commands/seclabel.c | 1 + src/backend/commands/tablecmds.c | 142 +++-- src/backend/executor/execMain.c | 6 + src/backend/foreign/foreign.c | 60 ++ src/backend/nodes/copyfuncs.c | 22 + src/backend/nodes/equalfuncs.c | 16 + src/backend/optimizer/path/allpaths.c | 8 + src/backend/parser/gram.y | 532 +++++++++++++++-- src/backend/parser/parse_utilcmd.c | 7 +- src/backend/rewrite/rewriteDefine.c | 13 +- src/backend/tcop/utility.c | 33 ++ src/backend/utils/adt/acl.c | 4 + src/backend/utils/cache/syscache.c | 12 + src/bin/pg_dump/common.c | 2 +- src/bin/pg_dump/dumputils.c | 2 + src/bin/pg_dump/pg_backup_archiver.c | 2 + src/bin/pg_dump/pg_dump.c | 93 ++- src/bin/psql/command.c | 5 +- src/bin/psql/describe.c | 111 +++- src/bin/psql/describe.h | 3 + src/bin/psql/help.c | 1 + src/include/catalog/dependency.h | 1 + src/include/catalog/indexing.h | 3 + src/include/catalog/pg_class.h | 1 + src/include/catalog/pg_foreign_table.h | 53 ++ src/include/commands/defrem.h | 1 + src/include/foreign/foreign.h | 17 +- src/include/nodes/nodes.h | 1 + src/include/nodes/parsenodes.h | 23 +- src/include/utils/acl.h | 2 + src/include/utils/syscache.h | 1 + src/test/regress/expected/alter_table.out | 6 +- src/test/regress/expected/foreign_data.out | 169 ++++++ src/test/regress/expected/sanity_check.out | 3 +- src/test/regress/expected/type_sanity.out | 2 +- src/test/regress/sql/foreign_data.sql | 84 +++ src/test/regress/sql/type_sanity.sql | 2 +- 61 files changed, 3130 insertions(+), 163 deletions(-) create mode 100644 doc/src/sgml/ref/alter_foreign_table.sgml create mode 100644 doc/src/sgml/ref/create_foreign_table.sgml create mode 100644 doc/src/sgml/ref/drop_foreign_table.sgml create mode 100644 src/include/catalog/pg_foreign_table.h diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 8e4081cb33..3eeecb9385 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -148,6 +148,11 @@ foreign server definitions + + pg_foreign_table + additional foreign table information + + pg_index additional index information @@ -1582,8 +1587,8 @@ r = ordinary table, i = index, S = sequence, v = view, c = - composite type, t = TOAST - table + composite type, t = TOAST table, + f = foiregn table @@ -2834,6 +2839,61 @@ + + <structname>pg_foreign_table</structname> + + + pg_foreign_table + + + + The catalog pg_foreign_table contains part + of the information about foreign tables. + The rest is mostly in pg_class. + + + + <structname>pg_foreign_table</> Columns + + + + + Name + Type + References + Description + + + + + + ftrelid + oid + pg_class.oid + The OID of the pg_class entry for this foreign table + + + + ftserver + oid + pg_foreign_server.oid + The OID of the foreign server for this foreign table + + + + srvoptions + text[] + + + Foreign table specific options, as keyword=value strings. + + + + +
+ + + <structname>pg_index</structname> diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index 509efea8e1..dd08c4da4c 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -2384,6 +2384,132 @@ ORDER BY c.ordinal_position; + + <literal>foreign_table_options</literal> + + + The view foreign_table_options contains all the + options defined for foreign tables in the current database. Only + those foreign tables are shown that the current user has access to + (by way of being the owner or having some privilege). + + + + <literal>foreign_table_options</literal> Columns + + + + + Name + Data Type + Description + + + + + + foreign_table_catalog + sql_identifier + Name of the database that contains the foreign table (always the current database) + + + + foreign_table_schema + sql_identifier + Name of the schema that contains the foreign table + + + + foreign_table_name + sql_identifier + Name of the foreign table + + + + foreign_server_catalog + sql_identifier + Name of the database that the foreign server is defined in (always the current database) + + + + foreign_server_name + sql_identifier + Name of the foreign server + + + + option_name + sql_identifier + Name of an option + + + + option_value + character_data + Value of the option + + + +
+ + + + <literal>foreign_tables</literal> + + + The view foreign_tables contains all foreign + tables defined in the current database. Only those foreign + tables are shown that the current user has access to (by way of + being the owner or having some privilege). + + + + <literal>foreign_tables</literal> Columns + + + + + Name + Data Type + Description + + + + + + foreign_table_catalog + sql_identifier + Name of the database that the foreign table is defined in (always the current database) + + + + foreign_table_schema + sql_identifier + Name of the schema that contains the foreign table + + + + foreign_table_name + sql_identifier + Name of the foreign table + + + + foreign_server_catalog + sql_identifier + Name of the database that the foreign server is defined in (always the current database) + + + + foreign_server_name + sql_identifier + Name of the foreign server + + + +
+ + <literal>key_column_usage</literal> @@ -4730,8 +4856,9 @@ ORDER BY c.ordinal_position; Type of the table: BASE TABLE for a persistent base table (the normal table type), - VIEW for a view, or LOCAL - TEMPORARY for a temporary table + VIEW for a view, FOREIGN TABLE + for a foreign table, or + LOCAL TEMPORARY for a temporary table diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index f5d67a2078..92be337747 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -12,6 +12,7 @@ Complete list of usable sgml source files in this directory. + @@ -51,6 +52,7 @@ Complete list of usable sgml source files in this directory. + @@ -86,6 +88,7 @@ Complete list of usable sgml source files in this directory. + diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgml index e1aa293c7f..612645d4a4 100644 --- a/doc/src/sgml/ref/alter_default_privileges.sgml +++ b/doc/src/sgml/ref/alter_default_privileges.sgml @@ -71,8 +71,8 @@ REVOKE [ GRANT OPTION FOR ] ALTER DEFAULT PRIVILEGES allows you to set the privileges that will be applied to objects created in the future. (It does not affect privileges assigned to already-existing objects.) Currently, - only the privileges for tables (including views), sequences, and - functions can be altered. + only the privileges for tables (including views and foreign tables), + sequences, and functions can be altered. diff --git a/doc/src/sgml/ref/alter_foreign_table.sgml b/doc/src/sgml/ref/alter_foreign_table.sgml new file mode 100644 index 0000000000..c0719e1b7e --- /dev/null +++ b/doc/src/sgml/ref/alter_foreign_table.sgml @@ -0,0 +1,556 @@ + + + + + ALTER FOREIGN TABLE + 7 + SQL - Language Statements + + + + ALTER FOREIGN TABLE + change the definition of a foreign table + + + + ALTER FOREIGN TABLE + + + + +ALTER FOREIGN TABLE name [ * ] + action [, ... ] +ALTER FOREIGN TABLE name [ * ] + RENAME [ COLUMN ] column TO new_column +ALTER FOREIGN TABLE name + RENAME TO new_name +ALTER FOREIGN TABLE name + SET SCHEMA new_schema + +where action is one of: + + ADD [ COLUMN ] column type [ column_constraint [ ... ] ] + DROP [ COLUMN ] [ IF EXISTS ] column [ RESTRICT | CASCADE ] + ALTER [ COLUMN ] column [ SET DATA ] TYPE type + ALTER [ COLUMN ] column SET DEFAULT expression + ALTER [ COLUMN ] column DROP DEFAULT + ALTER [ COLUMN ] column { SET | DROP } NOT NULL + ADD table_constraint + DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ] + DISABLE RULE rewrite_rule_name + ENABLE RULE rewrite_rule_name + ENABLE REPLICA RULE rewrite_rule_name + ENABLE ALWAYS RULE rewrite_rule_name + SET WITH OIDS + SET WITHOUT OIDS + INHERIT parent_table + NO INHERIT parent_table + OWNER TO new_owner + OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ]) + + + + + Description + + + ALTER FOREIGN TABLE changes the definition of an existing table. + There are several subforms: + + + + ADD COLUMN + + + This form adds a new column to the foreign table, using the same syntax as + . + + + + + + DROP COLUMN [ IF EXISTS ] + + + This form drops a column from a foreign table. + Table constraints involving the column will be automatically + dropped as well. You will need to say CASCADE if + anything outside the table depends on the column, for example, + views. + If IF EXISTS is specified and the column + does not exist, no error is thrown. In this case a notice + is issued instead. + + + + + + SET DATA TYPE + + + This form changes the type of a column of a foreign table. + Simple table constraints involving the column will be automatically + converted to use the new column type by reparsing the originally + supplied expression. The optional USING + clause specifies how to compute the new column value from the old; + if omitted, the default conversion is the same as an assignment + cast from old data type to new. A USING + clause must be provided if there is no implicit or assignment + cast from old to new type. + + + + + + SET/DROP NOT NULL + + + These forms change whether a column is marked to allow null + values or to reject null values. You can only use SET + NOT NULL when the column contains no null values. + + + + + + ADD table_constraint + + + This form adds a new constraint to a foreign table using the same syntax as + . + + + + + + DROP CONSTRAINT [ IF EXISTS ] + + + This form drops the specified constraint on a foreign table. + If IF EXISTS is specified and the constraint + does not exist, no error is thrown. In this case a notice is issued instead. + + + + + + DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE + + + These forms configure the firing of rewrite rules belonging to the table. + A disabled rule is still known to the system, but is not applied + during query rewriting. + This configuration is ignored for ON SELECT rules, which + are always applied in order to keep views working even if the current + session is in a non-default replication role. + + + + + + SET WITH OIDS + + + This form adds an oid system column to the + table (see ). + It does nothing if the table already has OIDs. + + + + Note that this is not equivalent to ADD COLUMN oid oid; + that would add a normal column that happened to be named + oid, not a system column. + + + + + + SET WITHOUT OIDS + + + This form removes the oid system column from the + table. This is exactly equivalent to + DROP COLUMN oid RESTRICT, + except that it will not complain if there is already no + oid column. + + + + + + INHERIT parent_table + + + This form adds the target table as a new child of the specified parent + table. Subsequently, queries against the parent will include records + of the target table. To be added as a child, the target table must + already contain all the same columns as the parent (it could have + additional columns, too). The columns must have matching data types, + and if they have NOT NULL constraints in the parent + then they must also have NOT NULL constraints in the + child. + + + + There must also be matching child-table constraints for all + CHECK constraints of the parent. + + + + + + NO INHERIT parent_table + + + This form removes the target table from the list of children of the + specified parent table. + Queries against the parent table will no longer include records drawn + from the target table. + + + + + + OWNER + + + This form changes the owner of the foreign table to the + specified user. + + + + + + RENAME + + + The RENAME forms change the name of a foreign table + or the name of an individual column in + a foreign table. There is no effect on the stored data. + + + + + + SET SCHEMA + + + This form moves the foreign table into another schema. Associated + constraints owned by table columns are moved as well. + + + + + + OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] ) + + + Change options for the foreign table. + ADD, SET, and DROP + specify the action to be performed. ADD is assumed + if no operation is explicitly specified. Option names must be + unique; names and values are also validated using the foreign + data wrapper library. + + + + + + + + + All the actions except RENAME and SET SCHEMA + can be combined into + a list of multiple alterations to apply in parallel. For example, it + is possible to add several columns and/or alter the type of several + columns in a single command. This is particularly useful with large + foreign tables, since only one pass over the table need be made. + + + + You must own the table to use ALTER FOREIGN TABLE. + To change the schema of a foreign table, you must also have + CREATE privilege on the new schema. + To add the table as a new child of a parent table, you must own the + parent table as well. + To alter the owner, you must also be a direct or indirect member of the new + owning role, and that role must have CREATE privilege on + the table's schema. (These restrictions enforce that altering the owner + doesn't do anything you couldn't do by dropping and recreating the table. + However, a superuser can alter ownership of any table anyway.) + + + + + Parameters + + + + + name + + + The name (possibly schema-qualified) of an existing table to + alter. If ONLY is specified, only that table is + altered. If ONLY is not specified, the table and any + descendant foreign tables are altered. + + + + + + column + + + Name of a new or existing column. + + + + + + new_column + + + New name for an existing column. + + + + + + new_name + + + New name for the table. + + + + + + type + + + Data type of the new column, or new data type for an existing + column. + + + + + + table_constraint + + + New table constraint for the table. + + + + + + constraint_name + + + Name of an existing constraint to drop. + + + + + + CASCADE + + + Automatically drop objects that depend on the dropped column + or constraint (for example, views referencing the column). + + + + + + RESTRICT + + + Refuse to drop the column or constraint if there are any dependent + objects. This is the default behavior. + + + + + + storage_parameter + + + The name of a foreign table storage parameter. + + + + + + value + + + The new value for a foreign table storage parameter. + This might be a number or a word depending on the parameter. + + + + + + parent_table + + + A parent table to associate or de-associate with this table. + + + + + + new_owner + + + The user name of the new owner of the table. + + + + + + new_schema + + + The name of the schema to which the table will be moved. + + + + + + + + + Notes + + + The key word COLUMN is noise and can be omitted. + + + + Consistency with the foreign server is not checked even when a column is + added or removed with ADD COLUMN or + DROP COLUMN, a system oid column is added + or removed, a CHECK or NOT NULL constraint is + added, or column type is changed with ALTER TYPE. + + + + Refer to for a further description of valid + parameters. has further information on + inheritance. + + + + + Examples + + + To add a column of type varchar to a foreign table: + +ALTER FOREIGN TABLE distributors ADD COLUMN address varchar(30); + + + + + To drop a column from a foreign table: + +ALTER FOREIGN TABLE distributors DROP COLUMN address RESTRICT; + + + + + To change the types of two existing columns in one operation: + +ALTER FOREIGN TABLE distributors + ALTER COLUMN address TYPE varchar(80), + ALTER COLUMN name TYPE varchar(100); + + + + + To rename an existing column: + +ALTER FOREIGN TABLE distributors RENAME COLUMN address TO city; + + + + + To rename an existing table: + +ALTER FOREIGN TABLE distributors RENAME TO suppliers; + + + + + To add a not-null constraint to a column: + +ALTER FOREIGN TABLE distributors ALTER COLUMN street SET NOT NULL; + + To remove a not-null constraint from a column: + +ALTER FOREIGN TABLE distributors ALTER COLUMN street DROP NOT NULL; + + + + + To add a check constraint to a foreign table: + +ALTER FOREIGN TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5); + + + + + To remove a check constraint from a foreign table and all its children: + +ALTER FOREIGN TABLE distributors DROP CONSTRAINT zipchk; + + + + + To remove a check constraint from a foreign table only: + +ALTER FOREIGN TABLE ONLY distributors DROP CONSTRAINT zipchk; + + + + + To move a foreign table to a different schema: + +ALTER FOREIGN TABLE myschema.distributors SET SCHEMA yourschema; + + + + + To chage options of a foreign table: + +ALTER FOREIGN TABLE myschema.distributors OPTIONS (ADD opt1 'value', SET opt2, 'value2', DROP opt3 'value3'); + + + + + + + Compatibility + + + The forms ADD, DROP, + and SET DATA TYPE + conform with the SQL standard. The other forms are + PostgreSQL extensions of the SQL standard. + Also, the ability to specify more than one manipulation in a single + ALTER FOREIGN TABLE command is an extension. + + + + ALTER FOREIGN TABLE DROP COLUMN can be used to drop the only + column of a foreign table, leaving a zero-column table. This is an + extension of SQL, which disallows zero-column foreign tables. + + + diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml index b5f7e3196c..a14e292590 100644 --- a/doc/src/sgml/ref/comment.sgml +++ b/doc/src/sgml/ref/comment.sgml @@ -31,6 +31,7 @@ COMMENT ON CONVERSION object_name | DATABASE object_name | DOMAIN object_name | + FOREIGN TABLE object_name | FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) | INDEX object_name | LARGE OBJECT large_object_oid | @@ -247,6 +248,7 @@ COMMENT ON COLUMN my_table.my_column IS 'Employee ID number'; COMMENT ON CONVERSION my_conv IS 'Conversion to UTF8'; COMMENT ON DATABASE my_database IS 'Development Database'; COMMENT ON DOMAIN my_domain IS 'Email Address Domain'; +COMMENT ON FOREIGN TABLE my_schema.my_foreign_table IS 'Employee Information in other database'; COMMENT ON FUNCTION my_function (timestamp) IS 'Returns Roman Numeral'; COMMENT ON INDEX my_index IS 'Enforces uniqueness on employee ID'; COMMENT ON LANGUAGE plpython IS 'Python support for stored procedures'; diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml new file mode 100644 index 0000000000..d012298fdd --- /dev/null +++ b/doc/src/sgml/ref/create_foreign_table.sgml @@ -0,0 +1,519 @@ + + + + + CREATE FOREIGN TABLE + 7 + SQL - Language Statements + + + + CREATE FOREIGN TABLE + define a new foreign table + + + + CREATE FOREIGN TABLE + + + + +CREATE FOREIGN TABLE table_name ( [ + { column_name data_type [ column_constraint [ ... ] ] + | table_constraint + | LIKE parent_table [ like_option ... ] } + [, ... ] +] ) +[ INHERITS ( parent_table [, ... ] ) ] + SERVER server_name +[ WITH OIDS | WITHOUT OIDS ] +[ OPTIONS ( option 'value' [, ... ] ) ] + +CREATE FOREIGN TABLE table_name + OF type_name [ ( + { column_name WITH OPTIONS [ column_constraint [ ... ] ] + | table_constraint } + [, ... ] +) ] +[ INHERITS ( parent_table [, ... ] ) ] + SERVER server_name +[ WITH OIDS | WITHOUT OIDS ] +[ OPTIONS ( option 'value' [, ... ] ) ] + +where column_constraint is: + +[ CONSTRAINT constraint_name ] +{ NOT NULL | + NULL | + CHECK ( expression ) +} + +and table_constraint is: + +[ CONSTRAINT constraint_name ] +CHECK ( expression ) + +and like_option is: + +{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | COMMENTS | ALL } + + + + + + Description + + + CREATE FOREIGN TABLE will create a new foreign table + in the current database. The table will be owned by the user issuing the + command. + + + + If a schema name is given (for example, CREATE FOREIGN TABLE + myschema.mytable ...) then the table is created in the specified + schema. Otherwise it is created in the current schema. + The name of the foreign table must be + distinct from the name of any other foreign table, table, sequence, index, + or view in the same schema. + + + + CREATE FOREIGN TABLE also automatically creates a data + type that represents the composite type corresponding to one row of + the foreign table. Therefore, foreign tables cannot have the same + name as any existing data type in the same schema. + + + + The optional constraint clauses specify constraints (tests) that + retrieved rows must satisfy for an select operation + to succeed. A constraint is an SQL object that helps define the + set of valid values in the table in various ways. + + + + There are two ways to define constraints: table constraints and + column constraints. A column constraint is defined as part of a + column definition. A table constraint definition is not tied to a + particular column, and it can encompass more than one column. + Every column constraint can also be written as a table constraint; + a column constraint is only a notational convenience for use when the + constraint only affects one column. + + + + + + Parameters + + + + + table_name + + + The name (optionally schema-qualified) of the table to be created. + + + + + + OF type_name + + + Creates a typed table, which takes its + structure from the specified composite type (name optionally + schema-qualified). A typed table is tied to its type; for + example the table will be dropped if the type is dropped + (with DROP TYPE ... CASCADE). + + + + When a typed table is created, then the data types of the + columns are determined by the underlying composite type and are + not specified by the CREATE FOREIGN TABLE command. + But the CREATE FOREIGN TABLE command can add defaults + and constraints to the table. + + + + + + column_name + + + The name of a column to be created in the new table. + + + + + + data_type + + + The data type of the column. This can include array + specifiers. For more information on the data types supported by + PostgreSQL, refer to . + + + + + + INHERITS ( parent_table [, ... ] ) + + + The optional INHERITS clause specifies a list of + tables from which the new table automatically inherits all + columns. + + + + Use of INHERITS creates a persistent relationship + between the new child table and its parent table(s). Schema + modifications to the parent(s) normally propagate to children + as well, and by default the data of the child table is included in + scans of the parent(s). + + + + If the same column name exists in more than one parent + table, an error is reported unless the data types of the columns + match in each of the parent tables. If there is no conflict, + then the duplicate columns are merged to form a single column in + the new table. If the column name list of the new table + contains a column name that is also inherited, the data type must + likewise match the inherited column(s), and the column + definitions are merged into one. If the + new table explicitly specifies a default value for the column, + this default overrides any defaults from inherited declarations + of the column. Otherwise, any parents that specify default + values for the column must all specify the same default, or an + error will be reported. + + + + CHECK constraints are merged in essentially the same way as + columns: if multiple parent tables and/or the new table definition + contain identically-named CHECK constraints, these + constraints must all have the same check expression, or an error will be + reported. Constraints having the same name and expression will + be merged into one copy. Notice that an unnamed CHECK + constraint in the new table will never be merged, since a unique name + will always be chosen for it. + + + + Column STORAGE settings are also copied from parent tables. + + + + + + + LIKE parent_table [ like_option ... ] + + + The LIKE clause specifies a table from which + the new table automatically copies all column names, their data types, + and their not-null constraints. + + + Unlike INHERITS, the new table and original table + are completely decoupled after creation is complete. Changes to the + original table will not be applied to the new table, and it is not + possible to include data of the new table in scans of the original + table. + + + Default expressions for the copied column definitions will only be + copied if INCLUDING DEFAULTS is specified. The + default behavior is to exclude default expressions, resulting in the + copied columns in the new table having null defaults. + + + Not-null constraints are always copied to the new table. + CHECK constraints will only be copied if + INCLUDING CONSTRAINTS is specified; other types of + constraints will never be copied. Also, no distinction is made between + column constraints and table constraints — when constraints are + requested, all check constraints are copied. + + + Comments for the copied columns and constraints + will only be copied if INCLUDING COMMENTS + is specified. The default behavior is to exclude comments, resulting in + the copied columns and constraints in the new table having no comments. + + + INCLUDING ALL is an abbreviated form of + INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING COMMENTS. + + + Note also that unlike INHERITS, columns and + constraints copied by LIKE are not merged with similarly + named columns and constraints. + If the same name is specified explicitly or in another + LIKE clause, an error is signalled. + + + + + + CONSTRAINT constraint_name + + + An optional name for a column or table constraint. If the + constraint is violated, the constraint name is present in error messages, + so constraint names like col must be positive can be used + to communicate helpful constraint information to client applications. + (Double-quotes are needed to specify constraint names that contain spaces.) + If a constraint name is not specified, the system generates a name. + + + + + + NOT NULL + + + The column is not allowed to contain null values. + + + + + + NULL + + + The column is allowed to contain null values. This is the default. + + + + This clause is only provided for compatibility with + non-standard SQL databases. Its use is discouraged in new + applications. + + + + + + CHECK ( expression ) + + + The CHECK clause specifies an expression producing a + Boolean result which new or updated rows must satisfy for an + insert or update operation to succeed. Expressions evaluating + to TRUE or UNKNOWN succeed. Should any row of an insert or + update operation produce a FALSE result an error exception is + raised and the insert or update does not alter the database. A + check constraint specified as a column constraint should + reference that column's value only, while an expression + appearing in a table constraint can reference multiple columns. + + + + Currently, CHECK expressions cannot contain + subqueries nor refer to variables other than columns of the + current row. + + + + + + WITH OIDS | WITHOUT OIDS + + + This clause specifies whether rows of the foreign table should have + OIDs (object identifiers) assigned to them, or not. + If WITH OIDS is not specified, the default setting depends + upon the configuration parameter. + (If the new foreign table inherits from any tables that have OIDs, then + WITH OIDS is forced even if the command says + WITHOUT OIDS.) + + + + If WITH OIDS is specified or implied, the first + column of the rows which was retrieved from the foreign table + must be the oid column. + + + + To remove OIDs from a foreign table after it has been created, use . + + + + + + + + + + + Examples + + + Create foreign table films with film_server: + + +CREATE FOREIGN TABLE films ( + code char(5) NOT NULL, + title varchar(40) NOT NULL, + did integer NOT NULL, + date_prod date, + kind varchar(10), + len interval hour to minute +) +SERVER film_server; + + + + + Define a check column constraint: + + +CREATE FOREIGN TABLE distributors ( + did integer CHECK (did > 100), + name varchar(40) +) +SERVER distributor_server; + + + + + Define a check table constraint: + + +CREATE FOREIGN TABLE distributors ( + did integer, + name varchar(40) + CONSTRAINT con1 CHECK (did > 100 AND name <> '') +) +SERVER distributor_server; + + + + + Create a composite type and a typed foreign table: + +CREATE TYPE employee_type AS (name text, salary numeric); + +CREATE FOREIGN TABLE employees OF employee_type ( + salary WITH OPTIONS CHECK (salary > 0) +) +SERVER employee_server; + + + + + + Compatibility + + + The CREATE FOREIGN TABLE command conforms to the + SQL standard, with exceptions listed below. + + + + Column generic options + + + The SQL standard says that column definition can contain + column generic options, but currently column generic options are + not supported. + + + + + Column Check Constraints + + + The SQL standard says that CHECK column constraints + can only refer to the column they apply to; only CHECK + table constraints can refer to multiple columns. + PostgreSQL does not enforce this + restriction; it treats column and table check constraints alike. + + + + + <literal>NULL</literal> <quote>Constraint</quote> + + + The NULL constraint (actually a + non-constraint) is a PostgreSQL + extension to the SQL standard that is included for compatibility with some + other database systems (and for symmetry with the NOT + NULL constraint). Since it is the default for any + column, its presence is simply noise. + + + + + Inheritance + + + Multiple inheritance via the INHERITS clause is + a PostgreSQL language extension. + SQL:1999 and later define single inheritance using a + different syntax and different semantics. SQL:1999-style + inheritance is not yet supported by + PostgreSQL. + + + + + Zero-column tables + + + PostgreSQL allows a table of no columns + to be created (for example, CREATE FOREIGN TABLE foo();). This + is an extension from the SQL standard, which does not allow zero-column + tables. Zero-column tables are not in themselves very useful, but + disallowing them creates odd special cases for ALTER TABLE + DROP COLUMN, so it seems cleaner to ignore this spec restriction. + + + + + <literal>WITH</> clause + + + The WITH clause is a PostgreSQL + extension; neither storage parameters nor OIDs are in the standard. + + + + + Typed Tables + + + Typed tables implement a subset of the SQL standard. According to + the standard, a typed table has columns corresponding to the + underlying composite type as well as one other column that is + the self-referencing column. PostgreSQL does not + support these self-referencing columns explicitly, but the same + effect can be had using the OID feature. + + + + + + + See Also + + + + + + + + + + diff --git a/doc/src/sgml/ref/create_sequence.sgml b/doc/src/sgml/ref/create_sequence.sgml index 3298c0ad27..89d802eada 100644 --- a/doc/src/sgml/ref/create_sequence.sgml +++ b/doc/src/sgml/ref/create_sequence.sgml @@ -45,7 +45,7 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE name diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 8635e80faf..4314fb13ec 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -96,8 +96,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE [ IF NOT EXISTS ] diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml index 48e2e0b963..a69c6d39e3 100644 --- a/doc/src/sgml/ref/create_view.sgml +++ b/doc/src/sgml/ref/create_view.sgml @@ -50,7 +50,7 @@ CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW n schema. Otherwise it is created in the current schema. Temporary views exist in a special schema, so a schema name cannot be given when creating a temporary view. The name of the view must be - distinct from the name of any other view, table, sequence, or index + distinct from the name of any other view, table, sequence, index or foreign table in the same schema. diff --git a/doc/src/sgml/ref/drop_foreign_table.sgml b/doc/src/sgml/ref/drop_foreign_table.sgml new file mode 100644 index 0000000000..1b3af5b3e8 --- /dev/null +++ b/doc/src/sgml/ref/drop_foreign_table.sgml @@ -0,0 +1,123 @@ + + + + + DROP FOREIGN TABLE + 7 + SQL - Language Statements + + + + DROP FOREIGN TABLE + remove a foreign table + + + + DROP FOREIGN TABLE + + + + +DROP FOREIGN TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] + + + + + Description + + + DROP FOREIGN TABLE removes foreign tables from the database. + Only its owner can drop a foreign table. + + + + DROP FOREIGN TABLE always removes any rules + and constraints that exist for the target table. + However, to drop a foreign table that is referenced by a view, + CASCADE must be + specified. (CASCADE will remove a dependent view entirely, + + + + + Parameters + + + + IF EXISTS + + + Do not throw an error if theforeign table does not exist. A notice is issued + in this case. + + + + + + name + + + The name (optionally schema-qualified) of theforeign table to drop. + + + + + + CASCADE + + + Automatically drop objects that depend on theforeign table (such as + views). + + + + + + RESTRICT + + + Refuse to drop theforeign table if any objects depend on it. This is + the default. + + + + + + + + Examples + + + To destroy two foreign tables, films and + distributors: + + +DROP FOREIGN TABLE films, distributors; + + + + + + Compatibility + + + This command conforms to the ISO/IEC 9075-9 (SQL/MED), except that the + standard only allows one foreign table to be dropped per command, and apart + from the IF EXISTS option, which is a PostgreSQL + extension. + + + + + See Also + + + + + + + + diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml index 8242b536d7..8a1823198d 100644 --- a/doc/src/sgml/ref/pg_dump.sgml +++ b/doc/src/sgml/ref/pg_dump.sgml @@ -408,9 +408,9 @@ PostgreSQL documentation - Dump only tables (or views or sequences) matching table. Multiple tables can be - selected by writing multiple