PostgreSQL Exception

Summary: in this tutorial, you will learn how to catch PostgreSQL exceptions in PL/pgSQL.

---

Introduction to the PL/pgSQL Exception clause

When an error occurs in a block, PostgreSQL will abort the execution of the block and also the surrounding transaction.

To recover from the error, you can use the exception clause in the begin...end block.

The following illustrates the syntax of the exception clause:

<>
 declare
 begin
     statements;
 exception
     when condition [or condition…] then
        handle_exception;
    [when condition [or condition…] then
        handle_exception;]
    [when others then
        handle_other_exceptions;
    ]
 end;

How it works.

• First, when an error occurs between the begin and exception, PL/pgSQL stops the execution and passes the control to the exception list.
• Second, PL/pgSQL searches for the first condition that matches the occurring error.
• Third, if there is a match, the corresponding handle_exception statements will execute. PL/pgSQL passes the control to the statement after the end keyword.
• Finally, if no match found, the error propagates out and can be caught by the exception clause of the enclosing block. In case there is no enclosing block with the exception clause, PL/pgSQL will abort the processing.

The condition names can be no_data_found in case of a select statement return no rows or too_many_rows if the select statement returns more than one row. For a complete list of condition names on the PostgreSQL website.

It’s also possible to specify the error condition by SQLSTATE code. For example, P0002 for no_data_found and P0003 for too_many_rows.

Typically, you will catch a specific exception and handle it accordingly. To handle other exceptions rather than the one you specify on the list, you can use the when others then clause.

Handling exception examples

We’ll use the film table from the sample database for the demonstration.

1. Handling no_data_found exception example

The following example issues an error because the film with id 2000 does not exist.

do
 $$
 declare
     rec record;
     v_film_id int = 2000;
 begin
     -- select a film 
     select film_id, title 
     into strict rec
     from film
     where film_id = v_film_id;
 end;
 $$
 language plpgsql;

Output:

ERROR:  query returned no rows
 CONTEXT:  PL/pgSQL function inline_code_block line 6 at SQL statement
 SQL state: P0002

The following example uses the exception clause to catch the no_data_found exception and report a more meaningful message:

do
 $$
 declare
     rec record;
     v_film_id int = 2000;
 begin
     -- select a film 
     select film_id, title 
     into strict rec
     from film
     where film_id = v_film_id;
         -- catch exception
     exception 
        when no_data_found then 
           raise exception 'film % not found', v_film_id;
 end;
 $$
 language plpgsql;

Output:

ERROR:  film 2000 not found
 CONTEXT:  PL/pgSQL function inline_code_block line 14 at RAISE
 SQL state: P0001

2. Handling too_many_rows exception example

The following example illustrates how to handle the too_many_rows exception:

do
 $$
 declare
     rec record;
 begin
     -- select film 
     select film_id, title 
     into strict rec
     from film
     where title LIKE 'A%';
 exception     when too_many_rows then       raise exception 'Search query returns too many rows';
 end;
 $$
 language plpgsql;

Output:

ERROR:  Search query returns too many rows
 CONTEXT:  PL/pgSQL function inline_code_block line 15 at RAISE
 SQL state: P0001

In this example, the too_many_rows exception occurs because the select into statement returns more than one row while it is supposed to return one row.

3. Handling multiple exceptions

The following example illustrates how to catch multiple exceptions:

do
 $$
 declare
     rec record;
     v_length int = 90;
 begin
     -- select a film 
     select film_id, title 
     into strict rec
     from film
     where length = v_length;
     -- catch exception exception     when sqlstate 'P0002' then        raise exception 'film with length % not found', v_length;    when sqlstate 'P0003' then        raise exception 'The with length % is not unique', v_length;
 end;
 $$
 language plpgsql;

Output:

ERROR:  The with length 90 is not unique
 CONTEXT:  PL/pgSQL function inline_code_block line 17 at RAISE
 SQL state: P0001

4. Handling exceptions as SQLSTATE codes

The following example is the same as the one above except that it uses the SQLSTATE codes instead of the condition names:

do
 $$
 declare
     rec record;
     v_length int = 30;
 begin
     -- select a film 
     select film_id, title 
     into strict rec
     from film
     where length = v_length;
     -- catch exception exception     when sqlstate 'P0002' then        raise exception 'film with length % not found', v_length;    when sqlstate 'P0003' then        raise exception 'The with length % is not unique', v_length;
 end;
 $$
 language plpgsql;

Output:

ERROR:  film with length 30 not found
 CONTEXT:  PL/pgSQL function inline_code_block line 15 at RAISE
 SQL state: P0001

Summary

• Use the exception clause in the begin...end block to catch and handle exceptions.

---