PostgreSQL transactional DDL and to_regclass

Question

Following the suggestion at this question, I'm using the to_regclass function to check if a table exists, creating it if it doesn't. However, it appears that if the table was created in the current transaction, to_regclass still returns null.

Is this behaviour expected? Or is this a bug?

Detail

Here's a short example of where this goes wrong:

begin;
create schema test;
create table test.test ( id serial, category integer );

create or replace function test.test_insert () returns trigger as $$
declare
    child_table_name text;
    table_id     text;
begin
    child_table_name = concat('test.test_', text(new.category));
    table_id = to_regclass(child_table_name::cstring);
    if table_id is null then
        execute format('create table %I ( primary key (id), check ( category = %L ) ) inherits (test.test)', child_table_name, new.category);
    end if;
    execute format ('insert into %I values ($1.*)', child_table_name) using new;
    return null;
end;
$$ language plpgsql;

create trigger test_insert before insert on test.test for each row execute procedure test.test_insert();

insert into test.test (category) values (1);
insert into test.test (category) values (1);
insert into test.test (category) values (1);
commit;

Answer 1

You're using the %I format specifier incorrectly.

If your category is 1, then you end up calling to_regclass('test.test_1'), i.e. checking for the table test_1 in schema test.

However, format('create table %I', 'test.test_1') will treat the format argument as a single identifier and quote it accordingly, evaluating to 'create table "test.test_1"'. This will create a table called "test.test_1" in your default schema (probably public).

Instead, you need to treat your schema and table names as separate identifiers. Define your table name as:

child_table_name = format('test.%I', 'test_' || new.category);

... and when building your SQL strings, just substitute this value directly (i.e. with %s rather than %I).