Plpgsql_basics

Chapter 26: Introduction to PL/pgSQL

Writing Stored Procedures in PostgreSQL

---

26.1 PL/pgSQL Fundamentals

PL/pgSQL is PostgreSQL’s procedural language, adding procedural capabilities to SQL.

                    PL/pgSQL Overview
    ========================================================================

    Advantages of PL/pgSQL:
    =======================

    ✓ Server-side execution - reduces network traffic
    ✓ Reusable code - stored procedures and functions
    ✓ Complex logic - loops, conditionals, variables
    ✓ Performance - pre-compiled, cached execution plans
    ✓ Security - can access tables directly

    What Can You Build?
    ===================

    • Functions that return scalar values
    • Functions that return tables
    • Stored procedures (PostgreSQL 11+)
    • Triggers
    • Complex business logic
    • Data validation and transformation
    ========================================================================

---

26.2 Basic Function Structure

-- Simple function returning scalar
CREATE OR REPLACE FUNCTION get_user_count()
RETURNS INTEGER AS $$
BEGIN
    RETURN (SELECT COUNT(*) FROM users);
END;
$$ LANGUAGE plpgsql;

-- Calling the function
SELECT get_user_count();

-- Function with parameters
CREATE OR REPLACE FUNCTION get_user_by_id(user_id INTEGER)
RETURNS users AS $$
BEGIN
    RETURN (SELECT * FROM users WHERE id = user_id);
END;
$$ LANGUAGE plpgsql;

-- Function with multiple parameters
CREATE OR REPLACE FUNCTION calculate_total(
    p_subtotal DECIMAL(10,2),
    p_tax_rate DECIMAL(5,2) DEFAULT 0.08
)
RETURNS DECIMAL(10,2) AS $$
DECLARE
    v_total DECIMAL(10,2);
BEGIN
    v_total := p_subtotal * (1 + p_tax_rate);
    RETURN v_total;
END;
$$ LANGUAGE plpgsql;

-- Using the function
SELECT calculate_total(100.00, 0.10);  -- 110.00
SELECT calculate_total(100.00);         -- 108.00 (uses default)

---

26.3 Variable Declaration

CREATE OR REPLACE FUNCTION process_order(p_order_id INTEGER)
RETURNS VOID AS $$
DECLARE
    v_order RECORD;
    v_customer_name VARCHAR(100);
    v_total DECIMAL(10,2);
    v_status VARCHAR(20) := 'pending';
BEGIN
    -- Query into variable
    SELECT * INTO v_order FROM orders WHERE id = p_order_id;

    -- Direct assignment
    v_customer_name := 'John Doe';
    v_total := 100.00;

    -- Using INTO with aggregate
    SELECT SUM(amount) INTO v_total FROM order_items WHERE order_id = p_order_id;

    RAISE NOTICE 'Processing order % for customer % - Total: %',
        p_order_id, v_customer_name, v_total;
END;
$$ LANGUAGE plpgsql;

---

26.4 Control Structures

-- IF-THEN-ELSE
CREATE OR REPLACE FUNCTION get_discount(p_amount DECIMAL(10,2))
RETURNS DECIMAL(5,2) AS $$
DECLARE
    v_discount DECIMAL(5,2) := 0;
BEGIN
    IF p_amount > 1000 THEN
        v_discount := 0.20;
    ELSIF p_amount > 500 THEN
        v_discount := 0.10;
    ELSIF p_amount > 100 THEN
        v_discount := 0.05;
    ELSE
        v_discount := 0;
    END IF;

    RETURN v_discount;
END;
$$ LANGUAGE plpgsql;

-- CASE statement
CREATE OR REPLACE FUNCTION get_status_text(p_status VARCHAR)
RETURNS VARCHAR AS $$
DECLARE
    v_text VARCHAR(100);
BEGIN
    CASE p_status
        WHEN 'pending' THEN
            v_text := 'Order is pending processing';
        WHEN 'completed' THEN
            v_text := 'Order has been completed';
        WHEN 'cancelled' THEN
            v_text := 'Order was cancelled';
        ELSE
            v_text := 'Unknown status';
    END CASE;

    RETURN v_text;
END;
$$ LANGUAGE plpgsql;

---

26.5 Loops

-- Simple FOR loop (integer range)
CREATE OR REPLACE FUNCTION generate_series()
RETURNS TABLE(n INTEGER) AS $$
BEGIN
    FOR n IN 1..10 LOOP
        RETURN NEXT;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM generate_series();

-- FOR loop over query results
CREATE OR REPLACE FUNCTION process_all_users()
RETURNS VOID AS $$
DECLARE
    v_user RECORD;
    v_count INTEGER := 0;
BEGIN
    FOR v_user IN SELECT * FROM users WHERE is_active = TRUE LOOP
        v_count := v_count + 1;
        RAISE NOTICE 'Processing user: %', v_user.username;
    END LOOP;

    RAISE NOTICE 'Processed % users', v_count;
END;
$$ LANGUAGE plpgsql;

-- WHILE loop
CREATE OR REPLACE FUNCTION factorial(n INTEGER)
RETURNS BIGINT AS $$
DECLARE
    v_result BIGINT := 1;
BEGIN
    WHILE n > 1 LOOP
        v_result := v_result * n;
        n := n - 1;
    END LOOP;

    RETURN v_result;
END;
$$ LANGUAGE plpgsql;

---

26.6 Returning Tables

-- Function returning table (SETOF)
CREATE OR REPLACE FUNCTION get_active_users()
RETURNS TABLE(
    user_id INTEGER,
    username VARCHAR(50),
    email VARCHAR(255)
) AS $$
BEGIN
    RETURN QUERY
    SELECT u.id, u.username, u.email
    FROM users u
    WHERE u.is_active = TRUE;
END;
$$ LANGUAGE plpgsql;

-- Using the function
SELECT * FROM get_active_users();

-- Function with parameters
CREATE OR REPLACE FUNCTION get_orders_by_user(
    p_user_id INTEGER,
    p_status VARCHAR DEFAULT NULL
)
RETURNS TABLE(
    order_id INTEGER,
    total_amount DECIMAL(10,2),
    status VARCHAR(20)
) AS $$
BEGIN
    RETURN QUERY
    SELECT o.id, o.total_amount, o.status
    FROM orders o
    WHERE o.user_id = p_user_id
      AND (p_status IS NULL OR o.status = p_status);
END;
$$ LANGUAGE plpgsql;

---

26.7 Error Handling

-- Basic exception handling
CREATE OR REPLACE FUNCTION safe_divide(p_a NUMERIC, p_b NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    IF p_b = 0 THEN
        RETURN NULL;
    END IF;
    RETURN p_a / p_b;
END;
$$ LANGUAGE plpgsql;

-- Exception handling with GET STACKED DIAGNOSTICS
CREATE OR REPLACE FUNCTION insert_user(
    p_username VARCHAR,
    p_email VARCHAR
)
RETURNS INTEGER AS $$
DECLARE
    v_user_id INTEGER;
BEGIN
    INSERT INTO users (username, email)
    VALUES (p_username, p_email)
    RETURNING id INTO v_user_id;

    RETURN v_user_id;

EXCEPTION WHEN unique_violation THEN
    RAISE EXCEPTION 'User with email % already exists', p_email
        USING ERRCODE = '23505';
WHEN OTHERS THEN
    RAISE EXCEPTION 'Error inserting user: %', SQLERRM;
END;
$$ LANGUAGE plpgsql;

-- Handling multiple exceptions
CREATE OR REPLACE FUNCTION process_data()
RETURNS VOID AS $$
BEGIN
    -- Some operation
    NULL;

EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table does not exist';
WHEN duplicate_column THEN
    RAISE NOTICE 'Column already exists';
WHEN OTHERS THEN
    RAISE;
END;
$$ LANGUAGE plpgsql;

---

26.8 Transactions in Functions

-- Function with transaction control
CREATE OR REPLACE FUNCTION transfer_funds(
    p_from_account INTEGER,
    p_to_account INTEGER,
    p_amount DECIMAL(10,2)
)
RETURNS BOOLEAN AS $$
BEGIN
    -- Check balance
    IF (SELECT balance FROM accounts WHERE id = p_from_account) < p_amount THEN
        RAISE EXCEPTION 'Insufficient funds';
    END IF;

    -- Deduct from source
    UPDATE accounts
    SET balance = balance - p_amount
    WHERE id = p_from_account;

    -- Add to destination
    UPDATE accounts
    SET balance = balance + p_amount
    WHERE id = p_to_account;

    RETURN TRUE;

EXCEPTION WHEN OTHERS THEN
    RAISE EXCEPTION 'Transfer failed: %', SQLERRM;
END;
$$ LANGUAGE plpgsql;

-- Savepoints within function
CREATE OR REPLACE FUNCTION complex_operation()
RETURNS VOID AS $$
BEGIN
    INSERT INTO logs (message) VALUES ('Step 1');
    SAVEPOINT sp1;

    -- Something might fail
    BEGIN
        INSERT INTO logs (message) VALUES ('Step 2');
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'Step 2 failed, continuing...';
        ROLLBACK TO SAVEPOINT sp1;
    END;

    INSERT INTO logs (message) VALUES ('Step 3');
END;
$$ LANGUAGE plpgsql;

---

26.9 Best Practices

                    PL/pgSQL Best Practices
    ========================================================================

    1. Use RETURN QUERY Instead of RETURN
       ┌──────────────────────────────────────────────────────────────┐
       │  ✓ RETURN QUERY for set-returning functions               │
       │  ✓ More memory efficient                                    │
       └──────────────────────────────────────────────────────────────┘

    2. Always Handle NULL Values
       ┌──────────────────────────────────────────────────────────────┐
       │  • Use COALESCE for default values                         │
       │  • Use IS NULL / IS NOT NULL for checks                    │
       └──────────────────────────────────────────────────────────────┘

    3. Use EXCEPTION for Error Handling
       ┌──────────────────────────────────────────────────────────────┐
       │  • Catch expected errors                                   │
       │  • Provide meaningful error messages                       │
       │  • Use proper error codes                                  │
       └──────────────────────────────────────────────────────────────┘

    4. Avoid Dynamic SQL When Possible
       ┌──────────────────────────────────────────────────────────────┐
       │  • Use EXECUTE only when necessary                         │
       │  • Dynamic SQL bypasses some optimizations                 │
       └──────────────────────────────────────────────────────────────┘

    5. Use Proper Data Types
       ┌──────────────────────────────────────────────────────────────┐
       │  • Use DECIMAL for money                                   │
       │  • Use appropriate integer types                           │
       │  • Consider BIGINT for large counts                        │
       └──────────────────────────────────────────────────────────────┘
    ========================================================================

---

Next Chapter

Chapter 27: Creating Functions

---

Last Updated: February 2026