rQ2H3XQ5

1create or replace function add_on_update_trigger
2       (schema_name text, table_name text, column_name text)
3       returns void AS $body$
4declare
5  target_table text =
6    quote_ident(schema_name) || '.' || quote_ident(table_name);
7  trig_name text =
8    quote_ident(
9      'update_' || schema_name || '_' || table_name || '_' || column_name
10    );
11  _sql text;
12begin
13  -- drop previous trigger/function with the same name
14  EXECUTE 'DROP TRIGGER IF EXISTS ' || trig_name ||
15          ' ON ' || target_table;
16  EXECUTE 'DROP FUNCTION IF EXISTS ' || trig_name || '()';
17
18  -- create the function for the trigger
19  _sql =
20    'CREATE FUNCTION ' || trig_name ||
21    '() RETURNS TRIGGER AS $$ BEGIN NEW.' || quote_ident(column_name) ||
22    ' = now(); RETURN NEW; END; $$ language plpgsql;';
23  RAISE NOTICE '%',_sql;
24  EXECUTE _sql;
25
26  -- now add the trigger on the table
27  _sql =
28    'CREATE TRIGGER ' || trig_name ||
29    ' BEFORE UPDATE ON ' || target_table ||
30    ' FOR EACH ROW EXECUTE PROCEDURE ' || trig_name || '();';
31  RAISE NOTICE '%',_sql;
32  EXECUTE _sql;
33end;
34$body$
35language plpgsql;
36
37create or replace function drop_on_update_trigger
38(schema_name text, table_name text, column_name text)
39returns void AS $body$
40declare
41  target_table text =
42    quote_ident(schema_name) || '.' || quote_ident(table_name);
43  trig_name text =
44    quote_ident(
45      'update_' || schema_name || '_' || table_name || '_' || column_name
46    );
47  _sql text;
48begin
49  -- drop previous trigger/function with the same name
50  EXECUTE 'DROP TRIGGER IF EXISTS ' || trig_name ||
51          ' ON ' || target_table;
52  EXECUTE 'DROP FUNCTION IF EXISTS ' || trig_name || '()';
53end;
54$body$
55language plpgsql;
56
57-- helper functions for 'public' schema tables
58create or replace function add_on_update_trigger
59(table_name text, column_name text)
60returns void AS $body$
61select add_on_update_trigger('public'::text, table_name, column_name)
62$body$
63language sql;
64
65create or replace function drop_on_update_trigger
66(table_name text, column_name text)
67returns void AS $body$
68select drop_on_update_trigger('public'::text, table_name, column_name)
69$body$
70language sql;
71
72-- -- Usage:
73-- -- Add the above defined functions
74-- -- Example:
75-- create table note
76--   ( id serial primary key
77--   , content text not null
78--   , created_at timestamptz not null default now()
79--   , updated_at timestamptz not null default now()
80--   );
81
82-- -- now create the trigger for 'updated_at' column on 'note' table
83-- select add_on_update_trigger('note', 'updated_at');
84
85-- insert into note (content) values ('hello');
86-- select * from note;
87
88-- update note set content = 'hello' where content = 'hello';
89-- select * from note;
90
91-- -- you can drop the created triggers on a table
92-- select drop_on_update_trigger('note', 'updated_at');