On 10/28/2013 04:36 PM, Perry Smith wrote:
On Oct 28, 2013, at 6:13 PM, John R Pierce <pierce@xxxxxxxxxxxx> wrote:
On 10/28/2013 3:58 PM, Adrian Klaver wrote:
The docs do a good job of illustrating:
http://www.postgresql.org/docs/9.3/interactive/plpgsql-cursors.html
thats for cursors created within a plpgsql function.
I think what the OP wants is a top level cursor, which is a different thing...
see
http://www.postgresql.org/docs/current/static/sql-declare.html
http://www.postgresql.org/docs/current/static/sql-fetch.html
http://www.postgresql.org/docs/current/static/sql-close.html
the fetch page shows an example of the complete usage in the context of a database transaction.
Thank you to Merlin. I now understand better where my confusion was.
John:
Those examples are great except there is no way that I know of to loop on
the "top level" as you call it. I'm trying to do something that I can give to
psql which will loop through the entire set that is produced.
The FETCH example shows you how. You do not have FOR but you do have
FORWARD and if you DECLARE SCROLL, BACKWARD. If you need to do actions
on each row as it is fetched then you will probably need to do it in a
function. Your original post though was concerned with dealing with an
out of memory error caused by returning to large a result set at one
time and that can be handled in psql as illustrated.
I came across the FOR-IN loop but that needs a function. But as Merlin
points out, the function is on the server side and I need the loop on the
client side.
Perhaps the
\set FETCH_COUNT 500000
solution that Merln point out originally is the only choice.
I feel like I've learned a lot even though it might not have been what
I was originally trying to learn :-)
Thank you again,
Perry
--
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
