Re: use cursor in a function

On 17/06/03, Joe Conway (mail(at)joeconway(dot)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(at)campbell-lange(dot)net>
<www.campbell-lange.net>