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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
