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
localstack 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