There are SQL Server 2008 R2 and PostgreSQL 9.2 under Windows (currently XP SP3). How can I query SQL Server from PostgreSQL? No problem when SQL Server->PostgreSQL via Linked Server.
Searching web I found odbc_fdw which is not possible to compile for 9.2.
Is there any working solution?
-
PostgreSQL dblink only connects PostgreSQL to PostgreSQLOMG Ponies– OMG Ponies2013-02-02 23:15:18 +00:00Commented Feb 2, 2013 at 23:15
-
@OMG Ponies: exactly, but dblink is not the issue... I am planning to use OLE DB or ODBC plus any available working fdw.revoua– revoua2013-02-02 23:31:13 +00:00Commented Feb 2, 2013 at 23:31
-
Best of luck. How current does the data need to be? Could it work as a nightly job to suck the data from SQL Server to populate a PostgreSQL database?OMG Ponies– OMG Ponies2013-02-03 00:12:27 +00:00Commented Feb 3, 2013 at 0:12
-
Thanks. Not nightly, should run as regular queries. Currently application layer is used (php script) which takes data from both dbs and operate with arrays outputting result to client. But because of big number of rows I need to iterate several times instead of doing everything in db in one query. To transfer data costs a lot (revoua– revoua2013-02-03 00:29:52 +00:00Commented Feb 3, 2013 at 0:29
Add a comment
|
2 Answers
In addition to the standard but immature SQL/MED and foreign data wrappers mentioned by @vyegorov, the traditional solution to this problem has been DBI-Link, a set of foreign data mapping functions that use Perl DBI via plperl.
Comments
There is a SQL/MED extension that describes how database can access external data.
PostgreSQL supports this part of the standard via the Foreign Data Wrappers. The linked page describes all (well, almost all I suppose) FDWs that are available now. I think that your finding on odbc_fdw are correct, but I admit, that it is not always straightforward to make FDWs working, especially on Windows.
You can take a look at Multicorn if you're familiar with Python.
