1

I am searching for a way to execute a system command after update or insert with a trigger. I hope that postgres can do this. Is it possible?

CREATE TRIGGER check_update
AFTER UPDATE ON allowed_member_type
FOR EACH ROW
EXECUTE PROCEDURE check_account_update();

thanks in advance

Andreas Fritsch
  • 244
  • 3
  • 6
  • 19
  • 2
    Possible duplicate of [Call command-line function in Perl and get output as String](http://stackoverflow.com/questions/10211410/call-command-line-function-in-perl-and-get-output-as-string) – Kenney Feb 09 '16 at 15:44
  • This isn´t a duplicate. On Postgres 9.4 it dont works. – Andreas Fritsch Feb 11 '16 at 08:40

1 Answers1

5

Disclamer: I work with Andreas Fritsch on the same project.

We have solved this problem in the following way.

There is an "Language"-extension PL/sh Procedural Language Handler for PostgreSQL coded by Peter Eisentraut which does exactly what we need.

You define a shell-script like this:

CREATE or REPLACE FUNCTION test(text) RETURNS text AS '
#!/bin/bash
echo Test: $1 is working
' LANGUAGE plsh;

This is an example of a trigger-function with some usefull environment-variables for triggers:

CREATE or REPLACE FUNCTION TriggerTest() RETURNS trigger AS $$
#!/bin/bash
#mkdir /has/triggertest/$PLSH_TG_NAME
cd /has/triggertest
touch PLSH_TG_NAME-$PLSH_TG_NAME
touch PLSH_TG_WHEN-$PLSH_TG_WHEN
touch PLSH_TG_LEVEL-$PLSH_TG_LEVEL
touch PLSH_TG_OP-$PLSH_TG_OP
touch PLSH_TG_TABLE_NAME-$PLSH_TG_TABLE_NAME
touch PLSH_TG_TABLE_SCHEMA-$PLSH_TG_TABLE_SCHEMA
touch new-$new.x
#touch "arg-0-'$0'"
touch "arg-1-'$1'"
touch "arg-2-'$2'"
touch "arg-3-'$3'"
touch "arg-4-'$4'"

for arg do
    touch "Arg is '$arg'"
done

exit 0
$$ LANGUAGE plsh;

You create a before-insert-trigger with the following SQL-Statement

CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttest2
    FOR EACH ROW EXECUTE PROCEDURE TriggerTest(new);

I hope this helps anybody else who is looking for a similar solution for his problem.

Stelzi79
  • 585
  • 3
  • 12