Run external program on trigger in Postgresql

Question

When a new row is added to our Postgres database, I want to run a bash script. Is there any sort of system() function in PostgreSQL that would allow me to do this?

To give more context, when a new row is created in the database, I need to make a new file containing information from that row on an external server via SSH.

An alternate solution would just be to have a cron job run every 5 minutes or so and check for new rows, but then it might not catch if someone deletes a row, and then adds a row.

Ian Ling · Accepted Answer · 2015-09-01 17:47:39Z

2

It looks like there is no way to do this. The simplest way I can find is to use Postgres' NOTIFY command, and then write a daemon in some other language to listen for those notifications.

The cool thing about those notifications is that you can send a 'payload' with them, which can include anything you want, so you could send "ROW CREATED: ID# 455029" and then your daemon would know to SELECT that row to get more info about it. Neato

answered Sep 1, 2015 at 17:47

Ian Ling

3342 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

If you install an untrusted language (e.g PL/perlU), then you can do this (because an untrusted language lets you access any operating system call). But even if you can do it, doesn't mean it's a good idea to do do it. It will make your system very fragile and calling an external program in a trigger is going to make your database inserts really slow. Your solution with LISTEN/NOTIFY is much better.

Collectives™ on Stack Overflow

Run external program on trigger in Postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related