Create partition table using execute

Question

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?

You'll have to use format for the value as well, that cannot be a parameter. — Laurenz Albe
– Laurenz Albe, Commented Sep 19, 2022 at 12:02
@LaurenzAlbe and how can I do that? I tried using %I like with the table name, but it also fails: EXECUTE format('CREATE table if not exists metrics.%I PARTITION OF metrics.my_table for VALUES IN (%I)', partition_name, partition_date); SQL Error [0A000]: ERROR: cannot use column reference in partition bound expression — Homer
– Homer, Commented Sep 19, 2022 at 12:06
@Homer you need to format as a literal value for the IN expression, not an identifier — Bergi
– Bergi, Commented Sep 19, 2022 at 12:15
Thank you, it works like the following: EXECUTE format('CREATE table if not exists metrics.%I PARTITION OF metrics.my_table for VALUES IN (''%I'')', partition_name, partition_date); — Homer
– Homer, Commented Sep 19, 2022 at 12:20

klin · Accepted Answer · 2022-09-19 12:45:15Z

2

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);

answered Sep 19, 2022 at 12:45

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create partition table using execute

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related