Skip to content
PostgreSQL Foreign-data Wrapper for ODBC
C
Find file
Failed to load latest commit information.
expected skeleton Jun 14, 2011
sql skeleton Jun 14, 2011
.gitignore skeleton Jun 14, 2011
Makefile skeleton Jun 14, 2011
README supported platforms information Aug 26, 2011
odbc_fdw--1.0.sql skeleton Jun 14, 2011
odbc_fdw.c minor changes to get rid of visual studios error C2143 Aug 26, 2011
odbc_fdw.control skeleton Jun 14, 2011

README

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
Something went wrong with that request. Please try again.