Re: Error Message

On Wed, Oct 26, 2005 at 07:45:19PM -0400, Terry Lee Tucker wrote:
> You cannot pass argments to trigger functions. You can to other types of
> functions, but not functions used as triggers. Arguments are passed regarding
> the old and new records and other built in variables regarding what kind of
> operation is going on, but all of that is "unseen".
>
> They must be created as in:
> CREATE TRIGGER trig1 AFTER INSERT
> ON process FOR EACH ROW
> EXECUTE PROCEDURE base();
> ^^^^^^
> Note: no argument.

You *can* pass arguments to trigger functions but it's done a little
differently than with non-trigger functions. The function must be
defined to take no arguments; it reads the arguments from a context
structure instead of in the normal way. PL/pgSQL trigger functions,
for example, read their arguments from the TG_ARGV array.

http://www.postgresql.org/docs/8.0/interactive/plpgsql-trigger.html
http://www.postgresql.org/docs/8.0/interactive/trigger-interface.html

Example:

CREATE TABLE foo (id integer, x integer);

CREATE FUNCTION func() RETURNS trigger AS $$
BEGIN
NEW.x := TG_ARGV[0];
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER footrig BEFORE INSERT OR UPDATE ON foo
FOR EACH ROW EXECUTE PROCEDURE func(12345);

INSERT INTO foo (id) VALUES (1);

SELECT * FROM foo;
id | x
----+-------
1 | 12345
(1 row)

However, it's not clear if this is what Bob is trying to do. His
original attempt was:

> CREATE TRIGGER trig1 AFTER INSERT
> ON process FOR EACH ROW
> EXECUTE PROCEDURE base(int4);

He's given what looks like a function signature instead of passing
an argument. Even if this worked, he hasn't specified what argument
should be passed. Bob, can you explain what you're trying to do?

--
Michael Fuhr