From: Chris Travers <chris@travelamericas.com>
To: Kyle Bateman <kyle@actarg.com>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Trigger on select?
Date: Tue, 02 Aug 2005 17:54:59 -0700
Message-ID: <42F015E3.8070308@travelamericas.com> (raw)
In-Reply-To: <42F01AAF.6040903@actarg.com>
References: <42F01AAF.6040903@actarg.com>
Kyle Bateman wrote:
> 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.
No. You must instead generate a view.
> 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?
something like create view wrapper_table as
select * from original table where (select pseudo_trigger_function())
IS TRUE;
The above example is off the top of my head. It may require some editing.
Best Wishes,
Chris Travers
Metatron Technology Consulting
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: chris@travelamericas.com, kyle@actarg.com
Subject: Re: Trigger on select?
In-Reply-To: <42F015E3.8070308@travelamericas.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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