PostgreSQL Stored Procedures, Functions & Triggers: Complete Guide

Database logic doesn’t always belong in application code. Sometimes it makes sense to encapsulate calculations, enforce rules, or automate updates directly in the database using functions, procedures, and triggers.

PostgreSQL’s PL/pgSQL language lets you write complex business logic in the database itself. Functions return values you can use in queries. Triggers automatically execute when data changes. This guide covers when to use each and how to write robust, efficient code.

What You’ll Learn

This guide covers:

  • Functions and stored procedures
  • PL/pgSQL syntax and control flow
  • Parameters and return types
  • Triggers and trigger events
  • Error handling and exceptions
  • Real-world business logic examples
  • Performance optimization

Why Use Database Functions?

Code reuse. If multiple applications need the same logic, write it once in the database. All applications automatically use the same behavior.

Data integrity. Enforce constraints and consistency at the database level, not relying on application validation.

Performance. Complex calculations in SQL are often faster than shipping data to the application and back.

Consistency. All users get the same logic, even if they bypass the application.

PostgreSQL Functions

Simple Function: SQL

The simplest functions are pure SQL.

CREATE FUNCTION get_employee_age(emp_id INTEGER)
RETURNS INTEGER AS $$
    SELECT EXTRACT(YEAR FROM AGE(NOW(), hire_date))::INTEGER
    FROM employees
    WHERE emp_id = get_employee_age.emp_id;
$$ LANGUAGE SQL;

-- Call the function
SELECT
    emp_id,
    first_name,
    hire_date,
    get_employee_age(emp_id) as years_employed
FROM employees
LIMIT 3;

Result:

emp_id | first_name | hire_date  | years_employed
-------+------------+------------+---------------
      1 | James      | 2020-01-15 |              6
      2 | Mary       | 2019-03-20 |              7
      7 | David      | 2018-06-11 |              8

The $$ delimiters allow the SQL to span multiple lines. LANGUAGE SQL declares this is pure SQL.

PL/pgSQL Function: With Control Flow

Add logic like IF statements, loops, and variable assignments.

CREATE FUNCTION calculate_bonus(emp_id INTEGER)
RETURNS DECIMAL AS $$
DECLARE
    v_salary DECIMAL;
    v_years_employed INTEGER;
    v_bonus DECIMAL;
BEGIN
    -- Get employee salary
    SELECT salary INTO v_salary
    FROM employees
    WHERE emp_id = calculate_bonus.emp_id;

    -- Calculate years employed
    SELECT EXTRACT(YEAR FROM AGE(NOW(), hire_date))::INTEGER INTO v_years_employed
    FROM employees
    WHERE emp_id = calculate_bonus.emp_id;

    -- Calculate bonus based on salary and tenure
    IF v_years_employed >= 10 THEN
        v_bonus := v_salary * 0.15;  -- 15% bonus
    ELSIF v_years_employed >= 5 THEN
        v_bonus := v_salary * 0.10;  -- 10% bonus
    ELSE
        v_bonus := v_salary * 0.05;  -- 5% bonus
    END IF;

    RETURN v_bonus;
END;
$$ LANGUAGE plpgsql;

-- Call the function
SELECT
    emp_id,
    first_name,
    salary,
    calculate_bonus(emp_id) as bonus_amount
FROM employees
WHERE dept_id = 2
LIMIT 3;

Result:

emp_id | first_name | salary  | bonus_amount
-------+------------+---------+-------------
      7 | David      | 105000  | 15750.00
      8 | Jennifer   | 102000  | 15300.00
      9 | Richard    | 98000   | 14700.00

PL/pgSQL functions have:

  • DECLARE section for variables
  • BEGIN…END block for logic
  • SELECT…INTO to fetch values
  • IF/ELSIF/ELSE for conditionals
  • RETURN to send results back

Functions with Multiple Parameters

CREATE FUNCTION get_salary_increase(
    emp_id INTEGER,
    increase_percent DECIMAL
)
RETURNS DECIMAL AS $$
DECLARE
    v_current_salary DECIMAL;
    v_new_salary DECIMAL;
BEGIN
    SELECT salary INTO v_current_salary
    FROM employees
    WHERE emp_id = get_salary_increase.emp_id;

    v_new_salary := v_current_salary * (1 + increase_percent / 100);
    RETURN v_new_salary;
END;
$$ LANGUAGE plpgsql;

-- Call with parameters
SELECT
    emp_id,
    first_name,
    salary,
    get_salary_increase(emp_id, 10) as salary_with_10_percent_raise
FROM employees
WHERE dept_id = 1
LIMIT 3;

Result:

emp_id | first_name | salary | salary_with_10_percent_raise
-------+------------+--------+-----------------------
      1 | James      | 75000  | 82500.00
      2 | Mary       | 78000  | 85800.00
      3 | Robert     | 72000  | 79200.00

Functions Returning Tables

Return multiple rows and columns.

CREATE FUNCTION get_department_stats(dept_id_param INTEGER)
RETURNS TABLE(
    department_name VARCHAR,
    total_employees INTEGER,
    average_salary DECIMAL,
    highest_salary DECIMAL,
    lowest_salary DECIMAL
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        d.dept_name,
        COUNT(e.emp_id)::INTEGER,
        AVG(e.salary),
        MAX(e.salary),
        MIN(e.salary)
    FROM employees e
    JOIN departments d ON e.dept_id = d.dept_id
    WHERE e.dept_id = dept_id_param
    GROUP BY d.dept_id, d.dept_name;
END;
$$ LANGUAGE plpgsql;

-- Call the function
SELECT * FROM get_department_stats(2);

Result:

department_name | total_employees | average_salary | highest_salary | lowest_salary
-----------------+-----------------+----------------+----------------+---------------
Engineering     |               8 |       97625.00 |        105000   |         90000

Stored Procedures (Procedures with OUT Parameters)

Procedures are functions that modify data (INSERT, UPDATE, DELETE) rather than just returning values.

Procedure to Give Raises

CREATE PROCEDURE give_department_raise(
    dept_id_param INTEGER,
    raise_percent DECIMAL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_count INTEGER;
BEGIN
    UPDATE employees
    SET salary = salary * (1 + raise_percent / 100)
    WHERE dept_id = dept_id_param;

    GET DIAGNOSTICS v_count = ROW_COUNT;

    RAISE NOTICE 'Updated % employees with % percent raise', v_count, raise_percent;
END;
$$;

-- Execute the procedure
CALL give_department_raise(1, 5);  -- Give Sales 5% raise

The procedure updates salary and uses GET DIAGNOSTICS to report how many rows changed.

Procedure with Output Parameters

CREATE PROCEDURE process_employee_bonus(
    emp_id_param INTEGER,
    bonus_amount DECIMAL,
    OUT new_salary DECIMAL,
    OUT total_comp DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees
    SET salary = salary + bonus_amount
    WHERE emp_id = emp_id_param
    RETURNING salary INTO new_salary;

    SELECT (new_salary + bonus_amount) INTO total_comp;
END;
$$;

-- Call and capture outputs
DO $$
DECLARE
    v_new_salary DECIMAL;
    v_total_comp DECIMAL;
BEGIN
    CALL process_employee_bonus(1, 5000, v_new_salary, v_total_comp);
    RAISE NOTICE 'New salary: %, Total comp: %', v_new_salary, v_total_comp;
END;
$$;

Procedures can return values through OUT parameters.

Triggers

Triggers automatically execute when data changes (INSERT, UPDATE, DELETE).

Basic BEFORE INSERT Trigger

Validate data before inserting.

CREATE TABLE salary_audit (
    audit_id SERIAL PRIMARY KEY,
    emp_id INTEGER,
    old_salary DECIMAL,
    new_salary DECIMAL,
    change_date TIMESTAMP,
    action VARCHAR(10)
);

-- Create trigger function
CREATE FUNCTION validate_salary() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.salary <= 0 THEN
        RAISE EXCEPTION 'Salary must be positive';
    END IF;

    IF NEW.salary > 500000 THEN
        RAISE WARNING 'Salary exceeds $500,000';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER trigger_validate_salary
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION validate_salary();

-- Test the trigger
INSERT INTO employees (emp_id, first_name, last_name, salary, dept_id, hire_date)
VALUES (100, 'Test', 'Employee', 85000, 1, '2026-02-21');  -- Success

-- This will fail
INSERT INTO employees (emp_id, first_name, last_name, salary, dept_id, hire_date)
VALUES (101, 'Invalid', 'Employee', -5000, 1, '2026-02-21');  -- ERROR

The trigger checks salary validity before INSERT or UPDATE. NEW refers to the incoming values.

AFTER UPDATE Trigger for Audit Trail

Track salary changes.

CREATE FUNCTION audit_salary_change() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.salary != OLD.salary THEN
        INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, action)
        VALUES (NEW.emp_id, OLD.salary, NEW.salary, NOW(), 'UPDATE');
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_audit_salary
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION audit_salary_change();

-- Update a salary
UPDATE employees SET salary = 80000 WHERE emp_id = 1;

-- Check the audit log
SELECT * FROM salary_audit WHERE emp_id = 1;

Result:

audit_id | emp_id | old_salary | new_salary | change_date         | action
---------+--------+------------+------------+---------------------+--------
       1 |      1 | 75000      | 80000      | 2026-02-21 10:30:00 | UPDATE

BEFORE DELETE Trigger

Prevent deletion of important records.

CREATE FUNCTION prevent_manager_deletion() RETURNS TRIGGER AS $$
DECLARE
    v_is_manager BOOLEAN;
BEGIN
    SELECT EXISTS(
        SELECT 1 FROM departments
        WHERE manager_id = OLD.emp_id
    ) INTO v_is_manager;

    IF v_is_manager THEN
        RAISE EXCEPTION 'Cannot delete manager. Reassign first.';
    END IF;

    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_prevent_manager_deletion
BEFORE DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION prevent_manager_deletion();

Prevents accidental deletion of department managers.

Error Handling

RAISE for Errors and Warnings

CREATE FUNCTION validate_hire_date(hire_date DATE)
RETURNS VOID AS $$
BEGIN
    IF hire_date > CURRENT_DATE THEN
        RAISE EXCEPTION 'Hire date cannot be in the future: %', hire_date;
    END IF;

    IF hire_date < CURRENT_DATE - INTERVAL '50 years' THEN
        RAISE WARNING 'Employee hired over 50 years ago: %', hire_date;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Call the function
SELECT validate_hire_date('2010-01-15');  -- Works
SELECT validate_hire_date('2030-01-15');  -- EXCEPTION

Exception Handling with TRY/CATCH (EXCEPTION block)

CREATE FUNCTION safe_delete_employee(emp_id INTEGER)
RETURNS TEXT AS $$
BEGIN
    DELETE FROM employees WHERE emp_id = safe_delete_employee.emp_id;
    RETURN 'Employee deleted successfully';
EXCEPTION
    WHEN foreign_key_violation THEN
        RETURN 'Cannot delete: Employee referenced by other records';
    WHEN OTHERS THEN
        RETURN 'Error: ' || SQLERRM;
END;
$$ LANGUAGE plpgsql;

-- Call the function
SELECT safe_delete_employee(1);

Catches specific errors (FOREIGN KEY VIOLATION) and generic errors (OTHERS).

Real-World Examples

Calculate Employee Tenure

CREATE FUNCTION employee_tenure_category(emp_id INTEGER)
RETURNS VARCHAR AS $$
DECLARE
    v_years INTEGER;
    v_category VARCHAR;
BEGIN
    SELECT EXTRACT(YEAR FROM AGE(NOW(), hire_date))::INTEGER INTO v_years
    FROM employees
    WHERE emp_id = employee_tenure_category.emp_id;

    CASE
        WHEN v_years < 1 THEN v_category := 'New';
        WHEN v_years < 3 THEN v_category := 'Junior';
        WHEN v_years < 5 THEN v_category := 'Mid-Level';
        WHEN v_years < 10 THEN v_category := 'Senior';
        ELSE v_category := 'Veteran';
    END CASE;

    RETURN v_category;
END;
$$ LANGUAGE plpgsql;

SELECT
    emp_id,
    first_name,
    employee_tenure_category(emp_id) as tenure_level
FROM employees
LIMIT 5;

Automatic Timestamp Updates

CREATE TABLE employee_updates (
    emp_id INTEGER,
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_timestamp
BEFORE UPDATE ON employee_updates
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

Every update automatically records the current timestamp.

Calculate Total Compensation

CREATE FUNCTION get_total_compensation(emp_id INTEGER)
RETURNS TABLE(
    employee_name VARCHAR,
    salary DECIMAL,
    bonus DECIMAL,
    total_comp DECIMAL
) AS $$
DECLARE
    v_salary DECIMAL;
    v_bonus DECIMAL;
BEGIN
    SELECT salary INTO v_salary FROM employees WHERE emp_id = get_total_compensation.emp_id;
    v_bonus := v_salary * 0.10;  -- Assume 10% bonus

    RETURN QUERY
    SELECT
        (SELECT first_name || ' ' || last_name FROM employees WHERE emp_id = get_total_compensation.emp_id),
        v_salary,
        v_bonus,
        v_salary + v_bonus;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_total_compensation(7);

Common Errors & Solutions

Error: “Syntax error at or near $$”

Problem:

CREATE FUNCTION test() RETURNS INTEGER AS $$
BEGIN
    RETURN 5
END;
$$ LANGUAGE plpgsql;

Missing semicolon after statements inside function.

Solution:

CREATE FUNCTION test() RETURNS INTEGER AS $$
BEGIN
    RETURN 5;
END;
$$ LANGUAGE plpgsql;

Error: “Column does not exist”

Problem:

CREATE FUNCTION get_salary(emp_id INTEGER)
RETURNS DECIMAL AS $$
BEGIN
    RETURN salary FROM employees WHERE emp_id = get_salary.emp_id;
END;
$$ LANGUAGE plpgsql;

Can’t use RETURN with FROM clause.

Solution:

Use SELECT INTO:

CREATE FUNCTION get_salary(emp_id INTEGER)
RETURNS DECIMAL AS $$
DECLARE
    v_salary DECIMAL;
BEGIN
    SELECT salary INTO v_salary FROM employees WHERE emp_id = get_salary.emp_id;
    RETURN v_salary;
END;
$$ LANGUAGE plpgsql;

Error: “Trigger function must return trigger”

Problem:

CREATE FUNCTION my_trigger() RETURNS INTEGER AS $$
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER my_trigger_name AFTER INSERT ON table FOR EACH ROW EXECUTE FUNCTION my_trigger();

Trigger functions must return TRIGGER.

Solution:

CREATE FUNCTION my_trigger() RETURNS TRIGGER AS $$
BEGIN
    RETURN NEW;  -- For INSERT/UPDATE
    -- or RETURN OLD;  -- For DELETE
END;
$$ LANGUAGE plpgsql;

Performance Tips

1. Use IMMUTABLE for Optimizable Functions

CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

PostgreSQL can optimize IMMUTABLE functions (always return same output for same input).

2. Avoid Scalar Functions in WHERE

-- Slow: Calls function for every row
SELECT * FROM employees WHERE calculate_bonus(emp_id) > 5000;

-- Better: Calculate once in a CTE
WITH bonuses AS (
    SELECT emp_id, calculate_bonus(emp_id) as bonus
    FROM employees
)
SELECT * FROM bonuses WHERE bonus > 5000;

3. Use STRICT for NULL Input Handling

CREATE FUNCTION add_or_zero(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
    RETURN COALESCE(a, 0) + COALESCE(b, 0);
END;
$$ LANGUAGE plpgsql STRICT;

STRICT tells PostgreSQL to return NULL if any input is NULL (avoids executing the function).

4. Index Trigger Lookups

CREATE TRIGGER my_trigger AFTER INSERT ON table FOR EACH ROW
EXECUTE FUNCTION trigger_func();

-- Ensure the join column is indexed
CREATE INDEX idx_table_lookup_col ON table(lookup_col);

5. Limit Trigger Overhead

Keep triggers simple and fast. Complex logic slows INSERT/UPDATE operations.

FAQ

Q: Should I use functions or application code? A: Shared logic that multiple apps need → functions. App-specific logic → application code.

Q: Can triggers call functions? A: Yes, that’s common. Triggers invoke functions that perform the actual logic.

Q: What’s the difference between a function and a procedure? A: Functions return values and are used in queries. Procedures don’t return values and are called with CALL.

Q: How do I drop a function? A: DROP FUNCTION function_name(param_types); Include parameter types if multiple overloads exist.

Q: Can I update a function after creating it? A: Yes: CREATE OR REPLACE FUNCTION ... But you can only change the function body, not parameters or return type.

Q: Are triggers slower than application logic? A: For simple checks, triggers add minimal overhead. Complex triggers can slow INSERT/UPDATE.

Q: How do I debug triggers? A: Use RAISE NOTICE 'message'; to print debug info. Check logs with: tail -f /var/log/postgresql.log

Deepen your PostgreSQL expertise:

Functions, procedures, and triggers let you move business logic into the database, improving data integrity and consistency across all applications.