How to reuse an update trigger for multiple tables in postgresql?

Question

In the database for a new project, I've started the convention of having each table have a last modified timestamp column. To implement this, I wrote a trigger for each table:

CREATE TRIGGER touch_users
  BEFORE UPDATE
  ON users
  FOR EACH ROW
  WHEN (OLD.modification_time IS NOT DISTINCT FROM NEW.modification_time)
  EXECUTE PROCEDURE touch_modification_time();

CREATE TRIGGER touch_company
  BEFORE UPDATE
  ON company
  FOR EACH ROW
  WHEN (OLD.modification_time IS NOT DISTINCT FROM NEW.modification_time)
  EXECUTE PROCEDURE touch_modification_time();

-- etc for each table

This gets tedious fairly quickly; especially since they're exactly the same, except for the table name.

Is there a way I can reduce the boilerplate required for each table? Or possibly even eliminate it altogether with a single trigger that acts on every table by default?

Answer 1

Single trigger function for multiple triggers: possible - that's what you do.
Single trigger for multiple tables: not possible.

But you can shorten the code for creating lots of triggers:

DO
$$
BEGIN

EXECUTE (
SELECT string_agg('CREATE TRIGGER touch_users
   BEFORE UPDATE ON ' || quote_ident(t) || '
   FOR EACH ROW
   WHEN (OLD.modification_time IS NOT DISTINCT FROM NEW.modification_time)
   EXECUTE PROCEDURE touch_modification_time();'
 , E'\n')
FROM unnest('{users, company, foo, bar}'::text[]) t -- list your tables here
);

END
$$;

Or gather table names from system catalogs:
Tables accessed during last period