Trigger return new infinite loop

Question

CREATE OR REPLACE FUNCTION updatepersonne()
  RETURNS trigger AS
$BODY$
DECLARE 
IDToInsert numeric;
BEGIN

    SELECT "PersonneID" FROM "Personne" p INTO IDToInsert WHERE (p."Prenom"=NEW."Prenom" AND p."Nom"=NEW."Nom" AND p."Adresse"=NEW."Adresse");

IF IDToInsert IS NULL THEN
    INSERT INTO "Personne" ("PersonneID","Nom","Prenom","Adresse") VALUES (Default, NEW."Nom",NEW."Prenom",NEW."Adresse") RETURNING "PersonneID" into IDToInsert;
END IF;
NEW."PersonneID":=IDToInsert;
--IF TG_RELNAME="Emprunteur" 
--THEN
--  NEW."EmprunteurID":=IDToInsert;
--ELSE
--  NEW."DetenteurID":=IDToInsert;
--END IF;

return new;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION updatepersonne()
  OWNER TO postgres;

CREATE TRIGGER updatepersonnefromemprunteur
  BEFORE INSERT
  ON "Emprunteur"
  FOR EACH ROW
  EXECUTE PROCEDURE updatepersonne();

I wanted to check the existence of a tuple on another table in order to get the ID and use it for the new tuple or if not existing insert it on this other table. It seems to get the ID or insert it on the other table properly (tested with return null) but the main insert (on the table that triggered the thing) goes on a crazy infinite loop. Sorry for my poor english/ expression skill. Any hint? Thcks in advance.

score 1 · Answer 1 · edited May 23 '17 at 10:31

I have a few hints on how to improve the function. But there is nothing here that would cause an infinite loop. The cause must be something that is not in the question, like other triggers or rules on any of the involved tables.

The hints:

CREATE OR REPLACE FUNCTION updatepersonne()
  RETURNS trigger AS
$func$
BEGIN

WITH sel AS (
   SELECT "PersonneID"
   FROM   "Personne"
   WHERE  "Prenom"   = NEW."Prenom" 
   AND    "Nom"      = NEW."Nom"
   AND    "Adresse" = NEW."Adresse"
   )
   , ins AS (
    INSERT INTO "Personne" ("Nom", "Prenom", "Adresse")
    SELECT (NEW."Nom", NEW."Prenom", NEW."Adresse")
    WHERE  NOT EXISTS (SELECT 1 FROM sel)
    RETURNING "PersonneID"
   )
SELECT COALESCE(sel."PersonneID", ins."PersonneID")
FROM   ins, sel
INTO   NEW."PersonneID";

RETURN NEW;

END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER updatepersonnefromemprunteur
  BEFORE INSERT
  ON "Emprunteur"
  FOR EACH ROW
  EXECUTE PROCEDURE updatepersonne();

Use a data-modifying CTE to minimize the time window between SELECT and INSERT to avoid a possible race condition with heavy concurrent load. More on that:
Is SELECT or INSERT in a function prone to race conditions?
You don't need to declare additional variables, you can select into NEW."PersonneID" directly.
To insert the default into a column of a new row, just omit the column from the INSERT.

Trigger return new infinite loop

1 Answers1