I want to execute a python script form a stored procedure in PostgreSql
I want something like this:
CREATE TRIGGER executePython
AFTER INSERT ON mytable
FOR EACH ROW
BEGIN
SYSTEM('python MyApp');
END;
Anyone can help me, please?
Add a comment
|
1 Answer
Are you able to install extensions? If so, you can use the the PL/Python extension
CREATE FUNCTION callMyApp()
RETURNS VOID
AS $$
import subprocess
subprocess.call(['/usr/bin/python', '/path/to/MyApp'])
$$ LANGUAGE plpythonu;
CREATE TRIGGER executePython
AFTER INSERT ON messages
FOR EACH ROW EXECUTE PROCEDURE callMyApp();
Note that this will run as the postgres user, so there may be permission issues.
There's also an extension called PL/SH, which might be of use, but doesn't appear to be an official package.
CREATE FUNCTION executePython()
RETURNS VOID
AS $$
#!/bin/sh
python /path/to/MyApp
$$
LANGUAGE plsh;
This blog post might be of interest to you, as well.
Also, this is nearly a duplicate of this thread entitled Run a shell script when a database record is written to postgres.
(Note: I haven't tested any of this code.)
