Re: Automatic partition creation? - Mailing list pgsql-admin

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