Stack depth limit exceeded in Postgres

Question

CREATE OR REPLACE FUNCTION verificar_pagina_inicial_final()
  RETURNS trigger AS
$BODY$
BEGIN
IF NEW.pg_inicial < NEW.pg_final THEN
 INSERT INTO artigos(id_artigo,id_editora,tipo_artigo,pg_inicial,pg_final)
VALUES(NEW.id_artigo,NEW.id_editora,NEW.tipo_artigo,NEW.pg_inicial,NEW.pg_final);
END IF;

END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER verifiar_paginas_novo_artigo
  BEFORE INSERT OR UPDATE
  ON artigos
  FOR EACH ROW
  EXECUTE PROCEDURE  verificar_pagina_inicial_final();

When I try to insert it returns me this:

INSERT INTO public.artigos(id_artigo, id_editora, tipo_artigo, pg_inicial, pg_final)
VALUES (30, 3, 'teste', 1, 2);

Returns:

ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
CONTEXT:  SQL statement "INSERT INTO artigos(id_artigo,id_editora,tipo_artigo,pg_inicial,pg_final)
VALUES(NEW.id_artigo,NEW.id_editora,NEW.tipo_artigo,NEW.pg_inicial,NEW.pg_final)"
PL/pgSQL function verificar_pagina_inicial_final() line 4 at SQL statement

Answer 1

Your trigger inserts the same row over and over again. There are various ways to prevent this. Like:

CREATE TRIGGER verifiar_paginas_novo_artigo
BEFORE INSERT OR UPDATE ON artigos
FOR EACH ROW
WHEN (pg_trigger_depth() < 1)  -- !
EXECUTE FUNCTION verificar_pagina_inicial_final();

See:

• How to prevent a PostgreSQL trigger from being fired by another trigger?

EXECUTE FUNCTION requires Postgres 11. See:

• Trigger function does not exist, but I am pretty sure it does

But it seems you just want to disallow pg_inicial >= pg_final. You could do that with a CHECK constraint:

ALTER TABLE artigos ADD CONSTRAINT pg_final_must_be_greater_than_pg_inicial
CHECK (pg_inicial < pg_final);

A CHECK constraint is simpler, faster, and more reliable. See:

• Trigger vs. check constraint
• How much cost check constraints in Postgres 9.x?

Raises an exception when violated, of course, which typically is the way to go.
To do it silently, you are back to triggers. Just simpler:

CREATE OR REPLACE FUNCTION verificar_pagina_inicial_final()
  RETURNS trigger LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW.pg_inicial < NEW.pg_final THEN
      RETURN NEW;   -- proceed
   ELSE
      RETURN NULL;  -- skip insert / update
   END IF;
END
$func$;

To proceed normally with an INSERT / UPDATE, a BEFORE trigger must RETURN NEW;. RETURN NULL cancels the row.