Re: Update tsvector trigger

From: Richard Huxton <dev(at)archonet(dot)com>
To: x asasaxax <xanaruto(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Update tsvector trigger
Date: 2008-08-06 08:00:29
Message-ID: 48995A1D.3070905@archonet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Richard Huxton 2008-08-06 08:03:34 Re: FTS on patterns
Previous Message Albe Laurenz 2008-08-06 06:50:31 Re: Installation problem ~!