PostgreSQL Stored Procedures

Key Procedure Concepts

CREATE FUNCTION func() RETURNS type

CREATE PROCEDURE proc()

IN param_name type

CALL procedure_name()

🔹 Creating Functions

Functions in PostgreSQL return values and can be used in SELECT statements. They're ideal for calculations, data transformations, and queries that need to return results to the caller.

-- Create sample table
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10,2),
    department VARCHAR(50)
);

INSERT INTO employees (name, salary, department) VALUES
    ('Alice', 75000, 'IT'),
    ('Bob', 65000, 'HR'),
    ('Charlie', 80000, 'IT');

-- Simple function that returns a value
CREATE OR REPLACE FUNCTION get_employee_count()
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    emp_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO emp_count FROM employees;
    RETURN emp_count;
END;
$$;

-- Call the function
SELECT get_employee_count() AS total_employees;

total_employees
-----------------
3

🔹 Functions with Parameters

Functions can accept input parameters to make them flexible and reusable. Parameters allow you to pass values into the function and customize its behavior for different scenarios.

-- Function with input parameters
CREATE OR REPLACE FUNCTION calculate_bonus(
    emp_salary DECIMAL,
    bonus_percent DECIMAL
)
RETURNS DECIMAL
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN emp_salary * (bonus_percent / 100);
END;
$$;

-- Use the function
SELECT 
    name,
    salary,
    calculate_bonus(salary, 10) AS bonus
FROM employees;

name    | salary   | bonus
--------+----------+--------
Alice   | 75000.00 | 7500.00
Bob     | 65000.00 | 6500.00
Charlie | 80000.00 | 8000.00

🔹 Creating Procedures

Procedures perform actions but don't return values directly. They're perfect for data modifications, complex business logic, and operations that need transaction control without returning query results.

-- Create procedure for salary increase
CREATE OR REPLACE PROCEDURE increase_salary(
    dept_name VARCHAR,
    increase_percent DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees
    SET salary = salary * (1 + increase_percent / 100)
    WHERE department = dept_name;
    
    RAISE NOTICE 'Salaries increased for % department', dept_name;
END;
$$;

-- Call the procedure
CALL increase_salary('IT', 5);

-- Check results
SELECT name, salary, department 
FROM employees 
WHERE department = 'IT';

name    | salary   | department
--------+----------+------------
Alice   | 78750.00 | IT
Charlie | 84000.00 | IT

🔹 OUT Parameters

OUT parameters allow procedures to return multiple values without using a RETURN statement. This is useful when you need to return several pieces of information from a single procedure call.

-- Procedure with OUT parameters
CREATE OR REPLACE PROCEDURE get_salary_stats(
    dept_name VARCHAR,
    OUT min_sal DECIMAL,
    OUT max_sal DECIMAL,
    OUT avg_sal DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT 
        MIN(salary),
        MAX(salary),
        AVG(salary)
    INTO min_sal, max_sal, avg_sal
    FROM employees
    WHERE department = dept_name;
END;
$$;

-- Call procedure with OUT parameters
CALL get_salary_stats('IT', NULL, NULL, NULL);

min_sal  | max_sal  | avg_sal
---------+----------+---------
78750.00 | 84000.00 | 81375.00

🔹 Control Structures

PL/pgSQL supports standard programming constructs like IF statements and loops. These control structures enable complex logic, conditional operations, and iterative processing within your stored procedures and functions.

-- Function with IF-ELSE
CREATE OR REPLACE FUNCTION get_salary_grade(emp_salary DECIMAL)
RETURNS VARCHAR
LANGUAGE plpgsql
AS $$
BEGIN
    IF emp_salary >= 80000 THEN
        RETURN 'Senior';
    ELSIF emp_salary >= 70000 THEN
        RETURN 'Mid-Level';
    ELSE
        RETURN 'Junior';
    END IF;
END;
$$;

-- Function with LOOP
CREATE OR REPLACE FUNCTION factorial(n INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    result INTEGER := 1;
    i INTEGER := 1;
BEGIN
    WHILE i <= n LOOP
        result := result * i;
        i := i + 1;
    END LOOP;
    RETURN result;
END;
$$;

-- Use the functions
SELECT name, salary, get_salary_grade(salary) AS grade
FROM employees;

🔹 Exception Handling

Stored procedures can catch and handle errors gracefully using exception blocks. This prevents errors from crashing your application and allows you to implement custom error handling logic.

-- Function with exception handling
CREATE OR REPLACE FUNCTION safe_divide(
    numerator DECIMAL,
    denominator DECIMAL
)
RETURNS DECIMAL
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN numerator / denominator;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Cannot divide by zero';
        RETURN NULL;
    WHEN OTHERS THEN
        RAISE NOTICE 'An error occurred: %', SQLERRM;
        RETURN NULL;
END;
$$;

-- Test the function
SELECT safe_divide(100, 0) AS result;

🔹 Managing Procedures

PostgreSQL provides commands to view, modify, and remove stored procedures and functions. You can also query system catalogs to see all routines and their definitions in your database.

-- View all functions and procedures
SELECT routine_name, routine_type
FROM information_schema.routines
WHERE routine_schema = 'public';

-- Drop function
DROP FUNCTION IF EXISTS get_employee_count();

-- Drop procedure
DROP PROCEDURE IF EXISTS increase_salary(VARCHAR, DECIMAL);

-- Replace existing function
CREATE OR REPLACE FUNCTION get_employee_count()
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN (SELECT COUNT(*) FROM employees);
END;
$$;

🔹 Benefits of Stored Procedures

Stored procedures offer significant advantages for database applications. They centralize business logic, improve performance through reduced network traffic, and provide better security through controlled data access.