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