In PostgreSQL, exception handling is implemented using the PL/pgSQL procedural language, which extends the capabilities of SQL with additional programming constructs, such as variables, loops, and conditionals. PL/pgSQL provides a comprehensive exception-handling mechanism that enables developers to catch and handle a wide range of errors that may occur during the execution of database functions and procedures.

PostgreSQL stops the execution of the block and the related transaction when a block contains an error. A block is a collection of statements that are contained within a BEGIN and END block structure in PL/pgSQL. In PostgreSQL, blocks are used to define unique procedures, triggers, and functions. The beginning and end of the block are indicated by the terms BEGIN and END, respectively.

Syntax

BEGIN
    -- Code goes here
EXCEPTION
    WHEN exception_type THEN
        -- Exception handling code goes here
END;

Besides Begin and End block, the EXCEPTION keyword indicates the start of the exception handling section, which is executed if an exception is thrown, inside which the WHEN clause specifies the type of exception that the exception handler will handle. The THEN keyword indicates the start of the code block that handles the exception.

PostgreSQL has a wide range of built-in exception types such as SQLSTATE, SQLERRM, NO_DATA_FOUND, and TOO_MANY_ROWS. Moreover, users can also create custom exceptions using the RAISE statement. For a complete list of condition names on the PostgreSQL website. To illustrate how PostgreSQL handles exceptions, let's take the basic example of a divide-by-zero exception. The PL/pgSQL block below demonstrates how to handle this exception:

do
$$
declare 
    result int;
begin
    SELECT 1/0 INTO result;

    -- exception example based on SQL ERROR CODE
    exception
        WHEN division_by_zero THEN
            result := NULL;
end;
$$
language plpgsql;

In the above block, if the SELECT statement attempts to divide by zero, a division_by_zero exception is raised. The exception handling code, specified in the WHEN clause, sets the result variable to NULL in this case.

When an error occurs within the BEGIN...EXCEPTION block in PL/pgSQL, the execution is stopped and the control is transferred to the exception list. Then, PL/pgSQL scans the exception list to find the first match for the error that occurred. If a match is found, the statements inside the corresponding EXCEPTION block execute, and the control passes to the statement after the END keyword. If no match is found, the error propagates outwards 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 aborts processing.

Example Syntax Code block for Multiple Exceptions:

do
$$
declare
    rec record;
    emp_name varchar = 'abc';
begin
    select 
        <column_names>
    into strict rec
    from <table_name>
    where employee_name = emp_name; -- example where clause.

    -- exception example based on SQL ERROR CODE
    exception
        when sqlstate 'P0002' then
            raise exception 'employee with name % not found', emp_name;
        when sqlstate 'P0003' then
            raise exception 'employee with name % is already present', emp_name;

    -- exception example based on Expection Condition
    exception
        when too_many_rows then
            raise exception 'Search query returns more than one rows';
end;
$$
language plpgsql;

This exception-handling mechanism is crucial for handling errors gracefully and preventing application crashes or data corruption. It allows developers to provide better user experiences by responding appropriately to errors, rather than simply halting execution.