Skip to content

Instantly share code, notes, and snippets.

@cristianp6
Last active August 9, 2022 04:28
Show Gist options
  • Save cristianp6/29ce1c942448e95c2f95 to your computer and use it in GitHub Desktop.
Save cristianp6/29ce1c942448e95c2f95 to your computer and use it in GitHub Desktop.
Log row changes in PostgreSQL - Any advice/improvement is welcome ;-)
/**
* Creates a "logging" schema with an "history" table where are stored records in JSON format
*
* Requires PostgreSQL >= 9.3 since data is stored in JSON format
*
* Credits: http://www.cybertec.at/2013/12/tracking-changes-in-postgresql/
*/
/* Create a schema dedicated to logs */
CREATE SCHEMA logging;
/* Create the table in which to store logs */
CREATE TABLE logging.history (
id serial,
time timestamptz DEFAULT CURRENT_TIMESTAMP,
schema_name text,
table_name text,
user_name text DEFAULT current_user,
operation text,
new_row json,
old_row json
);
/* Create the function */
CREATE OR REPLACE FUNCTION logging.change_trigger() RETURNS trigger AS $$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO logging.history (table_name, schema_name, operation, new_row)
VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW));
RETURN NEW;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO logging.history (table_name, schema_name, operation, new_row, old_row)
VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW), row_to_json(OLD));
RETURN NEW;
ELSIF (TG_OP = 'DELETE') THEN
INSERT INTO logging.history (table_name, schema_name, operation, old_row)
VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD));
RETURN OLD;
END IF;
RETURN NULL;
END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;
/* Create the triggers */
/* Single statement: change <schema_name> and <table_name> according to your needs */
/*
DROP TRIGGER IF EXISTS table_change_trigger ON <schema_name>.<table_name>;
CREATE TRIGGER table_change_trigger
BEFORE INSERT OR UPDATE OR DELETE ON <schema_name>.<table_name>
FOR EACH ROW EXECUTE PROCEDURE logging.change_trigger();
*/
/* Selects all tables in "public" schema and outputs the statements you need to execute for create the triggers */
SELECT 'DROP TRIGGER IF EXISTS table_change_trigger ON ' || schemaname || '.' || tablename || ';
CREATE TRIGGER table_change_trigger
BEFORE INSERT OR UPDATE OR DELETE ON ' || schemaname || '.' || tablename || '
FOR EACH ROW EXECUTE PROCEDURE logging.change_trigger();
' AS trigger_statements_to_execute
FROM pg_tables
WHERE schemaname = 'public';
@yanphysics
Copy link

Thanks! Is it possible to use the logging table to revert the table back to a pervious state?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment