Find all tables that have created_at and updated_at columns, and create trigger to update timestamps automatically.

pg_ts_triggers.sql

create or replace function set_timestamps()
    returns trigger
as
$$
begin
    NEW.created_at = (
        case
            when TG_OP = 'INSERT' then
                NOW()
            else
                OLD.created_at
            end);
    NEW.updated_at = (
        case
            when TG_OP = 'UPDATE'
                and OLD.updated_at >= NOW() then
                OLD.updated_at + interval '1 millisecond'
            else
                NOW()
            end);
    return NEW;
end;
$$
    language plpgsql
    volatile;

do $ts_triggers$
    declare
        ts_tables record;
        trig_name text;
    begin
        for ts_tables in
            select t.table_name,
                   t.table_schema
            from information_schema.tables t
            where t.table_schema = 'app_public'
              and exists(
                    select 1
                    from information_schema.columns c
                    where c.table_name = t.table_name
                      and c.column_name in ('created_at', 'updated_at'))
            loop
                trig_name = '_100_ts_' || ts_tables.table_schema || '_' || ts_tables.table_name;
                raise notice E'\n\n';
                raise notice 'drop trigger if exists ''%'' on ''%''', trig_name, ts_tables;
                execute 'drop trigger if exists "' || trig_name || '" on ' || quote_ident(ts_tables.table_schema) ||
                        '.' || ts_tables.table_name;
                raise notice 'creating trigger ''%'' for ''%'' table', trig_name, ts_tables;
                execute 'create trigger _100_ts_' || ts_tables.table_schema || '_' || ts_tables.table_name ||
                        ' before insert or update on ' || ts_tables.table_name ||
                        ' for each row execute procedure set_timestamps();';
                execute 'comment on column ' || quote_ident(ts_tables.table_schema) || '.' ||
                        quote_ident(ts_tables.table_name) || '.created_at is ' || quote_literal('@omit create,update');
                execute 'comment on column ' || quote_ident(ts_tables.table_schema) || '.' ||
                        quote_ident(ts_tables.table_name) || '.updated_at is ' || quote_literal('@omit create,update');
            end loop;
    end;
    $ts_triggers$;