On 10/28/2013 03:49 PM, Perry Smith wrote:
On Oct 28, 2013, at 5:21 PM, Adrian Klaver <adrian.klaver@xxxxxxxxx> wrote:
On 10/28/2013 02:27 PM, Perry Smith wrote:
On Oct 28, 2013, at 4:11 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:
On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith <pedzsan@xxxxxxxxx> wrote:
When I execute the SELECT statement directly I get:
psql:table.sql:28: out of memory for query result
psql will do this automatically if you tell it to:
http://doginpool.blogspot.com/2011/11/retrieving-large-resultsets-from.html
cursors can work with your code, but they need participation from the
client side. basically you stage the cursor then repeatedly FETCH
until done.
Ok. I'll try that. I'm still curious how to do it using cursors if anyone wants
to provide a sample.
http://www.postgresql.org/docs/9.3/interactive/sql-fetch.html
Yea, I looked at those. Part of my original email got lost. Here is from the original
email:
Well the point of the example at the above link is that you do not have
to do it in a function:)
CREATE OR REPLACE FUNCTION blah() RETURNS integer AS $$
DECLARE
xyz CURSOR FOR SELECT * FROM shipped_files WHERE aix_file = '/usr/lib/drivers/vioentdd' AND service_pack = '7100-01-06';
BEGIN
FOR my_rec IN xyz LOOP
-- xyxyxy what to do where? xyxyxy These things don't work:
COPY ( my_rec ) TO stdout;
SELECT * FROM my_rec;
etc.
END LOOP;
RETURN 5;
END
$$ LANGUAGE plpgsql;
SELECT * FROM blah();
Perhaps my question is what do I do with my_rec once I have it? I want
output the same as if I did the top SELECT statement directly.
The docs do a good job of illustrating:
http://www.postgresql.org/docs/9.3/interactive/plpgsql-cursors.html
--
Adrian Klaver
adrian.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
