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.albe@wien.gv.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