Nick Guenther wrote > Dear List, > > I am interested in replicating views of my data in real time to a > frontend visualizer. I've looked around, and it seems that most > applications in this direction write some ad-hoc json-formatter that > spits out exactly the columns it is interested in. I want something > more like Cubes[1], where a user (or at least, some javascript) can > say "I am interested in this slice of the world", and then get updates > to that slice, but unlike Cubes it must be near-real-time: I want to > hook events, not just redownload tables. > > > In principle, I am looking for some way to say > ``` > CREATE VIEW view13131 AS select (id, name, bank_account) from actors > where age > 22; > WATCH view13131; > ``` > > and get output to stdout like > ``` > .... > INSERT view13131 VALUES (241, "Mortimer", 131.09); > ... > INSERT view13131 VALUES (427, "Schezwan", 95.89); > UPDATE view13131 SET bank_account = 1017.12 WHERE id = 427; > DELETE FROM view13131 WHERE id = 92; > ... > ``` 9.4 - http://www.postgresql.org/docs/9.4/static/logicaldecoding.html Though I doubt your requirement to obtain only a subset of data is something that can be accommodated; especially in SQL form. And, yes, you can create triggers on views. http://www.postgresql.org/docs/9.4/static/sql-createtrigger.html But assuming your view is meant to be dynamic, covering only the subset of data you wish to watch, no one is going to be using your view to actually Insert/Update/Delete against the underlying table(s) so it will not do you any good to add triggers to it. You probably need to create some kind of materialized view and add a trigger to the relevant source table to maintain that view on an ongoing basis. Then remove the trigger (and optionally the materialized view) when you no longer care to watch. This: http://www.postgresql.org/docs/9.4/static/sql-creatematerializedview.html .... or roll your own. You can also use the full power of whatever programming languages you can install onto the server (e.g., pl/perl, pl/python, pl/sh) to link up with the outside world from inside one of those triggers... David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Watching-Views-tp5811927p5811931.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
