1

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.

Kev
  • 15,899
  • 15
  • 79
  • 112
  • Did you have a look at the dblink module? http://www.postgresql.org/docs/current/static/dblink.html –  Jan 22 '13 at 15:07
  • Sounded promising, but it only supports connections to other PostgreSQL databases. – Kev Jan 22 '13 at 15:10
  • 1
    Ah, right. I always forget that. If you are adventorous you might want to try a foreign data wrapper (odbc_fdw or jdbc_fdw): http://wiki.postgresql.org/wiki/Foreign_data_wrappers –  Jan 22 '13 at 15:12
  • Aha, sounds good. They link specifically to this article http://www.postgresonline.com/journal/archives/249-ODBC-Foreign-Data-wrapper-to-query-SQL-Server-on-Window---Part-2.html from there, right on! – Kev Jan 22 '13 at 15:17
  • Although, doesn't seem production-ready by the sounds of the article. – Kev Jan 22 '13 at 15:21
  • That's why I said "*adventurous*" ;) –  Jan 22 '13 at 15:21
  • Right. Apparently I skimmed over that word. :) – Kev Jan 22 '13 at 15:28

1 Answers1

1

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.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182