Date: Fri, 10 Jan 2003 16:23:34 -0600
From: "Ross J. Reedstrom" <reedstrm@rice.edu>
To: "Dave A." <pgadmin@pod13.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: function does not exist
Message-ID: <20030110222334.GD8689@wallace.ece.rice.edu>
Mail-Followup-To: "Ross J. Reedstrom" <reedstrm@ece.rice.edu>,
	"Dave A." <pgadmin@pod13.com>, pgsql-sql@postgresql.org
References: <001c01c2b8f6$ce15dc80$fd00000a@pod13.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <001c01c2b8f6$ce15dc80$fd00000a@pod13.com>
User-Agent: Mutt/1.4i

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