Exceptions and error handling

Every PL/pgSQL code block can include a section for exception handling. Exceptions are errors that arise during the flow of the code block and are of different types. A block can handle these errors in order to recover. The exception handling part of a code block begins with an EXCEPTION keyword and can include different branches, marked with the WHEN keyword, to handle different error types. Error types are coded into the server and are identified by either a mnemonic name, such as unique_violation, to indicate a unique constraint violation, or a numeric SQL State, such as 23505.

By default, WHEN branches refer to error names, while numeric SQL States have to be placed as strings preceded by the SQLSTATE keyword. The OTHERS special error name is used as a general way to catch any kind of error other than user cancelled statements.

If a code block does not provide an EXCEPTION section, or if the EXCEPTION section does not catch the exception thrown, the block execution is aborted. Every time a code block enters its EXCEPTION section, all changes made to the database are rolled back, without any regard to whether or not the EXCEPTION section handles the specific error.

In order to demonstrate the exception handling capabilities, let's consider Listing 26. This listing implements a kind of UPSERT; the block tries to perform an INSERT statement and, if the record already exists in the table, the server raises a unique_violation exception. The block then catches the exception and converts the INSERT statement into an UPDATE of the very same record. If the exception is different from unique_violation, the others branch catches it but does nothing. This means it gracefully ends the control flow:

testdb=> DO $code$
 DECLARE
   file_hash text := 'f029d04a81c322f158c608596951c105';
 BEGIN
    -- try to perform the insert
    INSERT INTO files( f_name, f_hash )
    VALUES ( 'foo.txt', file_hash );

  EXCEPTION
      -- did the insert fail due to a unique constraint?
      WHEN unique_violation THEN
            UPDATE files
            SET f_name = 'foo.txt'
            WHERE f_hash = file_hash;
     WHEN others THEN
          -- don't know how to recover from other errors
          NULL;
 END $code$;

Since every PL/pgSQL code block can have its own EXCEPTION section, it is possible to nest different blocks with different levels of exception handling. For instance, as shown in Listing 27, the inner code block handles the division_by_zero exception without aborting the outer loop:

testdb=> DO $code$
 DECLARE
    a real := 10;
    b real := 10;
    c numeric(3,1);
 BEGIN
    LOOP
      b := b - 5;
      BEGIN
        c := a / b;
        RAISE INFO 'a/b= %', c;
        EXCEPTION
           WHEN division_by_zero THEN
                RAISE INFO 'b is now zero!';
                b := -1;
          END;
      EXIT WHEN b <= -5;
    END LOOP;
 END $code$;

INFO:  a/b= 2.0
INFO:  b is now zero!
INFO:  a/b= -1.7

It is possible to throw an exception by means of the RAISE statement with the EXCEPTION level. These exceptions can then either be caught in other code blocks or cause the abortion of the block. As an example, Listing 28 is a modified version of Listing 27, where the LOOP has a separated block that handles the newly thrown exception from the inner block:

testdb=> DO $code$
 DECLARE
    a real := 10;
    b real := 10;
    c numeric(3,1);
 BEGIN
    LOOP
     BEGIN
      b := b - 5;
      BEGIN
        c := a / b;
        RAISE INFO 'a/b= %', c;
        EXCEPTION
           WHEN division_by_zero THEN
                -- throw another exception
                RAISE EXCEPTION 'b is now zero!';
                b := -1; -- this is never executed!
          END;
      EXIT WHEN b <= -5;
     EXCEPTION WHEN others THEN EXIT;
     END;
    END LOOP;
 END $code$;

INFO:  a/b= 2.0

It is also possible to re-throw an exception once it has been handled. From within an EXCEPTION block, a RAISE statement without any arguments will re-throw the current exception:

BEGIN
   ...
EXCEPTION
  WHEN unique_violation THEN
     -- do something
     ...
     -- re-throw unique_violation
     RAISE;
END;

Alternatively, RAISE can also accept the error name or the SQL State to throw that specific exception:

BEGIN
   ...
EXCEPTION
  WHEN unique_violation THEN
     -- convert an exception into another
     RAISE division_by_zero;
END;

It is worth noting that a code block with an EXCEPTION section is much more expensive, in terms of resources, than a block without. Exception handling should therefore only be used when it is really necessary.