public inbox for [email protected]
help / color / mirror / Atom feedProblem with refcursor
3+ messages / 2 participants
[nested] [flat]
* Problem with refcursor
@ 2024-01-09 08:23 Maximilian Tyrtania <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Maximilian Tyrtania @ 2024-01-09 08:23 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Problem with refcursor
@ 2024-01-09 11:17 Maximilian Tyrtania <[email protected]>
parent: Maximilian Tyrtania <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Maximilian Tyrtania @ 2024-01-09 11:17 UTC (permalink / raw)
To: [email protected]
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 protected]>:
>
> 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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Problem with refcursor
@ 2024-01-31 14:59 Samed YILDIRIM <[email protected]>
parent: Maximilian Tyrtania <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Samed YILDIRIM @ 2024-01-31 14:59 UTC (permalink / raw)
To: Maximilian Tyrtania <[email protected]>; +Cc: [email protected]
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-...
Best regards.
Samed YILDIRIM
On Tue, 9 Jan 2024 at 13:17, Maximilian Tyrtania <
[email protected]> 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:
>
> 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 protected]>:
> >
> > 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
> >
>
>
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-01-31 14:59 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-01-09 08:23 Problem with refcursor Maximilian Tyrtania <[email protected]>
2024-01-09 11:17 ` Maximilian Tyrtania <[email protected]>
2024-01-31 14:59 ` Samed YILDIRIM <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox