CREATE FOREIGN TABLE (Define a foreign table)

Function

Defines a foreign table.

Creates definition information in HiRDB. Foreign tables on a foreign server cannot be defined.

Privileges

Users who own a schema and a foreign server definition
These users can define foreign tables associated with tables on a foreign server that they have defined in a foreign server definition that they own.

Format

CREATE FOREIGN TABLE [authorization-identifier.]table-identifier
  (column-name data-type[general-column-option]
  [, column-name data-type[general-column-option]]...)
  SERVER foreign-server-name
  [OPTIONS (option-name 'option-value' [, option-name 'option-value']...)]

general-column-option::=
  OPTIONS (option-name 'option-value' [, option-name 'option-value']...)

Operands

authorization-identifier
Specifies the authorization identifier of the user who will be the owner of the foreign table to be defined.
The default is the authorization identifier of the user who executes CREATE FOREIGN TABLE.
table-identifier
Specifies the name of the foreign table to be defined.
Only one table identifier can be specified per authorization identifier.
column-name
Specifies the name of the column that comprises the table.
In column-name, specify the column name that was defined for the associated table for the foreign server. The same column name cannot be specified more than once.
data-type
Specifies the data type of the column.
Specifiable data types are HiRDB's predefined types. Specify a predefined type that is compatible with the data type of the column of the table on the foreign server. Specifying an incompatible data type can cause an error. For data type compatibility, see C. Correspondence Between Data Types When a Foreign Table is used.
Any of the following data types cannot be specified:
  • CHAR, VARCHAR, MCHAR, or MVARCHAR with a definition length of 256 bytes or greater
  • NCHAR or NVARCHAR with a definition length of 128 characters or greater
  • BLOB
  • BINARY with a definition length of 256 bytes or greater

Specify options for a column.

The following option names and option values can be specified; option names cannot be specified in duplicate.

COLLATING_SEQUENCE {'SAME'|'DIFFERENT'}
If a given column is of the character string type, specify whether the character code and collating sequence applicable to the given column on the foreign server are the same as those of HiRDB.
The default is SAME.
This option can be specified for character string-type (CHAR, VARCHAR, NCHAR, NVARCHAR, MCHAR, and MVARCHAR) columns only.
SAME
This operand is specified when using the same character code and collating sequence.
DIFFERENT
This operand is specified when using a character code or a collating sequence that is different, or when both items are different.
If DIFFERENT is specified, HiRDB performs a collating sequence comparison on the character string (<, <=, >, >=) and processes the BETWEEN predicate, the set functions (MIN, MAX), and ORDER BY. Specifying SAME when the foreign database and the HiRDB have different character code sets or collating sequences can produce different results of collating sequence comparisons (<, <=, >, >=) and of the processing of the BETWEEN predicate, the set functions (MIN, MAX), and ORDER BY on the character string.
TRAILING_SPACE {'YES'|'NO'}
For a variable-length character string (VARCHAR, NVARCHAR, or MVARCHAR), specifies whether the character string to be stored in a given column has a trailing single-byte space or double-byte space character string.
The default is NO.
This option can be specified only for columns of a variable-length character string type (VARCHAR, NVARCHAR, or MVARCHAR).
Specifying an option value different from the data stored in a foreign database can produce unexpected results from a character string comparison.
YES
This option is specified if the character string to be stored in a given column has a trailing single-byte space or double-byte space character string, either actual or potential.
If YES is specified and the value 2 is specified in the pd_hub_opt_trailing_spc operand of the Hub optimization information definition, HiRDB processes those parts in the following items that have specifications differences: character string comparisons (=, !=, < >, <, <=, >, and >=), quantified predicates, BETWEEN predicate, IN predicate, set functions (MIN, MAX), SELECT DISTINCT specification, DISTINCT set function, set operations other than UNION ALL, GROUP BY, and ORDER BY. However, if DIFFERENT is specified at the same time in the COLLATING_SEQUENCE option, HiRDB always performs character string comparisons (=, !=, < >, <, <=, >, and >=), BETWEEN predicate, set functions (MIN, MAX), and ORDER BY. For Hub optimization information definitions, see the manual HiRDB Version 8 System Definition.
NO
This option is specified if the character string to be stored in a given column does not have a trailing single-byte space or double-byte space character string.
NULLABLE {'YES'|'NO'}
Specifies whether the column is to allow the null value.
The default is YES.
Specifying an option different from a column in the foreign database can cause a decline in HiRDB performance or produce incorrect processing results. For details about performance design with respect to access to foreign tables, see the manual HiRDB External Data Access Version 7.
YES
This option is specified when allowing the null value.
If the value 2 is specified in the pd_hub_opt_nullable operand of the Hub optimization information definition and YES is specified in the character string column, HiRDB performs any concatenation operations (||) involving the specified column.
NO
This option is specified when not allowing the null value (for NOT NULL constraint). Specifying NO causes HiRDB to assume NOT NULL WITH DEFAULT for the column.

Specifies the name of the foreign server (the name defined in CREATE SERVER) on which the foreign table is located.

Specifies any of the following options on the foreign table; an option cannot be specified in duplicate.

The operands schema-name and table-identifier-on-foreign-server can each be specified in a maximum of 30 bytes.

SCHEMA 'schema-name'
Specifies the schema name of the table to be accessed from a foreign table.
If the foreign server is HiRDB, the schema name is equivalent to the authorization identifier.
The default for the schema name is the name that was specified in authorization-identifier. If both a schema name and an authorization identifier are omitted, the default is the authorization identifier of the user who executes CREATE FOREIGN TABLE.
If a table accessed from a foreign table is defined as a public view on the foreign server, specify PUBLIC in the schema name.
TABLE 'table-identifier-on-foreign-server'
Specifies the name defined on the foreign server of the table accessed from the foreign table.
The default is the name specified in table-identifier.

Common rules

  1. A maximum of 30,000 columns can be defined per foreign table.
  2. The user who executes CREATE FOREIGN TABLE is granted the privilege to access the applicable foreign table.

Notes

  1. Tables on the foreign server side cannot be defined by using CREATE FOREIGN TABLE.
  2. CREATE FOREIGN TABLE cannot be specified from an X/Open compliant UAP running under OLTP.
  3. If you define a read-only view table on a foreign server as a foreign table, you cannot perform row insertion, updating, or deletion for that foreign table. If an attempt is made to execute an SQL statement for such an operation, accessing the foreign server may result in an error. Do not grant the user the UPDATE, INSERT, or DELETE privilege to such a table.
  4. When the NOT NULL constraint is specified for a table on a foreign server and in the foreign table there is no column defined that does not have a default value specified, an INSERT statement cannot be executed for that foreign table. If an attempt is made to execute an INSERT statement, accessing the foreign server may result in an error. Do not grant the user the INSERT privilege to such a table. When you insert a row into a foreign table, include in the definition of the foreign table a column for which the NOT NULL constraint is specified and for which no default value is specified.
  5. The access privilege granted by virtue of the execution of CREATE FOREIGN TABLE cannot be granted to tables on the foreign server corresponding to the applicable foreign table. If a table without access privilege on the foreign server is defined as a foreign table, any access to the table on the foreign server may result in an error. Care should be taken so that access privileges not valid on the foreign server are not granted to other users.
  6. Any of the following definition lengths that are specified in CREATE FOREIGN TABLE should be the same as the table defined on the foreign server; if they are not the same, an error can occur on the foreign server:
    • DECIMAL-type precision and scaling
    • TIMESTAMP-type precision
    • Fixed-length character string type
    • Variable-length character string type
    • Fixed-length national character string type
    • Variable-length national character string type
    • Fixed-length mixed character string type
    • Variable-length mixed character string type
    • BINARY type