All;
In the past I have setup inheritance based partition using something
like the function snippet below, so the creation of new partitions is
automatic
Is it possible to auto create new partitions as needed with declarative
partitioning as well?
BEGIN CODE SNIPPET
customer_name := NEW.customer_name;
year := date_part('year', NEW.updated_at);
month := date_part('month', NEW.updated_at);
day := date_part('day', NEW.updated_at);
current__date := date_trunc('day', NEW.updated_at);
next__date := date_trunc('day', NEW.updated_at) + interval '1 day';
namespace := 'partitions_cst_ymd';
suffix := 'c_' || customer_name || '_' || year || '_' || month ||
'_' || day;
clean_table_name := 'process_states_' || suffix;
table_name := namespace || '.' || clean_table_name;
IF NOT EXISTS (SELECT relname FROM pg_class WHERE relname =
clean_table_name)
THEN
index_name_on_pkey := 'idx_' || suffix || '_on_pkey';
index_name_on_process_native_id := 'idx_' || suffix ||
'_on_process_native_id';
EXECUTE format('CREATE TABLE IF NOT EXISTS %s (
CHECK (
customer_name = ''%s'' AND
updated_at >= DATE ''%s'' AND
updated_at < DATE ''%s'')
) INHERITS (public.process_states)', table_name,
customer_name, current__date, next__date);
EXECUTE format('CREATE INDEX %s ON %s %s', index_name_on_pkey,
table_name, '(customer_name, process_native_id)');
EXECUTE format('CREATE INDEX %s ON %s %s',
index_name_on_process_native_id, table_name, '(process_native_id)');
END IF;
EXECUTE format('INSERT INTO %s SELECT $1.*', table_name) USING NEW;
set client_min_messages to NOTICE;
RETURN NULL;
END CODE SNIPPET
