PostgreSQL – Stored Procedures

Stored Procedures in PostgreSQL

In PostgreSQL, a stored procedure is a precompiled set of SQL statements that are stored and executed on the database server. Stored procedures can perform various operations, such as inserting, updating, and deleting records, as well as implementing complex business logic. Unlike functions, stored procedures allow transaction control (e.g., COMMIT and ROLLBACK).

This tutorial explores how to create, use, and manage stored procedures in PostgreSQL, with practical examples and detailed explanations.

---

Features of Stored Procedures

Stored procedures in PostgreSQL offer several features:

• Improved Performance: Precompiled and stored on the server, reducing runtime overhead.
• Transaction Control: Allow usage of COMMIT and ROLLBACK for managing transactions within the procedure.
• Encapsulation: Encapsulate complex SQL logic into a single callable entity.
• Security: Centralize logic on the database server, reducing the need to expose sensitive SQL to the application layer.

Basic Syntax

The general syntax for creating a stored procedure in PostgreSQL is:

CREATE OR REPLACE PROCEDURE procedure_name (parameter_list)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Procedure logic here
END;
$$;

Parameters:

• IN: Input parameter.
• OUT: Output parameter.
• INOUT: Acts as both input and output.

---

Example 1: Simple Stored Procedure

Let’s create a stored procedure to insert a new record into a table. First, create a table named employees:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary NUMERIC(10, 2)
);

Create a stored procedure to insert a new employee:

CREATE OR REPLACE PROCEDURE add_employee(
    IN emp_name VARCHAR,
    IN emp_department VARCHAR,
    IN emp_salary NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO employees (employee_name, department, salary)
    VALUES (emp_name, emp_department, emp_salary);
END;
$$;

Call the stored procedure to add a new employee:

CALL add_employee('Alice', 'HR', 50000);

Query the employees table to verify the insertion:

SELECT * FROM employees;

Explanation: The add_employee procedure inserts a new record into the employees table with the values passed as parameters.

---

Example 2: Stored Procedure with Transaction Control

Stored procedures can include transaction control statements such as COMMIT and ROLLBACK. Let’s create a procedure that tries to insert two records, but rolls back if an error occurs.

CREATE OR REPLACE PROCEDURE add_multiple_employees()
LANGUAGE plpgsql
AS $$
BEGIN
    -- Start a transaction
    BEGIN
        INSERT INTO employees (employee_name, department, salary)
        VALUES ('Bob', 'IT', 60000);
        
        -- Intentionally cause an error to demonstrate rollback
        INSERT INTO employees (employee_name, department, salary)
        VALUES (NULL, 'Finance', 70000); -- This will fail due to NOT NULL constraint
        
        COMMIT; -- This will not be reached
    EXCEPTION
        WHEN others THEN
            ROLLBACK; -- Rollback the transaction on any error
    END;
END;
$$;

Call the procedure:

CALL add_multiple_employees();

Query the employees table:

SELECT * FROM employees;

Explanation: Because of the error in the second INSERT statement, the entire transaction is rolled back, and no rows are added to the employees table.

---

Example 3: Stored Procedure with OUT Parameters

Stored procedures can return values using OUT parameters. Let’s create a procedure to fetch the total salary for a specific department.

CREATE OR REPLACE PROCEDURE get_total_salary(
    IN department_name VARCHAR,
    OUT total_salary NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT SUM(salary) INTO total_salary
    FROM employees
    WHERE department = department_name;
END;
$$;

Call the procedure:

CALL get_total_salary('HR', NULL);

Explanation: The procedure calculates the total salary for the specified department and returns it via the OUT parameter.

---

Conclusion

Stored procedures in PostgreSQL provide a way to encapsulate and execute complex SQL logic on the database server. They support transaction control, input/output parameters, and error handling, making them an essential tool for database management and application development.