Обсуждение: return next

I'm attempting to make a table function that starts returning to the
client before the entire result set is constructed.

From the docs on plperl:
"Usually you'll want to return rows one at a time, both to speed up
startup time and to keep from queueing up the entire result set in
memory. You can do this with return_next as illustrated below."

However, when I do a simple plperl function:

create or replace function itab(int) returns setof int as $$
for (0..$_[0]-1) {
    return_next $_;
}
return undef;
$$ language plperlu;

It seems to always try to build the entire result first. For example, if
I put a "sleep 1" in the loop and do "select * from itab(10) limit 1" it
takes 10 seconds rather than 1 second. Same if I use a cursor and just
fetch one.

Am I misunderstanding the docs? How do I just return one tuple at a time
without PostgreSQL continuing the loop?

I'm using PostgreSQL 8.1.

Regards,
    Jeff Davis

Jeff Davis <pgsql@j-davis.com> writes:
> From the docs on plperl:
> "Usually you'll want to return rows one at a time, both to speed up
> startup time and to keep from queueing up the entire result set in
> memory. You can do this with return_next as illustrated below."

> Am I misunderstanding the docs? How do I just return one tuple at a time
> without PostgreSQL continuing the loop?

The docs are perhaps a little misleading.  The perl function will
execute to completion in any case --- it's hard to see how to prevent
that from happening without breaking perl.  The point of the comment
is that with return_next, buffering of the result set happens in a
TupleStore object (which knows how to spill an oversize set to disk)
rather than inside perl (which doesn't).

            regards, tom lane