mNrZ2nBs

1-----------------------------------------------------------
2--------------- FDW ---------------------------------------
3-----------------------------------------------------------
4-- Create a Foreign Data Wrapper... We first create a 'server' object
5-- this describes a remote server, and is reusable.  You don't HAVE to do this
6-- but it frees you up from the need to define a connection string every time.
7DROP SERVER IF EXISTS mssql CASCADE;
8CREATE SERVER mssql_svr
9    FOREIGN DATA WRAPPER tds_fdw
10    OPTIONS (servername '127.0.0.1', port '1433', database 'tds_fdw_test', tds_version '7.1');
11    
12-- You can alter the server at anytime...
13-- ALTER SERVER foo OPTIONS (host 'foo', dbname 'foodb');
14 
15-- Our local user needs to map to a remote user... the default user for postgres is "postgres"
16CREATE USER MAPPING FOR postgres
17        SERVER mssql
18        OPTIONS (user 'mssql-user', password 'mssql-password');
19 
20-- You must create a definition of the remote table locally
21-- this is how Postgres knowns to construct queries against it
22-- You only need to do this once.
23CREATE FOREIGN TABLE mssql_table (
24    id integer,
25    data varchar)
26    SERVER mssql_svr
27    OPTIONS (table_name 'dbo.mytable', row_estimate_method 'showplan_all');
28SERVER mssql
29OPTIONS (schema_name 'public', table_name 'mssql-remote-table-name'); -- The foreign table name.
30
31-- Finally we can create a copy of the data-set anytime we want.
32-- a materialized view is a representation of a table, that contains
33CREATE MATERIALIZED VIEW IF NOT EXISTS mssql_table_v1 AS (
34    SELECT * FROM mssql_table
35);
36
37-- If you want to get a fresh copy of the data from the source, 
38-- you just need to issue:
39REFRESH MATERIALIZED VIEW CONCURRENTLY mssql_table_v1;