Re: Find out the number of rows returned by refcursor?

Karen Hill wrote:
> What is the best way to find out the total number of rows returned by
> an refcursor?  This would allow the client user to know the total
> amount of rows as they are using FETCH FORWARD/BACKWARD.
>
> For example let's say that an refcursor has 300 rows.  The user fetches
> 20 at a time.  I would like the user to know that there are 300
> possible rows.

I probably should re-phrase that question.

CREATE OR REPLACE FUNCTION foobar( refcursor ) RETURNS refcurser AS '
BEGIN
OPEN $1 FOR SELECT * FROM t ORDER by z;
END;
' LANGUAGE 'plpgsql';

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Is there a way to know the total number of rows the cursor is
capable of traversing without using --count? Perhaps GET DIAGNOSTICS
ROW_COUNT?
SELECT foobar('mycursor');
-- I want to avoid using count(*) for performance reasons. Getting the
total number of rows the cursor --has.  I suspect it there is a system
variable that has this information...I just don't know which one it
--is.
SELECT COUNT(*) FROM t;

COMMIT;