Stored Procedures and Functions

You are a stored procedure expert. Help me write database procedures.

## Procedure Needs
Purpose: ${{PURPOSE}}
Complexity: ${{COMPLEXITY}}
Performance: ${{PERFORMANCE}}
Database: ${{DATABASE}}

## Please Write:

1. **Basic Function**

-- PostgreSQL function:
CREATE OR REPLACE FUNCTION calculate_order_total(order_id INT)
RETURNS DECIMAL AS $$
DECLARE
  total DECIMAL;
BEGIN
  SELECT SUM(quantity * unit_price)
  INTO total
  FROM order_items
  WHERE order_id = calculate_order_total.order_id;

  RETURN COALESCE(total, 0);
END;
$$ LANGUAGE plpgsql;

-- Usage:
SELECT calculate_order_total(123);


2. **Stored Procedure**

-- Transaction procedure:
CREATE OR REPLACE PROCEDURE transfer_funds(
  from_account INT,
  to_account INT,
  amount DECIMAL
)
LANGUAGE plpgsql AS $$
BEGIN
  -- Debit from source
  UPDATE accounts
  SET balance = balance - amount
  WHERE id = from_account;

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

  -- Credit to destination
  UPDATE accounts
  SET balance = balance + amount
  WHERE id = to_account;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Destination account not found';
  END IF;

  -- Log transaction
  INSERT INTO transactions (from_account, to_account, amount)
  VALUES (from_account, to_account, amount);

  COMMIT;
END;
$$;

-- Usage:
CALL transfer_funds(1, 2, 100.00);


3. **Table-Returning Function**

CREATE OR REPLACE FUNCTION get_user_orders(user_id INT)
RETURNS TABLE (
  order_id INT,
  total DECIMAL,
  status TEXT,
  created_at TIMESTAMP
) AS $$
BEGIN
  RETURN QUERY
  SELECT o.id, o.total, o.status, o.created_at
  FROM orders o
  WHERE o.user_id = get_user_orders.user_id
  ORDER BY o.created_at DESC;
END;
$$ LANGUAGE plpgsql;

-- Usage:
SELECT * FROM get_user_orders(123);


4. **Error Handling**

CREATE OR REPLACE FUNCTION safe_division(a DECIMAL, b DECIMAL)
RETURNS DECIMAL AS $$
BEGIN
  IF b = 0 THEN
    RAISE EXCEPTION 'Division by zero'
      USING ERRCODE = 'division_by_zero';
  END IF;

  RETURN a / b;
EXCEPTION
  WHEN division_by_zero THEN
    RETURN NULL;
  WHEN OTHERS THEN
    RAISE;
END;
$$ LANGUAGE plpgsql;


5. **Cursor Operations**

CREATE OR REPLACE PROCEDURE process_large_batch()
LANGUAGE plpgsql AS $$
DECLARE
  batch_cursor CURSOR FOR
    SELECT id FROM large_table WHERE processed = FALSE;
  rec RECORD;
  batch_count INT := 0;
BEGIN
  OPEN batch_cursor;

  LOOP
    FETCH batch_cursor INTO rec;
    EXIT WHEN NOT FOUND;

    -- Process record
    UPDATE large_table SET processed = TRUE WHERE id = rec.id;

    batch_count := batch_count + 1;
    IF batch_count % 1000 = 0 THEN
      COMMIT;
    END IF;
  END LOOP;

  CLOSE batch_cursor;
END;
$$;


6. **Dynamic SQL**

CREATE OR REPLACE FUNCTION dynamic_query(
  table_name TEXT,
  filter_column TEXT,
  filter_value TEXT
)
RETURNS SETOF RECORD AS $$
BEGIN
  RETURN QUERY EXECUTE format(
    'SELECT * FROM %I WHERE %I = $1',
    table_name,
    filter_column
  ) USING filter_value;
END;
$$ LANGUAGE plpgsql;


7. **Triggers**

-- Trigger function:
CREATE OR REPLACE FUNCTION update_modified_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger:
CREATE TRIGGER update_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_modified_timestamp();


8. **Performance Tips**

-- Use IMMUTABLE/STABLE when possible:
CREATE FUNCTION fast_lookup(key TEXT)
RETURNS TEXT
IMMUTABLE  -- Can be cached
PARALLEL SAFE  -- Can run in parallel
AS $$
  SELECT value FROM lookup_table WHERE lookup_key = key;
$$ LANGUAGE sql;