-- 20_partition_maintenance_template.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: Template to create next month 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;

-- Template to create next month's partition for tx_partitioned
DO $$
DECLARE next_month date := (date_trunc('month', CURRENT_DATE) + INTERVAL '1 month')::date;
DECLARE month_after date := (date_trunc('month', CURRENT_DATE) + INTERVAL '2 month')::date;
DECLARE pname text := to_char(next_month, '"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, next_month, month_after);
END $$;
