and AFAIK you cannot call trigger functions directly neither from SQL
nor pl/pgsql
ok. so the returning value from the tsearch2
function/proceduer/whateveritreallyis is the culprit. thanks.
when using the public.tsearch2 function directly as a trigger, it works
fine:
CREATE TRIGGER "posts_ts_update" BEFORE INSERT OR UPDATE
ON "forum"."posts" FOR EACH ROW
EXECUTE PROCEDURE "public"."tsearch2"(idxfti, p_msg_clean);
i would stick with this - it does exactly the same as your non-working
code above, and is faster
nah, its not faster when you use the new GIN indextype as tsvector index,
which takes considerably longer to (re)index. we got a view-counter for our
news-messages. each time a user reads the news, the counter is changed and
the trigger fired. thus the field would be reindexed all day long...
meanwhile, i've found an easy solution. while the comparsion of the new and
old text takes quite some time on large text, its still lot faster than
having the tsvector rebuild every time.
CREATE OR REPLACE FUNCTION "forum"."tsearch2_trigger_posts" () RETURNS
trigger AS
$body$
BEGIN
IF (TG_OP = 'UPDATE') THEN
IF (NEW.p_msg_clean != OLD.p_msg_clean) THEN
NEW.idxfti = to_tsvector(NEW.p_msg_clean);
END IF;
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
NEW.idxfti = to_tsvector(NEW.p_msg_clean);
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
regards,
thomas
