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?