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!

Answer 1

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.