Re: caching subtotals: update vs sum -- aaugh!
От | will trillich |
---|---|
Тема | Re: caching subtotals: update vs sum -- aaugh! |
Дата | |
Msg-id | 20020111033508.A28156@serensoft.com обсуждение исходный текст |
Ответ на | Re: caching subtotals: update vs sum -- aaugh! (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | 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/ ...
В списке pgsql-general по дате отправления: