Re: table function: limit, offset, order

Joe Conway <mail(at)joeconway(dot)com> writes:
> Vadim Menshakov wrote:
>> select * from My_Table_Func ( arg1, arg2, .... ) limit 3;
>> expecting the function to execute only 3 times. But it executes 10000 times
>> (taking lots of time), and THEN returns only 3 rows. Of course, I can pass
>> the limit into my function as an argument, but this will increase the number
>> of arguments (in fact, there's plenty of them already).

> I don't think there is any way for the function to determine that there
> is a limit clause. An argument is most likely your only choice.

This is one of the limitations of the present table function
implementation; there should be a way for the function to return one row
per call. (We talked about that back in the early stages of the table
function project, but it remains undone.) Given that, a LIMIT would
simply cause the executor to stop fetching rows from the function.

I'm not sure the plpgsql implementation could support such an operating
mode, but the SQL-function implementation could do it easily; and of
course C functions could do it if they don't mind saving their state
from call to call.

Actually, the pieces are all in place for this already, now that
TupleStore can support interleaved read and write. For a set function
using the row-per-call behavior, it'd be possible to run the function
only when new rows are actually demanded from the FunctionScan node.
However, making this work in parallel with the single-call-returns-a-
TupleStore mode could make the code pretty ugly...

regards, tom lane