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: <none> The following parameter can be set on a ODBC foreign table: database: The name of the database to query. Default: <none> schema: The schema of the database to query. Default: <none> table: The name of the table to query. Default: <none> sql_query: Optional: User defined SQL statement for querying the foreign table. Default: <none> sql_count: Optional: User defined SQL statement for counting number of records in the foreign table. Default: <none> <column name>: 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: <none> password: The password to authenticate to the foreign server with. Default: <none> 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 zhengyang4k@gmail.com