Create partition table using execute-CodePudding

I would like to create N partition tables for the last N days. I have created a table like the following

create table metrics.my_table (
    id bigserial NOT NULL primary key,
    ...
    logdate date NOT NULL
) PARTITION BY LIST (logdate);

Then I have the following function to create those tables:

CREATE OR REPLACE function metrics.create_my_partitions(init_date numeric default 30, current_date_parameter timestamp default current_date)
returns void as $$
DECLARE
    partition_date TEXT;
    partition_name TEXT;
begin
    for cnt in 0..init_date loop
        partition_date := to_char((current_date_parameter - (cnt * interval '1 day')),'YYYY-MM-DD');
        raise notice 'cnt: %', cnt;
        raise notice 'partition_date: %', partition_date;
        partition_name := 'my_table_' || partition_date;
        raise notice 'partition_name: %', partition_name;
        EXECUTE format('CREATE table if not exists metrics.%I PARTITION OF metrics.my_table for VALUES IN ($1)', partition_name) using partition_date;
    end loop;
END
$$
LANGUAGE plpgsql;

select metrics.create_my_partitions(30, current_date);

But it throws the following error in the EXECUTE format line:

SQL Error [42P02]: ERROR: there is no parameter $1

Any idea on how to create those tables?

CodePudding user response：

The EXECUTE ... USING ... option only works for data values in DML commands (SELECT,INSERT, etc.). Since CREATE TABLE is a DDL command, use a parameter in format():

execute format(
    'create table if not exists metrics.%I partition of metrics.my_table for values in (%L)', 
    partition_name, partition_date);