How to select all inserted rows to execute an insert trigger with a stored procedure in postgresql?

Question

I'm trying to set an "after insert" trigger that executes a procedure. The procedure would take all inserted rows in table A, group them by a column and insert the result in a table B. I know about "new" variable but it gets inserted rows one by one. Is it possible to get all of them?

I think I can't use a for each row statement as I need to group rows depending on the "trackCode" variable, shared by different rows in tableA.

CREATE OR REPLACE PROCEDURE Public.my_procedure(**inserted rows in tableA?**)
LANGUAGE 'plpgsql'
AS $$
BEGIN

INSERT INTO Public."tableB" ("TrackCode", "count")
SELECT "TrackCode", count(*) as "count" FROM Public."tableA" --new inserted rows in this table 
 GROUP BY "vmsint"."TrackCode" ;


COMMIT;
END;
$$;

create trigger Public.my_trigger
after insert ON Public.tableA
execute procedure Public.my_procedure(**inserted rows in tableA?**)

Thank you!

As far as I know, this is not possible. The trigger is executed after each row insert. — Bjarni Ragnarsson, Oct 04 '22 at 13:38
See `transition table` here [Create Trigger](https://www.postgresql.org/docs/current/sql-createtrigger.html) and example here [plpgsql trigger](https://www.postgresql.org/docs/current/plpgsql-trigger.html) *Example 43.7. Auditing with Transition Tables* — Adrian Klaver, Oct 04 '22 at 15:31

score 0 · Accepted Answer · answered Oct 04 '22 at 20:02

You create a statement lever trigger, but do not attempt to pass parameters. Instead use the clause referencing new table as reference_table_name. In the trigger function you use the reference_table_name in place of the actual table name. Something like: (see demo)

create or replace function group_a_ais()
  returns trigger
 language 'plpgsql'
as $$
begin
    insert into  table_b(track_code, items)
          select track_code, count(*)
            from rows_inserted_to_a
           group by track_code ;
    return null;
end;
$$;

create trigger table_a_ais
    after insert on table_a
    referencing new table as rows_inserted_to_a
    for each statement  
        execute function group_a_ais();

Do not attempt to commit in a trigger, it is a very bad id even if allowed. Suppose the insert to the main table is part of a larger transaction, which fails later in its process.

Be sure to refer to links provided by Adrian.

How to select all inserted rows to execute an insert trigger with a stored procedure in postgresql?

1 Answers1