Control_structures

Chapter 29: Control Structures

Logic Flow in PL/pgSQL

---

29.1 IF/THEN/ELSIF/ELSE

Conditional logic is fundamental to programming. PL/pgSQL provides several conditional structures.

                    IF Statement Variations
    ========================================================================

    ┌─────────────────────────────────────────────────────────────────────┐
    │                      IF Statement Types                              │
    │                                                                      │
    │   Simple IF:                                                         │
    │   ───────────                                                        │
    │   IF condition THEN                                                  │
    │       statements;                                                    │
    │   END IF;                                                            │
    │                                                                      │
    │   IF-THEN-ELSE:                                                     │
    │   ─────────────────                                                  │
    │   IF condition THEN                                                  │
    │       statements_if_true;                                           │
    │   ELSE                                                               │
    │       statements_if_false;                                          │
    │   END IF;                                                            │
    │                                                                      │
    │   IF-THEN-ELSIF-ELSE:                                               │
    │   ─────────────────────                                              │
    │   IF condition1 THEN                                                 │
    │       statements1;                                                  │
    │   ELSIF condition2 THEN                                              │
    │       statements2;                                                  │
    │   ELSE                                                               │
    │       statements3;                                                  │
    │   END IF;                                                            │
    │                                                                      │
    └─────────────────────────────────────────────────────────────────────┘

IF Examples

-- Simple IF
CREATE OR REPLACE FUNCTION check_age(p_age INTEGER)
RETURNS TEXT AS $$
BEGIN
    IF p_age >= 18 THEN
        RETURN 'Adult';
    END IF;

    RETURN 'Minor';
END;
$$ LANGUAGE plpgsql;

-- IF-THEN-ELSE
CREATE OR REPLACE FUNCTION get_discount(p_amount DECIMAL)
RETURNS DECIMAL AS $$
DECLARE
    v_discount DECIMAL;
BEGIN
    IF p_amount > 1000 THEN
        v_discount := 0.15;
    ELSE
        v_discount := 0.05;
    END IF;

    RETURN p_amount * v_discount;
END;
$$ LANGUAGE plpgsql;

-- IF-THEN-ELSIF-ELSE
CREATE OR REPLACE FUNCTION get_shipping_cost(p_weight DECIMAL, p_distance INTEGER)
RETURNS DECIMAL AS $$
DECLARE
    v_cost DECIMAL;
BEGIN
    IF p_weight <= 1 THEN
        v_cost := 5.00;
    ELSIF p_weight <= 5 THEN
        v_cost := 10.00;
    ELSIF p_weight <= 20 THEN
        v_cost := 20.00;
    ELSE
        v_cost := 50.00;
    END IF;

    -- Add distance surcharge
    IF p_distance > 500 THEN
        v_cost := v_cost + 10.00;
    END IF;

    RETURN v_cost;
END;
$$ LANGUAGE plpgsql;

---

29.2 CASE Statements

CASE provides another way to handle multiple conditions.

-- Simple CASE
CREATE OR REPLACE FUNCTION get_day_type(p_day INTEGER)
RETURNS TEXT AS $$
DECLARE
    v_day_name TEXT;
BEGIN
    v_day_name := CASE p_day
        WHEN 1 THEN 'Sunday'
        WHEN 2 THEN 'Monday'
        WHEN 3 THEN 'Tuesday'
        WHEN 4 THEN 'Wednesday'
        WHEN 5 THEN 'Thursday'
        WHEN 6 THEN 'Friday'
        WHEN 7 THEN 'Saturday'
        ELSE 'Invalid'
    END;

    RETURN v_day_name;
END;
$$ LANGUAGE plpgsql;

-- Searched CASE
CREATE OR REPLACE FUNCTION calculate_rating(p_score INTEGER)
RETURNS TEXT AS $$
BEGIN
    RETURN CASE
        WHEN p_score >= 90 THEN 'Excellent'
        WHEN p_score >= 80 THEN 'Very Good'
        WHEN p_score >= 70 THEN 'Good'
        WHEN p_score >= 60 THEN 'Fair'
        WHEN p_score >= 50 THEN 'Pass'
        ELSE 'Fail'
    END;
END;
$$ LANGUAGE plpgsql;

-- CASE in SQL expressions
SELECT
    product_name,
    price,
    CASE
        WHEN price < 10 THEN 'Budget'
        WHEN price < 50 THEN 'Economy'
        WHEN price < 100 THEN 'Standard'
        ELSE 'Premium'
    END as price_category
FROM products;

---

29.3 LOOP Statements

Loops allow repeated execution of statement blocks.

-- Simple LOOP (with EXIT)
CREATE OR REPLACE FUNCTION find_first_match(p_target INTEGER)
RETURNS INTEGER AS $$
DECLARE
    v_result INTEGER;
    v_counter INTEGER := 0;
BEGIN
    LOOP
        v_counter := v_counter + 1;

        IF v_counter = p_target THEN
            v_result := v_counter;
            EXIT;
        END IF;

        -- Safety limit
        IF v_counter > 1000 THEN
            RAISE EXCEPTION 'Target not found';
        END IF;
    END LOOP;

    RETURN v_result;
END;
$$ LANGUAGE plpgsql;

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

    RETURN v_result;
END;
$$ LANGUAGE plpgsql;

-- FOR loop (integer range)
CREATE OR REPLACE FUNCTION sum_to_n(p_n INTEGER)
RETURNS INTEGER AS $$
DECLARE
    v_total INTEGER := 0;
BEGIN
    FOR i IN 1..p_n LOOP
        v_total := v_total + i;
    END LOOP;

    RETURN v_total;
END;
$$ LANGUAGE plpgsql;

-- FOR loop (reverse)
CREATE OR REPLACE FUNCTION sum_n_to_m(p_start INTEGER, p_end INTEGER)
RETURNS INTEGER AS $$
DECLARE
    v_total INTEGER := 0;
BEGIN
    FOR i IN REVERSE p_end..p_start LOOP
        v_total := v_total + i;
    END LOOP;

    RETURN v_total;
END;
$$ LANGUAGE plpgsql;

---

29.4 FOR Loops with Queries

The most powerful loop type - iterates over query results.

-- FOR loop over query results
CREATE OR REPLACE FUNCTION process_all_users()
RETURNS INTEGER AS $$
DECLARE
    v_user RECORD;
    v_count INTEGER := 0;
BEGIN
    FOR v_user IN SELECT user_id, email FROM users WHERE status = 'active' LOOP
        -- Process each user
        RAISE NOTICE 'Processing user: % (%)', v_user.user_id, v_user.email;
        v_count := v_count + 1;
    END LOOP;

    RETURN v_count;
END;
$$ LANGUAGE plpgsql;

-- FOR loop with explicit cursor
CREATE OR REPLACE FUNCTION get_user_totals()
RETURNS TABLE(user_id INTEGER, total_orders INTEGER, total_spent DECIMAL) AS $$
BEGIN
    RETURN QUERY
    SELECT
        u.user_id,
        COUNT(o.order_id)::INTEGER,
        COALESCE(SUM(o.total), 0)::DECIMAL
    FROM users u
    LEFT JOIN orders o ON u.user_id = o.user_id
    GROUP BY u.user_id;
END;
$$ LANGUAGE plpgsql;

-- Nested loops
CREATE OR REPLACE FUNCTION generate_combinations()
RETURNS VOID AS $$
DECLARE
    v_result TEXT := '';
BEGIN
    FOR i IN 1..3 LOOP
        FOR j IN 1..3 LOOP
            v_result := v_result || '(' || i || ',' || j || ') ';
        END LOOP;
    END LOOP;

    RAISE NOTICE '%', v_result;
END;
$$ LANGUAGE plpgsql;

---

29.5 EXIT and CONTINUE

Control loop execution with EXIT and CONTINUE.

-- EXIT (like break)
CREATE OR REPLACE FUNCTION find_first_prime_above(p_start INTEGER)
RETURNS INTEGER AS $$
DECLARE
    v_num INTEGER;
    v_is_prime BOOLEAN;
    v_divisor INTEGER;
BEGIN
    v_num := p_start + 1;

    LOOP
        v_is_prime := TRUE;

        FOR v_divisor IN 2..SQRT(v_num)::INTEGER LOOP
            IF v_num % v_divisor = 0 THEN
                v_is_prime := FALSE;
                EXIT;
            END IF;
        END LOOP;

        IF v_is_prime THEN
            RETURN v_num;
        END IF;

        v_num := v_num + 1;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- EXIT WHEN
CREATE OR REPLACE FUNCTION find_value_in_array(p_target INTEGER)
RETURNS BOOLEAN AS $$
DECLARE
    v_found BOOLEAN := FALSE;
BEGIN
    FOR i IN 1..10 LOOP
        EXIT WHEN i = p_target;
    END LOOP;

    RETURN i = p_target;
END;
$$ LANGUAGE plpgsql;

-- CONTINUE (skip iteration)
CREATE OR REPLACE FUNCTION sum_odd_numbers(p_n INTEGER)
RETURNS INTEGER AS $$
DECLARE
    v_total INTEGER := 0;
BEGIN
    FOR i IN 1..p_n LOOP
        IF i % 2 = 0 THEN
            CONTINUE;  -- Skip even numbers
        END IF;
        v_total := v_total + i;
    END LOOP;

    RETURN v_total;
END;
$$ LANGUAGE plpgsql;

---

29.6 RETURN Variations

Multiple ways to return values from functions.

-- RETURN (simple)
CREATE OR REPLACE FUNCTION double_value(p_value INTEGER)
RETURNS INTEGER AS $$
BEGIN
    RETURN p_value * 2;
END;
$$ LANGUAGE plpgsql;

-- RETURN NEXT (for sets)
CREATE OR REPLACE FUNCTION generate_series_n(p_start INTEGER, p_end INTEGER)
RETURNS SETOF INTEGER AS $$
BEGIN
    FOR i IN p_start..p_end LOOP
        RETURN NEXT i;
    END LOOP;

    RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM generate_series_n(5, 10);

-- RETURN QUERY (query results)
CREATE OR REPLACE FUNCTION get_active_users()
RETURNS SETOF users AS $$
BEGIN
    RETURN QUERY SELECT * FROM users WHERE status = 'active';
END;
$$ LANGUAGE plpgsql;

-- Multiple RETURN NEXT
CREATE OR REPLACE FUNCTION get_user_levels()
RETURNS TABLE(level_name TEXT, min_sales INTEGER) AS $$
BEGIN
    RETURN NEXT ('Bronze', 0);
    RETURN NEXT ('Silver', 1000);
    RETURN NEXT ('Gold', 5000);
    RETURN NEXT ('Platinum', 10000);
    RETURN;
END;
$$ LANGUAGE plpgsql;

---

29.7 Practical Examples

-- Complex business logic with control structures
CREATE OR REPLACE FUNCTION calculate_commission(
    p_sales_amount DECIMAL,
    p_years_experience INTEGER,
    p_is_new_customer BOOLEAN
) RETURNS DECIMAL AS $$
DECLARE
    v_base_rate DECIMAL;
    v_experience_bonus DECIMAL := 0;
    v_customer_bonus DECIMAL := 0;
    v_final_rate DECIMAL;
BEGIN
    -- Determine base rate by amount
    IF p_sales_amount >= 100000 THEN
        v_base_rate := 0.15;
    ELSIF p_sales_amount >= 50000 THEN
        v_base_rate := 0.12;
    ELSIF p_sales_amount >= 10000 THEN
        v_base_rate := 0.10;
    ELSE
        v_base_rate := 0.05;
    END IF;

    -- Add experience bonus
    CASE
        WHEN p_years_experience >= 10 THEN v_experience_bonus := 0.03;
        WHEN p_years_experience >= 5 THEN v_experience_bonus := 0.02;
        WHEN p_years_experience >= 2 THEN v_experience_bonus := 0.01;
    END CASE;

    -- Add new customer bonus
    IF p_is_new_customer THEN
        v_customer_bonus := 0.02;
    END IF;

    -- Calculate final commission
    v_final_rate := v_base_rate + v_experience_bonus + v_customer_bonus;

    -- Cap at 25%
    IF v_final_rate > 0.25 THEN
        v_final_rate := 0.25;
    END IF;

    RETURN p_sales_amount * v_final_rate;
END;
$$ LANGUAGE plpgsql;

---

Summary

Control Structure	Purpose
IF/THEN/ELSIF/ELSE	Conditional execution
CASE	Multiple condition handling
LOOP	Repeated execution
FOR	Iterate over range or query
EXIT/CONTINUE	Loop control
RETURN	Exit function

---

Next: Chapter 30: Cursors