"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
