Re: Automatic partition creation?

> On Mar 15, 2023, at 1:24 PM, Sbob <sbob(at)quadratum-braccas(dot)com> 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
;