Thread: Number of items in a cursor...

Is there any way to get the numbers of items inside 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

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

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