Re: caching subtotals: update vs sum -- aaugh!

On Thu, 10 Jan 2002, will trillich wrote:

> or should i settle for something like
>
> select
> item_id,
> sum(bal) as bal,
> sum(cost) as cost,
> sum(charge) as charge
> into
> fooey
> from
> line_item
> group by
> item_id
> ;
> update invoice set
> bal = fooey.bal,
> cost = fooey.cost,
> charge = fooey.charge
> where fooey.item_id = id
> ;
> drop table fooey
> ;
>
> ...? seems a bit of the old "long-way-around"... surely there's
> a way--

A stored procedure sounds the way ahead to me. DO it with a trigger if you
want updates to happen automagically whenever the line_items are changed:

http://www.postgresql.org/idocs/index.php?plpgsql-trigger.html
http://www.postgresql.org/idocs/index.php?sql-createtrigger.html

--
Alaric B. Snell, Developer
abs(at)frontwire(dot)com