Re: caching subtotals: update vs sum -- aaugh! - Mailing list pgsql-general

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@frontwire.com