Re: Database query: Notification about change?

On Tue, Dec 06, 2005 at 02:00:18 +0100,
Erik Sigra <sigra(at)kth(dot)se> wrote:
> Thanks for the hint! I thought I need to put the triggers on
> materialized views. If I would have to put them on the base tables, the
> application program would have to understand the query to figure out
> which tables it uses. I really do not want to make a parser for SQL.
> What if the user of the spreadsheet-like application enters a formula
> containing a query like this:

I would say, that this is a really odd thing to do. If you are going to allow
any old user written query, you are going to find it hard to update the cells
at the time the data changes. It might be better to have the user hit a
refresh button or to have timed refreshes.

> And I think it will be much more efficient to watch a materialized view
> than to rerun the query whenever one of the base tables change. That is
> what materialized view optimization is about, right? A typical query

Its pretty much the same thing. The materialized view is going to be
maintained by triggers. Postgres doesn't have a built in way to automate
this, so the application would have to parse the queries and create the
trigger functions.