Hello,
I have a rather complicated view that is dependent upon multiple
tables, consisting of several windowing and aggregate functions, as
well as some time intervals. I would like to be able to perform a
function, i.e. pg_notify(), whenever a row is added, changed, or
removed from the view's result set.
I think the kicker is the fact that the set of results returned by the
view is dependent on the current time.
Here's a simplified version of what's going on:
CREATE VIEW view2 AS (SELECT view1.id, view1.ts FROM view1 WHERE view1.ts > (now() - '1 day'::interval)
);
As such, even if there are no inserts, deletes, or updates performed
on any of the tables that view1 depends on, the data contained in
view2 will change as a function of time (i.e. rows will disappear
from the view as time elapses). I have been unable to come up with a
trigger or rule that can detect this situation and provide the
notification I'm looking for.
I could just query the view over and over again, and look for changes
as they occur. But I'm hoping to find a more elegant (and less
resource-intensive) solution. Any ideas?
Thanks!
-Adam