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