Обсуждение: cursor_to_xml iteration of a table

Hi all -

I'm currently wrestling with generating XML output from a table that has grown to a size where simply using
table_to_xmlor query_to_xml is no longer feasible due to the whole result set getting loaded into memory. I've been
gettingfamiliar 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
exita 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
memoryuntil the function completes? Ideally, I'd like to write the output to a file with each iteration of the cursor,
butI 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!

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