PostgreSQL trigger on insert external script with arguments

Question

I have created function calling external script with arguments:

CREATE OR REPLACE FUNCTION foo3(text, text) RETURNS TEXT AS $$
$ip = shift;
$username = shift;
$cmd="/bin/echo -n $ip $username > /tmp/test";
return `$cmd`;
$$ LANGUAGE plperlu;

It works fine when executed manually. But I wanted to call it every time new record in inserted in my table (a pass arguments which are record values):

postgres=# CREATE TRIGGER t_foo
postgres-#   BEFORE UPDATE
postgres-#   ON mapping
postgres-#   FOR EACH ROW
postgres-#   EXECUTE PROCEDURE foo3(OLD.ip, OLD.username);
ERROR:  syntax error at or near "."
LINE 5:   EXECUTE PROCEDURE foo3(OLD.ip, OLD.username);
                                ^

Clearly I can not pass any arguments to that function. How to fix it?

Answer 1

As documented in the manual

The trigger function must be declared as a function taking no arguments and returning type trigger.

Your function is neither declared as returns trigger nor does it have "no arguments".

You need to create a trigger function that calls your function:

create function foo_trigger_func()
  returns trigger
as
$$
begin
  perform foo3(old.ip, old.username);
end;
$$
language plpgsql;

Then you create a trigger using that function:

CREATE TRIGGER t_foo
  BEFORE UPDATE ON mapping
   FOR EACH ROW
   EXECUTE PROCEDURE foo_trigger_func();