On Monday 02 Jun 2003 4:47 pm, Samuel Tardieu wrote: > I have an "ips" table with 100000+ records, each record having a > "catid" field representing its category. "catid" references a row in a > table called "categories". > > For statistics purpose (generation of images with the evolution of the > number of rows by category), I am trying to reduce the load on the > database. > > The request I was doing at the beginning was: > > SELECT catid, COUNT(*) FROM ips GROUP BY catid; > > I then added a "nentries" field to the "categories" table with some > rules to maintain the counters up-to-date: > > CREATE RULE cat_ins AS [snip] > This works fine when inserting, deleting or updating one row in the > "ips" table. However, when i/d/u several rows at a time with the same > "catid", I only got an increment or decrement by one of the counter.
You want to use triggers not rules here (see the "server programming" and "procedural language" manual sections). A trigger will be fired for each row inserted/deleted/updated. Of course this means it will be fired 5000 times for 5000 updated rows. -- Richard Huxton ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
