Postgresql: Partitioning a table for values in SELECT

Question

I want to use PostgreSQL's declarative table partitioning on a column with UUID values. I want it partitioned by values that are returned by a SELECT.

CREATE TABLE foo (
    id uuid NOT NULL,
    type_id uuid NOT NULL,
    -- other columns
    PRIMARY KEY (id, type_id)
) PARTITION BY LIST (type_id);

CREATE TABLE foo_1 PARTITION OF foo
    FOR VALUES IN (SELECT id FROM type_ids WHERE type_name = 'type1');
CREATE TABLE foo_2 PARTITION OF foo
    FOR VALUES IN (SELECT id FROM type_ids WHERE type_name = 'type2');

I don't want to use specific UUIDs in FOR VALUES IN ('uuid'), since the UUIDs may differ by environment (dev, qa, prod). However, the SELECT syntax doesn't seem to be accepted by Postgres. Any suggestions?

Answer 1

I just wanted the SELECT to be evaluated at the table creation time

You should have made that clear in the question, not in a comment.

In that case - if this is a one time thing, you can use dynamic SQL, e.g. wrapped into a procedure

create procedure create_partition(p_part_name text, p_type_name text)
as
$$
declare
  l_sql text;
  l_id uuid;
begin
  select id
    into l_id
  from type_ids
  where type_name = p_type_name;

  l_sql := format('CREATE TABLE %I PARTITION OF foo FOR VALUES IN (%L)', p_part_name, l_id);
  execute l_sql;
end;
$$
language plpgsql;

Then you can do:

call create_partition('foo_1', 'type1');
call create_partition('foo_2', 'type2');