Thread: 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 ?
thanks and best regards,
Marc Mamin
Hi
2015-11-19 14:51 GMT+01:00 Marc Mamin <M.Mamin@intershop.de>:
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 and best regards,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
>> 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
2015-11-20 9:14 GMT+01:00 Marc Mamin <M.Mamin@intershop.de>:
>> 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.
sure
Regards
Pavel
regards,
Marc Mamin