I think you need delete, update, and insert rules for warehouse.
Subtract out the old on delete & update. Add the new in update and
insert. Aggregates would be an incredible waste of processor time.
pgsql-user wrote:
Hi,
I got stuck with the following problem and strangely couldn't find
anything similar in the list:
Assume two tables:
table: warehouse, columns: id, incoming, outgoing
table: articles, columns: id, articles_in_stock
All I want is to create a rule, that everytime when articles leave or
get in the warehouse, the sum of articles is updated in articles database.
Tried :
CREATE RULE upd_articles AS ON UPDATE TO warehouse
DO ALSO UPDATE articles SET articles_in_stock =SUM(NEW.incoming)-SUM
(NEW.outgoing)
WHERE articles.id=OLD.id;
All I get is a "aggregate functions not allowed on update".
So I tried to wrap it in a SELECT:
CREATE RULE upd_articles AS ON UPDATE TO warehouse
DO ALSO UPDATE articles SET articles_in_stock =(SELECT SUM(NEW.incoming)-
SUM(NEW.outgoing)
FROM warehouse WHERE NEW.id=OLD.id)
WHERE articles.id=OLD.id;
with the same result.
What is the right way to achieve my goal? Or are rules the wrong
instrument for it?
Any help is kindly appreciated,
Thanks, Sebastian
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
