CODEWITHSUNDEEP
postgresqlpostgresql-9.3
atom.gregg
atom.gregg

Reputation: 1007

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

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.

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

Upvotes: 3

Views: 4950

Answers (1)

harmic
harmic

Reputation: 30597

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.

Upvotes: 4

Related Questions