Functions vs. Procedures in PL/pgSQL: Key Differences Explained

Hello, fellow PL/pgSQL enthusiasts! In this blog post, I will introduce you to Functions vs Procedures in PL/pgSQL – one of the most important and commonly used concepts in PL/p

gSQL: functions and procedures. Both functions and procedures allow you to execute reusable blocks of code, but they serve different purposes and have unique characteristics. Understanding their differences is crucial for writing efficient and maintainable database code. In this post, I will explain what functions and procedures are, highlight their key differences, and show how to use them effectively in PL/pgSQL. By the end of this guide, you will have a clear understanding of when to use functions and when to use procedures in your PL/pgSQL programs. Let’s dive in!

Introduction to Functions and Procedures in PL/pgSQL: Key Differences Explained

Functions and procedures are two fundamental components of PL/pgSQL (Procedural Language/PostgreSQL) that enable you to execute complex logic within the PostgreSQL database. While both are used to encapsulate reusable code, they serve distinct purposes and operate differently. Functions typically return a value and are used for calculations or data manipulation, whereas procedures perform actions without returning a result and are ideal for tasks like data modifications or administrative processes. Understanding the differences between functions and procedures is crucial for writing efficient and maintainable PL/pgSQL code. In this post, we will explore their key differences, use cases, and when to choose one over the other to optimize your database operations.

What are the Differences Between Functions and Procedures in PL/pgSQL?

Functions and procedures in PL/pgSQL are both used to execute blocks of code within the PostgreSQL database, but they serve different purposes and have distinct characteristics. Below is a detailed explanation of the key differences between functions and procedures in PL/pgSQL with examples.

Feature	Functions	Procedures
Return Value	Yes (Scalar, Record, Set)	No direct return value (OUT params)
Transaction Control	Not allowed	Allowed (COMMIT, ROLLBACK)
Usage	Data retrieval, computations	Data modification, administrative tasks
Invocation	Via SELECT	Via CALL
Error Handling	Basic with BEGIN...EXCEPTION	Advanced with transaction control
Performance	Optimized for queries	Suitable for batch processing
Triggers and Constraints	Usable in triggers	Not usable in triggers
Complexity	Simple to moderate logic	Complex, multi-step processes

Return Value

• Functions: Always return a value. They are used when you need to calculate and return data (e.g., a number, string, or table).

Example of a Function Returning a Value

CREATE OR REPLACE FUNCTION get_total_sales()
RETURNS INTEGER AS $$
DECLARE
    total_sales INTEGER;
BEGIN
    SELECT SUM(amount) INTO total_sales FROM sales;
    RETURN total_sales;
END;
$$ LANGUAGE plpgsql;

-- Calling the function
SELECT get_total_sales();

• Procedures: Do not return any value directly. They are designed for performing actions like updating records, logging information, or managing data.

Example of a Procedure Without a Return Value

CREATE OR REPLACE PROCEDURE update_stock(p_product_id INT, p_quantity INT)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE products SET stock = stock + p_quantity
    WHERE product_id = p_product_id;
    RAISE NOTICE 'Stock updated for product %', p_product_id;
END;
$$;

-- Calling the procedure
CALL update_stock(101, 50);

Usage Context

• Functions: Used when you need to perform a computation and return a result, especially within SELECT statements.
• Procedures: Used for administrative tasks such as modifying multiple tables, performing batch operations, or controlling transactional logic.

Transaction Control

• Functions: Cannot manage transactions (e.g., COMMIT or ROLLBACK is not allowed inside a function).
• Procedures: Can control transactions using COMMIT and ROLLBACK, making them useful for complex operations requiring rollback on failure.

Example of Transaction Control in a Procedure

CREATE OR REPLACE PROCEDURE transfer_funds(from_acc INT, to_acc INT, amount NUMERIC)
LANGUAGE plpgsql AS $$
BEGIN
    BEGIN
        UPDATE accounts SET balance = balance - amount WHERE account_id = from_acc;
        UPDATE accounts SET balance = balance + amount WHERE account_id = to_acc;
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE NOTICE 'Transaction failed';
    END;
END;
$$;

-- Calling the procedure
CALL transfer_funds(1, 2, 500);

Invocation Method

• Functions: Invoked using SELECT or other SQL queries.
• Procedures: Invoked using the CALL statement.

Example Code:

-- Function Call
SELECT get_total_sales();

-- Procedure Call
CALL update_stock(101, 50);

Performance Considerations

• Functions: Optimized for returning values and can be used in queries, which makes them faster when processing and retrieving data.
• Procedures: Better for bulk operations or tasks involving complex transaction management but may incur overhead due to their ability to handle transactions.

Error Handling

• Functions: Handle errors using BEGIN…EXCEPTION blocks but cannot control transactions.
• Procedures: Provide advanced error handling with the ability to ROLLBACK or COMMIT based on conditions.

Output Parameters

• Functions: Can return a single value, a record, or a set of records.
• Procedures: Support OUT parameters for returning multiple values.

Example of OUT Parameters in a Procedure

CREATE OR REPLACE PROCEDURE get_customer_info(p_id INT, OUT p_name TEXT, OUT p_email TEXT)
LANGUAGE plpgsql AS $$
BEGIN
    SELECT name, email INTO p_name, p_email FROM customers WHERE id = p_id;
END;
$$;

-- Calling the procedure
CALL get_customer_info(1, NULL, NULL);

Use in Constraints and Triggers

• Functions: Can be used in triggers and as part of constraints (e.g., CHECK constraints).
• Procedures: Cannot be directly used in triggers or constraints.

Example of a Function in a Trigger

CREATE OR REPLACE FUNCTION check_stock()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.stock < 0 THEN
        RAISE EXCEPTION 'Stock cannot be negative';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER stock_check
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION check_stock();

Portability

• Functions: More portable across different databases since many relational databases support functions similarly.
• Procedures: Less portable because the CALL syntax and transaction handling can vary between database systems.

Complexity of Logic

• Functions: Suitable for simple to moderately complex operations like calculations, data retrieval, and basic processing.
• Procedures: Better for complex workflows, batch processing, or operations involving multiple steps and tables.