cursor_to_xml iteration of a table
От | Richard Wallace |
---|---|
Тема | cursor_to_xml iteration of a table |
Дата | |
Msg-id | 0866883A-AAB1-4348-9412-AC02E04ACFD5@intellum.com обсуждение исходный текст |
Ответы |
Re: cursor_to_xml iteration of a table
|
Список | pgsql-general |
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!
В списке pgsql-general по дате отправления: