Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE FUNCTION get_next_user_group_index(group_id int)
RETURNS int AS
$BODY$
    DECLARE
        group_index int;
    BEGIN
        SELECT nextval('user_group_index_' || group_id::text)
        INTO group_index;
        RETURN group_index;
    END
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE;

CREATE FUNCTION


CREATE TABLE users(
  id SERIAL PRIMARY KEY,
  group_id INTEGER NOT NULL,
  group_index INTEGER GENERATED ALWAYS AS (get_next_user_group_index(group_id)) STORED
);

CREATE TABLE


CREATE OR REPLACE FUNCTION create_user_group_sequence()
RETURNS TRIGGER AS
$BODY$
   BEGIN
       EXECUTE 'CREATE SEQUENCE IF NOT EXISTS user_group_index_' || NEW.group_id::TEXT;
       RETURN NEW;
   END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER new_user_in_group
BEFORE INSERT
ON users
FOR EACH ROW
EXECUTE PROCEDURE create_user_group_sequence();

CREATE FUNCTION



CREATE TRIGGER


INSERT INTO users(id, group_id)
VALUES (1,1), (2,1), (3,1);

INSERT 0 3


INSERT INTO users(id, group_id)
VALUES (4,2), (5,2);

INSERT 0 2


INSERT INTO users(id, group_id)
VALUES (6,1), (7,2);

INSERT 0 2


SELECT * FROM users;


id
group_id
group_index


1
1
1

2
1
2

3
1
3

4
2
1

5
2
2

6
1
4

7
2
3



SELECT 7