The following sql is a part from a FUNCTION ,i want to rollback to rbk SAVEPOINT :
for ptrEnt in entite loop
-- positionnement du point de retour pour une entité
savepoint rbk;
-- purge de la table des redevances de l''entite
test := pckg_activ.initredevanceentite(ptrEnt.cod_ent);
-- calcul de la redevance
test := pckg_activ.calculredevanceentite(ptrEnt.cod_ent);
-- controle calcul de la bonne execution pour l''entité
if (test = 0) then
-- initialisation des departements associes a une entite
test := pckg_activ.initdepartement(ptrEnt.cod_ent);
-- validation des modifications
commit;
-- generation de l''etat recapitulatif
test := pckg_activ.recapentitegestionnaire(ptrEnt.cod_ent,ficHisto);
else
rollback TO SAVEPOINT rbk;
report := jour + 7;
update sav_rdv_date
set date_dem = report
where cod_ent = ptrEnt.cod_ent;
RAISE NOTICE '!!erreur de patrimoine => abandon du traitement pour cette entite';
commit;
end if;
end loop;
entite is a cursor that has been declared and the for loop is in a BEGIN bloc.
I get the error ERROR: syntax error at or near "TO"
. I think that the syntax is correct , it it related to the cursor ?
Best Answer
You cannot use transaction management statements in PostgreSQL functions.
Limited support for that was added in procedures, introduced in v11.
But you can (implicitly) use savepoints by using an
EXCEPTION
clause in a block.So instead of
you should use