As promised in our prior article: ODBC Foreign Data wrapper on windows, we'll demonstrate how to query SQL Server using the Foreign Data Wrapper. This we are testing on windows. As far as querying SQL Server / PostgreSQL goes, the Foreign Data Wrapper still lacks many features that the SQL Server Linked Server approach provides. The key ones we find currently lacking: ability to do updates and reference a table directly from server without knowing underlying structure. That said the Foreign data Wrapper approach has possiblity to support a lot more data sources with ease. We'll demonstrate in subsequent articles using the www_fdw to query web services which we've been playing a lot with and the often packaged in file_fdw. Enough of that let's start with a concrete example.
Warning, this is not production ready, but seems like a very promising start and with more testing can become very robust. Although we are demonstrating odbc_fdw on windows, it is supported on Unix via the UnixODBC, but the data sources you can query will probably be different. I'm really looking forward to how the FDW technology in PostgreSQL will push the envelop. I've been playing around with the www_fdw as well and been impressed how easily it is to query webservices with SQL. A very ah-hah moment.
Copy the odbc_fdw binaries in your PostgreSQL install folder -- for windows we have these for both PostgreSQL 9.1 32-bit and PostgreSQL 9.1 64-bit in our windows FDW packages. PostgreSQL windows 32-bit 9.1 FDWs and PostgreSQL windows 64-bit 9.1 FDWs We'll be adding more fdws to these zip packages as we experiment with new ones.
CREATE EXTENSION odbc_fdw;
CREATE SERVER localsql2008 FOREIGN DATA WRAPPER odbc_fdw OPTIONS (dsn 'LocalSQL2008ForPg');
Next create a User Mapping that maps a postgres role to a SQL Account. It doesn't need to be a specific postgres user, you can map a whole role group to one SQL Server account and in fact define multiple. Note that the account in the username has to be a valid account on SQL Server and have access to the tables you'll be foreigning.
CREATE USER MAPPING FOR postgres SERVER localsql2008 OPTIONS (username 'pg_reader',password 'helosqlserver!');
Now we are ready to create tables to query. You can place your Foreign Tables in any postgres schema you want. I like to put ones from the same SQL database in same
schema. So for example if I want to query my SuperStore sql server database, I'll create a schema in my postgres database called
super_store like shown
below. Observe that you don't need to select all columns of a table, you could have a shortened version of your table in postgres that has just the columns you want to select.
CREATE SCHEMA super_store;
Next create the table
CREATE FOREIGN TABLE super_store.orders (orderid integer NOT NULL , customerid varchar(20) NOT NULL ) SERVER localsql2008 OPTIONS (database 'superstore', schema 'dbo', sql_query 'SELECT orderid, customerid FROM orders', sql_count 'select count(orderid) from orders');
If I'm going to have a remote server, I'll like to have a catalog of tables. Luckily SQL server has an information_schema.tables similar to postgresql, but the types don't seem to map well and even will CRASH your PostgreSQL server with an Opps! exclamation. work around we found was to do this:
CREATE FOREIGN TABLE super_store.is_tables (table_catalog character varying(128) , table_schema character varying(128) , table_name character varying(128) , table_type character varying(128) ) SERVER localsql2005 OPTIONS (database 'superstore', sql_query 'SELECT CAST(TABLE_CATALOG As varchar(128)) As table_catalog , CAST(TABLE_SCHEMA As varchar(128)) As table_schema, CAST(TABLE_NAME As varchar(128)) As table_name , CAST(TABLE_TYPE As varchar(128)) As table_type FROM information_schema.tables', sql_count 'select count(TABLE_NAME) from information_schema.tables');
The beauty of the FDW approach is you can query the foreign tables much like you can with other tables. Caution must be taken since the planner doesn't know much about the other side. So for example to pull a listing of views from our
super_store.is_tables foreign table.
SELECT table_name, table_type FROM is_tables WHERE table_type = 'VIEW';
To drop a foreign table, which it seems you must if you need to change structure, you can do a:
DROP FOREIGN TABLE IF EXISTS super_store.is_tables;
I must stress that this is not a production FDW. So you will have issues, couple we have observed with expermenting with it.
sql_queryeven if they are mixed case in SQL Server. If you don't the mapping fails and you end up with blank data in columns when you query.
Oppsmessage and the postgresql service crashes. Perhaps an issue with unicode or exotic types. So casting is necessary as we did in the information_schema example.