Execute function returning data in remote PostgreSQL database from local PostgreSQL database

Question

Postgres version: 9.3.4

I have the need to execute a function which resides in a remote database. The function returns a table of statistic data based on the parameters given.

I am in effect only mirroring the function in my local database to lock down access to this function using my database roles and grants.

I have found the following which seem to only provide table-based access.

First question: is that correct or are there ways to use these libraries for non-table based operations?

I have found the following which seems to provide me with any SQL operation on the foreign database. The negative seems to be increased complexity and reduced performance due to manual connection and error handling.

http://www.postgresql.org/docs/9.3/static/dblink.html

Second question: are these assumptions correct, and are there any ways to bypass these concerns or libraries/samples one can begin from?

There's wiki.postgresql.org/wiki/PL/Proxy but that might also be overkill too. — Richard Huxton
– Richard Huxton, Commented Jul 21, 2014 at 12:11

harmic · Accepted Answer · 2014-07-21 12:35:32Z

4

The fdw interface provides a way to make a library which can allow a postgresql database to query any external data source as though it was a table. From that point of view, it could do what you want.

The inbuilt postgresql_fdw driver, however, does not allow you to specify a function as a remote table.

You could write your own fdw driver, possibly using the multicorn library, or some other language. That is likely to be a bit of work though, and would have some specific disadvantages, in particular I don't know how you would pass parameters to the function.

dblink is probably going to be the easiest solution. It allows you to execute arbitrary SQL on the remote server, returning a set of records.

SELECT *
FROM dblink('dbname=mydb', 'SELECT * FROM thefunction(1,2,3)')
     AS t1(col1 INTEGER, col2 INTEGER);

There are other potential solutions but they would all be more effort to set up.

answered Jul 21, 2014 at 12:35

harmic

31k6 gold badges75 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

atom.gregg Over a year ago

Thanks for confirming what I had suspected. I will wait some time and see if anyone knows how to use a function in an FDW, and fallback to using dblink as it seems to be far less work than writing a custom FDW.

Collectives™ on Stack Overflow

Execute function returning data in remote PostgreSQL database from local PostgreSQL database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related