Re: function does not exist

Quoting from http://www.postgresql.org/idocs/index.php?plpgsql-trigger.html :

PL/pgSQL can be used to define trigger procedures. A trigger procedure
is created with the CREATE FUNCTION command as a function with no
arguments and a return type of OPAQUE. Note that the function must be
declared with no arguments even if it expects to receive arguments
specified in CREATE TRIGGER --- trigger arguments are passed via
TG_ARGV, as described below.

So, you need to write a trigger version of your function, or write a
trigger wrapper that pulls out the args and calls your work function.

Ross

On Fri, Jan 10, 2003 at 05:22:47PM -0500, Dave A. wrote:
> I am using postgresql version 7.2.3, and have the following situation.
>
> When I attempt to add a function, I get the error CreateTrigger: function
> mem_leveled() does not exist. Using the function in psql (i.e. SELECT
> mem_leveled('fubar', 4, '2002/12/30 10:09:00 GMT'); ) works fine, I just
> can't create a trigger for it.
>
> I know its something stupid I'm doing (or not doing).
>
> CREATE TABLE members (
> name CHARACTER VARYING(256),
> level smallint,
> date_updated timestamptz,
> ... other stuff ...
> )
>
> CREATE TABLE mem_history (
> name CHARACTER VARYING(256),
> level smallint,
> date_achieved timestamptz
> )
>
> CREATE FUNCTION mem_leveled (varchar, smallint, timestamptz) RETURN BOOLEAN
> AS
> 'DELCARE
> mem_lvl RECORD;
> BEGIN
> SELECT INTO mem_lvl * FROM mem_history WHERE name = $1 AND level = $2;
> IF NOT FOUND THEN
> INSERT INTO mem_history VALUES ($1, $2, $3);
> END IF;
> RETURN TRUE;
> END;
> ' LANGUAGE 'plpgsql';
>
> CREATE TRIGGER update_mem_level AFTER INSERT OR UPDATE
> ON members FOR EACH ROW
> EXECUTE PROCEDURE mem_leveled('name', 'level', 'date_updated');
>
> ------
> Dave A.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html