Re: Re: can external C-function get multiple rows?

Alexey Nalbat <alexey(at)price(dot)ru> writes:
> But I also want this function to work correctly, when used in a query
> with limit clause, like "select myarr(100) limit 6;". After a bit of
> experiments I supposed that while executing this query postgres called
> myarr() seven times (not six!).

Indeed. Observe the comments in nodeLimit.c:

* If we have reached the subplan EOF or the limit, just quit.
*
* NOTE: when scanning forwards, we must fetch one tuple beyond the
* COUNT limit before we can return NULL, else the subplan won't
* be properly positioned to start going backwards. Hence test
* here is for position > netlimit not position >= netlimit.
*
* Similarly, when scanning backwards, we must re-fetch the last
* tuple in the offset region before we can return NULL.
* Otherwise we won't be correctly aligned to start going forward
* again. So, although you might think we can quit when position
* equals offset + 1, we have to fetch a subplan tuple first, and
* then exit when position = offset.

Relying on static state as you are having your function do is hopelessly
unreliable anyway --- what happens if the query is aborted partway
through by some error? You'll be messed up when a new query is issued,
that's what.

I would suggest storing the cross-call state you need in a memory
block that you allocate on first call and save a pointer to in
fcinfo->flinfo->fn_extra. Strictly speaking this is an abuse of the
fn_extra feature, since the caller is not required to preserve that
across successive calls in one query, but in practice it will work.
Don't forget to do the allocation in the proper context, viz

ptr = MemoryContextAlloc(fcinfo->flinfo->fn_mcxt, sizewanted);

In this way, the state automatically goes away at end of query,
and you'll always see a NULL fcinfo->flinfo->fn_extra at first
call in a new query.

regards, tom lane