Re: Fetching from psql procedures

2015-11-20 9:14 GMT+01:00 Marc Mamin <M(dot)Mamin(at)intershop(dot)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
>
>
>