Re: refcursor and number of records

Jochem van Dieten <jochemd(at)oli(dot)tudelft(dot)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