pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedStatement triggers 7.4 NEW/OLD
4+ messages / 3 participants
[nested] [flat]
* Statement triggers 7.4 NEW/OLD
@ 2003-04-24 11:41 Andreas Pflug <Andreas.Pflug@web.de>
0 siblings, 1 reply; 4+ messages in thread
From: Andreas Pflug @ 2003-04-24 11:41 UTC (permalink / raw)
To: pgsql-sql
How can I address the rows affected by an insert/update/delete statement
in pl/pgsql?
The doc says that NEW and OLD will be NULL for statement triggers, which
makes sense because NEW and OLD are of type RECORD, capable of holding
only a single row.
So where's the beef?
Regards,
Andreas
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Statement triggers 7.4 NEW/OLD
@ 2003-04-26 02:32 Jan Wieck <JanWieck@Yahoo.com>
parent: Andreas Pflug <Andreas.Pflug@web.de>
0 siblings, 1 reply; 4+ messages in thread
From: Jan Wieck @ 2003-04-26 02:32 UTC (permalink / raw)
To: Andreas Pflug <Andreas.Pflug@web.de>; +Cc: pgsql-sql
Andreas Pflug wrote:
>
> How can I address the rows affected by an insert/update/delete statement
> in pl/pgsql?
> The doc says that NEW and OLD will be NULL for statement triggers, which
> makes sense because NEW and OLD are of type RECORD, capable of holding
> only a single row.
> So where's the beef?
IMHO statement triggers instead should have access to the NEW and OLD
rows through some mechanism. I can think of NEW and OLD being reference
cursors in the case of statement triggers, and the trigger then can
FETCH the rows from there.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Statement triggers 7.4 NEW/OLD
@ 2003-04-26 04:20 Josh Berkus <josh@agliodbs.com>
parent: Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Josh Berkus @ 2003-04-26 04:20 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; Andreas Pflug <Andreas.Pflug@web.de>; +Cc: pgsql-sql
Jan,
> IMHO statement triggers instead should have access to the NEW and OLD
> rows through some mechanism. I can think of NEW and OLD being reference
> cursors in the case of statement triggers, and the trigger then can
> FETCH the rows from there.
Yeah; how else could it possibly work?
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Statement triggers 7.4 NEW/OLD
@ 2003-04-26 08:50 Andreas Pflug <Andreas.Pflug@web.de>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 4+ messages in thread
From: Andreas Pflug @ 2003-04-26 08:50 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; pgsql-sql
Josh Berkus wrote:
>>IMHO statement triggers instead should have access to the NEW and OLD
>>rows through some mechanism. I can think of NEW and OLD being reference
>>cursors in the case of statement triggers, and the trigger then can
>>FETCH the rows from there.
>>
>>
>
>Yeah; how else could it possibly work?
>
>
>
Why do it easy if you can have it difficult? ;-) If I want to perform
row by row actions I use a row level trigger!
Statement triggers need to work like this:
Instead of
NEW.foo := 1;
you code
UPDATE NEW SET foo=1;
If the statement trigger doesn't offer a complete SETOF RECORD, It's
pretty useless.
In my case, updates and deletes on a table could possibly touch
thousands of rows at the same time, and the trigger will update
aggregated values in another table, and insert or delete a third one
with 3-4 additional statements. This is pretty fast if implemented as
true statement trigger, but will possibly blow the system if done row by
row. Imagine a single statement, that triggers ROW_COUNT*4 statements,
where ROW_COUNT can easily be 1000, 10000 or much more!
Regards,
Andreas
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-04-26 08:50 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-24 11:41 Statement triggers 7.4 NEW/OLD Andreas Pflug <Andreas.Pflug@web.de>
2003-04-26 02:32 ` Jan Wieck <JanWieck@Yahoo.com>
2003-04-26 04:20 ` Josh Berkus <josh@agliodbs.com>
2003-04-26 08:50 ` Andreas Pflug <Andreas.Pflug@web.de>
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