PL/pgSQL ASSERT

Summary: in this tutorial, you’ll learn how to use the PL/pgSQL ASSERT statement to debug and validate assumptions in your code.

Introduction to the PL/pgSQL ASSERT Statement #

The ASSERT statement evaluates a condition and raises an error if the condition does not evaluate to true.

The ASSERT statement is helpful for the following scenarios:

• Verifying preconditions and postconditions of functions and procedures.
• Detecting unexpected conditions during development.
• Debugging complex logic.

Here’s the syntax of the ASSERT statement:

ASSERT condition [ , message ];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• condition is a Boolean expression that the ASSERT statement evaluates. If the condition is not true, the assertion will fail.
• message is a custom error message to send back to the client when the assertion fails. Therefore, you should use a meaningful message to make troubleshooting easier.

Configuration Parameters #

PostgreSQL uses the plpgsql.check_asserts configuration parameter to turn assertions on and off.

You can check the current status of the plpgsql.check_asserts parameter using the following statement:

SHOW plpgsql.check_asserts;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 plpgsql.check_asserts
-----------------------
 onCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To enable assertions, you can set the plpgsql.check_asserts parameter to true in your session:

SET plpgsql.check_asserts = true;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Please note that you should disable assertions in your production environment to avoid performance overhead.

To do that, you can set the plpgsql.check_asserts configuration parameter to false:

SET plpgsql.check_asserts = false;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Basic PL/pgSQL ASSERT Usage #

The following example shows how the ASSERT statement works:

DO
$$
BEGIN
    ASSERT 1 + 1 = 2;
    ASSERT 1 + 1 = 3, 'This is not mathematically true';
END;
$$
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

If you run the block, you’ll see the message in the output:

ERROR:  This is not mathematically true
CONTEXT:  PL/pgSQL function inline_code_block line 4 at ASSERTCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Checking Preconditions #

The following example uses the ASSERT statement to ensure the caller provides positive numbers:

CREATE OR REPLACE FUNCTION update_product_weight(
    id INT, 
    new_weight DEC
) 
RETURNS VOID
AS
$$
BEGIN

    ASSERT id > 0, 'The id must be positive';
    ASSERT new_weight > 0, 'The new_weight must be positive';

    UPDATE products
    SET weight = new_weight
    WHERE product_id = id;

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

Try it

The following statement calls the update_product_weight function to update the weight for the product with id 1 to 0:

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

Try it

Error:

ERROR:  The new_weight must be positive
CONTEXT:  PL/pgSQL function update_product_weight(integer,numeric) line 5 at ASSERTCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Debugging Logic Example #

The ASSERT statement can help you examine complex logic by verifying intermediate states.

For example, the following calculate_stock function uses an ASSERT statement to verify if the stock is greater than zero:

CREATE OR REPLACE FUNCTION calculate_stock(
     onhand_stock INT, 
     safety_stock INT
)
RETURNS INT 
AS 
$$
DECLARE
    v_stock DEC;
BEGIN
    v_stock = onhand_stock - safety_stock;
    ASSERT v_stock >= 0,  'The stock cannot be negative';
    RETURN v_stock;
END;
$$ 
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

The following example raises an error because the input arguments cause the stock to be negative:

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

Try it

Error:

ERROR:  The stock cannot be negative
CONTEXT:  PL/pgSQL function calculate_stock(integer,integer) line 6 at ASSERTCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the ASSERT statement to check for assumptions and troubleshoot your code.

Quiz #