Re: Number of items in a cursor...

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073