PL/pgSQL Block Structure in PostgreSQL

In this PostgreSQL tutorial, I will demonstrate how to implement the PL/pgSQL block structure in PostgreSQL.

PL/pgSQL Block Structure in PostgreSQL

The PL/pgSQL block structure acts as a prototype and is used when creating a function or procedure in PostgreSQL. Also, you will understand “What is an anonymous code block” and the concept of subblocks.

Additionally, you will understand how to implement PL/pgSQL block structure with real-world examples.

What is PL/pgSQL Block Structure?

PL/pgSQL is a PostgreSQL procedural language that allows us to create complex functions and procedures. PL/pgSQL provides a concept of a framework called block structure that helps in organizing code, managing variables, and handling exceptions.

In other words, PL/pgSQL is also referred to as a block-structured language. The block consists of declarations and statements, which are enclosed within keywords BEGIN and END.

The syntax of the block in PostgreSQL is given below.

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
[ EXCEPTION
    exception-handlers ]
END [label];

• [ <<label>> ]: The label is optional, and it is used to specify the label for the block. You may have a nested block, allowing you to provide different labels for each block for clarity.
• [ DECLARE declarations ]: This is a declaration section, which is optional. Here, you define all the variables, constants, and data types used in the BEGIN and END body sections. Remember, always terminate each declaration with a semicolon (;).
• BEGIN…END: It is also called the logic section, where you specify all the logic that you want to execute sequentially. So, whatever statement you have, such as SELECT, UPDATE, etc., all the statements are written within the BEGIN and END sections.
• [ EXCEPTION exception-handlers ]: It is the exception handler section, where you write all the statements that you want to execute for a specific exception or error that occurs while executing the block. Always specify the exception before the END.

There is one more thing called Anonymous code blocks. Anonymous code blocks don’t have a name and are not stored in the database; however, they function like a function or procedure.

The anonymous code blocks are used for one-time tasks, and after execution, it is discarded.

The syntax is given below.

DO $$
[ DECLARE
    declarations ]
BEGIN
    statements
[ EXCEPTION
    exception-handlers ]
END $$;

• DO $$: The DO command is used for the execution of the anonymous code block and double dollar $$ indicate the start and end of the block code. This delimiter $$ is customizable, which means you can specify your own delimiter, such as ‘$myblock$’.

Let’s see how an anonymous code block works with an example.

DO $$
DECLARE
    num INTEGER := 5;
BEGIN
    IF num = 5 THEN
        RAISE NOTICE 'The number is %',num;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'An error occurred!';
END $$;

In the above code, the variable num is declared with a value of 5 of type integer, then within the BEGIN and END sections, the IF statement is used to check whether variable num = 5 or not. If the IF statement becomes true, then it prints the value of the variable num, which is 5.

Otherwise, if any error occurs while executing the block, the exception handler section is executed to display the message ‘An error occurred!’.

The above is a simple example, but to understand how to use the block structure in detail, let’s examine how to utilize the block structure or anonymous code block in various scenarios. Most of the time, you will see the anonymous code block with examples.

Case 1: Anonymous Code

Example 1

Suppose you are a backend developer in a company that has an E-commerce website, and you need to create a function that can identify the product with negative prices and fix the negative price of that product.

The company now stores all products in a table called ‘products,’ as shown below.

As you can see, some of the product has a negative price. Now, to identify the product with negative prices, let’s create a function named ‘get_products_negative_price,‘ which is defined below.

CREATE OR REPLACE FUNCTION get_products_negative_price()
RETURNS TABLE (
    product_id INTEGER,
    product_name TEXT,
    product_price DECIMAL(10, 2)
) AS $$
BEGIN
    RETURN QUERY 
    SELECT id, name, price 
    FROM products 
    WHERE price < 0;
END;
$$ LANGUAGE plpgsql;

Now, run the above function to retrieve products with negative prices.

SELECT * FROM get_products_negative_price();

Suppose the price of the products is correct, but it is a negative value. To fix this problem, create an anonymous code block as you learned above.

DO $$
BEGIN
    UPDATE products 
    SET price = ABS(price) 
    WHERE price < 0;

    RAISE NOTICE 'Fixed negative product prices.';
END $$;

When you run the above code, it converts the negative prices of the product into positive prices using the ABS() function and updates the price value.

Let’s view the table products using the code below.

SELECT * FROM products;

As you can see, there are no products with negative prices in the table ‘products’.

Example 2

Let’s take one more example where you need to verify the users, suppose there is a table called ‘users’ and recently new column named ‘verified’ is added to this table, and you need to verify all the existing users for their email by assuming that they were verified before this system was implemented.

The ‘users’ table is shown below.

The ‘verified’ column for all existing users is null, so create an anonymous code block that can verify or set the column value to true.

DO $$
BEGIN
    UPDATE users 
    SET verified = true 
    WHERE verified IS NULL;

    RAISE NOTICE 'Marked all existing users as verified.';
END $$;

Now, view the ‘users’ table to check whether existing users are verified or not.

Look at the ‘verified’ column in the ‘users’ table; it shows that all users are verified.

Case 2: With Subblocks

Example

Take one more example where you need to check the availability of products in stock. You have a table called ‘products’ that is shown below.

As you can see, some of the product stock is 0, indicating that the product is currently unavailable. As a database developer in a company, you need to create a feature or function that checks the stock availability of a product.

For that, create a function named ‘is_product_available_in_stock()’ by using the code below.

CREATE OR REPLACE FUNCTION is_product_available_in_stock(productId INTEGER)
RETURNS BOOLEAN AS $$
DECLARE
    stock_count INTEGER;
BEGIN
    BEGIN
        SELECT stock INTO stock_count FROM products WHERE id = productId;
        IF stock_count > 0 THEN
            RETURN TRUE;
        ELSE
            RETURN FALSE;
        END IF;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE EXCEPTION 'Product with ID % not found.', productId;
    END;
EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Error checking product stock.';
        RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

The above function includes the concept of subblocks. The primary or outer block contains the entire function and handles errors. If any error occurs while executing the code, the outer block’s exception handler will handle the error.

• Within an outer or main block, there is an additional block called the inner block that retrieves the stock count for a specific product. If the product doesn’t exist in the ‘products’ table, then it displays the error ‘Product with ID % not found.’
• If the product exists in the ‘products’ table, it checks the stock count and returns a true or false value. If the stock count is greater than zero, it returns the actual value; otherwise, it returns the false value in case the stock count is equal to zero.

Let’s check the availability of the specific product in stock by calling the function ‘is_product_available_in_stock()’. Check the product Laptop with an ID equal to 1.

SELECT  is_product_available_in_stock(1);

The output shows the value as True, indicating that the product Laptop is in Stock. Again, check the product Smartphone with an ID equal to 2 using the code below.

SELECT  is_product_available_in_stock(2);

The output shows the value as False, which means the product Smartphone is not available in Stock.

Conclusion

In this PostgreSQL tutorial, you learned how to use the PL/pgSQL block structure in PostgreSQL with different scenarios, such as fixing product negative prices, user verification, and checking the availability of specific products in stock. Also learned the concept of anonymous code blocks and subblocks.

You may also read:

• PostgreSQL function returns a table
• Create a stored procedure in PostgreSQL
• PostgreSQL Joins – How to use