Created
December 9, 2024 10:13
-
-
Save mxgrn/9900c28c7d4fc021f7c0ac5f8ab782fc to your computer and use it in GitHub Desktop.
Track Postgres table changes in audit_logs
This file contains 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
| -- Step 1: Create the audit logs table | |
| CREATE TABLE audit_logs ( | |
| id SERIAL PRIMARY KEY, | |
| table_name TEXT NOT NULL, | |
| operation TEXT NOT NULL, | |
| json_diff JSONB, | |
| inserted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP | |
| ); | |
| -- Step 2: Create the trigger function | |
| CREATE OR REPLACE FUNCTION audit_trigger() | |
| RETURNS TRIGGER AS $$ | |
| DECLARE | |
| diff JSONB; | |
| BEGIN | |
| IF TG_OP = 'UPDATE' THEN | |
| -- Compute the difference as an array with [old_value, new_value] | |
| diff := ( | |
| SELECT jsonb_object_agg(key, jsonb_build_array(to_jsonb(OLD) -> key, to_jsonb(NEW) -> key)) | |
| FROM ( | |
| SELECT key | |
| FROM jsonb_object_keys(to_jsonb(NEW)) AS keys(key) | |
| WHERE (to_jsonb(NEW) ->> key IS DISTINCT FROM to_jsonb(OLD) ->> key) | |
| ) AS changed_keys | |
| ); | |
| INSERT INTO audit_logs (table_name, operation, json_diff, inserted_at) | |
| VALUES (TG_TABLE_NAME, TG_OP, diff, now()); | |
| ELSIF TG_OP = 'DELETE' THEN | |
| -- Log the entire old row for DELETE | |
| INSERT INTO audit_logs (table_name, operation, json_diff, inserted_at) | |
| VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(OLD), now()); | |
| ELSIF TG_OP = 'INSERT' THEN | |
| -- Log the entire new row for INSERT | |
| INSERT INTO audit_logs (table_name, operation, json_diff, inserted_at) | |
| VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(NEW), now()); | |
| END IF; | |
| RETURN NULL; | |
| END; | |
| $$ LANGUAGE plpgsql; | |
| -- Step 3: Attach the trigger to the target table | |
| -- Replace `my_table` with the name of your target table | |
| CREATE OR REPLACE TRIGGER my_audit_trigger | |
| AFTER INSERT OR UPDATE OR DELETE ON my_table | |
| FOR EACH ROW EXECUTE FUNCTION audit_trigger(); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment