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

["Karen Hill" <karen_hill22@xxxxxxxxx>]

Karen Hill wrote:
> Tom Lane wrote:
> > "Karen Hill" <karen_hill22@xxxxxxxxx> 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.