⚡ Stored Procedures & Functions

Microsoft & Amazon Interview Deep Dive

🏢 Microsoft🏢 Amazon⚡ Difficulty: Medium-Hard⏱️ 40 min

📋 Interview Question

ℹ️🔴 Microsoft/Amazon Interview Question

"Create a stored procedure to transfer money between accounts with: 1) Input validation, 2) Proper error handling, 3) Transaction logging, 4) Deadlock retry logic. Also create a trigger to audit all balance changes."

Companies: Microsoft, Amazon | Difficulty: Medium-Hard | Time: 40 minutes

📊 Setup: Banking Schema

CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    account_number VARCHAR(20) UNIQUE,
    holder_name VARCHAR(100),
    balance DECIMAL(15, 2) CHECK (balance >= 0),
    currency VARCHAR(3) DEFAULT 'USD',
    is_frozen BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE transaction_log (
    log_id BIGSERIAL PRIMARY KEY,
    transaction_id UUID,
    from_account VARCHAR(20),
    to_account VARCHAR(20),
    amount DECIMAL(15, 2),
    currency VARCHAR(3),
    status VARCHAR(20),
    error_message TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE audit_log (
    audit_id BIGSERIAL PRIMARY KEY,
    table_name VARCHAR(100),
    record_id INT,
    action VARCHAR(10),
    old_values JSONB,
    new_values JSONB,
    changed_by VARCHAR(100),
    changed_at TIMESTAMP DEFAULT NOW()
);

-- Insert sample data
INSERT INTO accounts (account_number, holder_name, balance) VALUES
('ACC001', 'Alice Johnson', 10000.00),
('ACC002', 'Bob Smith', 5000.00),
('ACC003', 'Charlie Brown', 8000.00),
('ACC004', 'Diana Ross', 12000.00),
('ACC005', 'Eve Davis', 3000.00);

📝 Part 1: Basic Stored Procedures

ℹ️🔍 Stored Procedure vs Function

Stored Procedure: Performs actions, can have output parameters, called with CALL
Function: Returns a value, can be used in SQL expressions, must return a value

Simple Transfer Procedure

-- Basic money transfer procedure
CREATE OR REPLACE PROCEDURE transfer_money(
    p_from_account VARCHAR,
    p_to_account VARCHAR,
    p_amount DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Input validation
    IF p_amount <= 0 THEN
        RAISE EXCEPTION 'Transfer amount must be positive';
    END IF;

    IF p_from_account = p_to_account THEN
        RAISE EXCEPTION 'Cannot transfer to same account';
    END IF;

    -- Check sufficient funds
    IF (SELECT balance FROM accounts WHERE account_number = p_from_account) < p_amount THEN
        RAISE EXCEPTION 'Insufficient funds';
    END IF;

    -- Perform transfer
    UPDATE accounts
    SET balance = balance - p_amount,
        updated_at = NOW()
    WHERE account_number = p_from_account;

    UPDATE accounts
    SET balance = balance + p_amount,
        updated_at = NOW()
    WHERE account_number = p_to_account;

    -- Log transaction
    INSERT INTO transaction_log (from_account, to_account, amount, status)
    VALUES (p_from_account, p_to_account, p_amount, 'completed');

    RAISE NOTICE 'Transfer of % from % to % completed', p_amount, p_from_account, p_to_account;
END;
$$;

-- Call the procedure
CALL transfer_money('ACC001', 'ACC002', 500.00);

Procedure with Transaction ID

-- Enhanced procedure with transaction tracking
CREATE OR REPLACE PROCEDURE transfer_with_tracking(
    p_from_account VARCHAR,
    p_to_account VARCHAR,
    p_amount DECIMAL,
    p_currency VARCHAR DEFAULT 'USD'
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_transaction_id UUID;
    v_from_balance DECIMAL;
BEGIN
    -- Generate unique transaction ID
    v_transaction_id := gen_random_uuid();

    -- Input validation
    IF p_amount <= 0 THEN
        RAISE EXCEPTION 'Amount must be positive';
    END IF;

    -- Check from account exists and has funds
    SELECT balance INTO v_from_balance
    FROM accounts
    WHERE account_number = p_from_account
    FOR UPDATE;  -- Lock the row

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

    IF v_from_balance < p_amount THEN
        RAISE EXCEPTION 'Insufficient funds: has %, needs %', v_from_balance, p_amount;
    END IF;

    -- Check to account exists
    IF NOT EXISTS (SELECT 1 FROM accounts WHERE account_number = p_to_account) THEN
        RAISE EXCEPTION 'Destination account % not found', p_to_account;
    END IF;

    -- Check accounts are not frozen
    IF EXISTS (SELECT 1 FROM accounts WHERE account_number = p_from_account AND is_frozen) THEN
        RAISE EXCEPTION 'Source account is frozen';
    END IF;

    IF EXISTS (SELECT 1 FROM accounts WHERE account_number = p_to_account AND is_frozen) THEN
        RAISE EXCEPTION 'Destination account is frozen';
    END IF;

    -- Perform transfer atomically
    UPDATE accounts
    SET balance = balance - p_amount,
        updated_at = NOW()
    WHERE account_number = p_from_account;

    UPDATE accounts
    SET balance = balance + p_amount,
        updated_at = NOW()
    WHERE account_number = p_to_account;

    -- Log successful transaction
    INSERT INTO transaction_log (transaction_id, from_account, to_account, amount, currency, status)
    VALUES (v_transaction_id, p_from_account, p_to_account, p_amount, p_currency, 'completed');

    RAISE NOTICE 'Transaction % completed: % transferred from % to %',
        v_transaction_id, p_amount, p_from_account, p_to_account;
END;
$$;

🔁 Part 2: Error Handling with EXCEPTION

-- Procedure with comprehensive error handling
CREATE OR REPLACE PROCEDURE safe_transfer(
    p_from_account VARCHAR,
    p_to_account VARCHAR,
    p_amount DECIMAL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_transaction_id UUID := gen_random_uuid();
    v_from_balance DECIMAL;
BEGIN
    -- Log attempt
    INSERT INTO transaction_log (transaction_id, from_account, to_account, amount, status)
    VALUES (v_transaction_id, p_from_account, p_to_account, p_amount, 'pending');

    -- Validation
    IF p_amount <= 0 THEN
        UPDATE transaction_log SET status = 'failed', error_message = 'Invalid amount'
        WHERE transaction_id = v_transaction_id;
        RAISE EXCEPTION 'Invalid amount: %', p_amount;
    END IF;

    -- Check funds
    SELECT balance INTO v_from_balance
    FROM accounts WHERE account_number = p_from_account;

    IF NOT FOUND THEN
        UPDATE transaction_log SET status = 'failed', error_message = 'Account not found'
        WHERE transaction_id = v_transaction_id;
        RAISE EXCEPTION 'Account % not found', p_from_account;
    END IF;

    IF v_from_balance < p_amount THEN
        UPDATE transaction_log SET status = 'failed', error_message = 'Insufficient funds'
        WHERE transaction_id = v_transaction_id;
        RAISE EXCEPTION 'Insufficient funds';
    END IF;

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

    -- Update log
    UPDATE transaction_log SET status = 'completed' WHERE transaction_id = v_transaction_id;

EXCEPTION
    WHEN OTHERS THEN
        -- Log error
        UPDATE transaction_log
        SET status = 'failed',
            error_message = SQLERRM
        WHERE transaction_id = v_transaction_id;

        -- Re-raise with context
        RAISE EXCEPTION 'Transfer failed: %', SQLERRM;
END;
$$;

🔄 Part 3: Retry Logic for Deadlocks

-- Procedure with deadlock retry logic
CREATE OR REPLACE PROCEDURE transfer_with_retry(
    p_from_account VARCHAR,
    p_to_account VARCHAR,
    p_amount DECIMAL,
    p_max_retries INT DEFAULT 3
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_retry_count INT := 0;
    v_success BOOLEAN := false;
BEGIN
    WHILE v_retry_count < p_max_retries AND NOT v_success LOOP
        BEGIN
            -- Attempt transfer
            CALL safe_transfer(p_from_account, p_to_account, p_amount);
            v_success := true;

        EXCEPTION
            WHEN deadlock_detected THEN
                v_retry_count := v_retry_count + 1;
                RAISE NOTICE 'Deadlock detected, retry attempt % of %', v_retry_count, p_max_retries;

                -- Wait before retry (exponential backoff)
                PERFORM pg_sleep(0.1 * POWER(2, v_retry_count - 1));

            WHEN OTHERS THEN
                RAISE EXCEPTION 'Transfer failed after % retries: %', v_retry_count, SQLERRM;
        END;
    END LOOP;

    IF NOT v_success THEN
        RAISE EXCEPTION 'Transfer failed after % retry attempts', p_max_retries;
    END IF;
END;
$$;

🎯 Part 4: Functions

Scalar Function

-- Function to calculate account balance with interest
CREATE OR REPLACE FUNCTION calculate_balance_with_interest(
    p_account_number VARCHAR,
    p_interest_rate DECIMAL DEFAULT 0.05
)
RETURNS DECIMAL
LANGUAGE plpgsql
AS $$
DECLARE
    v_balance DECIMAL;
    v_interest DECIMAL;
BEGIN
    SELECT balance INTO v_balance
    FROM accounts
    WHERE account_number = p_account_number;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Account not found';
    END IF;

    v_interest := v_balance * p_interest_rate;
    RETURN v_balance + v_interest;
END;
$$;

-- Use in queries
SELECT
    account_number,
    holder_name,
    balance,
    calculate_balance_with_interest(account_number, 0.05) AS balance_with_5pct_interest
FROM accounts;

Table-Returning Function

-- Function to get transaction history
CREATE OR REPLACE FUNCTION get_transaction_history(
    p_account_number VARCHAR,
    p_days_back INT DEFAULT 30
)
RETURNS TABLE (
    transaction_id UUID,
    counterparty VARCHAR,
    transaction_type VARCHAR,
    amount DECIMAL,
    transaction_date TIMESTAMP
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        tl.transaction_id,
        CASE
            WHEN tl.from_account = p_account_number THEN tl.to_account
            ELSE tl.from_account
        END AS counterparty,
        CASE
            WHEN tl.from_account = p_account_number THEN 'debit'
            ELSE 'credit'
        END AS transaction_type,
        CASE
            WHEN tl.from_account = p_account_number THEN -tl.amount
            ELSE tl.amount
        END AS amount,
        tl.created_at AS transaction_date
    FROM transaction_log tl
    WHERE (tl.from_account = p_account_number OR tl.to_account = p_account_number)
    AND tl.created_at >= NOW() - (p_days_back || ' days')::INTERVAL
    ORDER BY tl.created_at DESC;
END;
$$;

-- Use function
SELECT * FROM get_transaction_history('ACC001', 30);

🔔 Part 5: Triggers

Audit Trigger

-- Trigger function for auditing balance changes
CREATE OR REPLACE FUNCTION audit_balance_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD.balance IS DISTINCT FROM NEW.balance THEN
        INSERT INTO audit_log (table_name, record_id, action, old_values, new_values, changed_by)
        VALUES (
            'accounts',
            NEW.account_id,
            'UPDATE',
            jsonb_build_object(
                'balance', OLD.balance,
                'holder_name', OLD.holder_name,
                'is_frozen', OLD.is_frozen
            ),
            jsonb_build_object(
                'balance', NEW.balance,
                'holder_name', NEW.holder_name,
                'is_frozen', NEW.is_frozen
            ),
            current_user
        );
    END IF;
    RETURN NEW;
END;
$$;

-- Create trigger
CREATE TRIGGER trg_audit_balance_change
AFTER UPDATE ON accounts
FOR EACH ROW
EXECUTE FUNCTION audit_balance_change();

Validation Trigger

-- Trigger to prevent certain operations
CREATE OR REPLACE FUNCTION validate_account_operation()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- Prevent balance going negative
    IF NEW.balance < 0 THEN
        RAISE EXCEPTION 'Balance cannot be negative: %', NEW.balance;
    END IF;

    -- Log large transfers
    IF ABS(NEW.balance - OLD.balance) > 10000 THEN
        INSERT INTO audit_log (table_name, record_id, action, new_values)
        VALUES (
            'accounts',
            NEW.account_id,
            'LARGE_CHANGE',
            jsonb_build_object(
                'old_balance', OLD.balance,
                'new_balance', NEW.balance,
                'change_amount', NEW.balance - OLD.balance
            )
        );
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_validate_account
BEFORE UPDATE ON accounts
FOR EACH ROW
EXECUTE FUNCTION validate_account_operation();

Event Trigger

-- Trigger that fires on DDL events
CREATE OR REPLACE FUNCTION log_schema_changes()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO audit_log (table_name, action, new_values)
    VALUES (
        'schema',
        TG_EVENT,
        jsonb_build_object(
            'command_tag', TG_TAG,
            'user', current_user,
            'timestamp', NOW()
        )
    );
END;
$$;

CREATE EVENT TRIGGER trg_log_ddl
ON ddl_command_end
EXECUTE FUNCTION log_schema_changes();

📊 Part 6: Cursors

⚠️⚠️ Cursor Warning

Cursors are generally slower than set-based operations. Use them only when row-by-row processing is absolutely necessary (e.g., calling external APIs).

-- Cursor for batch processing
CREATE OR REPLACE PROCEDURE apply_interest_to_all_accounts()
LANGUAGE plpgsql
AS $$
DECLARE
    account_cursor CURSOR FOR
        SELECT account_number, balance
        FROM accounts
        WHERE balance > 0;
    account_record RECORD;
    v_new_balance DECIMAL;
    v_count INT := 0;
BEGIN
    -- Open cursor
    OPEN account_cursor;

    -- Loop through records
    LOOP
        FETCH account_cursor INTO account_record;
        EXIT WHEN NOT FOUND;

        -- Process each account
        v_new_balance := account_record.balance * 1.05;  -- 5% interest

        UPDATE accounts
        SET balance = v_new_balance,
            updated_at = NOW()
        WHERE account_number = account_record.account_number;

        v_count := v_count + 1;

        -- Log progress every 100 accounts
        IF v_count % 100 = 0 THEN
            RAISE NOTICE 'Processed % accounts', v_count;
        END IF;
    END LOOP;

    -- Close cursor
    CLOSE account_cursor;

    RAISE NOTICE 'Interest applied to % accounts', v_count;
END;
$$;

-- Implicit cursor for bulk operations
CREATE OR REPLACE PROCEDURE freeze_inactive_accounts()
LANGUAGE plpgsql
AS $$
DECLARE
    v_count INT;
BEGIN
    UPDATE accounts
    SET is_frozen = true
    WHERE last_login < NOW() - INTERVAL '1 year'
    AND is_frozen = false;

    GET DIAGNOSTICS v_count = ROW_COUNT;
    RAISE NOTICE 'Froze % inactive accounts', v_count;
END;
$$;

🎯 Quiz Section

🏆 Best Practices for Interviews

💡✅ Stored Procedure Best Practices

1. Always Handle Exceptions:

BEGIN
    -- Your logic here
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Error: %', SQLERRM;
        -- Log error, cleanup, or re-raise
END;

2. Use Named Exceptions When Possible:

EXCEPTION
    WHEN deadlock_detected THEN
        -- Handle deadlock specifically
    WHEN unique_violation THEN
        -- Handle duplicate key
    WHEN OTHERS THEN
        -- Catch-all for other errors

3. Keep Procedures Focused:

-- BAD: One procedure doing everything
CREATE PROCEDURE do_all_things()...

-- GOOD: Small, focused procedures
CREATE PROCEDURE validate_transfer()...
CREATE PROCEDURE execute_transfer()...
CREATE PROCEDURE log_transfer()...

4. Document Parameters:

-- Add comments for clarity
CREATE PROCEDURE transfer_money(
    p_from_account VARCHAR,  -- Source account number
    p_to_account VARCHAR,    -- Destination account number
    p_amount DECIMAL         -- Transfer amount (must be positive)
)

5. Test Edge Cases:

-- Test with NULL values
-- Test with zero amounts
-- Test with non-existent accounts
-- Test with frozen accounts
-- Test concurrent access

⚠️⚠️ Security Considerations

SQL Injection: Use parameterized queries, never concatenate user input
Permissions: Grant minimal necessary permissions
SECRET/ENCRYPTED: Don't store secrets in procedures
SECURITY DEFINER vs INVOKER: Understand the security implications

Topic: Stored Procedures and Functions for FAANG Interviews