pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRule for multiple entries
2+ messages / 2 participants
[nested] [flat]
* Rule for multiple entries
@ 2006-12-14 02:04 William Scott Jordan <wsjordan@brownpapertickets.com>
2006-12-14 02:53 ` Re: Rule for multiple entries Jim Buttafuoco <jim@contactbda.com>
0 siblings, 1 reply; 2+ messages in thread
From: William Scott Jordan @ 2006-12-14 02:04 UTC (permalink / raw)
To: pgsql-sql
Hi all!
I have a rule in place that is supposed to adjust a value in one
table based on how many rows are added or deleted to another table,
but I'm not getting the results that I hoped for. If a single sql
statement adds or deletes multiple entries, the rule still only
adjusts the value by one, when I want it to adjust the value by the
number of rows that were added or deleted.
To recreate this problem, first set up the schema:
CREATE TABLE counter (
counter_key int,
counter_value int
) ;
INSERT INTO counter (counter_key, counter_value) VALUES (1, 0) ;
CREATE TABLE entries (
entry_key int
) ;
CREATE RULE entries_insert AS ON INSERT TO entries DO UPDATE counter
SET counter_value = counter_value + 1 WHERE counter_key = 1 ;
CREATE RULE entries_delete AS ON DELETE TO entries DO UPDATE counter
SET counter_value = counter_value - 1 WHERE counter_key = 1 ;
Then enter some values:
INSERT INTO entries (entry_key) VALUES (1) ;
INSERT INTO entries (entry_key) VALUES (2) ;
INSERT INTO entries (entry_key) VALUES (3) ;
INSERT INTO entries (entry_key) VALUES (4) ;
INSERT INTO entries (entry_key) VALUES (5) ;
At this point the counter table should show a counter_value of 5,
because there are 5 entries in the entries table. Now, if you delete
3 of those entries, the counter table should show a value of 2, but
instead it only counts it as a single transaction.
DELETE FROM entries WHERE entry_key > 2 ;
Why doesn't the entries_delete rule hit for each row that's being
adjusted. Or, more importantly, how can I adjust the rule so that it
will count each row that's be added/deleted?
Any suggestions would be appreciated.
-Scott
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Rule for multiple entries
2006-12-14 02:04 Rule for multiple entries William Scott Jordan <wsjordan@brownpapertickets.com>
@ 2006-12-14 02:53 ` Jim Buttafuoco <jim@contactbda.com>
0 siblings, 0 replies; 2+ messages in thread
From: Jim Buttafuoco @ 2006-12-14 02:53 UTC (permalink / raw)
To: 'William Scott Jordan' <wsjordan@brownpapertickets.com>; pgsql-sql
Use a trigger instead, the rule is only run once per insert/update/delete
while the trigger is run for each row.
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of William Scott Jordan
Sent: Wednesday, December 13, 2006 9:05 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Rule for multiple entries
Hi all!
I have a rule in place that is supposed to adjust a value in one
table based on how many rows are added or deleted to another table,
but I'm not getting the results that I hoped for. If a single sql
statement adds or deletes multiple entries, the rule still only
adjusts the value by one, when I want it to adjust the value by the
number of rows that were added or deleted.
To recreate this problem, first set up the schema:
CREATE TABLE counter (
counter_key int,
counter_value int
) ;
INSERT INTO counter (counter_key, counter_value) VALUES (1, 0) ;
CREATE TABLE entries (
entry_key int
) ;
CREATE RULE entries_insert AS ON INSERT TO entries DO UPDATE counter
SET counter_value = counter_value + 1 WHERE counter_key = 1 ;
CREATE RULE entries_delete AS ON DELETE TO entries DO UPDATE counter
SET counter_value = counter_value - 1 WHERE counter_key = 1 ;
Then enter some values:
INSERT INTO entries (entry_key) VALUES (1) ;
INSERT INTO entries (entry_key) VALUES (2) ;
INSERT INTO entries (entry_key) VALUES (3) ;
INSERT INTO entries (entry_key) VALUES (4) ;
INSERT INTO entries (entry_key) VALUES (5) ;
At this point the counter table should show a counter_value of 5,
because there are 5 entries in the entries table. Now, if you delete
3 of those entries, the counter table should show a value of 2, but
instead it only counts it as a single transaction.
DELETE FROM entries WHERE entry_key > 2 ;
Why doesn't the entries_delete rule hit for each row that's being
adjusted. Or, more importantly, how can I adjust the rule so that it
will count each row that's be added/deleted?
Any suggestions would be appreciated.
-Scott
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-12-14 02:53 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-14 02:04 Rule for multiple entries William Scott Jordan <wsjordan@brownpapertickets.com>
2006-12-14 02:53 ` Jim Buttafuoco <jim@contactbda.com>
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