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
