Обсуждение: Update tsvector trigger

Поиск
Список
Период
Сортировка

Update tsvector trigger

От
"x asasaxax"
Дата:
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?


Re: Update tsvector trigger

От
Richard Huxton
Дата:
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