This topic describes the tds_fdw plug-in that is used to query data in other types of databases.

Prerequisites

The instance runs one of the following PostgreSQL versions:
  • PostgreSQL 12 (kernel version 20200421 and later)
  • PostgreSQL 11 (kernel version 20200402 and later)
Note To view the kernel version, perform the following steps: Log on to the ApsaraDB for RDS console, find the target RDS instance, and navigate to the Basic Information page. Then, in the Configuration Information section, check whether the Upgrade Minor Version button exists. If the button exists, click it to view the kernel version. If the button does not exist, it indicates that you are already using the latest kernel version. For more information, see Upgrade the kernel version of an ApsaraDB RDS for PostgreSQL instance.
Upgrade the kernel of PostgreSQL

Background information

tds_fdw is a PostgreSQL foreign data wrapper that you can use to connect to databases. These databases use the Tabular Data Stream (TDS) protocol. Databases include Sybase and Microsoft SQL Server.

For more information, visit postgres_fdw.

Create a tds_fdw plug-in

After you have connected to an instance, execute the following statement to create a tds_fdw plug-in:

create extension tds_fdw;

Use tds_fdw

  1. Execute the following statement to create a 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');
  2. Create a foreign table. You can use one of the following methods to create a foreign table:
    • Execute the following statement to create a foreign table. You must specify the table_name parameter.
      CREATE FOREIGN TABLE mssql_table (
       id integer,
       data varchar)
       SERVER mssql_svr
       OPTIONS (table_name 'dbo.mytable', row_estimate_method 'showplan_all');
    • Execute the following statement to create a foreign table. You must specify the schema_name and table_name parameters.
      CREATE FOREIGN TABLE mssql_table (
       id integer,
       data varchar)
       SERVER mssql_svr
       OPTIONS (schema_name 'dbo', table_name 'mytable', row_estimate_method 'showplan_all');
    • Execute the following statement to create a foreign table. You must specify the query parameter.
      CREATE FOREIGN TABLE mssql_table (
       id integer,
       data varchar)
       SERVER mssql_svr
       OPTIONS (query 'SELECT * FROM dbo.mytable', row_estimate_method 'showplan_all');
    • Execute the following statement to create a foreign table. You must specify a foreign column name.
      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');
  3. Execute the following statement to create a user mapping:
    CREATE USER MAPPING FOR postgres
      SERVER mssql_svr 
      OPTIONS (username 'sa', password '123456');
  4. Execute the following statement to import a schema from a foreign table:
    IMPORT FOREIGN SCHEMA dbo
      EXCEPT (mssql_table)
      FROM SERVER mssql_svr
      INTO public
      OPTIONS (import_default 'true');