Perform Function When The Rows Of A View Change

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed

Perform Function When The Rows Of A View Change
2+ messages / 2 participants
[nested] [flat]

* Perform Function When The Rows Of A View Change
@ 2013-02-15 20:31  Adam <adam.mailinglists@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Adam @ 2013-02-15 20:31 UTC (permalink / raw)
  To: pgsql-sql

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

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Perform Function When The Rows Of A View Change
@ 2013-02-17 10:50  Ben Morrow <ben@morrow.me.uk>
  parent: Adam <adam.mailinglists@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Ben Morrow @ 2013-02-17 10:50 UTC (permalink / raw)
  To: adam.mailinglists@gmail.com; pgsql-sql

Quoth adam.mailinglists@gmail.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



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2013-02-17 10:50 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-02-15 20:31 Perform Function When The Rows Of A View Change Adam <adam.mailinglists@gmail.com>
2013-02-17 10:50 ` Ben Morrow <ben@morrow.me.uk>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox