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

On Fri, Jan 11, 2002 at 03:11:16PM -0600, i whined about:
> > the amounts are right for _acct.id = 2, but should be zero for
> > both of the others. arggh!

then Tom Lane <tgl@sss.pgh.pa.us> replied
> [ scratches head ]  This may represent a bug.  I'm too lazy today to
> whip up a test case based on your emails --- could I trouble you for
> a script that creates and loads the test tables?

here i had my hopes up. alas, it was only me and my bungles.

i think i found it-- this finally works (sure would like to have
the subquery use the WHERE, though...)

create rule acct_edit as
on update to acct
do instead (
    update _acct set
        code   = NEW.code,
        charge = p.charge,
        cost   = p.cost
    from (
        select
            sum(charge) as charge,
            sum(cost  ) as cost,
            acct_id
        from
            _prop
--        where
--            acct_id = OLD.id -- can't see *OLD* record here
        group by
            acct_id
    ) p
    where
        id        = OLD.id
        and
        p.acct_id = OLD.id;
);

i also tried creating a temp table within the rule, but didn't
get very far. (and BOY is it crucial to have an index on
_prop(acct_id)!)

--

as for where the problem sat:

    UPDATE _acct SET
        cost    = ppp.cost,
        charge  = ppp.charge
    FROM
        (SELECT
            acct_id,
            SUM(cost)   AS cost,
            SUM(charge) AS charge
        FROM
            _prop
        GROUP BY
            acct_id) ppp
    WHERE
        acct_id = ppp.acct_id;

the WHERE was wrong -- shoulda been

    WHERE
        id          -- _acct.id, in top query
        =
        ppp.acct_id -- subquery from _prop.acct_id
    ;

--
DEBIAN NEWBIE TIP #60 from Vineet Kumar <debian-user@virtual.doorstop.net>
:
Been hoping to find A FEATURE-PACKED MUTT CONFIG FILE? Check
out the ones at Sven Guckes' site:
    http://www.fefe.de/muttfaq/muttrc
There's also some great vimrc ideas there, too.

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