Created
February 16, 2021 10:37
-
-
Save sabine/0ff8e19f92795e3a369bf6fb6abca7d9 to your computer and use it in GitHub Desktop.
Using triggers to keep a revision history for rows in table in PostgreSQL
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
CREATE TABLE projects ( | |
id BIGINT DEFAULT pseudo_encrypt(nextval('projects_id_seq')), | |
created_by BIGINT REFERENCES profiles(user_id) NOT NULL, | |
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), | |
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NULL, | |
deleted BOOLEAN NOT NULL DEFAULT FALSE, | |
slug VARCHAR(100) DEFAULT NULL, | |
featured_image_id BIGINT REFERENCES images(id), | |
name VARCHAR NOT NULL, | |
notes VARCHAR NOT NULL, | |
); | |
CREATE TABLE project_revisions ( | |
revision_created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), | |
like projects including all, | |
PRIMARY KEY (id, revision_created_at) | |
); | |
ALTER TABLE projects ADD CONSTRAINT projects_primary_key PRIMARY KEY(id); | |
ALTER TABLE projects ADD CONSTRAINT projects_unique_slug UNIQUE (slug); | |
create or replace function trigger_on_project_revision() | |
returns trigger | |
language plpgsql as $body$ | |
begin | |
if old is distinct from new then | |
insert into project_revisions SELECT NOW(), old.*; | |
new.updated_at = NOW(); | |
else | |
-- if the record is unchanged, we won't save it | |
new = NULL; | |
end if; | |
-- Return the `NEW` record so that update can carry on as usual | |
return new; | |
end; $body$; | |
create trigger trigger_project_revision | |
before update | |
on projects | |
for each row | |
execute procedure trigger_on_project_revision(); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment