How to use NOTIFY with postgres to send previous record and new record Say we update a record in a database table in postgres. How can I set up NOTIFY, to send a json blob of the previous version of the record and the new version of the record? I have read about NOTIFY quite a few times and never figured out how to use it.
You can call it from a trigger and pass both OLD and NEW in the notify payload. But by default the payload accepts only a maximum of 8000 bytes total, so if your JSON BLOBs are bigger than 4000 bytes each you will need to insert at least the old value in a table and pass the key in the payload.
If you are attempting to build logical replication, you might be better off using those facilities in newer versions of Postgres. You should be planning to upgrade soon as you are now 4 major versions behind current.