Thread: Parameters from trigger to function.

Everyone, I need your help,

I am trying to pass a parameter to a function that is to be called by a
trigger.  But, the correct function isn't being recognized and I am getting
an error stating that the function doesn't exist even though it does!

What I got going in psql...

CREATE TABLE testing (id SERIAL, data text);  -- sequence = testing_id_seq

CREATE OR REPLACE FUNCTION id_protect(text) RETURNS TRIGGER AS '
        DECLARE
                sequence ALIAS FOR $1;
        BEGIN
                IF TG_OP = ''INSERT'' THEN
                        NEW.id := nextval(sequence);
                        RETURN NEW;
                ELSIF TG_OP = ''UPDATE'' THEN
                        NEW.id := OLD.id;
                        RETURN NEW;
                ELSE
                        RETURN NEW;
                END IF;
        END;
' LANGUAGE PLPGSQL;

\df id_protect
            List of Functions
 Result data type | Schema |    Name    | Argument data types
-------------------+----------+------------+------------------------
"trigger"             | public    | id_protect | text

CREATE TRIGGER protector BEFORE INSERT OR UPDATE ON testing FOR EACH ROW
EXECUTE PROCEDURE id_protect('testing_id_seq');

ERROR:  function id_protect() does not exist

-----------------------------------------------------------------------------------------------------

Now, why am I getting that error?

Thx all,
  Vams

Vams <vmudrageda@charter.net> writes:
> CREATE OR REPLACE FUNCTION id_protect(text) RETURNS TRIGGER AS '
> ...
> CREATE TRIGGER protector BEFORE INSERT OR UPDATE ON testing FOR EACH ROW
> EXECUTE PROCEDURE id_protect('testing_id_seq');

> ERROR:  function id_protect() does not exist

> Now, why am I getting that error?

Trigger functions *never* take any normal parameters.

You can put parameters into the CREATE TRIGGER command, but they are
passed via a different mechanism (in plpgsql, it's an array called
tg_argv[]).  This is a bit weird but I think it goes along well with
the fact that triggers get a whole bunch of parameters, and from time
to time we add some.  We could not do that without breaking every
existing user trigger if the parameters were all to be declared as
explicit function parameters.

See http://www.postgresql.org/docs/7.4/static/plpgsql-trigger.html
for more ...

            regards, tom lane