PL/pgSQL Fundamentals — Functions, Variables, and Control Flow
PL/pgSQL Fundamentals — Functions, Variables, and Control Flow
PL/pgSQL is PostgreSQL's procedural extension to SQL. It adds variables, loops, conditionals, exception handling, and the ability to execute dynamic SQL — everything needed for logic that pure SQL cannot express.
When to use PL/pgSQL
- Reusable logic: a calculation used in 20 different queries, centralised as a function
- Multi-step transactions: operations that need to query, compute, then write back, atomically
- Trigger logic: automatically maintaining denormalised fields or audit logs
- Dynamic SQL: queries where table or column names are not known until runtime
The golden rule: push computation to the database only when it reduces network round trips or enables set-based operations. Don't rewrite application logic in PL/pgSQL just because you can.
Example 1: Basic function structure
-- A simple function that returns the current collection rate for a client
CREATE OR REPLACE FUNCTION client_collection_rate(p_client_id INT)
RETURNS NUMERIC AS $$
DECLARE
v_total_billed NUMERIC := 0;
v_total_collected NUMERIC := 0;
v_rate NUMERIC;
BEGIN
-- Calculate total billed
SELECT COALESCE(SUM(ii.quantity * ii.unit_price), 0)
INTO v_total_billed
FROM invoices i
JOIN invoice_items ii ON ii.invoice_id = i.invoice_id
WHERE i.client_id = p_client_id;
-- Calculate total collected
SELECT COALESCE(SUM(p.amount), 0)
INTO v_total_collected
FROM invoices i
JOIN payments p ON p.invoice_id = i.invoice_id
WHERE i.client_id = p_client_id;
-- Compute rate
IF v_total_billed = 0 THEN
RETURN NULL;
END IF;
v_rate := ROUND(100.0 * v_total_collected / v_total_billed, 1);
RETURN v_rate;
END;
$$ LANGUAGE plpgsql STABLE;
-- Usage:
SELECT company_name, client_collection_rate(client_id) AS collection_rate_pct
FROM clients ORDER BY collection_rate_pct NULLS LAST;Example 2: Control flow — IF/ELSIF, CASE, LOOP
-- Function to categorise a customer based on their order history
CREATE OR REPLACE FUNCTION classify_customer(p_customer_id INT)
RETURNS TEXT AS $$
DECLARE
v_order_count INT;
v_last_order DATE;
v_days_since INT;
BEGIN
SELECT COUNT(*) AS v_order_count, MAX(created_at::DATE) AS v_last_order
INTO v_order_count, v_last_order
FROM orders
WHERE customer_id = p_customer_id AND status = 'completed';
-- Handle customers with no orders
IF v_order_count IS NULL OR v_order_count = 0 THEN
RETURN 'Never Purchased';
END IF;
v_days_since := CURRENT_DATE - v_last_order;
-- Multi-condition classification (using order count as frequency threshold)
IF v_order_count >= 10 AND v_days_since <= 90 THEN
RETURN 'Champion';
ELSIF v_order_count >= 5 AND v_days_since <= 180 THEN
RETURN 'Loyal Customer';
ELSIF v_order_count >= 3 AND v_days_since <= 90 THEN
RETURN 'Potential Loyalist';
ELSIF v_days_since <= 30 THEN
RETURN 'Recent Customer';
ELSIF v_days_since <= 365 THEN
RETURN 'At Risk';
ELSE
RETURN 'Lost';
END IF;
END;
$$ LANGUAGE plpgsql STABLE;
-- Usage:
SELECT customer_id, email, classify_customer(customer_id) AS segment
FROM customers ORDER BY customer_id LIMIT 20;Example 3: Functions returning sets (SETOF and TABLE)
-- Function returning a table of invoice summaries for a date range
CREATE OR REPLACE FUNCTION invoice_summary(
p_start_date DATE,
p_end_date DATE
)
RETURNS TABLE (
industry TEXT,
invoice_count BIGINT,
total_billed NUMERIC,
total_collected NUMERIC,
collection_rate NUMERIC
) AS $$
BEGIN
RETURN QUERY
SELECT
c.industry,
COUNT(DISTINCT i.invoice_id),
ROUND(SUM(ii.quantity * ii.unit_price), 2),
ROUND(SUM(ii.quantity * ii.unit_price)
FILTER (WHERE i.status = 'paid'), 2),
ROUND(100.0 * SUM(ii.quantity * ii.unit_price) FILTER (WHERE i.status = 'paid')
/ NULLIF(SUM(ii.quantity * ii.unit_price), 0), 1)
FROM clients c
JOIN invoices i ON i.client_id = c.client_id
JOIN invoice_items ii ON ii.invoice_id = i.invoice_id
WHERE i.issue_date BETWEEN p_start_date AND p_end_date
GROUP BY c.industry
ORDER BY total_billed DESC NULLS LAST;
END;
$$ LANGUAGE plpgsql STABLE;
-- Usage — call like a table:
SELECT * FROM invoice_summary('2024-01-01', '2024-12-31');
SELECT * FROM invoice_summary('2025-01-01', CURRENT_DATE);
-- Can be used in JOINs and CTEs:
WITH q1 AS (SELECT * FROM invoice_summary('2025-01-01', '2025-03-31')),
q2 AS (SELECT * FROM invoice_summary('2024-01-01', '2024-03-31'))
SELECT q1.industry, q1.total_billed AS q1_billed, q2.total_billed AS q1_last_year,
ROUND(100.0 * (q1.total_billed - q2.total_billed) / NULLIF(q2.total_billed, 0), 1) AS yoy_growth
FROM q1 JOIN q2 ON q1.industry = q2.industry;Example 4: Exception handling
-- Robust data processing function with error handling
CREATE OR REPLACE FUNCTION safe_process_payment(
p_invoice_id INT,
p_amount NUMERIC,
p_method TEXT
)
RETURNS JSONB AS $$
DECLARE
v_invoice_total NUMERIC;
v_already_paid NUMERIC;
v_balance NUMERIC;
v_payment_id INT;
BEGIN
-- Validate invoice exists and get total
SELECT COALESCE(SUM(quantity * unit_price), 0)
INTO v_invoice_total
FROM invoice_items WHERE invoice_id = p_invoice_id;
IF v_invoice_total = 0 THEN
RETURN jsonb_build_object('success', false, 'error', 'Invoice not found or has no items');
END IF;
-- Check how much is already paid
SELECT COALESCE(SUM(amount), 0) INTO v_already_paid
FROM payments WHERE invoice_id = p_invoice_id;
v_balance := v_invoice_total - v_already_paid;
IF p_amount > v_balance * 1.01 THEN -- 1% tolerance
RETURN jsonb_build_object(
'success', false,
'error', FORMAT('Payment of %s exceeds balance of %s', p_amount, v_balance)
);
END IF;
-- Insert the payment
INSERT INTO payments (invoice_id, payment_date, amount, method)
VALUES (p_invoice_id, CURRENT_DATE, p_amount, p_method)
RETURNING payment_id INTO v_payment_id;
-- Update invoice status if fully paid
IF p_amount >= v_balance * 0.99 THEN
UPDATE invoices SET status = 'paid' WHERE invoice_id = p_invoice_id;
END IF;
RETURN jsonb_build_object(
'success', true,
'payment_id', v_payment_id,
'amount', p_amount,
'remaining_balance', GREATEST(0, v_balance - p_amount)
);
EXCEPTION
WHEN OTHERS THEN
RETURN jsonb_build_object(
'success', false,
'error', SQLERRM,
'sqlstate', SQLSTATE
);
END;
$$ LANGUAGE plpgsql;Key Takeaway
PL/pgSQL follows a clear structure: DECLARE variables, BEGIN logic, EXCEPTION handlers. Use SELECT ... INTO to capture query results into variables. RETURN QUERY in table-returning functions streams results directly. Exception handlers with SQLERRM and SQLSTATE produce structured error responses safe to return to application callers.