Perform Function When The Rows Of A View Change

From: Adam <adam(dot)mailinglists(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Perform Function When The Rows Of A View Change
Date: 2013-02-15 20:31:36
Message-ID: CABoSxBq2hsb38k+JZqtmhuE63=cibSVoO3DUvVaM=xOhA_db-Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Ben Morrow 2013-02-17 07:58:59 Volatile functions in WITH
Previous Message Alexander Gataric 2013-02-15 04:30:47 Re: Summing & Grouping in a Hierarchical Structure