Thread: function does not exist

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_historyVALUES ($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.

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
asa function with no   arguments and a return type of OPAQUE. Note that the function must be   declared with no
argumentseven 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

Dave,

A trigger function does not take variable parameters, gets its data
from the NEW or OLD records, and returns OPAQUE with the RETURN NEW
statement.

Please check out the documentation on writing PL/pgSQL triggers under
Procedural Languages in the online docs.

-Josh Berkus