Skip to content

Instantly share code, notes, and snippets.

@0x777
Last active August 5, 2019 20:25
Show Gist options
  • Save 0x777/706d8c6ff588e37141c08704010e905f to your computer and use it in GitHub Desktop.
Save 0x777/706d8c6ff588e37141c08704010e905f to your computer and use it in GitHub Desktop.
create or replace function add_on_update_trigger
(schema_name text, table_name text, column_name text)
returns void AS $body$
declare
target_table text =
quote_ident(schema_name) || '.' || quote_ident(table_name);
trig_name text =
quote_ident(
'update_' || schema_name || '_' || table_name || '_' || column_name
);
_sql text;
begin
-- drop previous trigger/function with the same name
EXECUTE 'DROP TRIGGER IF EXISTS ' || trig_name ||
' ON ' || target_table;
EXECUTE 'DROP FUNCTION IF EXISTS ' || trig_name || '()';
-- create the function for the trigger
_sql =
'CREATE FUNCTION ' || trig_name ||
'() RETURNS TRIGGER AS $$ BEGIN NEW.' || quote_ident(column_name) ||
' = now(); RETURN NEW; END; $$ language plpgsql;';
RAISE NOTICE '%',_sql;
EXECUTE _sql;
-- now add the trigger on the table
_sql =
'CREATE TRIGGER ' || trig_name ||
' BEFORE UPDATE ON ' || target_table ||
' FOR EACH ROW EXECUTE PROCEDURE ' || trig_name || '();';
RAISE NOTICE '%',_sql;
EXECUTE _sql;
end;
$body$
language plpgsql;
create or replace function drop_on_update_trigger
(schema_name text, table_name text, column_name text)
returns void AS $body$
declare
target_table text =
quote_ident(schema_name) || '.' || quote_ident(table_name);
trig_name text =
quote_ident(
'update_' || schema_name || '_' || table_name || '_' || column_name
);
_sql text;
begin
-- drop previous trigger/function with the same name
EXECUTE 'DROP TRIGGER IF EXISTS ' || trig_name ||
' ON ' || target_table;
EXECUTE 'DROP FUNCTION IF EXISTS ' || trig_name || '()';
end;
$body$
language plpgsql;
-- helper functions for 'public' schema tables
create or replace function add_on_update_trigger
(table_name text, column_name text)
returns void AS $body$
select add_on_update_trigger('public'::text, table_name, column_name)
$body$
language sql;
create or replace function drop_on_update_trigger
(table_name text, column_name text)
returns void AS $body$
select drop_on_update_trigger('public'::text, table_name, column_name)
$body$
language sql;
-- -- Usage:
-- -- Add the above defined functions
-- -- Example:
-- create table note
-- ( id serial primary key
-- , content text not null
-- , created_at timestamptz not null default now()
-- , updated_at timestamptz not null default now()
-- );
-- -- now create the trigger for 'updated_at' column on 'note' table
-- select add_on_update_trigger('note', 'updated_at');
-- insert into note (content) values ('hello');
-- select * from note;
-- update note set content = 'hello' where content = 'hello';
-- select * from note;
-- -- you can drop the created triggers on a table
-- select drop_on_update_trigger('note', 'updated_at');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment