tsearch trigger: function public.tsearch2(tsvector, text) does not exist
От | Thomas H. |
---|---|
Тема | tsearch trigger: function public.tsearch2(tsvector, text) does not exist |
Дата | |
Msg-id | 01bd01c70b8f$19f53e40$0201a8c0@iwing обсуждение исходный текст |
Ответы |
Re: tsearch trigger: function public.tsearch2(tsvector, text) does not exist
|
Список | pgsql-general |
hi list i'm trying to write a custom tsearch2 trigger that checks on update if the column value is changed. here's what i did so far: 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 EXECUTE public.tsearch2(NEW.idxfti, NEW.p_msg_clean); END IF; RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN EXECUTE public.tsearch2(NEW.idxfti, 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; CREATE TRIGGER "posts_ts_update" BEFORE INSERT OR UPDATE ON "forum"."posts" FOR EACH ROW EXECUTE PROCEDURE "forum"."tsearch2_trigger_posts"(idxfti, p_msg_clean); unfortunately, the "EXECUTE public.tsearch2" part does not work: Error while executing the query; ERROR: function public.tsearch2(tsvector, text) does not exist at character 9 HINT: No function matches the given name and argument types. You may need to add explicit type casts. QUERY: SELECT public.tsearch2( $1 , $2 ) CONTEXT: PL/pgSQL function "tsearch2_trigger_posts" line 4 at execute statement UPDATE forum.posts SET p_msg_clean" = 'test' WHERE p_t_id = 4468 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); when i'm trying to use EXECUTE PROCEDURE instead of EXECUTE within the function, it won't compile: ERROR: syntax error at or near "tsearch2" at character 19 QUERY: SELECT PROCEDURE public.tsearch2( $1 , $2 ) CONTEXT: SQL statement in PL/PgSQL function "tsearch2_trigger_news" near line 4 what am i missing? thanks, thomas
В списке pgsql-general по дате отправления: