Re: Problem with refcursor
От | Maximilian Tyrtania |
---|---|
Тема | Re: Problem with refcursor |
Дата | |
Msg-id | 94636222-967F-4594-9862-7A7DD6C06E0C@inqua-institut.de обсуждение исходный текст |
Ответ на | Problem with refcursor (Maximilian Tyrtania <maximilian.tyrtania@inqua-institut.de>) |
Ответы |
Re: Problem with refcursor
|
Список | pgsql-sql |
Oops, of course I messed with the outer message before sending it to the list, sorry for that, so the actual error messageis: 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 unclearratenumeric, 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 failratenumeric) 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 intosuccessrate,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 >
В списке pgsql-sql по дате отправления: