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

Karen Hill wrote:
> Tom Lane wrote:
> > "Karen Hill" <karen_hill22(at)yahoo(dot)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.