version: "3"

services: 

  pg-tds:

    image: dalongrong/pgspider:tds

    ports:

      - "5432:5432"

    environment:

      - "POSTGRES_PASSWORD:dalong"

  db:

   image: microsoft/mssql-server-linux

   environment:

   - "ACCEPT_EULA=Y"

   - "SA_PASSWORD=Dalong!123%"

   ports:

   - "1433:1433"

docker-compose up -d

create DATABASE appdemos;

use appdemos;

create table apps (

    id int,

    age int,

    name VARCHAR(256)

);

insert into apps VALUES(1,22,'appdemo');

// 创建扩展

CREATE EXTENSION tds_fdw;

// 创建server

CREATE SERVER mssql_svr

  FOREIGN DATA WRAPPER tds_fdw

  OPTIONS (servername 'db', port '1433', database 'appdemos');

// 创建用户映射

CREATE USER MAPPING FOR postgres

  SERVER mssql_svr 

  OPTIONS (username 'sa', password 'Dalong!123%');

// 创建外部表映射

CREATE FOREIGN TABLE mssql_table (

  id integer,

  age integer,

    name varchar)

  SERVER mssql_svr

  OPTIONS (table_name 'dbo.apps', row_estimate_method 'showplan_all');

// 或者，导入schema

IMPORT FOREIGN SCHEMA dbo

  FROM SERVER mssql_svr

  INTO public

  OPTIONS (import_default 'true');

select * from apps