Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -----------------------------------------------------------
- --------------- FDW ---------------------------------------
- -----------------------------------------------------------
- -- Create a Foreign Data Wrapper... We first create a 'server' object
- -- this describes a remote server, and is reusable. You don't HAVE to do this
- -- but it frees you up from the need to define a connection string every time.
- DROP SERVER IF EXISTS mssql CASCADE;
- 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');
- -- You can alter the server at anytime...
- -- ALTER SERVER foo OPTIONS (host 'foo', dbname 'foodb');
- -- Our local user needs to map to a remote user... the default user for postgres is "postgres"
- CREATE USER MAPPING FOR postgres
- SERVER mssql
- OPTIONS (user 'mssql-user', password 'mssql-password');
- -- You must create a definition of the remote table locally
- -- this is how Postgres knowns to construct queries against it
- -- You only need to do this once.
- CREATE FOREIGN TABLE mssql_table (
- id integer,
- data varchar)
- SERVER mssql_svr
- OPTIONS (table_name 'dbo.mytable', row_estimate_method 'showplan_all');
- SERVER mssql
- OPTIONS (schema_name 'public', table_name 'mssql-remote-table-name'); -- The foreign table name.
- -- Finally we can create a copy of the data-set anytime we want.
- -- a materialized view is a representation of a table, that contains
- CREATE MATERIALIZED VIEW IF NOT EXISTS mssql_table_v1 AS (
- SELECT * FROM mssql_table
- );
- -- If you want to get a fresh copy of the data from the source,
- -- you just need to issue:
- REFRESH MATERIALIZED VIEW CONCURRENTLY mssql_table_v1;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement