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