pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

function does not exist
3+ messages / 3 participants
[nested] [flat]

* function does not exist
@ 2003-01-10 22:22  Dave A. <pgadmin@pod13.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Dave A. @ 2003-01-10 22:22 UTC (permalink / raw)
  To: pgsql-sql

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.




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: function does not exist
@ 2003-01-10 22:23  Ross J. Reedstrom <reedstrm@rice.edu>
  parent: Dave A. <pgadmin@pod13.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Ross J. Reedstrom @ 2003-01-10 22:23 UTC (permalink / raw)
  To: Dave A. <pgadmin@pod13.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: function does not exist
@ 2003-01-10 22:26  Josh Berkus <josh@agliodbs.com>
  parent: Dave A. <pgadmin@pod13.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Josh Berkus @ 2003-01-10 22:26 UTC (permalink / raw)
  To: Dave A. <pgadmin@pod13.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2003-01-10 22:26 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-10 22:22 function does not exist Dave A. <pgadmin@pod13.com>
2003-01-10 22:23 ` Ross J. Reedstrom <reedstrm@rice.edu>
2003-01-10 22:26 ` Josh Berkus <josh@agliodbs.com>

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