Re: CREATE ROUTINE MAPPING

> > CREATE ROUTINE MAPPING local_routine_name
> > > FOR (FUNCTION | PROCEDURE) remote_routine_name ( [ [ argmode ] [
> argname ]
> > > argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
> > > [ RETURNS rettype
> > > | RETURNS TABLE ( column_name column_type [, ...] ) ]
> > > SERVER foreign_server_name
> > > [ (option [, ...]) ]
> > >
> > > Does that seem like too broad an interpretation?
> > >
> >
> > I had entertained having a pg_routine_mappings table like
> > pg_user_mappings, and we still could, if the proc's language of
> > 'external' clued the planner to look for the mapping. I can see
> > arguments for either approach.
>
> It would be good to have them in the catalog somehow if we make CREATE
> ROUTINE MAPPING a DDL. If I've read the standard correctly, there are
> parts of information_schema which come into play for those routine
> mappings.
>
> > Before anyone asks, I looked for, and did not find, any suggestion of
> > IMPORT FOREIGN ROUTINE a la IMPORT FOREIGN SCHEMA, so as of yet there
> > wouldn't be any way to grab all the functions that .a foreign server is
> > offering up.
>
> How about making an option to IMPORT FOREIGN SCHEMA do it?
>
>

Ok, so the steps seem to be:
1. settle on syntax.
2. determine data dictionary structures
3. parse and create those structures
4. "handle" external functions locally
5. provide structures passed to FDW handlers so that they can handle
external functions
6. implement those handlers in postgres_fdw

#1 is largely prescribed for us, though I'm curious as to how the CRM
statements I've made up in examples above would look like as CREATE
FUNCTION ... SERVER ...

#2 deserves a lot of debate, but probably mostly hinges on the new
"language" and how to associate a pg_proc entry with a pg_foreign_server

#3 i'm guessing this is a lot of borrowing code from CREATE ROUTINE MAPPING
but is otherwise pretty straightforward.

#4 an external function obviously cannot be executed locally, doing so
means that the planner failed to push it down, so this is probably
stub-error functions

#5 These functions would essentially be passed in the same as foreign
columns with the "name" as "f(a,b,4)", and the burden of forming the remote
query is on the FDW

Which gets tricky. What should happen in simple situations is obvious:

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;

And it's still simple when local functions consume remote input

SELECT local_func1(remote_func1(r.x)) FROM remote_table r WHERE r.active =
true;

But other situations seem un-handle-able to me:

SELECT remote_func1(l.x) FROM local_table l WHERE l.active = true;

In those cases, at least initially, I think the FDW handler is right to
raise an error, because the function inputs are unknowable at query time,
and the inputs cannot also be pushed down to the remote server. That might
not be common, but I can see situations like this:

SELECT r.*
FROM remote_srf( ( SELECT remote_code_value FROM
local_table_of_remote_codes WHERE local_code_value = 'xyz' ) ) r;

and we would want things like that to work. Currently is similar
table-situations the FDW has no choice but to fetch the entire table and
filter locally. That's good for tables, whose contents are knowable, but
the set of possible function inputs is unreasonably large. The current
workaround in table-land is to run the inner query locally, and present the
result at a constant to a follow-up query, so maybe that's what we have to
do here, at least initially.

#6 is where the FDW either does the translation or rejects the notion that
functions can be pushed down, either outright or based on the usage of the
function in the query.

I'm doing this thinking on the mailing list in the hopes that it evokes
suggestions, warnings, suggested code samples, and of course, help.