I came across this curious behaviour today, at least in the 2009-03-24
beta (I can't run a newer beta), that I couldn't find in the docs,
although maybe I missed it. I found this really confusing until I
figured it out, so I thought I should share, and if it's not in the
docs, it should probably be noted somewhere.
This may also be specific to plperl, but I would guess it's not. I
haven't tried other languages.
The docs say you can have a trigger function that accepts arguments,
which are static text values that you set when you're setting up the
trigger that uses the function. What I didn't know was that when
creating such a function, you cannot specify any arguments, even
though you're expecting it to be used that way. (A very welcome side
effect of this is that your function can take a variable number of
arguments.) If you do this:
CREATE OR REPLACE FUNCTION test(text, text) RETURNS trigger AS $BODY
$
my ($a, $b) = @{$_TD->{args}};
...
...you end up with a function that's a useless (AFAICT) half-trigger.
That is, it *is* a trigger function in that if you try "select test
('c','d')", you can an error about it being a trigger function. But
it's *not* a trigger function because if you then try:
CREATE TRIGGER test_trigger AFTER INSERT ON my_table FOR EACH ROW
EXECUTE PROCEDURE test('c','d');
...you'll receive an error message, "function test() does not exist."
Which is, strictly speaking, true, but my first interpretation of that
message was that whoever wrote the error message forgot to include the
parameters--after all, it was test('c','d') that I told it to execute
on insert. So while that is the way to create the trigger itself, the
trigger procedure must be created like this:
CREATE OR REPLACE FUNCTION test() RETURNS trigger AS $BODY$
my ($a, $b) = @{$_TD->{args}}; # $a and/or $b may be null, depending
on how CREATE TRIGGER was used
...
I hope someone finds that helpful,
Kev