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@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