Re: Fetching from psql procedures

>> Hi,
>>
>> I'm looking for a way to build large xml documents based on a set of tables, hence using recursive queries.
>>
>>
>> something like:
>>
>> FOR rec1 IN ...
>> FOR rec2 IN ...
>>
>> RETURN QUERY select '<foo>>
>> FOR rec3 IN ...
>>
>> RETURN QUERY select (XMLELEMENT (...))::text
>>
>> END LOOP;
>> RETURN QUERY select '</foo>>
>>
>> END LOOP;
>> END LOOP;
>>
>>
>>
>> but if I wrap this in a function, it seems that I won't get a result before the whole document is prepared.
>>
>> Is there any way to stream the result as it get produced ?
>> or does anybody knows of a good tutotial for a task like this ?

>
> you can try CTE Common Table Expression. It isn't possible with plpgsql :(
> Theoretically you can write C extension where SRF function can returns data continually.But plpgsql function using local stack and returns data as block.
> Regards
> Pavel

Thanks.
A simpler solution will be to store the result in a temp table and then call a second query to stream it out.

regards,

Marc Mamin