Advanced Stored Procedures, Functions & Triggers
Write production-grade procedures with error handling, audit triggers, and dynamic SQL.
🎯 What You'll Learn
- Procedures with error handling, locking, and transaction control
- Table-returning functions for reusable report logic
- Audit triggers that automatically log every data change
- Validation triggers to enforce business rules at the database level
- Dynamic SQL with injection-safe patterns
🏦 Production-Grade Procedures
Think of a stored procedure like a bank teller's checklist — validate the request, lock the accounts, perform the transfer, log it, and handle anything that goes wrong. This example shows a fund transfer with full error handling and row locking.
Fund Transfer Procedure
Complete procedure with validation, locking, and error handling
-- Advanced stored procedure with error handling
-- PostgreSQL PL/pgSQL
CREATE OR REPLACE FUNCTION transfer_funds(
p_from_account INT,
p_to_account INT,
p_amount DECIMAL(15,2)
)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
v_from_balance DECIMAL(15,2);
v_from_name VARCHAR(100);
v_to_name VARCHAR(100);
BEGIN
-- Validate amount
IF p_amount <= 0 THEN
RAISE EXCEPTION 'Transfer amount must be positive: %', p_amount;
END IF;
-- Lock rows to prevent race
...📊 Table-Returning Functions
Functions that return tables can be used in FROM clauses just like real tables. They're perfect for encapsulating complex report logic that's reused across the application.
💡 Pro Tip — Functions vs Procedures
Use functions when you need to return data (can be used in SELECT/FROM). Use procedures when you need to perform actions with transaction control (COMMIT/ROLLBACK inside the routine).
Table-Returning Function
Create reusable report functions with optional parameters
-- Table-returning function (set-returning function)
CREATE OR REPLACE FUNCTION get_employee_report(
p_department VARCHAR DEFAULT NULL,
p_min_salary DECIMAL DEFAULT 0
)
RETURNS TABLE (
employee_name VARCHAR,
department VARCHAR,
salary DECIMAL,
hire_date DATE,
years_employed INT,
salary_rank INT
)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
SELECT
e.name,
d.department_name,
e.salary,
e.hire_date,
EXTRACT(YEAR FROM AGE(C
...📝 Audit Triggers
An audit trigger automatically records who changed what, when, and what the old/new values were. One generic function can audit every table in your database.
Generic Audit Trigger
Automatically log INSERT, UPDATE, and DELETE on any table
-- Audit trigger: automatically log all changes
CREATE TABLE audit_log (
id SERIAL PRIMARY KEY,
table_name VARCHAR(100),
operation VARCHAR(10),
old_data JSONB,
new_data JSONB,
changed_by VARCHAR(100) DEFAULT current_user,
changed_at TIMESTAMP DEFAULT NOW()
);
-- Generic audit trigger function
CREATE OR REPLACE FUNCTION fn_audit_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO audit_log (table_name, operation, new
...✅ Validation Triggers
BEFORE triggers can validate and modify data before it's written. They enforce business rules that application code might miss — like a bouncer checking IDs at the door.
⚠️ Common Mistake
Forgetting to RETURN NEW in a BEFORE trigger. If you don't return the row, the INSERT/UPDATE is silently cancelled. AFTER triggers return value is ignored.
Validation Trigger
Enforce business rules before data is written
-- Validation trigger: enforce business rules
CREATE OR REPLACE FUNCTION fn_validate_order()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
-- Rule 1: Order total must be positive
IF NEW.total <= 0 THEN
RAISE EXCEPTION 'Order total must be positive, got: %', NEW.total;
END IF;
-- Rule 2: Cannot backdate orders more than 7 days
IF NEW.order_date < CURRENT_DATE - INTERVAL '7 days' THEN
RAISE EXCEPTION 'Cannot backdate order beyond 7 days';
END IF;
-- Rul
...🔧 Dynamic SQL
Dynamic SQL builds queries as strings at runtime. Powerful but dangerous — always validate inputs and use format(%I) for identifiers and parameterised queries for values to prevent SQL injection.
Safe Dynamic SQL
Build flexible queries with injection protection
-- Dynamic SQL for flexible queries
CREATE OR REPLACE FUNCTION search_table(
p_table_name TEXT,
p_column TEXT,
p_search_value TEXT,
p_limit INT DEFAULT 100
)
RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
v_query TEXT;
v_result JSONB;
BEGIN
-- Validate table exists (prevent SQL injection!)
IF NOT EXISTS (
SELECT 1 FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name = p_table_name
) THEN
RAISE EXCEPTION 'T
...📋 Quick Reference
| Feature | When to Use |
|---|---|
| FUNCTION | Return data, usable in SELECT/FROM |
| PROCEDURE | Actions with transaction control |
| BEFORE trigger | Validate/modify data before write |
| AFTER trigger | Audit logging, notifications |
| format(%I) | Safe dynamic identifier injection |
🎉 Lesson Complete!
You can now write production-grade stored procedures, audit triggers, and dynamic SQL. Next, dive into advanced transaction isolation levels!
Sign up for free to track which lessons you've completed and get learning reminders.