Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rVC3Z-00FQ4r-D2 for pgsql-sql@arkaria.postgresql.org; Wed, 31 Jan 2024 14:59:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rVC3Y-00Eg3Y-Dh for pgsql-sql@arkaria.postgresql.org; Wed, 31 Jan 2024 14:59:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rVC3Y-00Eg3Q-2B for pgsql-sql@lists.postgresql.org; Wed, 31 Jan 2024 14:59:16 +0000 Received: from mail-ed1-x536.google.com ([2a00:1450:4864:20::536]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rVC3U-004jFy-KY for pgsql-sql@lists.postgresql.org; Wed, 31 Jan 2024 14:59:15 +0000 Received: by mail-ed1-x536.google.com with SMTP id 4fb4d7f45d1cf-55c2c90c67dso6160733a12.1 for ; Wed, 31 Jan 2024 06:59:12 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=reddoc.net; s=google; t=1706713151; x=1707317951; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=sSgh6JuDTiKD1E3rgNeKJp04FCS/5M1HlqI8P1Hk9G4=; b=X03rL7BspUY7YlkRSco+Df1oFJ3RrgOJf23cOxyE4T5EnBU/joP+cBUYfR+gCrxCfE DeGfJ2mPoiaqjglX4e8F8usNgjJkMPEAb9gpkeIH09gJb56ZHSfNAvdEt5ASWu/Iu3iS 5rdzI73Vk8A/Wio7GE+kEzpy/v82e9zGH6SdE= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1706713151; x=1707317951; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=sSgh6JuDTiKD1E3rgNeKJp04FCS/5M1HlqI8P1Hk9G4=; b=Pk1LBOICrZjp6JSJbYEKYabTO94nikFiZqftylAe+4svhN8oSZHvnHgK0W8WDUlaOn RgycIuZ7kMk91EpUMLntSoLKMdQMxbh5lLy8wQUOqVGy9sHTzkfku8I9kY+jCFEqfiqT MJcRvv8vaEpU6jgQU9ZSKNri14HHMjqSkdLe+JSXSMZyFsJv3wOQ3qTFtYEbCPIuokUv Q7krwy+y/KlVmG0tVrPdpdUC5lFgb3at+7aJxY+VyarewFKO2/JEVF4GzVhuPOCKm10K tZkcdUGytkoofyVqthbC7CfZZGF8SUHRk91Sf0BFi1t2VvvI0Yv4oaYOGLWZMYpWzra4 PgxA== X-Gm-Message-State: AOJu0YzFn1zZkyL6O/y0Uopnz4c/LECJDUpKC2rOksRCTCm83F91dJvB eE036NInu9bN6gyV/yHbr8sVO2KiipgKP2NtxsZMHZgkTXIcXFq3q5CTbyGqnY6HSyELLij8maz lImxA0H4qPHokEsaczmrahQdB/zxr9RX5+sRbAg== X-Google-Smtp-Source: AGHT+IE7YG+mdmSWwmf5czo3sJj0qSYdNLDgzFB/roKczcWUowMOCNvIUpox6By2fsq6qsrxNzr5/IfZqinXSVQ00Dg= X-Received: by 2002:aa7:c2cc:0:b0:55f:314:6890 with SMTP id m12-20020aa7c2cc000000b0055f03146890mr1371505edp.22.1706713151122; Wed, 31 Jan 2024 06:59:11 -0800 (PST) MIME-Version: 1.0 References: <990A111A-190F-485A-9B96-665D6D3CABBD@inqua-institut.de> <94636222-967F-4594-9862-7A7DD6C06E0C@inqua-institut.de> In-Reply-To: <94636222-967F-4594-9862-7A7DD6C06E0C@inqua-institut.de> From: Samed YILDIRIM Date: Wed, 31 Jan 2024 16:59:00 +0200 Message-ID: Subject: Re: Problem with refcursor To: Maximilian Tyrtania Cc: pgsql-sql@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000001b3e2606103f1f7e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001b3e2606103f1f7e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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-U= SING-FETCH - https://www.postgresql.org/docs/16/plpgsql-control-structures.html#PLPGS= QL-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=E2=80=99m running into trouble with ref cursors. > > > > I=E2=80=99ve 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=3D0; > > vUnclearCounter BIGINT=3D0; > > vFailureCounter BIGINT=3D0; > > vCurSuccessState boolean; > > BEGIN > > FOR curClient IN FETCH ALL FROM p_clients LOOP > > =E2=80=94some processing > > END LOOP; > > successrate=3Df_bigint_getpercentage(vSuccessCounter,vNumberOfClients); > > unclearrate=3Df_bigint_getpercentage(vUnclearCounter,vNumberOfClients); > > failrate=3Df_bigint_getpercentage(vFailureCounter,vNumberOfClients); > > */ END; > > $$ LANGUAGE plpgsql; > > > > > > =E2=80=A6and 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=3Dc.id where e.textblock_id=3D340; > > --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 PERFOR= M > > > > Any pointers? > > > > Thanks, Max > > > > > > --0000000000001b3e2606103f1f7e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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 o= ver the refcursor.
FOR curClient I= N FETCH ALL FROM p_clients LOOP

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

Refs:

Best regards.
Samed YILDIRIM


On Tue, 9 Jan 2024 at 13:17, Maximilian Tyrtania <maximilian.tyrtania@inqua-inst= itut.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:=C2=A0 cannot open FETCH query as cursor
CONTEXT:=C2=A0 PL/pgSQL function f_client_getcoachingsuccessrate(refcursor)= line 10 at FOR over SELECT rows
SQL statement "Select rates.successrate,rates.unclearrate,rates.failra= te from f_client_getCoachingsuccessrate(invitedClients) rates"
PL/pgSQL function f_client_get3rdfeedbacksuccessrate() line 7 at SQL statem= ent

I am using PG 16.1 btw.=C2=A0

Max

> Am 09.01.2024 um 09:23 schrieb Maximilian Tyrtania <maximilian.tyrt= ania@inqua-institut.de>:
>
> Hi there,
>
> I=E2=80=99m running into trouble with ref cursors.
>
> I=E2=80=99ve got these 2 functions, this inner one:
>
> CREATE or replace FUNCTION f_client_getCoachingsuccessrate(p_clients r= efcursor,out successrate numeric, out unclearrate numeric, out failrate num= eric) AS $$
> DECLARE
> curClient record;
> vNumberOfClients bigint;
> vSuccessCounter BIGINT=3D0;
> vUnclearCounter BIGINT=3D0;
> vFailureCounter BIGINT=3D0;
> vCurSuccessState boolean;
> BEGIN
>=C2=A0 =C2=A0 FOR curClient IN FETCH ALL FROM p_clients LOOP
> =E2=80=94some processing
>=C2=A0 =C2=A0 END LOOP;
> successrate=3Df_bigint_getpercentage(vSuccessCounter,vNumberOfClients)= ;
> unclearrate=3Df_bigint_getpercentage(vUnclearCounter,vNumberOfClients)= ;
> failrate=3Df_bigint_getpercentage(vFailureCounter,vNumberOfClients); > */ END;
> $$ LANGUAGE plpgsql;
>
>
> =E2=80=A6and this outer one:
>
> create or replace function f_client_get3rdFeedbacksuccessrate(out succ= essrate 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.cli= ent_id=3Dc.id<= /a> where e.textblock_id=3D340;
> --raise notice 'all is fine so far';
> Select rates.successrate,rates.unclearrate,rates.failrate from f_clien= t_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:=C2=A0 cannot open FETCH query as cur= sor
> CONTEXT:=C2=A0 PL/pgSQL function f_client_getcoachingsuccessrate(refcu= rsor) line 10 at FOR over SELECT rows
> SQL statement "SELECT f_client_getCoachingsuccessrate(invitedClie= nts)"
> PL/pgSQL function f_client_get3rdfeedbacksuccessrate() line 8 at PERFO= RM
>
> Any pointers?
>
> Thanks, Max
>



--0000000000001b3e2606103f1f7e--