use variable in postgresql's stored procedure to for adding a partition

Question

i need to add a new partition in stored procedure, and a varialbe need to be used, the code is as follows:

create or replace procedure modifyPartition()
as
declare 
 nextDate date;
begin
 nextDate :=date_trunc('hour',sysdate);
 alter table TB_MOVIL_CDR add partition ***** values less than (nextDate);
end;
/

but i get the following error:

ERROR: column "nextDate" does not exists, what's the reason ? can we use varialbe in "alter table" command ?

Answer 1

You need dynamic SQL for that.

But there is a lot more that is wrong with your procedure.

There is no sysdate in Postgres, you have to use current_date and there is no need to remove "hours" from it, because a date in Postgres does not contain any time part.

alter table is not the way to create a new partition in Postgres. As documented in the manual you need to create a table that is a partition.

values less than is also not valid to define a (range) partition in Postgres. A range partitioned table needs a start and end date for the partition values. So you need to calculate two dates, not just one. Assuming you want to create a partition for the current month, you could do something like this:

create or replace procedure modify_partition()
as
$$
declare 
  l_partname text;
  l_part_start date; 
  l_part_end date;
  l_sql text;
begin
  l_part_start = date_trunc('month', current_date)::date;
  l_part_end = (date_trunc('month', current_date) + interval '1 month')::date;
  l_partname := 'movil_cdr_part_'||to_char(current_date, 'yyyy_mm');

  l_sql := format('CREATE TABLE %I PARTITION OF tb_movil_cdr 
    FOR VALUES FROM (%L) TO (%L)', l_partname, l_part_start, l_part_end);

  execute l_sql;
end;
$$
language plpgsql;

format() is the preferred way to create SQL for dynamic SQL. The placeholder %I will properly deal with identifiers. And %L will properly deal with literals.