version counter, PostgreSQL

Language: Layout:

drop table if exists version_counter, product cascade;
create table version_counter 
(
  product_no integer primary key, 
  version_nr integer not null default 1
);

create function next_version(p_product_no int) 
  returns integer
as
$$
   insert into version_counter (product_no, version_nr) 
   values (p_product_no, 1)
   on conflict (product_no) 
   do update 
      set version_nr = version_counter.version_nr + 1
   returning version_nr;
$$
language sql
volatile;

create table product
(
  product_no integer not null, 
  version_nr integer not null,
  created_at timestamp default clock_timestamp(),
  primary key (product_no, version_nr)
);

create function increment_version()
  returns trigger
as
$$
begin
  new.version_nr := next_version(new.product_no);
  return new;
end;
$$
language plpgsql;

create trigger base_table_insert_trigger
  before insert on product
  for each row
  execute procedure increment_version();
  
insert into product (product_no) values (1);
insert into product (product_no) values (2);
insert into product (product_no) values (3);
insert into product (product_no) values (1);
insert into product (product_no) values (3);
insert into product (product_no) values (2);

select *
from product
order by product_no, version_nr;


absolute service time: 0,57 sec