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?

Search Discussions

  • Richard Huxton at Aug 6, 2008 at 8:01 am

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedAug 5, '08 at 10:04p
activeAug 6, '08 at 8:01a
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Richard Huxton: 1 post X asasaxax: 1 post

People

Translate

site design / logo © 2022 Grokbase