jaitor1 / odbc_fdw Goto Github PK

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
[email protected]