clear markdown compare help donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 541799 fiddles created (11226 in the last week).

CREATE TABLE transactions( id SERIAL NOT NULL PRIMARY KEY, value NUMERIC(10,3), note TEXT, group_id INTEGER);
 hidden batch(es)


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 t SET group_id = max_group_id_ FROM inserted i WHERE t.id = i.id AND t.group_id IS NULL; RETURN NULL; END; $upd_group$ language plpgsql;
 hidden batch(es)


CREATE TRIGGER trg_upd_group AFTER INSERT ON transactions REFERENCING NEW TABLE AS inserted FOR EACH STATEMENT EXECUTE FUNCTION upd_group();
 hidden batch(es)


INSERT INTO transactions (value, note, group_id) VALUES (12, 'note 12', NULL), (13, 'note 13', NULL), (14, 'note 14', NULL), (15, 'note 15', NULL);
4 rows affected
 hidden batch(es)


SELECT * FROM transactions;
id value note group_id
1 12.000 note 12 1
2 13.000 note 13 1
3 14.000 note 14 1
4 15.000 note 15 1
 hidden batch(es)


INSERT INTO transactions (value, note, group_id) VALUES (112, 'note 112', NULL), (113, 'note 113', NULL), (114, 'note 114', NULL), (115, 'note 115', NULL);
4 rows affected
 hidden batch(es)


SELECT * FROM transactions;
id value note group_id
1 12.000 note 12 1
2 13.000 note 13 1
3 14.000 note 14 1
4 15.000 note 15 1
5 112.000 note 112 2
6 113.000 note 113 2
7 114.000 note 114 2
8 115.000 note 115 2
 hidden batch(es)