-- 46_monthly_partition_do_block.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: DO block to generate monthly partitions.
-- 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.
SET search_path = bank, public;

-- Generate monthly partitions for next 12 months
DO $$
DECLARE m int := 0;
DECLARE d1 date;
DECLARE d2 date;
DECLARE pname text;
BEGIN
  WHILE m < 12 LOOP
    d1 := (date_trunc('month', CURRENT_DATE) + (m||' month')::interval)::date;
    d2 := (date_trunc('month', CURRENT_DATE) + ((m+1)||' month')::interval)::date;
    pname := to_char(d1, '"tx_y"YYYY_"m"MM');
    EXECUTE format('CREATE TABLE IF NOT EXISTS bank.%I PARTITION OF bank.tx_partitioned FOR VALUES FROM (%L) TO (%L);', pname, d1, d2);
    m := m + 1;
  END LOOP;
END $$;
