PostgreSQL: Rolling back a transaction within a plpgsql function?
- by jamieb
Coming from the MS SQL world, I tend to make heavy use of stored procedures. I'm currently writing an application uses a lot of PostgreSQL plpgsql functions. What I'd like to do is rollback all INSERTS/UPDATES contained within a particular function if I get an exception at any point within it.
I was originally under the impression that each function is wrapped in it's own transaction and that an exception would automatically rollback everything. However, that doesn't seem to be the case. I'm wondering if I ought to be using savepoints in combination with exception handling instead? But I don't really understand the difference between a transaction and a savepoint to know if this is the best approach. Any advice please?
CREATE OR REPLACE FUNCTION do_something(
_an_input_var int
) RETURNS bool AS $$
DECLARE
_a_variable int;
BEGIN
INSERT INTO tableA (col1, col2, col3)
VALUES (0, 1, 2);
INSERT INTO tableB (col1, col2, col3);
VALUES (0, 1, 'whoops! not an integer');
-- The exception will cause the function to bomb, but the values
-- inserted into "tableA" are not rolled back.
RETURN True;
END; $$ LANGUAGE plpgsql;
