Re: Automatic partition creation?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]


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$
  _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;
  for _sql in 
    select replace(
            pg_get_expr(c.relpartbound, c.oid)
            , 'FOR VALUES FROM'
        , '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
    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);
  language plpgsql
  set search_path = dba

[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux