Date: Mon, 5 Dec 2005 14:40:53 -0600
From: Bruno Wolff III <bruno@wolff.to>
To: Erik Sigra <sigra@kth.se>
Cc: pgsql-sql@postgresql.org
Subject: Re: Database query: Notification about change?
Message-ID: <20051205204053.GB19356@wolff.to>
Mail-Followup-To: Bruno Wolff III <bruno@wolff.to>,
	Erik Sigra <sigra@kth.se>, pgsql-sql@postgresql.org
References: <4394855E.6060201@kth.se>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4394855E.6060201@kth.se>
User-Agent: Mutt/1.4.2.1i

On Mon, Dec 05, 2005 at 19:22:22 +0100,
  Erik Sigra <sigra@kth.se> wrote:
> Hi,
> I plan to develop an application that is somewhat like a spreadsheet
> with cells containing formulas. When a cell value is changed, things
> must be updated. But the formulas can contain database queries, which
> means that the cell has to be notified when the database changes in such 
> a way that the result of the query changes. How is this done? I would 
> really like to avoid recalculating the whole thing for each change to 
> the database.
> 
> I looked in my database book and read about materialized views and 
> triggers. It seems like I should do the following:
> 1. Make a materialized view from the query.
> 2. Add a trigger for changes to this view.
> 3. Make the trigger notify the application program when it is trigged.
> 
> Would this be possible? (I was planning to use Qt for application 
> programming and database access.)

Postgres provides the NOTIFY command
(http://developer.postgresql.org/docs/postgres/sql-notify.html)
and you could use that in appropiate triggers to let your application know
that it needs to refresh that values in at least some cells.
You shouldn't need to use materialized views though. Just put the triggers
on the base tables.