在postgres 9.4中使用触发器执行外部程序 [英] execute external program with trigger in postgres 9.4

问题描述

我正在寻找更新或插入触发器后执行系统命令的方法。我希望postgres可以做到这一点。
有可能吗？

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();

预先感谢

推荐答案

Disclamer：：我与Andreas Fritsch合作从事同一项目。

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

我们已通过以下方式解决了此问题。

We have solved this problem in the following way.

有一个语言扩展名 PL / sh由Peter Eisentraut编码的PostgreSQL的过程语言处理程序正是我们所需要的。

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

您定义如下的shell脚本：

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;

您使用以下SQL语句创建插入前触发

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.

这篇关于在postgres 9.4中使用触发器执行外部程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！