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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
