Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table documents (client_id int, document_id int);

CREATE OR REPLACE FUNCTION set_document_identifier()
RETURNS TRIGGER AS $f$
DECLARE
max_identifier INTEGER;
BEGIN
  SELECT MAX(document_id) INTO max_identifier
  FROM documents
  WHERE client_id = NEW.client_id;
IF max_identifier IS NULL THEN
    max_identifier := 1;
END IF;
NEW.document_id := max_identifier;
RETURN NEW;
END;
$f$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert_trigger_documents
BEFORE INSERT ON documents
FOR EACH ROW
EXECUTE FUNCTION set_document_identifier();

CREATE TABLE

CREATE FUNCTION

CREATE TRIGGER

begin;
insert into documents values (1,11),
                             (2,22),
                             (3,33) returning *;
select * from documents;
commit;

BEGIN

client_id
document_id

1
1

2
1

3
1

INSERT 0 3

client_id
document_id

1
1

2
1

3
1

SELECT 3

COMMIT

begin;
insert into documents values (1,11),
                             (2,22),
                             (3,33) returning *;
select * from documents;
commit;

BEGIN

client_id
document_id

1
1

2
1

3
1

INSERT 0 3

client_id
document_id

1
1

2
1

3
1

1
1

2
1

3
1

SELECT 6

COMMIT

begin;
insert into documents values (1,99),
                             (1,999),
                             (1,999) returning *;
select * from documents;
commit;

BEGIN

client_id
document_id

1
1

1
1

1
1

INSERT 0 3

client_id
document_id

1
1

2
1

3
1

1
1

2
1

3
1

1
1

1
1

1
1

SELECT 9

COMMIT