Search Postgresql Archives

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

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

 



"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



[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