Using Procedure

Introduction

When using older versions of Postgres, only Functions were available. However, from version 11 onwards, Postgres has officially added support for Procedures to address the limitations that Functions could not overcome. While their implementations are quite similar, they possess fundamental core differences:

Transaction Control
- The biggest difference is that a Procedure allows you to have full control over transactions. You can use COMMIT or ROLLBACK directly inside the body of the procedure. This is extremely useful when you need to process large amounts of data in batches without worrying about memory overflow or locking tables for too long.
- Conversely, a Function cannot contain COMMIT or ROLLBACK. The entire Function must execute within a single transaction. If a command at the end of the function fails, all previous commands are completely rolled back.
Return Value
- Using a Function requires returning a certain value, even if it is just void, a single value, or a TABLE dataset. It is designed to compute and return results.
- On the other hand, using a Procedure does not return values via the RETURNS keyword. If you want to output data, you must use OUT parameters.
The usage syntax is also completely different

Procedures use the CALL statement.
Functions use the SELECT statement. Because they use SELECT, you can nest functions inside other query statements and use them with WHERE and JOIN.

Use cases

Use a Function when you need to calculate, transform data, or need to return a specific result to be used in conjunction with SELECT statements.
Use a Procedure when you need to perform a series of complex data modification tasks, need to group and COMMIT data in stages to optimize performance.

Detail

First, let us create the tables as follows:

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    stock INT NOT NULL CHECK (stock >= 0)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    status VARCHAR(20) DEFAULT 'Pending'
);

CREATE TABLE order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_id INT REFERENCES products(product_id),
    quantity INT NOT NULL
);

Next, create the Procedure:

CREATE OR REPLACE PROCEDURE pr_process_pending_orders(
    INOUT total_processed INT DEFAULT 0,
    INOUT total_success INT DEFAULT 0,
    INOUT total_failed INT DEFAULT 0,
 INOUT v_error_msg TEXT DEFAULT ''
)
LANGUAGE plpgsql
AS $$
DECLARE
    r_order RECORD;
    r_item RECORD;
BEGIN
    total_processed := 0;
    total_success := 0;
    total_failed := 0;

    FOR r_order IN SELECT order_id FROM orders WHERE status = 'Pending' LOOP
        total_processed := total_processed + 1;
        
        BEGIN
            FOR r_item IN SELECT product_id, quantity FROM order_items WHERE order_id = r_order.order_id LOOP
                UPDATE products 
                SET stock = stock - r_item.quantity
                WHERE product_id = r_item.product_id;
            END LOOP;

            UPDATE orders SET status = 'Processed' WHERE order_id = r_order.order_id;
            
            total_success := total_success + 1;

        EXCEPTION 
            WHEN OTHERS THEN
                GET STACKED DIAGNOSTICS v_error_msg = MESSAGE_TEXT;
                
                UPDATE orders SET status = 'Failed' WHERE order_id = r_order.order_id;
                total_failed := total_failed + 1;
        END;
    END LOOP;
    COMMIT;    
END;
$$;

This Procedure is commonly used in batch updates, when users purchase a large quantity of products and the administrator needs to manually verify necessary information such as address, contact details and balance before approving to deduct stock.
The processing method uses FOR to check each record in the orders table where status = 'Pending'.
It will update the stock in the products table.
- If the deduction is successful, it updates the status in the orders table to Processed.
- If an error occurs, it changes the status to Failed and outputs the error message.
Whichever order is successfully processed or fails is completely separated, meaning it will process all Pending orders rather than crashing when a single order fails.

Note that since you are allowed to create multiple Procedures with the same name but different parameters, when you want to DROP one, you must pass the exact number and order of parameters to delete the correct Procedure:

DROP PROCEDURE IF EXISTS pr_process_pending_orders(INT, INT, INT);
DROP PROCEDURE IF EXISTS pr_process_pending_orders(INT, INT, INT, TEXT);

To verify, use the following statements:

-- Statement 1
INSERT INTO orders (customer_name) VALUES ('customer 1');
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1, 2);

-- Statement 2
INSERT INTO orders (customer_name) VALUES ('customer 2');
INSERT INTO order_items (order_id, product_id, quantity) VALUES (2, 1, 90);

-- Statement 3
CALL pr_process_pending_orders();

Statement 1: Creating this order succeeds because there is sufficient stock.
Statement 2: This order will fail because it exceeds the available stock quantity.
Statement 3: This is how to call a Procedure.