stack depth limit exceeded PostgreSQL

Question

I have a Trigger that comes from the insert, update, delete,

in this update going wrong stack depth limit exceeded whenever an update is run and it goes into infinite loop. (infinite recursion)

anyone have any tips?

CREATE OR REPLACE FUNCTION saldolinear() RETURNS TRIGGER AS 
'
DECLARE
    IDS  INTEGER:= 0;   MIN integer:= 0; MIN1 integer:= 0; valor1 float;saldo float;    saldoini float; saldoini1 float;i integer:=0;j integer:= 0;
    l integer:=0;   m integer:=0;var2 integer:=0;countid integer:=0;coun integer:=0;DATADB date;IDMAX integer:=0;dataup date;
    coundata date;  res boolean;    res1 boolean;   mov TB_MOV_BANCARIA%rowtype; registro integer;
BEGIN
     IF (TG_OP = ''INSERT'' ) THEN
        SELECT INTO registro count(TB_MOV_BANCARIA.data) FROM TB_MOV_BANCARIA where TB_MOV_BANCARIA.data<NEW.data group by TB_MOV_BANCARIA.data;
    saldoini:=0;        
    IF (registro>0) THEN    
       res:= true;
       SELECT INTO DATADB MAX(TB_MOV_BANCARIA.data) FROM TB_MOV_BANCARIA where TB_MOV_BANCARIA.data<NEW.data;
       FOR mov IN select tb_mov_bancaria.id from tb_mov_bancaria where tb_mov_bancaria.data = DATADB order by tb_mov_bancaria.data, tb_mov_bancaria.id LOOP
          SELECT INTO saldoini saldo_linha FROM TB_MOV_BANCARIA where id = mov.id;
       END LOOP;
    END IF;       
           FOR mov IN select * from tb_mov_bancaria where tb_mov_bancaria.data>=NEW.data order by tb_mov_bancaria.data, tb_mov_bancaria.id LOOP
              saldoini := saldoini + mov.valor;
          UPDATE tb_mov_bancaria set saldo_linha=saldoini where id = mov.id;
       END LOOP;
     END IF;
     IF (TG_OP = ''UPDATE'' ) THEN
        IF(OLD.data<NEW.data)THEN
       dataup:= OLD.data;
    ELSE
       dataup:= NEW.data;
        END IF;
        IF (NOT dataup is null) THEN
           SELECT INTO DATADB MAX(TB_MOV_BANCARIA.data) FROM TB_MOV_BANCARIA where TB_MOV_BANCARIA.data<dataup;
       FOR mov IN select tb_mov_bancaria.id from tb_mov_bancaria where tb_mov_bancaria.data = DATADB order by tb_mov_bancaria.data, tb_mov_bancaria.id LOOP
          SELECT INTO saldoini saldo_linha FROM TB_MOV_BANCARIA where id = mov.id;
       END LOOP;
       --RAISE EXCEPTION ''%'',dataup;
       FOR mov IN select * from tb_mov_bancaria where tb_mov_bancaria.data>=dataup order by tb_mov_bancaria.data, tb_mov_bancaria.id LOOP
              saldoini := saldoini + mov.valor;
              --RAISE EXCEPTION ''UPDATE tb_mov_bancaria set saldo_linha=% where id = %;'',saldoini,mov.id;
          UPDATE tb_mov_bancaria set saldo_linha=saldoini where id = mov.id;
       END LOOP;
      END IF;
     END IF;
      IF (TG_OP = ''DELETE'') THEN
       res:= true;
       SELECT INTO DATADB MAX(TB_MOV_BANCARIA.data) FROM TB_MOV_BANCARIA where TB_MOV_BANCARIA.data<OLD.data;
       FOR mov IN select tb_mov_bancaria.id from tb_mov_bancaria where  tb_mov_bancaria.data = DATADB order by tb_mov_bancaria.data, tb_mov_bancaria.id LOOP
          SELECT INTO saldoini saldo_linha FROM TB_MOV_BANCARIA where id = mov.id;
       END LOOP;

           FOR mov IN select * from tb_mov_bancaria where tb_mov_bancaria.data>=OLD.data           order by tb_mov_bancaria.data, tb_mov_bancaria.id LOOP
                saldoini := saldoini + mov.valor;
            UPDATE tb_mov_bancaria set saldo_linha=saldoini where id = mov.id;
         END LOOP;
       END IF;
           RETURN NULL;
   END
  '
  LANGUAGE 'plpgsql'

Answer 1

I think you are doing the wrong thing with your trigger. A trigger should almost never recurse and if it recurses, you have to make darned sure it can't recurse infinitely.

Infinite recursion happens because you try to perform the operation again instead of merely modifying what the operation occurs on. You return null, which indicates you have already done everything you are intended to do.

Instead of redoing the update statements, etc. just return a NEW value including the modified data. Only use UPDATE or DELETE to modify tables (or if you absolutely must, rows) which are not modified by the original request.