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?

Answer 1

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