Number of items in a cursor...
Cristian Prieto wrote:
Is there any way to get the numbers of items inside a cursor?
I can't see a way to do it except to do a FETCH ALL and count the
returned rows.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Nov 15, 2005, at 12:43 PM, Bruce Momjian wrote:
Cristian Prieto wrote:
Is there any way to get the numbers of items inside a cursor?
I can't see a way to do it except to do a FETCH ALL and count the
returned rows.
What we do, via JDBC is:
MOVE <Integer.MAX_VALUE> IN cursor_name;
The JDBC drivers are nice enough to return the output message from
the MOVE command, which is the number of records moved. We just keep
doing this until it returns something less than <Integer.MAX_VALUE>.
The sum of all the moves is the total number of records. Then we
just "MOVE ABSOLUTE 0 in cursor_name;" to make use of the cursor
using FETCH.
While this does force the server to process the entire query it at
least avoids the overhead of returning all the records (which is the
point of cursors!).
eric
Eric B. Ridge wrote:
On Nov 15, 2005, at 12:43 PM, Bruce Momjian wrote:
Cristian Prieto wrote:
Is there any way to get the numbers of items inside a cursor?
I can't see a way to do it except to do a FETCH ALL and count the
returned rows.What we do, via JDBC is:
MOVE <Integer.MAX_VALUE> IN cursor_name;
The JDBC drivers are nice enough to return the output message from
the MOVE command, which is the number of records moved. We just keep
doing this until it returns something less than <Integer.MAX_VALUE>.
The sum of all the moves is the total number of records. Then we
just "MOVE ABSOLUTE 0 in cursor_name;" to make use of the cursor
using FETCH.While this does force the server to process the entire query it at
least avoids the overhead of returning all the records (which is the
point of cursors!).
Yep, that works:
test=> BEGIN;
BEGIN
test=> DECLARE xx CURSOR FOR SELECT * FROM pg_language;
DECLARE CURSOR
test=> MOVE 9999999 from xx;
MOVE 3
Notice the "MOVE 3" returned.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073