Re: poc - possibility to write window function in PL languages

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> [ plpgsql-window-functions-20210104.patch.gz ]

I spent some time looking at this patch. It would certainly be
appealing to have some ability to write custom window functions
without descending into C; but I'm not very happy about the details.

I'm okay with the idea of having a special variable of a new pseudotype.
That's not exactly pretty, but it descends directly from how we handle
the arguments of trigger functions, so at least there's precedent.
What's bugging me though is the "typedvalue" stuff. That seems like a
conceptual mess, a performance loss, and a permanent maintenance time
sink. To avoid performance complaints, eventually this hard-wired set
of conversions would have to bloom to cover every built-in cast, and
as for extension types, you're just out of luck.

One way to avoid that would be to declare the argument-fetching
functions as polymorphics with a dummy argument that just provides
the expected result type. So users would write something like

create function pl_lag(x numeric)
...
v := get_input_value_in_partition(windowobject, x, 1, -1,
'seek_current', false);

where the argument-fetching function is declared

get_input_value_in_partition(windowobject, anyelement, int, ...)
returns anyelement

and internally it could verify that the n'th window function argument
matches the type of its second argument. While this could be made
to work, it's kind of unsatisfying because the argument number "1" is
so obviously redundant with the reference to "x". Ideally one should
only have to write "x". I don't quite see how to make that work,
but maybe there's a way?

On the whole though, I think your original idea of bespoke plpgsql
syntax is better, ie let's write something like

GET WINDOW VALUE v := x AT PARTITION CURRENT(-1);

and hide all the mechanism behind that. The reference to "x" is enough
to provide the argument number and type, and the window object doesn't
have to be explicitly visible at all.

Yeah, this will mean that anybody who wants to provide equivalent
functionality in some other PL will have to do more work. But it's
not like it was going to be zero effort for them before. Furthermore,
it's not clear to me that other PLs would want to adopt your current
design anyway. For example, I bet PL/R would like to somehow make
window arguments map into vectors on the R side, but there's no chance
of that with this SQL layer in between.

regards, tom lane