PL/pgSQL Block Structure in PostgreSQL

In this PostgreSQL tutorial, I will show you how to implement 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 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 and this language 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, so you can provide different labels to each block for clarity.
• [ DECLARE declarations ]: This is a declare section and it is optional, here you define all the variables, constants, and data type that is used in the BEGIN and END body section. Remember, always terminate each declaration with a semi-colon (;).
• 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 and etc, all the statement is written within BEGIN and END section.
• [ EXCEPTION exception-handlers ]: It is the exception handler section, where you write all the statement that you want to execute for specific exception or error that occurs while executing the block. Always specify the exception before the END.

There is one more thing that is called Anonymous code blocks, anonymous code blocks don’t have any name and it is not stored in the database but they act as a function and 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 means you can specify your own delimiter something like this ‘$myblock$’.

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

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 value 5 of type integer, then within 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 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 know how to use the block structure in detail, let’s see how to use the block structure or anonymous code block with different scenarios. Most of the time, you will see the anonymous code block with examples.

PL/pgSQL Block Structure with Function and Anonymous Code

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.

Now the company stores all the products in a table called ‘products’ which is 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 get the products with negative prices.

SELECT * FROM get_products_negative_price();

Suppose the price of the products is correct but it is in a negative value, So to fix this problem, create an anonymous code block that 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 table products using the below code.

SELECT * FROM products;

As you can see there is no products with negative price in the table ‘products’.

PL/pgSQL Block Structure Only Anonymous Code in PostgreSQL

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 column ‘verified’ for all existing users is null, so create an anonymous code block that can verify or set the column value equal 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 table ‘users’ to check whether existing users are verified or not.

Look at the column ‘verified’ of table ‘users’, it shows all the users are verified users.

PL/pgSQL Block Structure in PostgreSQL with Subblocks

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

As you can see some of the product stock is 0 which means the product is not available. As a database developer in a company, you need to create a feature or function that can check the stock availability of the product.

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

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 main or outer block contains the whole function and it handles errors, if any error occurs while the execution of the code, the outer block exception handler will handle the error.

• Within an outer or main block, there is one more block called the inner block that fetches the stock count for the specific product, if the product doesn’t exist in the table ‘products’, then it shows the error ‘Product with ID % not found.’
• If the particular product exists in the table ‘products’, it checks the stock count and returns the true or false value. if the stock count is greater than zero, it returns true value, otherwise returns false value in case of 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, which means the product Laptop is available in Stock. Again check the product Smartphone with an id equal to 2 using the below code.

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 return table
• Create a stored procedure in PostgreSQL
• Postgresql Joins – How to use