ODBC Foreign Data wrapper to query SQL Server on Window - Part 2   

How to Install ODBC FDW handler and Setup Foreign Server Connection

1. 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.

2. Install the ODBC FDW in your PostgreSQL with SQL Statement: CREATE EXTENSION odbc_fdw;
3. Setup a System DSN entry via ODBC manager. If you are running PostgreSQL 32-bit on a windows 64-bit machine, the ODBC manager you want is the one in C:\Windows\SysWOW64\odbcad32.exe. For postgresql 64-bit or postgresql 32-bit on 32-bit windows, its the one in C:\windows\System32\odbcad32.exe. I'm running SQL Server 2008 so I used the SQL Client 10. I named the DSN something meaningful so I know its specifically for postgres e.g. LocalSQL2008ForPg.
4. Next create a Foreign Server that uses this DSN connection CREATE SERVER localsql2008 FOREIGN DATA WRAPPER odbc_fdw OPTIONS (dsn 'LocalSQL2008ForPg');

5. 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!');

Creating Foreign Tables: The basics

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.

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');

More challenging table -- information_schema.tables

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');

Querying the 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.

Dropping Foreign tables

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;

Gotchas

I must stress that this is not a production FDW. So you will have issues, couple we have observed with expermenting with it.

• It doesn't like integer columns that have NULLs. So we had to declare our nullable integer fields as varchar instead of integer. Otherwise we get an error Can't CAST NULL to integer.
• Case sensitivity. It's best to lowercase all the fields in your sql_query even 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.
• Some tables just cause crashing. Haven't figured out that -- we just get a Opps message 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.
• The driver doesn't give any errors when you have typos in your SQL or invalid mappings. To find these out, we had to run them in SQL Server to confirm they run.