Created
August 1, 2018 07:20
-
-
Save Checksum/5942ad6a38e75d71e0a9c0912ac83601 to your computer and use it in GitHub Desktop.
Automatically create column on new tables using PostgreSQL event triggers
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 '{}'); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment