From: Maximilian Tyrtania <maximilian.tyrtania@inqua-institut.de>
Content-Type: text/plain;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.200.91.1.1\))
Subject: Re: Problem with refcursor
Date: Tue, 9 Jan 2024 12:17:06 +0100
References: <990A111A-190F-485A-9B96-665D6D3CABBD@inqua-institut.de>
To: pgsql-sql@lists.postgresql.org
In-Reply-To: <990A111A-190F-485A-9B96-665D6D3CABBD@inqua-institut.de>
Message-Id: <94636222-967F-4594-9862-7A7DD6C06E0C@inqua-institut.de>
Archived-At: 
 <https://www.postgresql.org/message-id/94636222-967F-4594-9862-7A7DD6C06E0C%40inqua-institut.de>
Precedence: bulk

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=20

I am using PG 16.1 btw. =20

Max

> Am 09.01.2024 um 09:23 schrieb Maximilian Tyrtania =
<maximilian.tyrtania@inqua-institut.de>:
>=20
> Hi there,
>=20
> I=E2=80=99m running into trouble with ref cursors.
>=20
> I=E2=80=99ve got these 2 functions, this inner one:
>=20
> 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;
>=20
>=20
> =E2=80=A6and this outer one:
>=20
> create or replace function f_client_get3rdFeedbacksuccessrate(out =
successrate numeric, out unclearrate numeric, out failrate numeric) as=20=

> $$
> 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;
>=20
> Now, calling the outer one like this:
>=20
> select * from f_client_get3rdFeedbacksuccessrate();
>=20
> results in:
>=20
> 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
>=20
> Any pointers?
>=20
> Thanks, Max
>=20