"Thomas H." wrote: > 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 actually, there is no such function as tsearch2(tsvector, text) - so postgres is right :) t1=> \df++ tsearch2 List of functions -[ RECORD 1 ]-------+---------- Schema | public Name | tsearch2 Result data type | "trigger" Argument data types | Owner | pgdba Language | c Source code | tsearch2 Description | and AFAIK you cannot call trigger functions directly neither from SQL nor pl/pgsql > > 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 > > 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 EXECUTE is not what you want, see http://www.postgresql.org/docs/current/static/sql-execute.html
