[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: Problem with refcursor
From: Samed YILDIRIM <samed () reddoc ! net>
Date: 2024-01-31 14:59:00
Message-ID: CAAo1mbmFccYBL37seno9EqYuTSgr4=bFdDXQ=ndDsQoT85Az-A () mail ! gmail ! com
[Download RAW message or body]
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 <
maximilian.tyrtania@inqua-institut.de> 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 <
> maximilian.tyrtania@inqua-institut.de>:
> >
> > 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
> >
>
>
>
>
[Attachment #3 (text/html)]
<div dir="ltr"><div>Hi Maximilian,</div><div><br></div><div>It has been a while since \
you sent the e-mail. I hope you have already fixed the \
problem.</div><div><br></div><div>I think the issue is the way you tried to loop over \
the refcursor.<br></div><div style="margin-left:40px">FOR curClient IN FETCH ALL FROM \
p_clients LOOP</div><div style="margin-left:40px"><br></div><div>I haven't \
tested. But, I think you should update your loop like this</div><div \
style="margin-left:40px">LOOP</div><div style="margin-left:40px"> FETCH \
p_clients INTO curClient;</div><div style="margin-left:40px"> EXIT WHEN NOT \
FOUND;<br></div><div><div><br></div><div>Refs:</div><div><ul><li><a \
href="https://www.postgresql.org/docs/16/plpgsql-cursors.html#PLPGSQL-CURSOR-USING-FET \
CH">https://www.postgresql.org/docs/16/plpgsql-cursors.html#PLPGSQL-CURSOR-USING-FETCH</a></li><li><a \
href="https://www.postgresql.org/docs/16/plpgsql-control-structures.html#PLPGSQL-CONTR \
OL-STRUCTURES-LOOPS-EXIT">https://www.postgresql.org/docs/16/plpgsql-control-structure \
s.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS-EXIT</a></li></ul></div><div><br></div><div><div><div \
dir="ltr" class="gmail_signature" data-smartmail="gmail_signature"><div \
dir="ltr"><div>Best regards.</div><div>Samed \
YILDIRIM<br></div></div></div></div><br></div></div></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, 9 Jan 2024 at 13:17, \
Maximilian Tyrtania <<a \
href="mailto:maximilian.tyrtania@inqua-institut.de">maximilian.tyrtania@inqua-institut.de</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Oops, of course I \
messed with the outer message before sending it to the list, sorry for that, so the \
actual error message is:<br> <br>
ERROR: cannot open FETCH query as cursor<br>
CONTEXT: PL/pgSQL function f_client_getcoachingsuccessrate(refcursor) line 10 at \
FOR over SELECT rows<br> SQL statement "Select \
rates.successrate,rates.unclearrate,rates.failrate from \
f_client_getCoachingsuccessrate(invitedClients) rates"<br> PL/pgSQL function \
f_client_get3rdfeedbacksuccessrate() line 7 at SQL statement <br> <br>
I am using PG 16.1 btw. <br>
<br>
Max<br>
<br>
> Am 09.01.2024 um 09:23 schrieb Maximilian Tyrtania <<a \
href="mailto:maximilian.tyrtania@inqua-institut.de" \
target="_blank">maximilian.tyrtania@inqua-institut.de</a>>:<br> > <br>
> Hi there,<br>
> <br>
> I'm running into trouble with ref cursors.<br>
> <br>
> I've got these 2 functions, this inner one:<br>
> <br>
> CREATE or replace FUNCTION f_client_getCoachingsuccessrate(p_clients \
refcursor,out successrate numeric, out unclearrate numeric, out failrate numeric) AS \
$$<br> > DECLARE<br>
> curClient record;<br>
> vNumberOfClients bigint;<br>
> vSuccessCounter BIGINT=0;<br>
> vUnclearCounter BIGINT=0;<br>
> vFailureCounter BIGINT=0;<br>
> vCurSuccessState boolean;<br>
> BEGIN<br>
> FOR curClient IN FETCH ALL FROM p_clients LOOP<br>
> —some processing<br>
> END LOOP;<br>
> successrate=f_bigint_getpercentage(vSuccessCounter,vNumberOfClients);<br>
> unclearrate=f_bigint_getpercentage(vUnclearCounter,vNumberOfClients);<br>
> failrate=f_bigint_getpercentage(vFailureCounter,vNumberOfClients);<br>
> */ END;<br>
> $$ LANGUAGE plpgsql;<br>
> <br>
> <br>
> …and this outer one:<br>
> <br>
> create or replace function f_client_get3rdFeedbacksuccessrate(out successrate \
numeric, out unclearrate numeric, out failrate numeric) as <br> > $$<br>
> DECLARE<br>
> invitedClients refcursor;<br>
> BEGIN<br>
> open invitedClients FOR SELECT c.* FROM client c join email e on e.client_id=<a \
href="http://c.id" rel="noreferrer" target="_blank">c.id</a> where \
e.textblock_id=340;<br> > --raise notice 'all is fine so far';<br>
> Select rates.successrate,rates.unclearrate,rates.failrate from \
f_client_getCoachingsuccessrate(invitedClients) rates into successrate,unclearrate \
,failrate;<br> > end;<br>
> $$<br>
> LANGUAGE plpgsql;<br>
> <br>
> Now, calling the outer one like this:<br>
> <br>
> select * from f_client_get3rdFeedbacksuccessrate();<br>
> <br>
> results in:<br>
> <br>
> Query 1 ERROR at Line 1: : ERROR: cannot open FETCH query as cursor<br>
> CONTEXT: PL/pgSQL function f_client_getcoachingsuccessrate(refcursor) line 10 \
at FOR over SELECT rows<br> > SQL statement "SELECT \
f_client_getCoachingsuccessrate(invitedClients)"<br> > PL/pgSQL function \
f_client_get3rdfeedbacksuccessrate() line 8 at PERFORM<br> > <br>
> Any pointers?<br>
> <br>
> Thanks, Max<br>
> <br>
<br>
<br>
<br>
</blockquote></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic