Hi! I am trying to see how I could use NOTIFY/LISTEN to watch changes of a complicated SELECT query which spans multiple tables. Ideally, I would like to leave to PostgreSQL to determine when some data (and which data) in the result of the SELECT query has changed. So I am thinking that creating a temporary view using that query could be a way, only if I would find a way to watch such view for changes somehow. But it seems this is not really possible. I looked into two mechanisms: - Logical replication. Instead of NOTIFY/LISTEN I could simply create a publication over a view and then subscribe to it. But it seems logical replication can be done only over base tables and not views. [1] - Using "after" trigger on the view to get notification when the view gets changed. I could even use transition relations to have information what changed. But sadly it seems that this is possible only if there is also INSTEAD OF trigger on the view. But I would like to get notification when the view has changed because underlying tables have changed, and not because of an UPDATE query on the view itself. Moreover, I do not really need writable views. [2] So I wonder if I am missing anything. Is there some other best practice how to get notifications when result of a query changes in real-time? And information what changed? How hard it would be to implement such triggers on a view for whenever a view changes? Is there a process to make a feature request? (Also, I have not really managed to get statement level "after" triggers to be run on a view for at all. Because if I rewrite a query with INSTEAD OF then triggers on those tables are triggered, not really view's. So not sure what is even expected use there.) [1] https://www.postgresql.org/docs/devel/logical-replication-restrictions.html [2] https://www.postgresql.org/docs/devel/trigger-definition.html Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
