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;

"Karen Hill" <karen_hill22@yahoo.com> writes:

-- Is there a way to know the total number of rows the cursor is
capable of traversing without using --count?

If you want an accurate count, the only way is to traverse the cursor.
Consider using MOVE FORWARD ALL and noting the rowcount, then MOVE
BACKWARD ALL to reset the cursor (the latter at least should be
reasonably cheap).

If you can settle for a (potentially very inaccurate) estimate, consider
using EXPLAIN on the query and noting the planner's rowcount estimate.

regards, tom lane

Tom Lane wrote:

"Karen Hill" <karen_hill22@yahoo.com> writes:

-- Is there a way to know the total number of rows the cursor is
capable of traversing without using --count?

If you want an accurate count, the only way is to traverse the cursor.
Consider using MOVE FORWARD ALL and noting the rowcount, then MOVE
BACKWARD ALL to reset the cursor (the latter at least should be
reasonably cheap).

Cool. Quick question, how does one go about noting the rowcount?
Using the rowcount in get diagnostics or something else?

regards,
karen.

Karen Hill wrote:

Tom Lane wrote:

"Karen Hill" <karen_hill22@yahoo.com> writes:

-- Is there a way to know the total number of rows the cursor is
capable of traversing without using --count?

If you want an accurate count, the only way is to traverse the cursor.
Consider using MOVE FORWARD ALL and noting the rowcount, then MOVE
BACKWARD ALL to reset the cursor (the latter at least should be
reasonably cheap).

Cool. Quick question, how does one go about noting the rowcount?
Using the rowcount in get diagnostics or something else?

A "MOVE FORWARD ALL FROM cur;" statement returns "MOVE x". Where x is
the number moved. The result seems to be of a NOTICE type, and I'm not
sure how I can pass that as a result from a pgsql function.

I guess what I'm looking for is this, if it is possible:

CREATE OR REPLACE FUNCTION FOOBAR(refcursor , out refcursor , out
total int4) AS '
BEGIN

OPEN $1 FOR SELECT * FROM t_table ORDER by c_column DESC;
total := (MOVE FORWARD ALL FROM $1);
MOVE BACKWARD ALL FROM $1;
$2 := $1;

END;
' LANGUAGE plpgsql;

Thanks in advance.

Also, is this possible? I would like to be able to plug in the name of
the refcursor returned by the above stored procedure and be able to
fetch data:

CREATE OR REPLACE FUNCTION MOVE(refcursor) RETURNS ROWTYPE AS '
BEGIN
FETCH FORWARD 20 FROM $1;
END;
' LANGUAGE plpgsql;

regards,
karen.

"Karen Hill" <karen_hill22@yahoo.com> writes:

Cool. Quick question, how does one go about noting the rowcount?
Using the rowcount in get diagnostics or something else?

A "MOVE FORWARD ALL FROM cur;" statement returns "MOVE x". Where x is
the number moved. The result seems to be of a NOTICE type, and I'm not
sure how I can pass that as a result from a pgsql function.

Um ... sorry, you can't really. I was thinking of doing this from
client-side logic, where that count is normally going to be accessible
as part of the command status. plpgsql has a pretty limited set of
cursor features --- I don't think it lets you get at MOVE ALL at all,
let alone check the number of rows moved over. Can you push the
operation over to the client side?

(We ought to try to improve that situation in future releases, but that
won't help you today.)

regards, tom lane