Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > CREATE TABLE transactions( > id SERIAL NOT NULL PRIMARY KEY, > value NUMERIC(10,3), > note TEXT, > group_id INTEGER); > > <pre> > ✓ > </pre> <!-- --> > CREATE FUNCTION upd_group() > RETURNS trigger AS > $upd_group$ > > DECLARE > > max_group_id_ INTEGER := 0; > > BEGIN > > SELECT MAX(group_id) INTO max_group_id_ FROM transactions; > max_group_id_ := COALESCE(max_group_id_, 0) + 1; > > UPDATE transactions > SET group_id = max_group_id_ > FROM inserted > WHERE inserted.id = transactions.id > AND inserted.group_id IS NULL ; > > RETURN NULL; > > END; > $upd_group$ language plpgsql; > > <pre> > ✓ > </pre> <!-- --> > CREATE TRIGGER trg_upd_group AFTER INSERT ON transactions > REFERENCING NEW TABLE AS inserted > FOR EACH STATEMENT > EXECUTE FUNCTION upd_group(); > > <pre> > ✓ > </pre> <!-- --> > INSERT INTO transactions (value, note, group_id) > VALUES > (12, 'note 12', NULL), > (13, 'note 13', 10), > (14, 'note 14', NULL), > (15, 'note 15', NULL); > > <pre> 4 rows affected > </pre> <!-- --> > SELECT * FROM transactions; > > <pre> > id | value | note | group_id > -: | -----: | :------ | -------: > 2 | 13.000 | note 13 | 10 > 1 | 12.000 | note 12 | 11 > 3 | 14.000 | note 14 | 11 > 4 | 15.000 | note 15 | 11 > </pre> <!-- --> > INSERT INTO transactions (value, note, group_id) > VALUES > (112, 'note 112', NULL), > (113, 'note 113', NULL), > (114, 'note 114', NULL), > (115, 'note 115', NULL); > > <pre> 4 rows affected > </pre> <!-- --> > SELECT * FROM transactions; > > <pre> > id | value | note | group_id > -: | ------: | :------- | -------: > 2 | 13.000 | note 13 | 10 > 1 | 12.000 | note 12 | 11 > 3 | 14.000 | note 14 | 11 > 4 | 15.000 | note 15 | 11 > 5 | 112.000 | note 112 | 12 > 6 | 113.000 | note 113 | 12 > 7 | 114.000 | note 114 | 12 > 8 | 115.000 | note 115 | 12 > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_11&fiddle=553636ea5159835e261dd2a24033ca5d)*
back to fiddle