Re: tsearch trigger: function public.tsearch2(tsvector, text) does not exist
От | Thomas H. |
---|---|
Тема | Re: tsearch trigger: function public.tsearch2(tsvector, text) does not exist |
Дата | |
Msg-id | 0a9a01c70d69$1aef0e30$0201a8c0@iwing обсуждение исходный текст |
Ответ на | tsearch trigger: function public.tsearch2(tsvector, text) does not exist ("Thomas H." <me@alternize.com>) |
Список | pgsql-general |
> 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
В списке pgsql-general по дате отправления: