Hi! On Thu, Dec 20, 2018 at 8:11 AM Mike Rylander <mrylander@xxxxxxxxx> wrote: > If, as I assume, the FROM clause of the SELECT is essentially static, > just with varying WHERE conditions, you could just use an AFTER > INSERT/UPDATE/DELETE trigger on each of the tables involved to send a > NOTIFY whenever data that is exposed by the SELECT is changed. You mean that for every SQL query I am making to a database, I would manually determine which tables are involved and then setup triggers with NOTIFY that the SELECT might have changed? I am trying to see if this could be something I could abstract out that it would be done automatically for any query. I have looked into using EXPLAIN to get a list of tables involved in a query, but the issue is that it look non-trivial to determine what has really changed as a consequence of those tables changing. I would have to cache myself what was the result of a query and then myself do a diff? Are there any ways to do this inside PostgreSQL without having to cache results on the client and do it there? > You can deliver a payload, such as the table name, primary key value and, > with a little work, even a list of fields that were modified as a JSON > blob, and let the application do whatever needs to be done to react > the the changes -- issue other queries, etc. But this would mean that I would have to know how changes on involved tables influence query results. I would like to not have to do SQL query parsing and understanding on the client. So ideally, I would get information directly from PostgreSQL. For me, an API where I could do AFTER UPDATE trigger on FOR EACH ROW on a view would be perfect. In that trigger I could get information which rows of the view changed and then use NOTIFY to inform the client. Or even use transition relations to get old and new state in the case FOR EACH STATEMENT (but then I would still have to diff it probably myself). And view could represent any query, without me having to try to understand and parse it. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
