Re: Number of items in a cursor...

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