-- 43_stored_procedure_transfer.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: Stored procedure to transfer funds.
-- Context: Retail bank example with customers, accounts, transactions, cards, loans.
-- Date: 2025-08-18
-- Notes:
--   * Safe to read; run on a development DB.
--   * Requires: CREATE privileges; some files may need superuser for extensions.
--   * Search path set to 'bank' schema for clarity.

-- Stored procedure: transfer funds (assumes sufficient balance, demo only)
CREATE OR REPLACE PROCEDURE bank.transfer(p_from BIGINT, p_to BIGINT, p_amount NUMERIC)
LANGUAGE plpgsql AS $$
BEGIN
  -- Lock rows
  PERFORM 1 FROM bank.accounts WHERE account_id = p_from FOR UPDATE;
  PERFORM 1 FROM bank.accounts WHERE account_id = p_to FOR UPDATE;
  UPDATE bank.accounts SET balance = balance - p_amount WHERE account_id = p_from;
  UPDATE bank.accounts SET balance = balance + p_amount WHERE account_id = p_to;
END $$;

-- CALL bank.transfer(1,2,100.00);
