CREATE FOREIGN TABLE

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide > CREATE statements >

CREATE FOREIGN TABLE

Previous pageReturn to chapter overviewNext page

A foreign table is a schema object that 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

create_foreign_table_01b

See also IMPORT FOREIGN SCHEMA.

Defining a Foreign Table

When you define a foreign table, you

1.Indicate a table and schema in the foreign database.
2.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.

SCHEMA_NAME

Name of the remote schema.

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';