I have a trigger:
CREATE OR REPLACE FUNCTION process_fillDerivedFromGenby() RETURNS TRIGGER AS $fillDerivedFromgenby$
DECLARE
prog varchar(255);
curent varchar(255);
BEGIN
SELECT u.iduseentity ,g.idCreatedEntity into prog,curent
FROM entity e
JOIN used u ON e.identity=u.iduseentity
JOIN activity a ON a.idactivity=u.idusedactivity
JOIN generatedby g ON g.idcreatoractivity=a.idactivity
WHERE g.idCreatedEntity =NEW.idCreatedEntity;
--raise notice 'curent: "%" prog by "%"', curent, prog;
INSERT INTO DERIVEDFROM VALUES(prog,curent);
return new;
END;
$fillDerivedFromgenby$ LANGUAGE plpgsql;
CREATE TRIGGER fillDerivedFromgenby AFTER INSERT ON GENERATEDBY
FOR EACH ROW EXECUTE PROCEDURE process_fillDerivedFromGenby();
It's work fine but if my select return 3 lines my trigger will only insert the last value of my select instead of the 3 lines. I have try to made a loop whit a "for" but it doesn't work.
Any suggestions ?
I already provided the answer in your other question. Ironically, the question is not a duplicate, but the answer is.
This will insert all the rows without going through variables:
CREATE OR REPLACE FUNCTION process_fillDerivedFrom_used() RETURNS TRIGGER AS $fillDerivedFrom_used$
BEGIN
INSERT INTO DERIVEDFROM (prog, current) -- or whatever the column names are
SELECT u.iduseentity as prog, g.idCreatedEntity as current
FROM entity e JOIN
used u
ON e.identity = u.iduseentity JOIN
activity a
ON a.idactivity = u.idusedactivity JOIN
generatedby g
ON g.idcreatoractivity = a.idactivity;
END;
Put the "order by desc" and "limit 1".
SELECT u.iduseentity ,g.idCreatedEntity into prog,curent
FROM entity e
JOIN used u ON e.identity=u.iduseentity
JOIN activity a ON a.idactivity=u.idusedactivity
JOIN generatedby g ON g.idcreatoractivity=a.idactivity
WHERE g.idCreatedEntity =NEW.idCreatedEntity
ORDER BY u.iduseentity DESC LIMIT 1;