Create a trigger on insert that creates another trigger on a different table in PostgreSQL

Question

I have the following scenario:

I have a table 'a' containing and id and other information. Whenever a new entry is added in table 'a', another table is created named 'a_id'. So for example, if I insert an entry in table 'a' that has the id 1, another table named 'a_1' is automatically created. Table 'a_1' contains an id of it's own, but the only relation with the first table is it's name. I need to insert in a separate table, whenever something is inserted in 'a_1' (or 'a_2', or 'a_3', or...) the id of the new entry in 'a_1' and the original id form table 'a' (in this case 1).

The solution I found for this is to create an insert trigger for table 'a' that creates another insert trigger for any subsequent tables created from it ('a_1', 'a_2', etc). The trigger on those tables would get the id of the new entry and the whatever is in the table name after '_', and would insert those values in a separate table.

Is it possible to do something like this (create a trigger that creates triggers on other tables named dynamically)?

Answer 1

Assuming a basic starting structure like this: (demo for all below)

create table a(
  id int generated by default as identity primary key,
  payload text default gen_random_uuid()::text);

create table a_gen_collector(
  a_id int references a(id) 
           on delete cascade
           on update cascade,
  a_gen_id int); --note this collects data from different a_N tables, so no FK

Dynamic SQL EXECUTE with format() lets you do all that. Named dollar quotes come useful with all the nesting:

create function tgf_a_gen() returns trigger language plpgsql as $f_1$
begin 
  execute format(
    $dynamic_sql_1$ 
     create table %1$I (id int generated by default as identity primary key);
     create function tgf_%1$I_collect() returns trigger language plpgsql as $f_2$
     begin 
        insert into a_gen_collector (a_id,a_gen_id) values (%2$L,NEW.id);
        return null;
     end $f_2$;
     create trigger tg_a_gen_collect after insert on %1$I
     for each row execute function tgf_%1$I_collect();
    $dynamic_sql_1$, 
    concat('a_',NEW.id), 
    NEW.id);
  return null;
end $f_1$;

create trigger tg_a_gen after insert on a
for each row execute function tgf_a_gen();

1. You might need to build out the trigger structure to emulate foreign key cascading behaviour between the collector table and all the a_N's. You could even reverse the link and make the collector centrally responsible for generating all the a_N.id, while the a_N tables only receive it into a foreign key column. That way you can update those on the collector table and the change will cascade to the corresponding a_N.
2. You might want to also handle dropping and renaming the a_N tables in response to updates and deletes from the main a table. Otherwise both of these operations are bound to orphan their corresponding table (a.id will no longer coincide with any a_N table).