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

On Thu, Jan 10, 2002 at 10:41:50AM -0500, Tom Lane wrote:
> Or ... wait a second.  How about
>
> update invoice set
>     bal = ss.bal,
>     cost = ss.cost,
>     charge = ss.charge
> from
>     (select
>         item_id,
>         sum(bal) as bal,
>         sum(cost) as cost,
>         sum(charge) as charge
>     from
>         line_item
>     group by
>         item_id) ss
> where ss.item_id = invoice.id;
>
> I haven't tried this but it seems like it should work.  Better check the
> quality of the generated plan though.  The temp table might be faster.

this is why we like having you around, Tom. you're sneaky in all
the right places. :)

i knew there was a solution in there somewhere...

    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;

this seems like it'd properly update all _acct records with
appropriate subtotals from child _prop records. BUT-- after
doing just that (above), given this data in then _prop table,

    db=# select id,acct_id,charge,cost from _prop;
     id | acct_id | charge | cost
    ----+---------+--------+-------
      3 |       4 |   0.00 |  0.00
      4 |       3 |   0.00 |  0.00
      5 |       2 | 210.98 |  7.25
      2 |       2 | 384.95 | 40.00
    (4 rows)

(then i do the update, above) i wind up with the following in
the _acct table:

    db=# select id,charge,cost from _acct;
     id | charge | cost
    ----+--------+-------
      3 | 595.93 | 47.25
      4 | 595.93 | 47.25
      2 | 595.93 | 47.25
    (3 rows)

the amounts are right for _acct.id = 2, but should be zero for
both of the others. arggh!

for completeness, i tried aliasing the main table, to no avail:

    UPDATE _acct a SET -- alias for subquery?
        cost       = ppp.cost,
        charge     = ppp.charge
    FROM
        (SELECT
            acct_id,
            SUM(cost) AS cost,
            SUM(charge) AS charge
        FROM
            _prop
        WHERE                 -- hmm?
            acct_id = a.id
        GROUP BY
            acct_id) ppp
    WHERE
        acct_id = ppp.acct_id;

    ERROR:  parser: parse error at or near "a"

i'm hoping to add this to a rule:

ON UPDATE TO acct DO INSTEAD (
    UPDATE _acct SET
        f1 = NEW.f1,
        f2 = NEW.f2,
        -- yada yada
        fN = NEW.fN
    WHERE
        id = OLD.id
    ;
    UPDATE _acct SET
        cost       = ppp.cost,
        charge     = ppp.charge
    FROM
        (SELECT
            acct_id,
            SUM(cost) AS cost,
            SUM(charge) AS charge
        FROM
            _prop
    --  WHERE
    --      acct_id = OLD.acct_id
        GROUP BY
            acct_id
        ) ppp
    WHERE
        id = OLD.acct_id;
);

i'm not having any luck with this, though.  i guess it's okay to
do the CREATE TEMP TABLE... DROP TABLE... within a rule, right?
:(

--
DEBIAN NEWBIE TIP #36 from Sean Quinlan <smq@gmx.co.uk>
:
Looking to CHANGE THE DEFAULT LS COLORS? It's simple: first,
    dircolors -p >~/.dircolors
and then edit the results to suit your tastes; finally, insert
    eval `dircolors -b ~/.dircolors`
in your ~/.bashrc. Next time you log in (or source ~/.bashrc)
your new colors will take effect.

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