Обсуждение: trigger functions with arguments

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

Kev wrote:
> 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.

[...]

> 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
>   ...

Maybe it is hidden too well, but the documentation states in
http://www.postgresql.org/docs/current/static/plpgsql-trigger.html

   A trigger procedure is created with the CREATE FUNCTION command, declaring it
   as a function with no arguments and a return type of trigger. 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.

Yours,
Laurenz Albe

On Jun 8, 5:18 am, laurenz.a...@wien.gv.at ("Albe Laurenz") wrote:
> Kev wrote:
> > 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.
>
> [...]
>
> > 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
> >   ...
>
> Maybe it is hidden too well, but the documentation states
inhttp://www.postgresql.org/docs/current/static/plpgsql-trigger.html
>
>    A trigger procedure is created with the CREATE FUNCTION command, declaring it
>    as a function with no arguments and a return type of trigger. 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.
>
> Yours,
> Laurenz Albe

Thanks, Laurenz, I had missed that.  However, I don't think it's in
the best spot: since I was writing a plperl trigger function, it makes
sense for me to read the plperl trigger function docs, and the general
trigger and trigger function docs, but not necessarily the plpgsql
trigger function docs.  IMO it should either be in the trigger
function docs for each language, or it should be in the general
trigger function docs, like maybe http://www.postgresql.org/docs/8.3/static/trigger-definition.html
since it talks about return values and such there.  That is, if it
actually applies to every language...

Kev