Thread: Stateful pointers in set-returning functions

Hi,

I'm writing a set-returning function which places a file handle into
PG's FuncCallContext's user_fctx space. My problem is that when the
function is ran with a limit clause (SELECT * FROM foo() LIMIT 10) the
server will stop calling the function automatically, not giving me a
chance to close the file handle. Is there a way to get the limit value
inside of foo() and set the max max_calls parameter correctly?

Thanks,

Ian

Ian Pye <ianpye@gmail.com> writes:
> I'm writing a set-returning function which places a file handle into
> PG's FuncCallContext's user_fctx space. My problem is that when the
> function is ran with a limit clause (SELECT * FROM foo() LIMIT 10) the
> server will stop calling the function automatically, not giving me a
> chance to close the file handle. Is there a way to get the limit value
> inside of foo() and set the max max_calls parameter correctly?

No, and even if there were, this would be a very unsafe practice,
since errors or other issues could result in early termination of the
query.

You would likely be better off using tuplestore return mode so that you
can do all the reading during one call and not have to assume that
you'll get control back again.
        regards, tom lane

Fair enough -- thanks for the tip.

On Thu, May 24, 2012 at 11:21 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ian Pye <ianpye@gmail.com> writes:
>> I'm writing a set-returning function which places a file handle into
>> PG's FuncCallContext's user_fctx space. My problem is that when the
>> function is ran with a limit clause (SELECT * FROM foo() LIMIT 10) the
>> server will stop calling the function automatically, not giving me a
>> chance to close the file handle. Is there a way to get the limit value
>> inside of foo() and set the max max_calls parameter correctly?
>
> No, and even if there were, this would be a very unsafe practice,
> since errors or other issues could result in early termination of the
> query.
>
> You would likely be better off using tuplestore return mode so that you
> can do all the reading during one call and not have to assume that
> you'll get control back again.
>
>                        regards, tom lane