PostgreSQL Stored Procedures

Stored procedures in PostgreSQL are reusable database objects that contain one or more SQL statements. They allow performing complex operations, batch processing, and encapsulating business logic directly in the database.

1. Functions vs Procedures

• Functions: Return a value, can be used in queries (SELECT, WHERE, etc.).
• Procedures: Do not return a value directly; executed with CALL and can perform transactions, modifications, and control logic.

2. Creating a Stored Procedure

-- Create a procedure to increase employee salaries
            CREATE OR REPLACE PROCEDURE increase_salary(dept_id INT, increment NUMERIC)
            LANGUAGE plpgsql
            AS $$
            BEGIN
                UPDATE employees
                SET salary = salary + increment
                WHERE department_id = dept_id;
            END;
            $$;

3. Calling a Procedure

-- Call the procedure
            CALL increase_salary(2, 1000);

4. Procedure with Parameters

Procedures can have IN, OUT, and INOUT parameters for flexible operations.

-- Procedure with IN and OUT parameters
            CREATE OR REPLACE PROCEDURE get_department_salary(
                dept_id INT,
                OUT total_salary NUMERIC
            )
            LANGUAGE plpgsql
            AS $$
            BEGIN
                SELECT SUM(salary) INTO total_salary
                FROM employees
                WHERE department_id = dept_id;
            END;
            $$;

            -- Call procedure
            CALL get_department_salary(1, total_salary);

5. Transactions in Procedures

Unlike functions, procedures can manage transactions using COMMIT and ROLLBACK inside their body.

CREATE OR REPLACE PROCEDURE transfer_salary(
                from_emp INT,
                to_emp INT,
                amount NUMERIC
            )
            LANGUAGE plpgsql
            AS $$
            BEGIN
                BEGIN
                    UPDATE employees SET salary = salary - amount WHERE id = from_emp;
                    UPDATE employees SET salary = salary + amount WHERE id = to_emp;
                    COMMIT;
                EXCEPTION WHEN OTHERS THEN
                    ROLLBACK;
                    RAISE NOTICE 'Transaction failed';
                END;
            END;
            $$;

            -- Call procedure
            CALL transfer_salary(101, 102, 500);

6. Dropping a Procedure

-- Drop procedure
            DROP PROCEDURE IF EXISTS increase_salary(INT, NUMERIC);

7. Best Practices

• Use procedures for operations that require transactions or batch updates.
• Keep logic modular and reusable.
• Use proper exception handling to avoid partial updates.
• Document procedures with input/output parameters for maintainability.
• Test procedures thoroughly before deploying to production databases.

Conclusion

Stored procedures in PostgreSQL are powerful tools for encapsulating business logic, performing batch operations, and managing transactions efficiently. Combined with triggers, functions, and transactions, they help build robust, maintainable, and high-performance database applications. In the next tutorial, we will explore Materialized Views for storing precomputed query results to improve performance.