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