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. 541803 fiddles created (11230 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 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;
 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', 10), (14, 'note 14', NULL), (15, 'note 15', NULL);
4 rows affected
 hidden batch(es)


SELECT * FROM transactions;
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
 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
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
 hidden batch(es)