David Vega
David Vega

Reputation: 21

How to avoid recursion in a trigger in PostgreSQL

I have a problem with recursion in a trigger.

I have the table:

CREATE TABLE employees(
   task int4 NOT NULL,
   first_name varchar(40) NOT NULL,
   last_name varchar(40) NOT NULL,
   PRIMARY KEY (task, cli_id_number)
);

When I insert the values:

(123, name, last)

i want to automatically insert these values as well:

(321, name, last)

I do this in the following way, but apparently the trigger is recursive and after the first recursion it tries to insert the previously inserted value.

CREATE OR REPLACE FUNCTION insert_task() RETURNS trigger AS $BODY$
BEGIN
  IF new.task = '123' AND (
     SELECT cant FROM (
       SELECT name, task, count(*) AS cant
       FROM client_task
       WHERE name = NEW.name AND task = NEW.task
       GOUP BY 1,2
       HAVING count(*) <= 1
     ) t) = 1 THEN
    INSERT INTO client_task(task, name, last_name)
    VALUES('321', NEW.task, NEW.name, NEW.last_name);
    RETURN NEW;
  ELSE 
    IF NEW.task = '321' AND (
      SELECT cant FROM (
        SELECT name, task, count(*) AS cant
        FROM client_task
        WHERE name = NEW.name AND task = NEW.task
        GROUP BY 1, 2
        HAVING count(*) <=1
      ) t) = 1 THEN
      INSERT INTO client_task(task, name, last_name)
      VALUES('123', NEW.task, NEW.name, NEW.last_name);
      RETURN NEW;
    END IF;
  END IF;
  RETURN NULL;
END; $BODY$ LANGUAGE 'plpgsql';

Any help appreciated.

Upvotes: 2

Views: 901

Answers (1)

klin
klin

Reputation: 121534

Use the function pg_trigger_depth(). According to the documentation it returns:

current nesting level of PostgreSQL triggers (0 if not called, directly or indirectly, from inside a trigger)

CREATE TRIGGER insert_task
AFTER INSERT ON employees
FOR EACH ROW
WHEN (pg_trigger_depth() = 0)
EXECUTE PROCEDURE insert_task()

Upvotes: 3

Related Questions