Re: use cursor in a function

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>