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.
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.