Thank you, this is very helpful, just 1 little question: Why do you write just EXCEPTION?
Shouldn't it be RAISE EXCEPTION? Regards Alex On Wed, Mar 2, 2016 at 11:18 AM, Albe Laurenz <laurenz.a...@wien.gv.at> wrote: > 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. > >
