Hi Corey Have you looked at pl/proxy ?
It does this and then some (sharding) It actually started out as a set of pl/pythonu functions, but then got formalized into a full extension language for defining remote (potentially sharded) function calls Best Regards Hannu Krosng On Fri, 12 Jan 2018 at 03:38, Corey Huinker <corey.huin...@gmail.com> wrote: > 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. >
