Exception Handling in PostgreSQL

By default, any error occurring in an SPL program aborts execution of the program. You can trap errors and recover from them by using a BEGIN block with an EXCEPTION section. The syntax is an extension of the normal syntax for a BEGIN block:

[ DECLARE
    <declarations> ]
  BEGIN
    <statements>
  EXCEPTION
    WHEN <condition> [ OR <condition> ]... THEN
      <handler_statements>
  [ WHEN <condition> [ OR <condition> ]... THEN
      <handler_statements> ]...
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.

here an example handling error:

create or replace function fn_archive_table() returns void as $$

  declare
    r_question_id record;

  begin

    for r_question_id in
      select id from process_question_ids where id not in (select id from archive.pending_delete where table_name = 'Questions')
    loop
      begin
        delete from "Questions" where id = r_question_id.id;
      EXCEPTION 
        when foreign_key_violation THEN 
          insert into archive.pending_delete values ('Questions', r_question_id.id);
      end;
    end loop;

  end;

$$ language plpgsql;

the function above intend to handling error when deleting rows from ‘Questions’ table, so if an error occur due to foreign key violation it will insert the id and the table name into the pending_delete table. this process is really handy when deleting data with huge table size.