Re: refcursor and number of records

Jochem van Dieten  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