Calling external program from PostgreSQL trigger

Question

I would like to execute external program (such as .net c# console) when PostgreSQL trigger is fired. How can I achieve it?

Answer 1

Since Postgres 9.3 there is a solution for invoking external programs. It is - for security reasons - limited to superusers and IMHO intended for exporting data, rather than doing a "notification on trigger":

COPY (SELECT 1) TO PROGRAM '/bin/touch /tmp/created_by_postgres'

If you want to actually export data to the invoked programm, you can provide any SELECT or a table name instead of SELECT 1. The query results will then be passed to the invoked program via its standard input.

You can find documentation of the feature in the Postgres docs: http://www.postgresql.org/docs/9.3/static/sql-copy.html

Answer 2

Postgres cannot normally run external programs for security reasons.

The typical solution is to use NOTIFY and have a daemon LISTEN to it. There are solutions for every major scripting language out there ...

• Examples for Java from @Craig: How to refresh JPA entities when backend database changes asynchronously?

• Relevant manual page for PHP.