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

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

"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

> 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