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
>
>
