-- 18_partitioning_range_tx_date.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: Range partitioning transactions by date (monthly).
-- 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.

-- Transactions partitioned monthly by created_at
CREATE TABLE IF NOT EXISTS bank.tx_partitioned (
  tx_id BIGINT GENERATED ALWAYS AS IDENTITY,
  account_id BIGINT NOT NULL,
  amount NUMERIC(18,2) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (tx_id, created_at)
) PARTITION BY RANGE (created_at);

-- Example partition for current month
DO $$
DECLARE d1 date := date_trunc('month', CURRENT_DATE)::date;
DECLARE d2 date := (d1 + INTERVAL '1 month')::date;
DECLARE pname text := to_char(d1, '"tx_y"YYYY_"m"MM');
BEGIN
  EXECUTE format('CREATE TABLE IF NOT EXISTS bank.%I PARTITION OF bank.tx_partitioned FOR VALUES FROM (%L) TO (%L);', pname, d1, d2);
END $$;
