Insert multiple row with trigger: Postgresql

Question

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 ?

Community · Accepted Answer · 2017-05-23 12:02:30Z

1

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;

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered Apr 25, 2017 at 12:23

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fedour Over a year ago

Sorry I didn't understand last time. But this is working, thank a lot !

Vitor Hallais · Accepted Answer · 2017-04-25 12:26:31Z

0

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;

answered Apr 25, 2017 at 12:26

Vitor Hallais

315 bronze badges

1 Comment

Fedour Over a year ago

Thank for your help !

Collectives™ on Stack Overflow

Insert multiple row with trigger: Postgresql

2 Answers 2

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Linked

Related