A few months ago, I was researching ways for formalizing calling functions on one postgres instance from another. RPC, basically. In doing so, I stumbled across an obscure part of the the SQL Standard called ROUTINE MAPPING, which is exactly what I'm looking for.
The syntax specified is, roughly:
CREATE ROUTINE MAPPING local_routine_name FOR remote_routine_spec
SERVER my_server [ OPTIONS( ... ) ]
Which isn't too different from CREATE USER MAPPING.
The idea here is that if I had a local query:
SELECT t.x, remote_func1(), remote_func2(t.y)
FROM remote_table t
WHERE t.active = true;
that would become this query on the remote side:
SELECT t.x, local_func1(), local_func2(t.y)
FROM local_table t
WHERE t.active = true;
That was probably the main intention of this feature, but I see a different possibility there. Consider the cases:
SELECT remote_func(1,'a');
and
SELECT * FROM remote_srf(10, true);
Now we could have written remote_func() and remote_srf() in plpythonu, and it could access whatever remote data that we wanted to see, but that exposes our local server to the untrusted pl/python module as well as python process overhead.
We could create a specialized foreign data wrapper that requires a WHERE clause to include all the require parameters as predicates, essentially making every function a table, but that's awkward and unclear to an end user.
Having the ability to import functions from other servers allows us to write foreign servers that expose functions to the local database, and those foreign servers handle the bloat and risks associated with accessing that remote data.
Moreover, it would allow hosted environments (AWS, etc) that restrict the extensions that can be added to the database to still connect to those foreign data sources.
I'm hoping to submit a patch for this someday, but it touches on several areas of the codebase where I have no familiarity, so I've put forth to spark interest in the feature, to see if any similar work is underway, or if anyone can offer guidance.
Thanks in advance.
