Update tsvector trigger
Hi,
i´m trying to do a trigger that its called when update or insert, that
update the tsvectors, for text-search. Here´s my code:
create table x(
cod serial,
texto text,
vectors tsvector,
constraint pk primary key(cod)
);
CREATE OR REPLACE FUNCTION atualiza_vectors() RETURNS trigger AS $$
BEGIN
IF NEW.texto<>NULL THEN
UPDATE x SET vectors = to_tsvector(lower(to_ascii(NEW.texto)))
where cod= NEW.cod;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER atualiza_vectors AFTER INSERT OR UPDATE ON x
FOR EACH ROW EXECUTE PROCEDURE atualiza_vectors();
When the trigger its called, postgre shows the following error: "stack depth
limit exceeded".
Did anyone knows what its wrong?
x asasaxax wrote:
Hi,
i´m trying to do a trigger that its called when update or insert, that
update the tsvectors, for text-search. Here´s my code:
CREATE OR REPLACE FUNCTION atualiza_vectors() RETURNS trigger AS $$
BEGIN
IF NEW.texto<>NULL THEN
UPDATE x SET vectors = to_tsvector(lower(to_ascii(NEW.texto)))
where cod= NEW.cod;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER atualiza_vectors AFTER INSERT OR UPDATE ON x
FOR EACH ROW EXECUTE PROCEDURE atualiza_vectors();When the trigger its called, postgre shows the following error: "stack depth
limit exceeded".
You're generating an UPDATE every time the trigger is called. That will
fire another trigger, which will generate another UPDATE, which will
fire another trigger, which will...
Things to change:
1. Use a BEFORE not an AFTER trigger
2. Just set NEW.vectors := ...
--
Richard Huxton
Archonet Ltd