updated_at_trigger.sql

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');