Message-ID: <42F01AAF.6040903@actarg.com>
Date: Tue, 02 Aug 2005 19:15:27 -0600
From: Kyle Bateman <kyle@actarg.com>
User-Agent: Mozilla Thunderbird 0.9 (X11/20041127)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Trigger on select?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hey, anyone know if it is possible to fire a trigger before a select?  
I'm considering creating some tables which contain data summarized from 
other tables as kind of a cache mechanism.  The hope is I can speed up 
some queries that get executed a lot (and are kind of slow) by 
maintaining data (like sums of big columns, for example).  I was all 
ready to go and then I discovered that trigger-before-select is not 
supported.  (Is it in any DB?)

The idea is this:

Any time I execute a query that would access the summary data, the 
"before select" trigger fires and goes out and builds any summary data 
missing from the summary table.  When I do an insert,update,delete on 
the primary data table, another trigger fires that removes the 
applicable data from the summary table.  This way, I only cache the 
information I need in the summary table, right before I need it.  But it 
can stay there as long as the base information doesn't change so I don't 
have to redo the expensive operation of summarizing it any more often 
than necessary.  Its kind of like an index in a way, but it is not 
maintained at insert/update time.  Rather, it is updated as it is needed.

Anyone have any ideas about how I can accomplish this?

Kyle