CREATE FOREIGN TABLE

Define a new appearance.

Summary

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
    column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
      [, ... ]
] )
    SERVER server_name
  [ OPTIONS ( [ mpp_execute { 'master' | 'any' | 'all segments' } [, ] ] option 'value' [, ... ] ) ]

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  DEFAULT default_expr }

describe

CREATE FOREIGN TABLE Creates a new external table in the current database. The user who creates an external table will become its owner.

If you schema qualify table names (for example, CREATE FOREIGN TABLE myschema.mytable ...), the YMatrix database creates the table in the specified schema. Otherwise, an external table is created in the current schema. The name of the external table must be different from the name of any other external table, table, sequence, index, or view in the same schema.

Because CREATE FOREIGN TABLE automatically creates a data type that represents a composite type corresponding to a row of the external table, the external table cannot have the same name as any existing data type in the same schema.

To create an external table, you must have USAGE privileges on the external server and have USAGE privileges for all column types used in the table.

Parameters

IF NOT EXISTS

If a relationship with the same name already exists, do not throw an error. In this case, the YMatrix database will issue a notification. Note that there is no guarantee that an existing relationship is similar to the one to be created.

table_name

The name of the external table to be created (can be specified by the schema).

column_name

The name of the column created in the new external table.

data_type

The data type of the column, including the array specifier.

NOT NULL

This column is not allowed to contain null values.

NULL

This column is allowed to contain null values. This is the default value.
This clause is provided for compatibility with non-standard SQL databases only. It is not recommended to use it in new applications.

DEFAULT default_expr

The DEFAULT clause assigns default values to the column in which it is defined. This value is any unvariable expression; the YMatrix database does not allow subqueries and cross-references to other columns in the current table. The data type of the default expression must match the data type of the column.
The YMatrix database uses default expressions in any insertion operations that do not specify a value for the column. If the column does not have a default value, the default value is null.

server_name

The name of the existing server used for external tables.

OPTIONS ( option 'value' [, ... ] )

Options for new external tables or one of its columns. Although the option name must be unique, the table option and the column option may have the same name. The option names and values are specific to the external data wrapper. The YMatrix database uses the validator_function validation options and values of the external data wrapper.

mpp_execute { 'master' | 'any' | 'all segments' }

An option to identify the host from which the external data wrapper requests data:
- master (default setting) - Request data from the master host.
- any — Request data from the master host or any segment, depending on which path is less expensive.
- all segments — Request data from all segments. To support this option value, the external data wrapper must have a policy that matches the segment with the data.
The use of external table mpp_execute options and supported specific schemas are specific to external data wrappers.

Example

Create an external table named films using a server named 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;

compatibility