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