Created
June 6, 2023 23:05
-
-
Save avinayak/a3119ccb8bef423e9f0877428b81d4a0 to your computer and use it in GitHub Desktop.
Automated jsonb audit_log generator for all tables in current schema
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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(); |
Author
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
only for postgres