I have a table that looks like this:
Create table transactions ( SERIAL NUMBER NO PRIMARY KEY, NUMERIC value (10.3), note TEXT, group_id INTEGER);
I would like to update the group ID automatically with an activator, if it is not configured in the insertion. So something like this:
CREATE FUNCTION trigger_update_group () RETURNS are triggered AS $$ DECLARE max_group_id_ INTEGER: = 0; START 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_; IT WILL END YES; BACK NEW; FINISH; $$ 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 that we also execute the SELECT statement for each row).
Instead, what I expected was that all the rows in a given INSERT statement would get the same group_id. Is there a way to do it (even if it involves transactions)? It seems
For each statement Does not allow access to individual rows. Maybe there is some way to wrap the INSERT?