PL/pgSQL Exception

Summary: in this tutorial, you’ll learn how to handle exceptions using the PL/pgSQL EXCEPTION block.

Introduction to the PL/pgSQL EXCEPTION Block #

In PL/pgSQL, an exception is an unexpected condition or error during execution. Exceptions may result from issues like constraint violation, division by zero, or data not found.

To handle exceptions, you use the EXCEPTION block within the BEGIN and END keywords of a block:

DO 
$$
DECLARE
   -- declaration
BEGIN
    -- code that may cause exceptions
EXCEPTION
    WHEN exception_name THEN
        -- Handle the specific exception
        RAISE NOTICE 'An error occurred: %', SQLERRM;
    WHEN OTHERS THEN
        -- Handle all other exceptions
        RAISE NOTICE 'An unknown error occurred: %', SQLERRM;
END;
$$
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• The WHEN clause catches specific exceptions such as division by zero or unique constraint violation.
• The WHEN OTHERS clause catches all exceptions you have not explicitly handled.

The SQLERRM and SQLSTATE are error message variables:

• The SQLERRM variable contains the exception’s message.
• The SQLSTATE variable holds the error code.

For a comprehensive list of exception names and SQL error codes, check out this SQL Error Codes page.

Handling a Division by Zero Exception Example #

The following statement create a function called try_divide that returns the division of two operands:

CREATE FUNCTION try_divide(x NUMERIC, y NUMERIC) 
RETURNS NUMERIC AS 
$$
BEGIN
    RETURN x / y;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Cannot divide by zero, returning NULL!';
        RETURN NULL;
END;
$$
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

How the function works:

• Divide x by y and return the result.
• If y is zero, a division_by_zero exception will occur. The EXCEPTION block handles it by raising a notice and returning NULL.

For example, the following statement calls the try_divide to divide 10 by 2:

SELECT try_divide(10, 2);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

     try_divide
--------------------
 5.0000000000000000Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

However, when you divide 10 by zero, it will return NULL and issue a notice:

SELECT try_divide(10, 0);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

NOTICE: Cannot divide by zero, returning NULL!Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Handling a NO_DATA_FOUND Exception Example #

The NO_DATA_FOUND exception occurs when a SELECT INTO statement does not retrieve any row.

The following example creates a function called get_inventory_quantity that get an inventory quantity of a product specified by a product id:

CREATE FUNCTION get_inventory_quantity (id INT) 
RETURNS INT AS 
$$
DECLARE 
     v_qty INT;
BEGIN
    SELECT quantity INTO STRICT v_qty 
    FROM inventories
    WHERE product_id = id;

    RETURN v_qty;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE NOTICE 'No product found with the id %', id;
        RETURN NULL;
END;
$$
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

How the function works:

First, retrieve the quantity of the product specified by id and assign it to the v_qty variable using the SELECT INTO statement with the STRICT option:

SELECT quantity 
INTO STRICT v_qty
FROM inventories
WHERE product_id = id;

Second, return the quantity:

RETURN v_qty;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, if the product with the id does not exist, the NO_DATA_FOUND exception occurs. In this case, we raise a notice and return NULL in the EXCEPTION block:

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE NOTICE 'No product found with the id %', id;
        RETURN NULL;Code language: PHP (php)

The following statement calls the get_inventory_quantity function to get the inventory quantity of the product with id 9999:

SELECT get_inventory_quantity (9999);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

NOTICE:   No product found with the id 9999
 get_inventory_quantity
------------------------
                   NULLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Handling a TOO_MANY_ROWS Exception #

The following creates a function that finds a product by safety stock:

CREATE FUNCTION find_product_by_safety_stock (qty INT) 
RETURNS products 
AS 
$$
DECLARE 
     v_product products%ROWTYPE;
BEGIN
    SELECT *  
    FROM products
    INTO STRICT v_product 
    WHERE safety_stock = qty;

    RETURN v_product;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE NOTICE 'No product found with the safety stock qty %', qty;
        RETURN NULL;
    WHEN TOO_MANY_ROWS THEN
        RAISE NOTICE 'More than one product found with the safety stock qty %. Getting the one with the highest price', qty;

        SELECT *  
        FROM products
        INTO STRICT v_product 
        WHERE safety_stock = qty  
        ORDER BY price DESC
        LIMIT 1;

        RETURN v_product;
END;
$$
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

How the function works:

The function finds the product with the specific safety stock from the products table. If there is more than one product, the EXCEPTION block raises a notice and returns the product with the highest price.

The following statement calls the find_product_by_safety_stock function to find the product with a safety stock of 10:

SELECT
  product_name,
  safety_stock,
  price
FROM
  find_product_by_safety_stock (10);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

NOTICE:   More than a product found with the safety stock qty 10
   product_name   | safety_stock | price
------------------+--------------+--------
 Sony Xperia 1 VI |           10 | 949.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the EXCEPTION block to handle exceptions that occur during the execution of your PL/pgSQL code.

Quiz #