Re: Perform Function When The Rows Of A View Change

Quoth adam(dot)mailinglists(at)gmail(dot)com (Adam):
>
> 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?

Well, in principle you could calculate the next time the view will
change assuming the tables don't change first, and have a client sit
there sleeping until that time. For instance, the view you posted will
next change at

select min(t.ts)
from (
select view1.ts + '1 day'::interval "ts"
from view1
) t
where t.ts > now()

unless the tables view1 is based on change first.

Apart from the potential difficulty calculating that time, you would
need to be able to wake up that client early if one of the tables
changed. Setting triggers on the tables to send a notify to that client
(probably a different notify from the one that client then sends out to
other clients) should be sufficient, as long as that client uses
select(2) and PQconsumeInput to make sure it receives the notifications
in a timely fashion.

Ben