Re: WIP patch: convert SQL-language functions to return tuplestores

2008/10/28 Dimitri Fontaine <dfontaine(at)hi-media(dot)com>:
> Hi,
>
> In the python language, functions that lazily return collections are called
> generators and use the yield keyword instead of return.
> http://www.python.org/doc/2.5.2/tut/node11.html#SECTION00111000000000000000000
>
> Maybe having such a concept in PostgreSQL would allow the user to choose
> between current behavior (materializing) and lazy computing, with a new
> internal API to get done in the executor maybe.
>

lazy computing is good idea, but I am afraid so it should be really
wery hard implemented. You should to store somewhere current state,
stop execution, return back from node, and you should be able restore
PL state and continue in process. I can't to see it without thread
support.

Regards
Pavel Stehule

> CREATE FUNCTION mygenerator()
> returns setof integer
> language PLPGSQL
> AS $f$
> BEGIN
> FOR v_foo IN SELECT foo FROM table LOOP
> YIELD my_expensive_function(v_foo);
> END LOOP;
> RETURN;
> END;
> $f$;
>
> At the plain SQL level, we could expose this with a new function parameter,
> GENERATOR maybe?
>
> CREATE FUNCTION my_generator_example(integer, integer)
> returns setof integer
> generator
> language SQL
> $f$
> SELECT generate_series($1, $2);
> $f$;
>
> Maybe we should prefer to add the GENERATOR (or LAZY or whatever sounds good
> for a native English speaker) parameter to PL functions to instead of
> providing YIELD, having RETURN doing YIELD in this case.
>
> Le mardi 28 octobre 2008, Tom Lane a écrit :
>> I suppose, but short of a fundamental rethink of how PL functions work
>> that's not going to happen. There's also the whole issue of when do
>> side-effects happen (such as before/after statement triggers).
>
> Would it be possible to forbid "generators" when using in those cases?
>
>> Agreed, but I think the fundamental solution there, for simple-select
>> functions, is inlining.
>
> Would it be possible to maintain current behavior with ROWS estimator for
> functions, even when inlining, as a way to trick the planner when you can't
> feed it good enough stats?
>
>> I think the PL side of the problem is the hard part --- if we knew how
>> to solve these issues for plpgsql then SQL functions would surely be
>> easy.
>
> What about this python idea of GENERATORS and the YIELD control for lazy
> evaluation of functions?
> --
> dim
>