pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Trigger on select?
6+ messages / 6 participants
[nested] [flat]
* Trigger on select?
@ 2005-08-03 01:15 Kyle Bateman <kyle@actarg.com>
2005-08-03 00:54 ` Re: Trigger on select? Chris Travers <chris@travelamericas.com>
0 siblings, 1 reply; 6+ messages in thread
From: Kyle Bateman @ 2005-08-03 01:15 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Trigger on select?
2005-08-03 01:15 Trigger on select? Kyle Bateman <kyle@actarg.com>
@ 2005-08-03 00:54 ` Chris Travers <chris@travelamericas.com>
2005-08-03 09:23 ` Re: Trigger on select? Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 1 reply; 6+ messages in thread
From: Chris Travers @ 2005-08-03 00:54 UTC (permalink / raw)
To: Kyle Bateman <kyle@actarg.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Trigger on select?
2005-08-03 01:15 Trigger on select? Kyle Bateman <kyle@actarg.com>
2005-08-03 00:54 ` Re: Trigger on select? Chris Travers <chris@travelamericas.com>
@ 2005-08-03 09:23 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 0 replies; 6+ messages in thread
From: Karsten Hilbert @ 2005-08-03 09:23 UTC (permalink / raw)
To: pgsql-sql
On Tue, Aug 02, 2005 at 05:54:59PM -0700, Chris Travers 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.
And then it needs to be a materialized view of some sort
to achieve the caching he's after.
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
^ permalink raw reply [nested|flat] 6+ messages in thread
* Trigger on select :-(
@ 2010-03-02 02:10 Ray Madigan <ray@madigans.org>
2010-03-11 16:33 ` Re: Trigger on select :-( Garrett Murphy <gmurphy@lawlogix.com>
2010-03-12 06:56 ` Re: Trigger on select :-( Jaime Casanova <jcasanov@systemguards.com.ec>
0 siblings, 2 replies; 6+ messages in thread
From: Ray Madigan @ 2010-03-02 02:10 UTC (permalink / raw)
To: pgsql-sql
What I want is to have a trigger on select, but since I have searched the
archives I know that is not possible. I also read that view rules is the
technology is whats available. Having never used views before I can't seem
to get my mind to figure out what I need to do to solve my problem.
I have a table that defines a key that I will use in another table in a list
of items defined in the previous table.
The tables are
CREATE TABLE Foo ( INTEGER key not null primary key,
... characteristics for the Foo item );
CREATE TABLE Catalog ( INTEGER FooKey FOREIGN KEY,
... other catalog data );
The catalog will have rows that reference the elements in the Foo table.
I also have another table that references the Foo table
CREATE TABLE ToDo ( INTEGER FooKey FOREIGN KEY,
... other ToDo information.
What I want to do is when I do a SELECT on the Catalog and deliver the
result to the user, I want to check to see if the FooKey is in the users
ToDo table and set the value of a column isToDo to true or false depending
on if the FooKey exists in the ToDo table for the user. If I were building
a table I would use a trigger and select on the row in the ToDo table.
Please, all I want is an idea where to start, what I should read and I will
figure out how to do it.
Thanks in advance.
Attachments:
winmail.dat (application/ms-tnef, 2.2K) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Trigger on select :-(
2010-03-02 02:10 Trigger on select :-( Ray Madigan <ray@madigans.org>
@ 2010-03-11 16:33 ` Garrett Murphy <gmurphy@lawlogix.com>
1 sibling, 0 replies; 6+ messages in thread
From: Garrett Murphy @ 2010-03-11 16:33 UTC (permalink / raw)
To: pgsql-sql
Are you able to set up a SELECT query to get the result you want? If
you are, it's as simple as putting "CREATE VIEW viewname AS ", followed
by your query.
Garrett Murphy
_____________________________________________
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Ray Madigan
Sent: Monday, March 01, 2010 7:11 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Trigger on select :-(
What I want is to have a trigger on select, but since I have searched
the archives I know that is not possible. I also read that view rules
is the technology is whats available. Having never used views before I
can't seem to get my mind to figure out what I need to do to solve my
problem.
I have a table that defines a key that I will use in another table in a
list of items defined in the previous table.
The tables are
CREATE TABLE Foo ( INTEGER key not null primary key,
... characteristics for the Foo item
);
CREATE TABLE Catalog ( INTEGER FooKey FOREIGN KEY,
... other catalog data );
The catalog will have rows that reference the elements in the Foo table.
I also have another table that references the Foo table
CREATE TABLE ToDo ( INTEGER FooKey FOREIGN KEY,
... other ToDo information.
What I want to do is when I do a SELECT on the Catalog and deliver the
result to the user, I want to check to see if the FooKey is in the users
ToDo table and set the value of a column isToDo to true or false
depending on if the FooKey exists in the ToDo table for the user. If I
were building a table I would use a trigger and select on the row in the
ToDo table.
Please, all I want is an idea where to start, what I should read and I
will figure out how to do it.
Thanks in advance. << File: ATT4311246.txt >>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Trigger on select :-(
2010-03-02 02:10 Trigger on select :-( Ray Madigan <ray@madigans.org>
@ 2010-03-12 06:56 ` Jaime Casanova <jcasanov@systemguards.com.ec>
1 sibling, 0 replies; 6+ messages in thread
From: Jaime Casanova @ 2010-03-12 06:56 UTC (permalink / raw)
To: Ray Madigan <ray@madigans.org>; +Cc: pgsql-sql
On Mon, Mar 1, 2010 at 9:10 PM, Ray Madigan <ray@madigans.org> wrote:
>
> What I want to do is when I do a SELECT on the Catalog and deliver the
> result to the user, I want to check to see if the FooKey is in the users
> ToDo table and set the value of a column isToDo to true or false depending
> on if the FooKey exists in the ToDo table for the user.
you should do this when inserting data (with triggers of course)
you can't use rules because a RULE ON SELECT you only can use one
SELECT, no INSERT, UPDATE nor DELETE
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
AsesorÃa y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-03-12 06:56 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-03 01:15 Trigger on select? Kyle Bateman <kyle@actarg.com>
2005-08-03 00:54 ` Chris Travers <chris@travelamericas.com>
2005-08-03 09:23 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2010-03-02 02:10 Trigger on select :-( Ray Madigan <ray@madigans.org>
2010-03-11 16:33 ` Re: Trigger on select :-( Garrett Murphy <gmurphy@lawlogix.com>
2010-03-12 06:56 ` Re: Trigger on select :-( Jaime Casanova <jcasanov@systemguards.com.ec>
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