Movatterモバイル変換

H.2. tds_fdw — connect to databases that use the TDS protocol#

servername

The servername, address or hostname of the foreign server. This can be a DSN, as specified infreetds.conf. SeeFreeTDS name lookup for details. You can set this option to a comma-separated list of server names, then each server is tried until the first connection succeeds. This is useful for automatic failover to a secondary server.

Required: Yes

Default: 127.0.0.1

port

The port of the foreign server. Instead of providing a port here, it can be specified infreetds.conf (ifservername is a DSN).

Required: No

database

The database to connect to for this server

Required: No

dbuse

Ifdbuse is 0,tds_fdw will connect directly todatabase. Ifdbuse is not 0,tds_fdw will connect to the server's default database and then select the database by calling theDB-Library'sdbuse() function. ForAzure,dbuse currently needs to be set to 0.

Required: No

Default: 0

language

The language to use for messages and the locale to use for date formats.FreeTDS may default to U.S. English on most systems. You can probably also change this infreetds.conf. For information related to this forMS SQL Server, seeSET LANGUAGE inMS SQL Server. For information related toSybase ASE, seeSybase ASE login options andSET LANGUAGE inSybase ASE.

Required: No

character_set

The client character set to use for the connection if you need to set this for some reason. For TDS protocol versions 7.0+, the connection always uses UCS-2, so this parameter does nothing in those cases. SeeLocalization and TDS 7.0 for details.

Required: No

tds_version

The version of the TDS protocol to use for this server. SeeChoosing a TDS protocol version andHistory of TDS Versions for details.

Required: No

msg_handler

The function used for the TDS message handler. Can be one of the following values:

• notice: TDS messages are turned intoPostgreSQL notices

• blackhole: TDS messages are ignored

Required: No

Default:blackhole

fdw_startup_cost

A cost that is used in query planning to represent the overhead of using this foreign data wrapper.

Required: No

fdw_tuple_cost

A cost that is used in query planning to represent the overhead of fetching rows from this server.

Required: No

sqlserver_ansi_mode

A cost that is used to represent the overhead of fetching rows from this server used in query planning.

This option is supported forSQL Server only. Setting this totrue will enable the following server-side settings after a successful connection to the foreign server:

• CONCAT_NULLS_YIELDS_NULL ON

• ANSI_NULLS ON

• ANSI_WARNINGS ON

• QUOTED_IDENTIFIER ON

• ANSI_PADDING ON

• ANSI_NULL_DFLT_ON ON

Those parameters in summary are comparable to theSQL Server optionANSI_DEFAULTS. In contrast,sqlserver_ansi_mode currently does not activate the following options:

• CURSOR_CLOSE_ON_COMMIT

• IMPLICIT_TRANSACTIONS

This follows the behavior of the native ODBC and OLEDB driver for SQL servers, which explicitly turn them off if not configured otherwise.

Required: No

Default:false

• use_remote_estimate

• row_estimate_method

Example H.1. Create a Foreign Server

CREATE SERVER mssql_svr    FOREIGN DATA WRAPPER tds_fdw    OPTIONS (servername '127.0.0.1', port '1433', database 'tds_fdw_test', tds_version '7.1');

username

The username of the account on the foreign server

Important

If you are usingAzure SQL, then your username for the foreign server will need to be in the formatusername@servername. If you only use the username, the authentication will fail.

Required: Yes

password

The password of the account on the foreign server

Required: Yes

Example H.2. Create a User Mapping

CREATE USER MAPPING FOR postgres    SERVER mssql_svr     OPTIONS (username 'sa', password '');

query#

The query string to use to query the foreign table

Required: Yes (mutually exclusive withtable_name)

schema_name#

The schema that the table is in. The schema name can also be included intable_name.

Required: No

table_name#

The table on the foreign server to query

Aliases:table

Required: Yes (mutually exclusive withquery)

match_column_names#

Match local columns with remote columns by comparing their table names instead of using the order in which they appear in the result set. Required forWHERE and column pushdowns.

Required: No

use_remote_estimate#

Estimate the size of the table by performing some operation on the remote server, as defined byrow_estimate_method, instead of using the local estimate, as defined bylocal_tuple_estimate

Required: No

local_tuple_estimate#

A locally set estimate of the number of tuples that is used whenuse_remote_estimate is disabled

Required: No

row_estimate_method#

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: Get the estimated number of rows usingMSSQL Server’sSET SHOWPLAN_ALL

Required: No

Default:execute

Example H.3. Create a Foreign Table

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

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

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

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

import_default

Controls whether columnDEFAULT expressions are included in the definitions of foreign tables

Required: No

Default: false

import_not_null

Controls whether columnNOT NULL constraints are included in the definitions of foreign tables

Required: No

Default: true

Example H.4. Import a Foreign Schema

IMPORT FOREIGN SCHEMA dbo    EXCEPT (mssql_table)    FROM SERVER mssql_svr    INTO public    OPTIONS (import_default 'true');

tds_fdw.show_before_row_memory_stats

Print memory context stats to thePostgres Pro log before each row is fetched

tds_fdw.show_after_row_memory_stats

Print memory context stats to thePostgres Pro log after each row is fetched

tds_fdw.show_finished_memory_stats

Print memory context stats to thePostgres Pro log when a query is finished

Example H.5. Set a Variable

postgres=# SET tds_fdw.show_finished_memory_stats=1;SET