How to avoid recursion in a trigger in PostgreSQL

Question

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.

@DavidVega según este hilo, están preparando el lanzamiento de StackOverflow en español. Lamentablemente esta página utiliza inglés exclusivamente, por lo tanto tendrán que cerrar tu pregunta. (I'm directing the user to a thread on a supposedly upcoming Spanish StackOverflow and explaining that this site is in English only so his question will be closed.) — eirikir
– eirikir, Commented Aug 1, 2015 at 23:01

klin · Accepted Answer · 2022-10-15 18:44:52Z

3

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()

edited Oct 15, 2022 at 18:44

answered Aug 2, 2015 at 10:41

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

David Vega Over a year ago

Thanks @klin it Was very helpfull

Collectives™ on Stack Overflow

How to avoid recursion in a trigger in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related