Thread: Rule, update and aggregate functions

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

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

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


Why are you trying rules? Trigger function would be much more logical
choice. There are some limitations in RULE syntax that won't let you use
all SQL statements there, so trigger might be your only way out

Peter