Stored_procedures

Chapter 28: Stored Procedures

Advanced Database Programming

---

28.1 Understanding Stored Procedures

Stored procedures are database objects that contain a collection of SQL statements. In PostgreSQL, procedures (introduced in v11) provide transaction control capabilities that functions cannot.

                    Functions vs Procedures
    ========================================================================

    ┌─────────────────────────────────────────────────────────────────────┐
    │                        Functions                                    │
    │                                                                      │
    │   • Always returns a value                                          │
    │   • Cannot control transactions internally                         │
    │   • Can be used in SELECT, WHERE, etc.                             │
    │   • Called like: SELECT my_func(params)                           │
    │                                                                      │
    └─────────────────────────────────────────────────────────────────────┘

    ┌─────────────────────────────────────────────────────────────────────┐
    │                       Procedures                                     │
    │                                                                      │
    │   • May or may not return values (OUT parameters)                │
    │   • Can COMMIT/ROLLBACK within the procedure                       │
    │   • Called with CALL statement                                     │
    │   • Called like: CALL my_proc(params)                             │
    │                                                                      │
    └─────────────────────────────────────────────────────────────────────┘

Basic Procedure Creation

-- Create a simple procedure (PostgreSQL 11+)
CREATE PROCEDURE create_user(
    p_username VARCHAR,
    p_email VARCHAR
) LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO users (username, email)
    VALUES (p_username, p_email);

    COMMIT;
END;
$$;

-- Call the procedure
CALL create_user('john_doe', 'john@example.com');

-- Procedure with transaction control
CREATE PROCEDURE batch_create_users(
    p_usernames VARCHAR[],
    p_emails VARCHAR[]
) LANGUAGE plpgsql AS $$
DECLARE
    i INTEGER;
BEGIN
    FOR i IN 1..array_length(p_usernames, 1) LOOP
        INSERT INTO users (username, email)
        VALUES (p_usernames[i], p_emails[i]);
    END LOOP;

    COMMIT;
END;
$$;

CALL batch_create_users(
    ARRAY['user1', 'user2', 'user3'],
    ARRAY['user1@example.com', 'user2@example.com', 'user3@example.com']
);

---

28.2 IN, OUT, and INOUT Parameters

-- Procedure with OUT parameter
CREATE PROCEDURE get_user_count(OUT p_count INTEGER)
LANGUAGE plpgsql AS $$
BEGIN
    SELECT COUNT(*) INTO p_count FROM users;
END;
$$;

-- Call with output parameter
DO $$
DECLARE
    v_count INTEGER;
BEGIN
    CALL get_user_count(v_count);
    RAISE NOTICE 'User count: %', v_count;
END;
$$;

-- Procedure with multiple OUT parameters
CREATE PROCEDURE get_user_stats(
    OUT p_total INTEGER,
    OUT p_active INTEGER,
    OUT p_inactive INTEGER
) LANGUAGE plpgsql AS $$
BEGIN
    SELECT COUNT(*),
           COUNT(*) FILTER (WHERE status = 'active'),
           COUNT(*) FILTER (WHERE status = 'inactive')
    INTO p_total, p_active, p_inactive
    FROM users;
END;
$$;

-- Call with multiple outputs
DO $$
DECLARE
    v_total INTEGER;
    v_active INTEGER;
    v_inactive INTEGER;
BEGIN
    CALL get_user_stats(v_total, v_active, v_inactive);
    RAISE NOTICE 'Total: %, Active: %, Inactive: %', v_total, v_active, v_inactive;
END;
$$;

-- INOUT parameter (both input and output)
CREATE PROCEDURE increment_value(INOUT p_value INTEGER)
LANGUAGE plpgsql AS $$
BEGIN
    p_value := p_value + 1;
END;
$$;

DO $$
DECLARE
    v_val INTEGER := 10;
BEGIN
    CALL increment_value(v_val);
    RAISE NOTICE 'Value is now: %', v_val;  -- 11
END;
$$;

---

28.3 Complex Business Logic with Procedures

-- Complete order processing procedure
CREATE PROCEDURE process_order(
    p_order_id INTEGER,
    p_payment_info JSONB
) LANGUAGE plpgsql AS $$
DECLARE
    v_user_id INTEGER;
    v_total DECIMAL(10,2);
    v_inventory_check BOOLEAN;
BEGIN
    -- Start transaction
    BEGIN

    -- Get order details
    SELECT user_id, total INTO v_user_id, v_total
    FROM orders WHERE order_id = p_order_id;

    IF v_user_id IS NULL THEN
        RAISE EXCEPTION 'Order not found: %', p_order_id;
    END IF;

    -- Check inventory
    SELECT EXISTS(
        SELECT 1 FROM order_items oi
        JOIN inventory i ON oi.product_id = i.product_id
        WHERE oi.order_id = p_order_id
        AND i.quantity >= oi.quantity
    ) INTO v_inventory_check;

    IF NOT v_inventory_check THEN
        RAISE EXCEPTION 'Insufficient inventory for order: %', p_order_id;
    END IF;

    -- Reserve inventory
    UPDATE inventory i
    SET quantity = quantity - oi.quantity
    FROM order_items oi
    WHERE oi.order_id = p_order_id
    AND i.product_id = oi.product_id;

    -- Process payment (simulated)
    -- In real scenario, integrate with payment gateway

    -- Update order status
    UPDATE orders
    SET status = 'processing',
        updated_at = NOW()
    WHERE order_id = p_order_id;

    -- Commit transaction
    COMMIT;

    RAISE NOTICE 'Order % processed successfully', p_order_id;

    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE NOTICE 'Order processing failed: %', SQLERRM;
END;
$$;

---

28.4 Bulk Operations with Procedures

-- Bulk update procedure
CREATE PROCEDURE bulk_update_prices(
    p_updates JSONB
) LANGUAGE plpgsql AS $$
DECLARE
    v_product_id INTEGER;
    v_new_price DECIMAL(10,2);
    v_key TEXT;
    v_value JSONB;
BEGIN
    -- p_updates format: [{"product_id": 1, "new_price": 99.99}, ...]

    FOR v_key, v_value IN SELECT * FROM jsonb_each(p_updates) LOOP
        v_product_id := (v_value->>'product_id')::INTEGER;
        v_new_price := (v_value->>'new_price')::DECIMAL;

        UPDATE products
        SET price = v_new_price,
            updated_at = NOW()
        WHERE product_id = v_product_id;
    END LOOP;

    COMMIT;

    RAISE NOTICE 'Updated % products', jsonb_array_length(p_updates);
END;
$$;

CALL bulk_update_prices(
    '[{"product_id": 1, "new_price": 99.99},
      {"product_id": 2, "new_price": 149.99}]'::jsonb
);

-- Bulk delete with archiving
CREATE PROCEDURE archive_and_delete_old_orders(
    p_days_old INTEGER DEFAULT 365
) LANGUAGE plpgsql AS $$
DECLARE
    v_order_ids INTEGER[];
    v_count INTEGER;
BEGIN
    -- Get old order IDs
    SELECT ARRAY_AGG(order_id)
    INTO v_order_ids
    FROM orders
    WHERE created_at < NOW() - (p_days_old || ' days')::INTERVAL
    AND status IN ('completed', 'cancelled');

    v_count := array_length(v_order_ids, 1);

    IF v_count > 0 THEN
        -- Archive order items first
        INSERT INTO order_items_archive
        SELECT oi.* FROM order_items oi
        WHERE oi.order_id = ANY(v_order_ids);

        -- Archive orders
        INSERT INTO orders_archive
        SELECT o.* FROM orders o
        WHERE o.order_id = ANY(v_order_ids);

        -- Delete from main tables
        DELETE FROM order_items WHERE order_id = ANY(v_order_ids);
        DELETE FROM orders WHERE order_id = ANY(v_order_ids);

        COMMIT;

        RAISE NOTICE 'Archived and deleted % orders', v_count;
    ELSE
        RAISE NOTICE 'No orders older than % days found', p_days_old;
    END IF;
END;
$$;

---

28.5 Error Handling in Procedures

-- Procedure with comprehensive error handling
CREATE PROCEDURE safe_transfer(
    p_from_account INTEGER,
    p_to_account INTEGER,
    p_amount DECIMAL
) LANGUAGE plpgsql AS $$
DECLARE
    v_from_balance DECIMAL;
    v_to_balance DECIMAL;
BEGIN
    -- Check if accounts exist
    IF NOT EXISTS(SELECT 1 FROM accounts WHERE account_id = p_from_account) THEN
        RAISE EXCEPTION 'Source account % does not exist', p_from_account;
    END IF;

    IF NOT EXISTS(SELECT 1 FROM accounts WHERE account_id = p_to_account) THEN
        RAISE EXCEPTION 'Destination account % does not exist', p_to_account;
    END IF;

    -- Get current balances with lock
    SELECT balance INTO v_from_balance
    FROM accounts
    WHERE account_id = p_from_account
    FOR UPDATE;

    SELECT balance INTO v_to_balance
    FROM accounts
    WHERE account_id = p_to_account
    FOR UPDATE;

    -- Validate amount
    IF p_amount <= 0 THEN
        RAISE EXCEPTION 'Transfer amount must be positive';
    END IF;

    -- Check sufficient funds
    IF v_from_balance < p_amount THEN
        RAISE EXCEPTION 'Insufficient funds. Balance: %, Required: %',
            v_from_balance, p_amount;
    END IF;

    -- Perform transfer
    UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_from_account;
    UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_to_account;

    COMMIT;

    RAISE NOTICE 'Successfully transferred % from account % to %',
        p_amount, p_from_account, p_to_account;

EXCEPTION
    WHEN OTHERS THEN
        -- Rollback any changes
        ROLLBACK;

        -- Re-raise with context
        RAISE EXCEPTION 'Transfer failed: %. Account IDs: from=%, to=%, amount=%',
            SQLERRM, p_from_account, p_to_account, p_amount;
END;
$$;

---

28.6 Using Procedures for Data Migration

-- Data migration procedure
CREATE PROCEDURE migrate_orders_to_new_schema(
    p_batch_size INTEGER DEFAULT 1000
) LANGUAGE plpgsql AS $$
DECLARE
    v_migrated_count INTEGER := 0;
    v_total_count INTEGER;
    v_batch CURSOR FOR
        SELECT order_id, user_id, total, status, created_at
        FROM orders_legacy
        WHERE migrated = FALSE
        ORDER BY order_id
        LIMIT p_batch_size;

    v_order_row RECORD;
BEGIN
    -- Get total count
    SELECT COUNT(*) INTO v_total_count
    FROM orders_legacy
    WHERE migrated = FALSE;

    RAISE NOTICE 'Starting migration of % orders', v_total_count;

    -- Process in batches
    OPEN v_batch;
    LOOP
        FETCH v_batch INTO v_order_row;
        EXIT WHEN NOT FOUND;

        BEGIN
            -- Insert into new schema
            INSERT INTO orders (order_id, user_id, total, status, created_at)
            VALUES (v_order_row.order_id, v_order_row.user_id,
                    v_order_row.total, v_order_row.status, v_order_row.created_at);

            -- Mark as migrated
            UPDATE orders_legacy
            SET migrated = TRUE, migrated_at = NOW()
            WHERE order_id = v_order_row.order_id;

            v_migrated_count := v_migrated_count + 1;

            -- Commit each row (or batch)
            IF v_migrated_count % 100 = 0 THEN
                COMMIT;
                RAISE NOTICE 'Migrated % orders', v_migrated_count;
            END IF;

        EXCEPTION
            WHEN OTHERS THEN
                -- Log error and continue
                RAISE WARNING 'Failed to migrate order %: %',
                    v_order_row.order_id, SQLERRM;

                UPDATE orders_legacy
                SET migration_error = SQLERRM
                WHERE order_id = v_order_row.order_id;
        END;
    END LOOP;

    CLOSE v_batch;
    COMMIT;

    RAISE NOTICE 'Migration complete. Migrated % out of % orders',
        v_migrated_count, v_total_count;
END;
$$;

---

Summary

Feature	Procedure	Function
Transaction Control	✓ COMMIT/ROLLBACK	✗
Return Value	OUT parameters	✓ RETURN
Use in SQL	Limited	✓ Full
CALL syntax	✓	✗

---

Next: Chapter 29: Control Structures