- [authorization-identifier.]table-identifier
- 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 data-type[general-column-option][, column-name data-type[general-column-option]]...
- 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
- general-column-option::=OPTIONS (option-name 'option-value' [, option-name 'option-value']...)
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.
- OPTIONS (option-name 'option-value'[, option-name 'option-value']...)
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.