Stored Procedure with Transaction Logic

CREATE OR REPLACE PROCEDURE transfer_funds(
    p_from_account INTEGER,
    p_to_account INTEGER,
    p_amount NUMERIC(10, 2),
    p_description VARCHAR(200) DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_balance NUMERIC(10, 2);
    v_from_name VARCHAR(100);
    v_to_name VARCHAR(100);
BEGIN
    -- Validate inputs
    IF p_amount <= 0 THEN
        RAISE EXCEPTION 'Transfer amount must be positive';
    END IF;

    IF p_from_account = p_to_account THEN
        RAISE EXCEPTION 'Source and destination accounts must differ';
    END IF;

    -- Check balance with row lock
    SELECT balance, account_name INTO v_balance, v_from_name
    FROM accounts
    WHERE account_id = p_from_account
    FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Source account % not found', p_from_account;
    END IF;

    IF v_balance < p_amount THEN
        RAISE EXCEPTION 'Insufficient funds: balance %, requested %',
            v_balance, p_amount;
    END IF;

    SELECT account_name INTO v_to_name
    FROM accounts
    WHERE account_id = p_to_account
    FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Destination account % not found', p_to_account;
    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;

    -- Log transaction
    INSERT INTO transfer_log (
        from_account, to_account, amount,
        description, transferred_at
    ) VALUES (
        p_from_account, p_to_account, p_amount,
        COALESCE(p_description, 'Transfer'),
        CURRENT_TIMESTAMP
    );

    RAISE NOTICE 'Transferred % from % to %',
        p_amount, v_from_name, v_to_name;
END;
$$;