From: | Mark Walker <furface(at)omnicode(dot)com> |
---|---|
To: | |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Rule, update and aggregate functions |
Date: | 2007-02-02 21:52:03 |
Message-ID: | 45C3B283.5000100@omnicode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | David Fetter | 2007-02-02 22:01:59 | Re: large document multiple regex |
Previous Message | pgsql-user | 2007-02-02 20:56:07 | Rule, update and aggregate functions |