Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table notes (
  note_id int not null, 
  version int not null,
  content text default 'some_text',
  primary key (note_id,version) );

CREATE TABLE


create table latest_note_versions(
  note_id int primary key,
  version int default 1          );

create function tgf_notes_version() returns trigger language plpgsql as $f$ 
begin 
  with version_upsert as (
      insert into latest_note_versions AS lnv 
      values (NEW.note_id)
      on conflict(note_id) do update set version=lnv.version+1
      returning version)
  select version from version_upsert into NEW.version;
  return NEW;
end $f$;

create trigger tg_a_gen before insert on notes
for each row execute function tgf_notes_version();

CREATE TABLE



CREATE FUNCTION



CREATE TRIGGER


insert into notes values 
  (1,999,'my_own_text11'), 
  (1,999,'my_own_text12'), 
  (3,123,'my_own_text31'), 
  (3,321,'my_own_text32'), 
  (3,0,'my_own_text33')
returning *;


note_id
version
content


1
1
my_own_text11

1
2
my_own_text12

3
1
my_own_text31

3
2
my_own_text32

3
3
my_own_text33



INSERT 0 5

note_id	version	content
1	1	my_own_text11
1	2	my_own_text12
3	1	my_own_text31
3	2	my_own_text32
3	3	my_own_text33