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

to save a few cycles, i'm hoping to cache subtotals of
subsidiary records into the corresponding parent records -- but
i can't figure out how to update the parent table with the sums
of the child table fields:

    create table invoice (
        id serial,
        bal numeric(8,2)
        cost numeric(8,2),
        charge numeric(8,2),
    );
    create table line_item (
        id serial,
        item_id integer references invoice ( id ),
        bal numeric(8,2)
        cost numeric(8,2),
        charge numeric(8,2),
    );

selecting the sums is easy:

    select
        item_id,
        sum(bal) as bal,
        sum(cost) as cost,
        sum(charge) as charge
    from
        line_item
    group by
        item_id;

but i can't seem to get by cerebellum around how to flow all the
subtotals upstream -- these don't work:

    -- all totals wind up in ONE invoice record
    update invoice set
      bal = sum(line_item.bal),
      cost = sum(line_item.cost),
      charge = sum(line_item.charge)
    where line_item.item_id = set.id;

    -- syntax error at 'group'
    update invoice set
      bal = sum(line_item.bal),
      cost = sum(line_item.cost),
      charge = sum(line_item.charge)
    from line_item
    where line_item.item_id = set.id GROUP BY line_item.item_id;

the next one works, but not even within earshot of 'elegant'.
there's GOTTA be a slicker way, right?

    -- ridiculosity of redundancy but it limps into the right result
    update invoice set
      bal = (select sum(line_item.bal) where item_id = invoice.id),
      cost = (select sum(line_item.cost) where item_id = invoice.id),
      charge = (select sum(line_item.charge) where item_id = invoice.id)
    from line_item
    where line_item.item_id = set.id GROUP BY line_item.item_id;

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

--
DEBIAN NEWBIE TIP #110 from Dimitri Maziuk <dmaziuk@yola.bmrb.wisc.edu>
:
Here's how to TUNNEL SECURE X11 CONNECTIONS THROUGH SSH: on the
client, do this:
    client# export DISPLAY=client:0.0
    client# ssh -X server
then once you're logged in at the server, do:
    server# netscape &
The environment created at the server will include the DISPLAY
variable, so netscape (or whatever) will dialogue with the
client machine. (See "man ssh" for more.)

Also see http://newbieDoc.sourceForge.net/ ...