From: Ross J. Reedstrom <reedstrm@rice.edu>
To: Dave A. <pgadmin@pod13.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: function does not exist
Date: Fri, 10 Jan 2003 16:23:34 -0600
Message-ID: <20030110222334.GD8689@wallace.ece.rice.edu> (raw)
In-Reply-To: <001c01c2b8f6$ce15dc80$fd00000a@pod13.com>
References: <001c01c2b8f6$ce15dc80$fd00000a@pod13.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: reedstrm@rice.edu, pgadmin@pod13.com
Subject: Re: function does not exist
In-Reply-To: <20030110222334.GD8689@wallace.ece.rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox