Re: Automatic partition creation?

[Rui DeSousa <rui@xxxxxxxxxxxxx>]

On Mar 15, 2023, at 1:24 PM, Sbob <sbob@xxxxxxxxxxxxxxxxxxxxx> wrote:

Is it possible to auto create new partitions as needed with declarative partitioning as well?

I’ve created and used the following function to create yearly partitions.  I would just call the function periodical from cron and provide a lead time; i.e. The partition should be there a month before the new year, etc. 

create or replace function dba.add_yearly_partition(_schema name, _table name, _lead_time interval)

  returns boolean

as $body$

declare

  _create_table_template constant text := '

    create table %1$s.%2$s_%3$s

    partition of %1$s.%2$s

    for values from (''%4$s'') to (''%5$s'')

    ;

  ';

  _date timestamp with time zone := now() + _lead_time;

  _is_valid boolean;

  _sql text;

begin

  for _sql in 

    select replace(

        replace(

            pg_get_expr(c.relpartbound, c.oid)

            , 'FOR VALUES FROM'

            ,'select'

          )

        , 'TO'

        , '<= $1 and $1 <'

      )

      || ';'

    from pg_class p

    join pg_namespace ns on ns.oid = p.relnamespace

      and ns.nspname = _schema

    join pg_inherits i on i.inhparent = p.oid

    join pg_class c on c.oid = i.inhrelid

    where p.relname = _table

    order by c.oid desc

  loop

    execute _sql into _is_valid using _date;

    exit when _is_valid;

  end loop;

  if _is_valid is null then

    raise exception 'Failed to identify partitioned table.';

  elsif not _is_valid then

    execute format(_create_table_template

        , _schema

        , _table

        , extract(year from _date)

        , date_trunc('year', _date)

        , date_trunc('year', _date) + '1 year'::interval

      );

  end if;

  return (not _is_valid);

end;

$body$

  language plpgsql

  set search_path = dba

;