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.