Postgres triggers for group_id

Question

I have a table that looks like this:

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

I'd like to update the group ID automatically with a trigger, if it's not set on insert. So something like this:

CREATE FUNCTION trigger_update_group() RETURNS trigger AS $$ DECLARE max_group_id_ INTEGER := 0; BEGIN IF NEW.group IS NULL THEN SELECT MAX(group_id) INTO max_group_id_ FROM transactions; max_group_id_ := 1; NEW.group = max_group_id_; END IF; RETURN NEW; END; $$ language plpgsql;

The problem with this is that if I set the trigger to FOR EACH ROW, then each inserted row gets a different group_id (not to mention we run the SELECT statement for each row too).

Instead, what I was hoping for was that all the rows in a given INSERT statement would get the same group_id. Is there a way to do that (even if it involves transactions)? It seems FOR EACH STATEMENT doesn't allow access to the individual rows. Maybe there's some way to wrap the INSERT?

McNets · Accepted Answer · 2018-12-14 09:00:12Z

Instead of triggering it by ROW you can use FOR EACH STATEMENT.

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

Quoted from Postgres Docs:

FOR EACH ROW
FOR EACH STATEMENT

This specifies whether the trigger function should be fired once for every row affected by the trigger event, or just once per SQL statement. If neither is specified, FOR EACH STATEMENT is the default. Constraint triggers can only be specified FOR EACH ROW.

The you can define an AFTER INSERT trigger that updates all rows WHERE group_id IS NULL, assigning to them the same group_id.

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;

 INSERT INTO transactions (value, note, group_id) VALUES (12, 'note 12', NULL), (13, 'note 13', NULL), (14, 'note 14', NULL), (15, 'note 15', NULL);

 INSERT INTO transactions (value, note, group_id) VALUES (112, 'note 112', NULL), (113, 'note 113', NULL), (114, 'note 114', NULL), (115, 'note 115', NULL);

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

db<>fiddle here

Or, to account for some rows having a group_id provided: dbfiddle.uk/… — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Dec 14, 2018 at 9:20

Stack Exchange Network

Postgres triggers for group_id

1 Answer 1

Hot Network Questions

Postgres triggers for group_id

1 Answer 1

Related

Hot Network Questions