sqlpostgresqltriggerspostgresql-triggers

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


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!


Solution

  • 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.