Search Postgresql Archives

tsearch trigger: function public.tsearch2(tsvector, text) does not exist

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux