Re: [GENERAL] cursor_to_xml iteration of a table

[Peter Eisentraut]

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).



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] cursor_to_xml iteration of a table

[Richard Wallace]

Hi all -

I'm currently wrestling with generating XML output from a table that has grown 
to a size where simply using table_to_xml or query_to_xml is no longer feasible 
due to the whole result set getting loaded into memory. I've been getting 
familiar with cursors and the cursor_to_xml command and have two issues that I 
can't seem to figure out:

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;


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.

Any insight is most appreciated. Thanks!


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general