-- 59_generic_search_function.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: Generic search over customers/products.
-- 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.

-- Generic search function across customers and accounts (simplified)
CREATE OR REPLACE FUNCTION bank.search(q TEXT)
RETURNS TABLE(kind TEXT, id BIGINT, label TEXT) LANGUAGE sql AS $$
  SELECT 'customer', customer_id, full_name FROM bank.customers WHERE full_name ILIKE '%'||q||'%'
  UNION ALL
  SELECT 'account', account_id, iban FROM bank.accounts WHERE iban ILIKE '%'||q||'%';
$$;
