From: | Peter Eisentraut <peter_e(at)gmx(dot)net> |
---|---|
To: | Richard Wallace <rwallace(at)intellum(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: cursor_to_xml iteration of a table |
Date: | 2010-05-26 20:12:31 |
Message-ID: | 1274904751.19408.14.camel@vanquo.pezone.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On tis, 2010-05-25 at 12:05 -0400, Richard Wallace wrote:
> 1) When using cursor_to_xml in a plpgsql function, the FOUND variable does not seem to get set, so there is no way to exit a loop that is iterating over the cursor. Below is the function code; it loops indefinitely when it is run.
>
> create or replace function getxml() returns setof xml as $$
> declare
> resultxml xml;
> curs refcursor;
> begin
> open curs for select * from groups;
> loop
> select cursor_to_xml(curs,1000, false, false, '') into resultxml;
> return next resultxml;
> exit when not found;
> end loop;
> end;
> $$ language plpgsql;
Yeah, there doesn't seem to be a good way out of that. When the end of
the cursor is reached, cursor_to_xml returns an empty xml value (which
is probably bogus in itself, since that is not a valid xml value to
begin with), so you could test it like this:
exit when resultxml::text = '';
> 2) Assuming the above issue is fixed, how can I go about ensuring that the result set from the function isn't stored in memory until the function completes? Ideally, I'd like to write the output to a file with each iteration of the cursor, but I know file IO is a big no-no with plpgsql since transactions can't manage the state of files being written.
cursor_to_xml is more meant to be used from a client. If you do it like
in the above function, you will indeed build the result in memory
(multiple times, perhaps).
From | Date | Subject | |
---|---|---|---|
Next Message | John Gage | 2010-05-26 20:29:58 | 110,000,000 rows |
Previous Message | Gauthier, Dave | 2010-05-26 19:14:37 | Re: Does update = delete + insert ? |