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.
