Accessing data on Microsoft SQL Server from a PostgreSQL function

Question

I have an application that uses PostgreSQL but also interacts with a third-party-controlled database on MSSQL. The data are sometimes tied together closely enough that it becomes desirable to do things like:

select thing_from_pg, thing_from_ms_crossover_function(thing_from_pg) -- etc

Currently I implement thing_from_ms_crossover_function in plperl. Is there a way to do this in plpgsql or something, so that I don't need to start a plperl interpreter for such cases?

Another option is obviously to access both databases from my client app, but that becomes far less convenient than the view syntax above.

Did you have a look at the dblink module? postgresql.org/docs/current/static/dblink.html — user330315, Commented Jan 22, 2013 at 15:07
Sounded promising, but it only supports connections to other PostgreSQL databases. — Kev, Commented Jan 22, 2013 at 15:10
Ah, right. I always forget that. If you are adventorous you might want to try a foreign data wrapper (odbc_fdw or jdbc_fdw): wiki.postgresql.org/wiki/Foreign_data_wrappers — user330315, Commented Jan 22, 2013 at 15:12
Aha, sounds good. They link specifically to this article postgresonline.com/journal/archives/… from there, right on! — Kev, Commented Jan 22, 2013 at 15:17
Although, doesn't seem production-ready by the sounds of the article. — Kev, Commented Jan 22, 2013 at 15:21

Chris Travers · Accepted Answer · 2013-04-22 06:26:47Z

You have two basic options, well three basic ones rather.

The first is to use DBI-Link and then access this via your pl/pgsql or pl/perl function. The nice thing about DBI-Link is that it is relatively older and mature. If it works for you I would start there.

The second option is to use foreign data wrappers.

The third option is to write a more general framework in something like pl/perl that you can call from pl/pgsql. However at that point you are basically looking at re-inventing DBI-Link so I think you are better off starting with DBI-Link and modifying it as needed.

Collectives™ on Stack Overflow

Accessing data on Microsoft SQL Server from a PostgreSQL function

1 Answer 1

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related