0

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 ?

1 Answer 1

1

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

LOOP
   SAVEPOINT a;

   /* something */

   IF /* something else */ THEN
      ROLLBACK TO SAVEPOINT a;
      /* still more */
   END IF;
END LOOP;

you should use

LOOP
   BEGIN
      /* something */

      IF /* something else */ THEN
         RAISE EXCEPTION 'catchme';
      END IF;
   EXCEPTION
      WHEN raise_exception THEN
         /* still more */
   END;
END LOOP;
2
  • i've been digging around documentation for information on the "limited support [for transaction management statemtns] in v11" but not finding anything... do you have any details about that which can share?
    – Reinsbrain
    Commented Aug 12, 2021 at 10:58
  • 1
    I have added a link to the documentation. Commented Aug 15, 2021 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.