Skip to content

Instantly share code, notes, and snippets.

@ad
Created May 8, 2021 18:44
Show Gist options
  • Save ad/24b606793aa73e8fd5bf1fe4a89c9507 to your computer and use it in GitHub Desktop.
Save ad/24b606793aa73e8fd5bf1fe4a89c9507 to your computer and use it in GitHub Desktop.
CREATE OR REPLACE FUNCTION change_trigger() RETURNS trigger AS $$
BEGIN
IF TG_OP = 'INSERT'
THEN
INSERT INTO history (tabname, schemaname, operation, new_val, item_id)
VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW), NEW.id);
RETURN NEW;
ELSIF TG_OP = 'UPDATE'
THEN
INSERT INTO history (tabname, schemaname, operation, new_val, old_val, item_id)
VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW), row_to_json(OLD), NEW.id);
RETURN NEW;
ELSIF TG_OP = 'DELETE'
THEN
INSERT INTO history (tabname, schemaname, operation, old_val, item_id)
VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD), OLD.id);
RETURN OLD;
END IF;
END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;
CREATE TABLE history (
id serial,
tstamp timestamp DEFAULT now(),
schemaname text,
tabname text,
operation text,
who text DEFAULT current_user,
new_val json,
old_val json,
item_id int8
);
CREATE TRIGGER tablename_trigger BEFORE INSERT OR UPDATE OR DELETE ON tablename FOR EACH ROW EXECUTE PROCEDURE change_trigger();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment