0

I want to listen to every transaction that is recorded in the database And I noticed that there is a concept in Postgres under the title LISTEN and NOTIFY (Of course, I am not sure that this is the right way)

Now I want to write a trigger that sends a notification to the channel when any operation occurs in any table

Is it possible?

My way is right?

thanks

Postgres TRIGGER to call NOTIFY with a JSON payload

mostafa hosseini
  • 341
  • 3
  • 11
  • 2
    It surely is possible. You may have a look [here](https://github.com/stefanov-sm/Postgres-generic-audit-trigger) for a close example. Instead of inserting into an audit table you may `perform pg_notify('the_channel_name', to_json(new)::text)` or similar. [This](https://stackoverflow.com/questions/74847675/create-notify-trigger-for-specific-json-value-in-inserted-row/74848375#74848375) SO thread is related to your question too so that you can figure out what is relevant for your case. – Stefanov.sm Dec 20 '22 at 16:02
  • I want to write a service like Debezium https://debezium.io/ Do you think I should use this? https://datacater.io/blog/2021-09-02/postgresql-cdc-complete-guide.html#cdc-logical-replication – mostafa hosseini Dec 21 '22 at 12:19
  • 1
    I have not tried CDC using logical replication but it does look to me to be the a very good and powerful possibility. Might be a lot better than the trigger approach that I commented first. – Stefanov.sm Dec 21 '22 at 12:25

0 Answers0