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;