Обсуждение: refcursor and number of records

Hi all,

After running a rather large query (30+ seconds) a resultset will be
returned. That resultset can be anywhere from 1 to 1500 records. To
reduce frontend memory requirements, I would like to create a function
that returns a refcursor for the query and just fetch 10 records at a
time from the frontend. But I would also like to show "displaying
records 11 to 20 of 1443". I just can't figure out how to get the number
of records in the cursor without fetching them all. Any suggestions on
how to get this number (1443 in the example)?

JDBC
PostgreSQL 7.2.1

Jochem

Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
> ... I would like to create a function
> that returns a refcursor for the query and just fetch 10 records at a
> time from the frontend. But I would also like to show "displaying
> records 11 to 20 of 1443". I just can't figure out how to get the number
> of records in the cursor without fetching them all. Any suggestions on
> how to get this number (1443 in the example)?

Well, you could do

regression=# begin;
BEGIN
regression=# declare c cursor for select unique1 from tenk1;
DECLARE CURSOR
regression=# move forward all in c;
MOVE 10000                             <--- here is your number
regression=# move backward all in c;
MOVE 10000
regression=# fetch 10 from c;
 unique1
---------
    8800
    1891
    ... etc ...

Keep in mind though that this is extremely expensive since it implies
that the backend actually internally fetches all the data --- the *only*
difference between MOVE and FETCH is that MOVE throws away the data it
would otherwise have sent you.  Also, I wouldn't care to bet that MOVE
BACKWARD will work reliably on any but the simplest query plans.  It's
got known problems.  (Re-creating the cursor might be safer.)

            regards, tom lane