Alexander Farber wrote: > how to set such a savepoint inside of a stored function? > > Can I call "START TRANSACTION", and then at some point later in the same stored function call RAISE > EXCEPTION?
I realize that what I wrote must be confusing.
You cannot use START TRANSACTION, BEGIN, SAVEPOINT, COMMIT or ROLLBACK inside a function. A function always runs within one transaction.
Savepoints or subtransactions are written with a BEGIN ... EXCEPTION block in PL/pgSQL, so you could write:
DECLARE FUNCTION .... AS $$BEGIN /* UPDATE 1 */ UPDATE ...; BEGIN /* sets a savepoint */ /* UPDATE 2, can cause an error */ UPDATE ...; EXCEPTION /* rollback to savepoint, ignore error */ WHEN OTHERS THEN NULL; END; END;$$;
Even if UPDATE 2 throws an error, UPDATE 1 will be committed.
