Advanced Data Modelling - Stored Procedures in PostgreSQL

Introduction

Stored procedures are functions stored in the database that can perform operations such as inserting, updating, and deleting data, as well as more complex logic. They help encapsulate business logic, improve performance, and maintain code consistency. This tutorial covers creating, managing, and using stored procedures in PostgreSQL.

Creating Stored Procedures

Stored procedures are created using the CREATE PROCEDURE statement. Here is an example:

CREATE OR REPLACE PROCEDURE add_employee(first_name text, last_name text, department text)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO employees (first_name, last_name, department)
    VALUES (first_name, last_name, department);
END;
$$;
                    

CREATE PROCEDURE
                    

In this example, a stored procedure named add_employee is created to insert a new employee into the employees table.

Calling Stored Procedures

Stored procedures are executed using the CALL statement. Here is an example:

CALL add_employee('John', 'Doe', 'Sales');
                    

CALL PROCEDURE
                    

This example calls the add_employee procedure to add a new employee named John Doe in the Sales department.

Using Parameters

Stored procedures can accept input parameters and return output parameters. Here is an example with an output parameter:

CREATE OR REPLACE PROCEDURE get_employee_count(OUT emp_count integer)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*) INTO emp_count FROM employees;
END;
$$;
                    

CREATE PROCEDURE
                    

This example creates a procedure named get_employee_count that returns the total number of employees.

Transaction Management

Stored procedures can manage transactions using BEGIN, COMMIT, and ROLLBACK statements. Here is an example:

CREATE OR REPLACE PROCEDURE transfer_funds(from_account integer, to_account integer, amount numeric)
LANGUAGE plpgsql
AS $$
BEGIN
    BEGIN
        UPDATE accounts SET balance = balance - amount WHERE account_id = from_account;
        UPDATE accounts SET balance = balance + amount WHERE account_id = to_account;
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE;
    END;
END;
$$;
                    

CREATE PROCEDURE
                    

This procedure, named transfer_funds, transfers money between two accounts. If any error occurs, it rolls back the transaction.

Dropping Stored Procedures

Stored procedures can be removed using the DROP PROCEDURE statement. Here is an example:

DROP PROCEDURE add_employee;
                    

DROP PROCEDURE
                    

This statement drops the add_employee procedure.

Best Practices

Here are some best practices for using stored procedures in PostgreSQL:

• Use stored procedures to encapsulate business logic and improve code maintainability.
• Handle exceptions properly to ensure data consistency and integrity.
• Optimize procedures for performance by minimizing complex logic and excessive database interactions.
• Document procedures thoroughly to ensure they are easily understood and maintained.