PL/pgSQL RAISE Statement

Summary: In this tutorial, you’ll learn how to generate messages using the PL/pgSQL RAISE statement.

Introduction to PL/pgSQL RAISE Statement #

In PL/pgSQL, the RAISE statement allows you to debug, inform, warn, and signal errors in your procedures, functions, and triggers.

Here’s the syntax of the RAISE statement:

RAISE [level] 'format' [expression, ...]Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The level specifies the severity level of the message. The valid levels are as follows:

• DEBUG: Use to debug the code.
• LOG: Write messages to the PostgreSQL server’s log, but don’t send them to the client.
• NOTICE: Return informational messages to the client.
• WARNING: Warn the client without interrupting code execution.
• EXCEPTION: Issue an error and abort the execution of the current block.

The format is a string that may include one or more placeholders (%). The RAISE statement will substitute the placeholders with the expression.

The number of placeholders and expressions must be the same, or the RAISE statement will issue an error.

Using PL/pgSQL RAISE Exception to Log Validation Errors #

The following statement creates a function called find_product_by_id that finds a product by its id from the products table:

CREATE OR REPLACE FUNCTION find_product_by_id (id INT) 
RETURNS products 
AS 
$$
DECLARE
    v_product products%ROWTYPE;
BEGIN
    IF id <= 0 THEN
         RAISE EXCEPTION 'The id must be positive integer %', id;
    END IF;

    SELECT * INTO v_product
    FROM products
    WHERE product_id = id;

    RETURN v_product;

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

Try it

The following statement calls the find_product_by_id function to retrieve the product with id -1:

SELECT
  product_id,
  product_name,
  price
FROM
  find_product_by_id (-1);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

The find_product_by_id function issues an error:

ERROR:  The id must be positive integer -1
CONTEXT:  PL/pgSQL function find_product_by_id(integer) line 6 at RAISECode language: plaintext (plaintext)

The function raises an exception and stops executing because the id is negative.

The following statement uses the find_product_by_id function to retrieve the product with id 1:

SELECT
  product_id,
  product_name,
  price
FROM
  find_product_by_id (1);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 product_id |    product_name    | price
------------+--------------------+--------
          1 | Samsung Galaxy S24 | 999.99Code language: plaintext (plaintext)

Customizing Options #

The RAISE statement allows you to customize the options with the USING clause:

RAISE level 'format' [expression, ...]
USING ERRCODE = <SQLSTATE code>, 
      HINT = <Hint>;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The SQLSTATE code specifies a SQLSTATE code you want to use. Other functions may handle it using the EXCEPTION block.

The hint provides more helpful information to the callers to gracefully handle the error.

The following find_product_by_id function uses the RAISE statement with the USING option:

CREATE OR REPLACE FUNCTION find_product_by_id(id INT) 
RETURNS products
AS
$$
DECLARE
    v_product products%ROWTYPE;
BEGIN
    IF id <= 0 THEN
         RAISE EXCEPTION 'The id must be positive integer %', id
         USING ERRCODE = '2200',
               HINT = 'Please provide a positive integer'; 
    END IF;

    SELECT * INTO v_product
    FROM products
    WHERE product_id = id;

    RETURN v_product;

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

Try it

We use 2200 as the custom SQLSTATE code in the function and provide a hint.

The following statement calls the find_product_by_id function:

SELECT
  product_id,
  product_name,
  price
FROM
  find_product_by_id (-1);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output

ERROR:  The id must be positive integer -1
HINT:  Please provide a positive integer
CONTEXT:  PL/pgSQL function find_product_by_id(integer) line 6 at RAISECode language: plaintext (plaintext)

The output includes the hint that we specified in the USING clause.

Summary #

• Use the PL/pgSQL RAISE statement for debugging, logging, and managing exceptions.

Quiz #