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

From: "Filip Rembialkowski" <plk(dot)zuber(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: tsearch trigger: function public.tsearch2(tsvector, text) does not exist
Date: 2006-11-21 11:23:09
Message-ID: 1164108189.708882.148110@b28g2000cwb.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


"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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thomas H. 2006-11-21 12:32:26 Re: tsearch trigger: function public.tsearch2(tsvector, text) does not exist
Previous Message Martijn van Oosterhout 2006-11-21 10:29:45 Re: [HACKERS] Client SSL validation using root.crt