Re: PostgreSQL and SOAP, suggestions?

mlw writes:

> That function looks great, but what happens if you need to return 1
> million records?

The same thing that happens with any set-returning function: memory
exhaustion.

> I have an actual libpq program which performs a query against a server,
> and will stream out the XML, so the number of records has very little
> affect on efficiency. I think the table2xml function is great for 99% of
> all the queries, but for those huge resultsets, I think it may be
> problematic.
>
> What do you think?

Clearly, my approach is not sufficient if you need to handle big result
sets. But perhaps a compromise based on cursors could be designed so that
large parts of the format can be managed centrally. Such as:

DECLARE foo CURSOR FOR SELECT ... ;

-- gives you the XML Schema for the result set
SELECT xmlschema_from_cursor(foo);

-- gives you ones row (<row>...</row>)
SELECT xmldata_from_cursor(foo);

--
Peter Eisentraut peter_e(at)gmx(dot)net