On 17/06/03, Joe Conway (mail@joeconway.com) wrote:
> Rory Campbell-Lange wrote:
> >In a nutshell:
> >
> >Is there a way of finding out how many rows (ROW_COUNT) are in a cursor
> >select? If one can, is there a way of returning a RECORD containing the
> >refcursor and the ROW_COUNT?
> >
>
> You aren't buying anything by using a cursor. AFAICS the only way that a
> cursor would be a benefit, would be if it could persist from page to
> page. Then you could grab just the needed tuples without requerying. But
> I'm not aware of any way to do that.
>
> So you might as well run a single
> "SELECT count(*) ... WHERE your_criteria_here"
> to get the overall count once, and then run your LIMIT/OFFSET query
> directly for each page.
I understand. I was trying to avoid having to replicate a very long,
complex query twice.
I was hoping to be able to use mycurcal() to return the row count on the
cursor (which I hoped would record all the rows in ROW_COUNT), then do a
MOVE and then a FETCH to simulate OFFSET and LIMIT.
However I get the following:
temporary=> select * from mycurcal();
NOTICE: Row Count 1
mycurcal
----------
1
(1 row)
create or replace function mycur(refcursor) returns refcursor AS '
BEGIN
open $1 for select * from abc;
RETURN $1;
END;
' LANGUAGE 'plpgsql';
create or replace function mycurcal() returns integer AS '
DECLARE
rc INTEGER;
this record;
BEGIN
select mycur(''cur'') into this;
GET DIAGNOSTICS rc = ROW_COUNT;
RAISE NOTICE '' Row Count % '', rc;
--fetch all in cur;
RETURN 1;
END;
' LANGUAGE 'plpgsql';
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>
