Postgresql get updated by sql script, get notification in backend

Question

I'm using django-rest-framework as backend and postgresql as the database. The database might be changed by raw SQL script and I want to get notified in the backend when those changes happen so that I can notify different users about the change.

I've checked about posts like https://gist.github.com/pkese/2790749 for receiving notification in python and some SQL scripts for

CREATE TRIGGER rec_notify_trig AFTER INSERT OR UPDATE OR DELETE ON rec
   FOR EACH ROW EXECUTE PROCEDURE rec_notify_func()

My question is that I don't know how to hook them together in the django-rest-framework, like where should I put the SQL script, where to put the python settup so that I can connect them together. Any advice will be appreciated.

monkut · Accepted Answer · 2019-07-18 01:02:58Z

2

I would create an endpoint on the djangorestframework side to accept a notification.

Then, in your rec_notify_func() you can call out and hit your endpoint where you can perform any enduser notification necessary.

CREATE EXTENSION plpython3u; 
CREATE FUNCTION rec_notify_func(notification_endpoint_uri text) RETURNS text AS $$
  from urllib.request import urlopen
  data = urlopen(notification_endpoint_uri)
  return data.read()
$$ LANGUAGE plpython3u;

NOTE: You need to have plpython installed on the system in order to enable the extension.

On ubuntu something like this: sudo apt-get install postgresql-plpython3-9.6

answered Jul 18, 2019 at 1:02

monkut

44.1k26 gold badges133 silver badges161 bronze badges

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

Collectives™ on Stack Overflow

Postgresql get updated by sql script, get notification in backend

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related