How to return an integer from a plpgsql function AND rollback every modifications?

Question

My application uses pl/pgsql functions that return integers. The returned integer is used as a return code, to distinguish between different errors.

For example, if a function that insert datas returns -1, it means that some datas exist already and this is forbidden to try to insert the same data again. But if it returns -2, it means something else. That way the application knows the error and can display a useful error message to the user.

My problem now is that i want, at some points in the function, to return immediately when i detect an error, and rollback everything done so far in the function. If i use "raise exception", it will rollback, but not return an integer. If i use "return -1;", it will return an integer, but not rollback modifications. So i'm stuck, because obviously i can't do both

Here's a phony example function:

CREATE OR REPLACE FUNCTION create_flight_and_passengers(
    _date timetamp,
    _name text,
    _passengers integer[]
)
RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
    return_code integer;
BEGIN
    INSERT INTO flights
    VALUES (_name);

    SELECT function_1(_date, _passengers) into return_code;
    if (return_code = -1) then
        -- [1] rollback everything done since beginning of function
        -- create_flight_and_passengers() and return -1
    end if;

    SELECT function_2(_date, _passengers) into return_code;
    if (return_code = -1) then
        -- [2] rollback everything done since beginning of function
        -- create_flight_and_passengers() and return -2
    end if;

    return 0;
END;
$$;

In [1] and [2] i could use raise exception to rollback, but when i do this i don't have a returned integer.

I could set a local variable in [1] and [2], then raise exception, and in EXCEPTION do tests on this variable to know where the exception come from, but this is bloated, there must be something better !

I'm not even sure you can rollback effects of a function you have called and that has terminated (function_1() and function_2() in my example)

Any ideas ?

Craig Ringer Craig Ringer · Accepted Answer · 2014-03-26T01:04:43

This is a pretty bizarre thing to want to do. If you really need to, you could do it like this:

DECLARE
  retval integer;
BEGIN
  retval := 0;
  BEGIN
    ... make my changes ...
    IF (... is something wrong? ...) THEN
      RAISE EXCEPTION SQLSTATE '0U001';
    END IF;
  EXCEPTION
    WHEN '0U001' THEN
      retval := -1;
  END;
END;

The concept here is that a BEGIN ... EXCEPTION block defines a subtransaction. A RAISE EXCEPTION within the block rolls back the subtransaction. We catch it at the outer level, preventing the exception from propagating outside the function and aborting the whole transaction.

See the PL/PgSQL documentation.

How to return an integer from a plpgsql function AND rollback every modifications?

1 Answers