Thread: plpgsql trigger function with arguments

I'm sure I saw something like this on the postgresql web
site but the the search function is down in the documentation
area.

I'm unable to pass a function arguments in a CREATE
TRIGGER statement.

What am I doing wrong here?

CREATE FUNCTION pregs_func (VARCHAR(15))
   RETURNS trigger
   LANGUAGE plpgsql
   AS '
   DECLARE
   trigger_type ALIAS FOR $1;

   BEGIN
     IF trigger_type = ''insert'' THEN
       ...
     END IF;
   END;
';

CREATE TRIGGER pregs_insert_trigger
   AFTER INSERT
   ON pregs FOR EACH ROW
   EXECUTE PROCEDURE pregs_func('insert');

CREATE TRIGGER pregs_update_trigger
   AFTER UPDATE
   ON pregs FOR EACH ROW
   EXECUTE PROCEDURE pregs_func('update');

The CREATE TRIGGER statements return:
ERROR:  CreateTrigger: function pregs_func() does not exist


I tried doing variations on:
   create trigger pregs_insert_trigger after insert on pregs
   for each row execute procedure
   pregs_func (cast('insert' as varchar(15)));

thinking that I don't have the right function because the
datatypes don't match, but I get:
ERROR:  parser: parse error at or near "cast" at character 106

PostgreSQL 7.3.

Thanks,

Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein

hello

You have to write functions without params. Params for triggers are
accessed not via function params, but via special variable TG_ARGV[]

http://archives.postgresql.org/pgsql-general/2002-03/msg01021.php

regards
Pavel Stehule



On Tue, 2 Mar 2004, Karl O. Pinc wrote:

> I'm sure I saw something like this on the postgresql web
> site but the the search function is down in the documentation
> area.
>
> I'm unable to pass a function arguments in a CREATE
> TRIGGER statement.
>
> What am I doing wrong here?
>
> CREATE FUNCTION pregs_func (VARCHAR(15))
>    RETURNS trigger
>    LANGUAGE plpgsql
>    AS '
>    DECLARE
>    trigger_type ALIAS FOR $1;
>
>    BEGIN
>      IF trigger_type = ''insert'' THEN
>        ...
>      END IF;
>    END;
> ';
>
> CREATE TRIGGER pregs_insert_trigger
>    AFTER INSERT
>    ON pregs FOR EACH ROW
>    EXECUTE PROCEDURE pregs_func('insert');
>
> CREATE TRIGGER pregs_update_trigger
>    AFTER UPDATE
>    ON pregs FOR EACH ROW
>    EXECUTE PROCEDURE pregs_func('update');
>
> The CREATE TRIGGER statements return:
> ERROR:  CreateTrigger: function pregs_func() does not exist
>
>
> I tried doing variations on:
>    create trigger pregs_insert_trigger after insert on pregs
>    for each row execute procedure
>    pregs_func (cast('insert' as varchar(15)));
>
> thinking that I don't have the right function because the
> datatypes don't match, but I get:
> ERROR:  parser: parse error at or near "cast" at character 106
>
> PostgreSQL 7.3.
>
> Thanks,
>
> Karl <kop@meme.com>
> Free Software:  "You don't pay back, you pay forward."
>                   -- Robert A. Heinlein
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

On Tue, 02 Mar 2004 21:35:27 -0600, Karl O. Pinc wrote:

<..>>
> CREATE TRIGGER pregs_insert_trigger
>    AFTER INSERT
>    ON pregs FOR EACH ROW
>    EXECUTE PROCEDURE pregs_func('insert');
>
> CREATE TRIGGER pregs_update_trigger
>    AFTER UPDATE
>    ON pregs FOR EACH ROW
>    EXECUTE PROCEDURE pregs_func('update');
>

According to the docs, this is unnecessary. A trigger function
automatically has a number of variables instantiated for it:

http://www.postgresql.org/docs/7.3/static/plpgsql-trigger.html#plpgsqltrigger

The TG_OP variable contains 'INSERT','UPDATE' or 'DELETE'.

--
Colin Fox
President
CF Consulting Inc.