CREATE FOREIGN TABLE

A foreign table is a schema objectthat records the metadata necessary for SQLstream s-Server to be able to access a table (or similar data structure) in a remote database. A foreign table can be used in SQL (both queries and updates) just like a local table. The only option always required when defining a JDBC foreign table is TABLE_NAME (or OBJECT). For servers which support multiple schemas, the SCHEMA_NAME option can be specified at the table level to choose a schema other than the default. To use a foreign table, you first need to define server information for the external database.

Syntax

### Defining a Foreign Table

When you define a foreign table, you

  • Indicate a table and schema in the foreign database.
  • Declare columns that match those in the target database table.

You can set these options when you declare a server, and override these options in a table definition.

| Name | Description | | --- | --- | | TABLE_NAME | Name of the remote table. |

Sample SQL

Like all tables, foreign tables need to be created within schemas. The following code first creates and sets a schema called "MOCHI," then creates a foreign table called "regional_activity" which uses the server defined above and indicates a schema in the foreign database called "public" and a table in the foreign database called "regional_activity."

CREATE OR REPLACE SCHEMA "MOCHI"
SET SCHEMA 'MOCHI';
CREATE OR REPLACE FOREIGN TABLE "regional_activity"
   SERVER "Postgres_DB"
   OPTIONS (
       SCHEMA\_NAME 'public'
       --schema name in the foreign database
       TABLE\_NAME 'regional_activity'
       --table name in the foreign database
   ) DESCRIPTION 'per-city summary of suspicious activity';

See also IMPORT FOREIGN SCHEMA .