Is it possible to cancel previous operations in a user defined function?
For example:
CREATE OR REPLACE FUNCTION transact_test () RETURNS BOOLEAN
AS $$
BEGIN
UPDATE table1 SET ...
UPDATE table2 SET ...
IF some_condition THEN
--Here is possible to cancel all above operations?
RETURN FALSE;
END IF;
RETURN TRUE;
END;
$$
LANGUAGE plpgsql;
Both answers so far are incorrect.
If you try to start a transaction or use a SAVEPOINT inside a plpgsql function you get an error message like this:
ERROR: cannot begin/end transactions in PL/pgSQL HINT: Use a BEGIN block with an EXCEPTION clause instead. CONTEXT: PL/pgSQL function "f_savepoint" line 6 at SQL statement
If you try a SAVEPOINT inside a plain SQL function:
ERROR: SAVEPOINT is not allowed in a SQL function CONTEXT: SQL function "f_savepoint2" during startup
As the error message instructs, use a BEGIN block inside a plpgsql function instead. Your demo could look like this:
CREATE OR REPLACE FUNCTION transact_test(boolean)
RETURNS boolean AS
$func$
BEGIN -- start a nested BEGIN block
UPDATE t SET i = i+1 WHERE i = 1;
UPDATE t SET i = i+1 WHERE i = 3;
IF $1 THEN
RAISE EXCEPTION 'foo'; -- cancels all of the above
END IF;
RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
RETURN FALSE;
-- do nothing
END
$func$ LANGUAGE plpgsql;
-> SQLfiddle demonstrating it all.
