14

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Thit Lwin Oo
  • 3,388
  • 3
  • 20
  • 23

3 Answers3

15

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Is the PHP example blocking? And what I want to continuously listen? Not just for one event, but for every event? – CMCDragonkai May 26 '15 at 13:18
  • Apparently there is a queue of events that have not been listened to, so all one needs to do is rerun the listen query again after processing an event. – CMCDragonkai May 26 '15 at 13:19
  • Link to the C++ (libpqxx) interface for this: [http://pqxx.org/devprojects/libpqxx/doc/4.0/html/Reference/a00208.html](http://pqxx.org/devprojects/libpqxx/doc/4.0/html/Reference/a00208.html) – villapx Aug 23 '16 at 21:31
9

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

pmenke
  • 93
  • 1
  • 5
  • Hello, could you please explain why you have two paths in your arguments `/bin/touch` and `/tmp/created_by_postgres`? Do you think this would work to execute an external python or shell script? – Alexis.Rolland Aug 07 '18 at 05:43
  • Alright, I understand that `PROGRAM` can actually run any shell command... Would you happen to know if there is a handshake between the postgres function executing the `COPY` method and the shell command executed by `PROGRAM`? Meaning the postgres function would wait for the shell command to complete before moving to its next task. – Alexis.Rolland Aug 07 '18 at 06:17
  • With great power comes great responsibility. [The manual:](https://www.postgresql.org/docs/current/sql-copy.html#id-1.9.3.55.8) *"`COPY` naming a file or command is only allowed to database superusers or users who are granted one of the roles `pg_read_server_files`, `pg_write_server_files`, or `pg_execute_server_program`, since it allows reading or writing any file or running a program that the server has privileges to access."* – Erwin Brandstetter Feb 14 '23 at 22:25
1

You can execute external scripts from inside trigger function with an "untrusted" language, like plpythonu. More details here: https://www.postgresql.org/docs/current/plpython.html

Trigger function example:

CREATE FUNCTION execute_python_script()
RETURNS trigger
AS $$
begin

import subprocess
result = subprocess.run(['/path/to/your/bin/python', '/some_folder/some_sub_folder/script.py'], stdout=subprocess.PIPE, stderr=subprocess.PIPE)

end;
$$ 
LANGUAGE plpythonu;

Trigger example:

CREATE TRIGGER trigger_name
AFTER INSERT ON table
EXECUTE PROCEDURE execute_python_script();
CristianG
  • 21
  • 3