Automated jsonb audit_log generator for all tables in current schema

audit_log.sql

CREATE TABLE audit_log (
    id serial PRIMARY KEY,
    object_id uuid NOT NULL,
    object_type VARCHAR(50),
    event VARCHAR(50),
    delta JSONB,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION jsonb_delta(jsonb1 jsonb, jsonb2 jsonb)
RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE
  result jsonb := '{}';
  key text;
BEGIN
  FOR key IN SELECT jsonb_object_keys(jsonb1)
  LOOP
    IF (jsonb1 -> key) IS DISTINCT FROM (jsonb2 -> key) THEN
      result := jsonb_set(result, ARRAY[key], jsonb1 -> key);
    END IF;
  END LOOP;
  RETURN result;
END;
$$;

CREATE OR REPLACE FUNCTION audit_log_func() RETURNS TRIGGER AS $audit_log_func$
DECLARE
    changes JSONB;
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        changes := jsonb_delta(to_jsonb(NEW), to_jsonb(OLD));
        INSERT INTO audit_log (object_id, object_type, event, delta)
        VALUES (NEW.id, TG_TABLE_NAME, 'update', changes);
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        changes := to_jsonb(NEW);
        INSERT INTO audit_log (object_id, object_type, event, delta)
        VALUES (NEW.id, TG_TABLE_NAME, 'insert', changes);
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        changes := to_jsonb(OLD);
        INSERT INTO audit_log (object_id, object_type, event, delta)
        VALUES (OLD.id, TG_TABLE_NAME, 'delete', changes);
        RETURN OLD;
    END IF;
    RETURN NULL; 
    
$audit_log_func$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION add_audit_trigger() RETURNS void AS $$
DECLARE
    table_name text;
BEGIN
    FOR table_name IN 
        SELECT tablename FROM pg_tables WHERE schemaname = 'public' and tablename != 'audit_log'
    LOOP
        EXECUTE format('CREATE TRIGGER audit_log_trigger
                        AFTER INSERT OR UPDATE ON %I
                        FOR EACH ROW EXECUTE FUNCTION audit_log_func();', table_name);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT add_audit_trigger();

only for postgres