TDS Foreign data wrapper

  • Author: Geoff Montee
  • Name: tds_fdw
  • File: tds_fdw/ForeignTableCreation.md

Creating a Foreign Table

Options

Foreign table parameters accepted:

  • query

Required: Yes (mutually exclusive with table)

The query string to use to query the foreign table.

  • schema_name

Required: No

The schema that the table is in. The schema name can also be included in table_name, so this is not required.

  • table_name

Aliases: table

Required: Yes (mutually exclusive with query)

The table on the foreign server to query.

  • match_column_names

Required: No

Whether to match local columns with remote columns by comparing their table names or whether to use the order that they appear in the result set.

  • use_remote_estimate

Required: No

Whether we estimate the size of the table by performing some operation on the remote server (as defined by row_estimate_method), or whether we just use a local estimate, as defined by local_tuple_estimate.

  • local_tuple_estimate

Required: No

A locally set estimate of the number of tuples that is used when use_remote_estimate is disabled.

  • row_estimate_method

Required: No

Default: execute

This can be one of the following values:

  • execute: Execute the query on the remote server, and get the actual number of rows in the query.
  • showplan_all: This gets the estimated number of rows using MS SQL Server's SET SHOWPLAN_ALL.

Foreign table column parameters accepted:

  • column_name

Required: No

The name of the column on the remote server. If this is not set, the column's remote name is assumed to be the same as the column's local name. If match_column_names is set to 0 for the table, then column names are not used at all, so this is ignored.

Example

Using a table_name definition:

SQL CREATE FOREIGN TABLE mssql_table ( id integer, data varchar) SERVER mssql_svr OPTIONS (table_name 'dbo.mytable', row_estimate_method 'showplan_all');

Or using a schema_name and table_name definition:

SQL CREATE FOREIGN TABLE mssql_table ( id integer, data varchar) SERVER mssql_svr OPTIONS (schema_name 'dbo', table_name 'mytable', row_estimate_method 'showplan_all');

Or using a query definition:

SQL CREATE FOREIGN TABLE mssql_table ( id integer, data varchar) SERVER mssql_svr OPTIONS (query 'SELECT * FROM dbo.mytable', row_estimate_method 'showplan_all');

Or setting a remote column name:

SQL CREATE FOREIGN TABLE mssql_table ( id integer, col2 varchar OPTIONS (column_name 'data')) SERVER mssql_svr OPTIONS (schema_name 'dbo', table_name 'mytable', row_estimate_method 'showplan_all');