From: | pgsql-user <pgsql(at)vunet(dot)de> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Rule, update and aggregate functions |
Date: | 2007-02-02 20:56:07 |
Message-ID: | 20070202205607.467595846@mail.vunet.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Walker | 2007-02-02 21:52:03 | Re: Rule, update and aggregate functions |
Previous Message | Dave Page | 2007-02-02 19:11:02 | Re: Predicted lifespan of different PostgreSQL branches |