Subject: Problem with refcursor

From: Maximilian Tyrtania <[email-masked]>
Date: Tue, 9 Jan 2024 09:23:49 +0100

Hi there,

I’m running into trouble with ref cursors.

I’ve got these 2 functions, this inner one:

CREATE or replace FUNCTION f_client_getCoachingsuccessrate(p_clients refcursor,out successrate numeric, out unclearrate numeric, out failrate numeric) AS $$
DECLARE
curClient record;
vNumberOfClients bigint;
vSuccessCounter BIGINT=0;
vUnclearCounter BIGINT=0;
vFailureCounter BIGINT=0;
vCurSuccessState boolean;
BEGIN
    FOR curClient IN FETCH ALL FROM p_clients LOOP
—some processing
    END LOOP;
	successrate=f_bigint_getpercentage(vSuccessCounter,vNumberOfClients);
	unclearrate=f_bigint_getpercentage(vUnclearCounter,vNumberOfClients);
	failrate=f_bigint_getpercentage(vFailureCounter,vNumberOfClients);
*/	END;
$$ LANGUAGE plpgsql;

…and this outer one:

create or replace function f_client_get3rdFeedbacksuccessrate(out successrate numeric, out unclearrate numeric, out failrate numeric) as 
$$
DECLARE
invitedClients refcursor;
BEGIN
open invitedClients FOR SELECT c.* FROM client c join email e on e.client_id=c.id where e.textblock_id=340;
--raise notice 'all is fine so far';
Select rates.successrate,rates.unclearrate,rates.failrate from f_client_getCoachingsuccessrate(invitedClients) rates into successrate,unclearrate ,failrate;
end;
$$
 LANGUAGE plpgsql;

Now, calling the outer one like this:

select * from f_client_get3rdFeedbacksuccessrate();

results in:

Query 1 ERROR at Line 1: : ERROR:  cannot open FETCH query as cursor
CONTEXT:  PL/pgSQL function f_client_getcoachingsuccessrate(refcursor) line 10 at FOR over SELECT rows
SQL statement "SELECT f_client_getCoachingsuccessrate(invitedClients)"
PL/pgSQL function f_client_get3rdfeedbacksuccessrate() line 8 at PERFORM

Any pointers?

Thanks, Max

---

From: Maximilian Tyrtania <[email-masked]>
Date: Tue, 9 Jan 2024 12:17:06 +0100

Oops, of course I messed with the outer message before sending it to the list, sorry for that, so the actual error message is:

ERROR:  cannot open FETCH query as cursor
CONTEXT:  PL/pgSQL function f_client_getcoachingsuccessrate(refcursor) line 10 at FOR over SELECT rows
SQL statement "Select rates.successrate,rates.unclearrate,rates.failrate from f_client_getCoachingsuccessrate(invitedClients) rates"
PL/pgSQL function f_client_get3rdfeedbacksuccessrate() line 7 at SQL statement 

I am using PG 16.1 btw.  

Max

> Am 09.01.2024 um 09:23 schrieb Maximilian Tyrtania <[email-masked]>:
> 
> Hi there,
>

---

From: Samed YILDIRIM <[email-masked]>
Date: Wed, 31 Jan 2024 16:59:00 +0200

Hi Maximilian,

It has been a while since you sent the e-mail. I hope you have already
fixed the problem.

I think the issue is the way you tried to loop over the refcursor.
FOR curClient IN FETCH ALL FROM p_clients LOOP

I haven't tested. But, I think you should update your loop like this
LOOP
    FETCH p_clients INTO curClient;
    EXIT WHEN NOT FOUND;

Refs:

   -
   https://www.postgresql.org/docs/16/plpgsql-cursors.html#PLPGSQL-CURSOR-USING-FETCH
   -
   https://www.postgresql.org/docs/16/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS-EXIT

Best regards.
Samed YILDIRIM

On Tue, 9 Jan 2024 at 13:17, Maximilian Tyrtania <
[email-masked]> wrote:

> Oops, of course I messed with the outer message before sending it to the
> list, sorry for that, so the actual error message is:
>
> SQL statement "Select rates.successrate,rates.unclearrate,rates.failrate
> from f_client_getCoachingsuccessrate(invitedClients) rates"
> > CREATE or replace FUNCTION f_client_getCoachingsuccessrate(p_clients
> refcursor,out successrate numeric, out unclearrate numeric, out failrate
> numeric) AS $$
> > DECLARE
> > curClient record;
> > vNumberOfClients bigint;
> > create or replace function f_client_get3rdFeedbacksuccessrate(out
> successrate numeric, out unclearrate numeric, out failrate numeric) as
> > $$
> > DECLARE
> > open invitedClients FOR SELECT c.* FROM client c join email e on
> e.client_id=c.id where e.textblock_id=340;
> > Select rates.successrate,rates.unclearrate,rates.failrate from
> f_client_getCoachingsuccessrate(invitedClients) rates into
> successrate,unclearrate ,failrate;
> > end;
> > $$
> > LANGUAGE plpgsql;
> >