From: | "Thomas H(dot)" <me(at)alternize(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | tsearch trigger: function public.tsearch2(tsvector, text) does not exist |
Date: | 2006-11-19 03:59:22 |
Message-ID: | 01bd01c70b8f$19f53e40$0201a8c0@iwing |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-11-19 07:09:11 | Re: composite type insert |
Previous Message | John Meyer | 2006-11-19 02:44:05 | Re: phpPgAdmin, cannot connect to server |