Re: refcursor and number of records

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