Re: Does RAISE EXCEPTION rollback previous commands in a stored function?

Alexander Farber wrote:
> On Wed, Mar 2, 2016 at 11:18 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:
>> 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.

> Thank you, this is very helpful, just 1 little question:
>
>
> Why do you write just EXCEPTION?
>
>
> Shouldn't it be RAISE EXCEPTION?

That's something entirely different, see
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

The above construct *catches* the exception, which might be
raised by the UPDATE statement.

Yours,
Laurenz Albe