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
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:
EXECUTE FUNCTION requires Postgres 11. See:
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:
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.
Assuming this is just a test before an insertion as you do not modify the data, maybe
IF (NEW.pg_inicial < NEW.pg_final) THEN
RETURN NEW;
ELSE
RETURN NULL;
END IF;
p.s: I'll make a DB fiddle to check if this syntax is OK. here is the fiddle, https://www.db-fiddle.com/f/9iDx4bmJdk5rUzPrFdevAt/2 .
Edit2: Erwin Brandstetter's answer is complete, you should add a constraint instead of this trigger.
Since you are "Inserting" only new columns, the insert, in the trigger function, is completely unnecessary - that's what is going to be inserted anyway. The question becomes what do you want then IF NEW.pg_inicial < NEW.pg_final" returns False. You have basically 3 options:
Row-level triggers fired BEFORE can return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row).
In which implement the trigger function as follows:
create or replace
function verificar_pagina_inicial_final()
returns trigger
language plpgsql
as $$
begin
if new.pg_inicial < new.pg_final
then
return new;
else
return null;
end if;
end;
$$ ;