I am making a simple note app, and I want to create a table to track a note's edit history and save them with an incrementing version as a composite key with the note's id. So for example:
note_id | version | content
----------------------------
1 | 1 | some_text
1 | 2 | some_text
2 | 1 | some_text
2 | 2 | some_text
1 | 3 | some_text
3 | 1 | some_text
1 | 4 | some_text
The composite key is made up of note_id and version I want to increment version for each note_id Is there a way to automate this? I have seen some other posts, and experimented with triggers and functions, but I have not yet been able to get anything to work. Any advice is greatly appreciated!
Cool I actually got this to work, hopefully this can help someone else in the future. Thanks to this answer which I made some slight tweaks too, but mostly it just worked.
--Logic to track and automatically increment a note's version number in the notes_history table
--A table to track the current version of each note
CREATE TABLE notes_version_counter(
note_id UUID PRIMARY KEY REFERENCES users(id),
current_version INTEGER NOT NULL
);
--A function to calculate the next version for a note
CREATE FUNCTION next_version(p_note_id UUID)
RETURNS INTEGER
AS
$$
INSERT INTO notes_version_counter (note_id, current_version)
VALUES (p_note_id, 1)
ON CONFLICT (note_id)
DO UPDATE
SET current_version = notes_version_counter.current_version + 1
RETURNING current_version;
$$
language sql
volatile;
--A function to increment the version of a note
CREATE FUNCTION increment_version()
RETURNS TRIGGER
AS
$$
BEGIN
new."note_version" := next_version(new."note_id");
RETURN new;
END;
$$
language plpgsql;
--A trigger when inserting a note into the history table to call the increment_version function
CREATE TRIGGER TR_notes_history_increment_version
BEFORE INSERT ON notes_history
FOR EACH ROW
EXECUTE PROCEDURE increment_version();
And for reference, here are my main tables
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE users (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
username varchar(20) NOT NULL UNIQUE,
password varchar(20)
);
CREATE TABLE notes (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
user_id UUID REFERENCES users(id) NOT NULL,
title VARCHAR(100),
note VARCHAR(5000),
tag VARCHAR(100),
created TIMESTAMPTZ NOT NULL,
edited TIMESTAMPTZ NOT NULL
);
CREATE TABLE notes_history (
note_id UUID REFERENCES users(id) NOT NULL,
note_version INTEGER NOT NULL,
title_historic VARCHAR(100),
note_historic VARCHAR(5000),
tag_historic VARCHAR(100),
edited TIMESTAMPTZ NOT NULL,
PRIMARY KEY(note_id, note_version)
);