Postgres trigger for all table changes (inserts, updates)

trigger.sql

CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    schema_name TEXT NOT NULL,
    table_name TEXT NOT NULL,
    operation CHAR(1) NOT NULL,
    new_data JSONB,
    old_data JSONB,
    changed_at TIMESTAMP NOT NULL,
    changed_by TEXT
);

-- create function definition
CREATE OR REPLACE FUNCTION audit_log_function() RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        INSERT INTO audit_log (schema_name, table_name, operation, new_data, old_data, changed_at, changed_by)
        VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, 'U', to_jsonb(NEW), to_jsonb(OLD), current_timestamp, current_user);
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO audit_log (schema_name, table_name, operation, new_data, changed_at, changed_by)
        VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, 'I', to_jsonb(NEW), current_timestamp, current_user);
        RETURN NEW;
    ELSE
        RAISE EXCEPTION 'This trigger should only be used for UPDATE and INSERT operations.';
    END IF;
END;
$$ LANGUAGE plpgsql;

-- create trigger for all tables
DO $$
DECLARE
   table_name TEXT;
BEGIN
   FOR table_name IN (SELECT t.table_name FROM information_schema.tables t WHERE t.table_schema = 'public' AND t.table_type = 'BASE TABLE' AND t.table_name != 'audit_log')
   LOOP
      EXECUTE format('CREATE TRIGGER audit_log_trigger
                      AFTER INSERT OR UPDATE ON %I
                      FOR EACH ROW
                      EXECUTE FUNCTION audit_log_function();', table_name);
   END LOOP;
END $$;

If stack size becomes an issue:

use a custom postgresql.conf

max_stack_depth = 7MB			# min 100kB

	docker run --name talenox-pg -d -p 5432:5432 \
		-v "$$PWD/postgresql.conf":/etc/postgresql/postgresql.conf \
		-e POSTGRES_USER=U -e POSTGRES_PASSWORD=P \
		postgres -c 'config_file=/etc/postgresql/postgresql.conf'

select * from audit_log where table_name NOT IN ('versions','version_associations') order by changed_at DESC

select count(1) as c, table_name from audit_log group by table_name order by c desc, table_name

delete from audit_log

select * from audit_log where table_name ILIKE '%sg_month_total_employee%'