Re: SQL advice needed

On 17/03/14 21:42, Merlin Moncure wrote:
>> I can do it in plpgsql. But that would mean to accumulate the complete
>> > result in memory first, right? I need to avoid that.
> I would test that assumption. This is better handled in loop IMO.
>
> LOOP
> RETURN QUERY SELECT * FROM xx();
> IF NOT found
> THEN
> RETURN;
> END IF;
> END LOOP;

At least according to the manual it is stored in memory:

<cite>
Note: The current implementation of RETURN NEXT and RETURN QUERY stores
the entire result set before returning from the function, as discussed
above. That means that if a PL/pgSQL function produces a very large
result set, performance might be poor: data will be written to disk to
avoid memory exhaustion, but the function itself will not return until
the entire result set has been generated. A future version of PL/pgSQL
might allow users to define set-returning functions that do not have
this limitation. Currently, the point at which data begins being written
to disk is controlled by the work_mem configuration variable.
Administrators who have sufficient memory to store larger result sets in
memory should consider increasing this parameter.
</cite>

I didn't test that, though.

Torsten