ODBC FDW (beta) for PostgreSQL 9.1+

This PostgreSQL extension implements a Foreign Data Wrapper (FDW) for remote databases using Open Database Connectivity(ODBC): http://msdn.microsoft.com/en-us/library/ms714562(v=VS.85).aspx

Building

To build the code, you need to have one of the ODBC driver managers installed on your computer.

A list of driver managers is available here: http://en.wikipedia.org/wiki/Open_Database_Connectivity

Once that's done, the extension can be built with:

PATH=/usr/local/pgsql91/bin/:$PATH make USE_PGXS=1 make sudo PATH=/usr/local/pgsql91/bin/:$PATH make USE_PGXS=1 install

(assuming you have PostgreSQL 9.1 in /usr/local/pgsql91).

I've tested on Mac OS X 10.6 and Windows 7. More testings on other platforms are coming soon.

TODO

I will test against other platforms with different driver managers and more foreign DBMS's.

Usage

The following parameters can be set on ODBC foreign server:

dsn: The Database Source Name for the foreign database system you're connecting to. Default:

The following parameter can be set on a ODBC foreign table:

database: The name of the database to query. Default:

schema: The schema of the database to query. Default:

table: The name of the table to query. Default:

sql_query: Optional: User defined SQL statement for querying the foreign table. Default:

sql_count: Optional: User defined SQL statement for counting number of records in the foreign table. Default:

: The column mapping to remote table columns. If there is no column mapping specified, default is the origninal column name.

The following parameter can be set on a user mapping for a ODBC foreign server:

username: The username to authenticate to the foreign server with. Default:

password: The password to authenticate to the foreign server with. Default:

Example

CREATE SERVER odbc_server FOREIGN DATA WRAPPER odbc_fdw OPTIONS (dsn 'test');

CREATE FOREIGN TABLE odbc_table ( db_id integer, db_name varchar(255), db_desc text, db_users float4, db_createdtime timestamp ) SERVER odbc_server OPTIONS ( database 'myplace', schema 'test', table 'dblist', sql_query 'select description,id,name,created_datetime,sd,users from test.dblist', sql_count 'select count(id) from test.dblist', db_id 'id', db_name 'name', db_desc 'description', db_users 'users', db_createdtime 'created_datetime' );

CREATE USER MAPPING FOR postgres SERVER odbc_server OPTIONS (username 'root', password '');

-- Zheng Yang [email protected]