Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE vendors (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL);

insert into vendors (name) values ('vendor1'),('vendor2'),('vendor3') returning *;

CREATE TABLE orders (
id SERIAL PRIMARY KEY,
order_number INT NOT NULL,
vendor_id INT REFERENCES vendors(id),
description text,
UNIQUE (order_number,vendor_id));

create function seeded_sequence_nextval(seed text) returns int as $f$
declare sequence_name text:='seeded_sequence_'||seed;
begin execute format('create sequence if not exists %I;',sequence_name);
      return nextval(format('%I',sequence_name));
end $f$ language plpgsql;

create function f_order_numbers()returns trigger language plpgsql as $f$
begin
    select seeded_sequence_nextval('orders_vendor_'||new.vendor_id) 
    into new.order_number;
    return new;
end $f$;

create trigger t_order_numbers before insert on orders 
for each row execute function f_order_numbers();

CREATE TABLE




id
name


1
vendor1

2
vendor2

3
vendor3



INSERT 0 3



CREATE TABLE



CREATE FUNCTION



CREATE FUNCTION



CREATE TRIGGER


insert into orders (vendor_id,description) values
(1,'v1order1'),
(1,'v1order2'),
(2,'v2order1'),
(3,'v3order1'),
(3,'v3order2');

select vendor_id,order_number,description,id from orders;

INSERT 0 5




vendor_id
order_number
description
id


1
1
v1order1
1

1
2
v1order2
2

2
1
v2order1
3

3
1
v3order1
4

3
2
v3order2
5



SELECT 5


delete from orders where id in (1,5);
update orders set vendor_id=1 where id=4;
--now vendor1's first order is number 2
--vendor3 has only order 1 and 3, a gap on number 2

DELETE 2



UPDATE 1


insert into orders (vendor_id,description) values
(1,'v1order3'),
(2,'v2order2'),
(3,'v3order3');

select vendor_id,order_number,description,id from orders;

INSERT 0 3




vendor_id
order_number
description
id


1
2
v1order2
2

2
1
v2order1
3

1
1
v3order1
4

1
3
v1order3
6

2
2
v2order2
7

3
3
v3order3
8



SELECT 6
vendor_id	order_number	description	id
1	2	v1order2	2
2	1	v2order1	3
1	1	v3order1	4
1	3	v1order3	6
2	2	v2order2	7
3	3	v3order3	8