Automatically create column on new tables using PostgreSQL event triggers

postgresql-event-trigger.sql

-- Function to automatically create a user_id column for new tables with name starting with user_
create or replace function create_user_id_column()
returns event_trigger
language plpgsql volatile as
$$
declare
    obj record;
    identity text[];
begin
    for obj in select * from pg_event_trigger_ddl_commands()
    loop
        if obj.object_type = 'table' then
            -- schema, table := identity[1], identity[2]
            identity := string_to_array(obj.object_identity, '.');
            -- if create table and table name begins with "user_", automatically create user_id column
            if obj.command_tag = 'CREATE TABLE' and identity[2] like 'user_%' then
                raise notice '[create_user_id_column] automatically creating user_id column for table %', identity[2];
                execute format('alter table %s add column if not exists user_id bigint not null references users(id) on delete cascade', obj.object_identity);
            end if;
        end if;
    end loop;
end
$$;

-- Create an event trigger with the function
--
-- The table name is unfortunately available only AFTER the DDL command
-- has executed for PSQL functions. However, it is available on ddl_command_start
-- if writing a C extention
create event trigger create_user_id_column
on ddl_command_end
when tag in('CREATE TABLE')
execute procedure create_user_id_column();

-- test
create table user_meta (content jsonb default '{}');