Re: Update tsvector trigger

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