I have a tree table. I need to ensure that when inserting or updating a record, the sumvalue field of all parents is updated - this is the sum of all child elements. I wrote a trigger (even a query is enough to demonstrate), but the values are summed up incorrectly.
create table tree (
id bigserial primary key,
parent_id bigint,
title text,
sumvalue bigint not null default 0,
constraint fk_parent_id foreign key (parent_id)
references tree(id)
on delete restrict
on update restrict
);
create or replace function update_value_trigger_proc() returns trigger as $$
begin
update tree
set sumvalue = (
with recursive tmp_tree as (
select id,
parent_id,
sumvalue
from tree
where id = new.id -- << the id of the "base" node
union all
select p.id,
p.parent_id,
p.sumvalue
from tree p
join tmp_tree t on t.id = p.parent_id
)
select coalesce(sum(sumvalue), 0)::bigint as sumvalue_sum
from tree
)
from tree t, (with recursive c as (
select
new.id as parent_id,
cast(0 as bigint) as lvl
union all
select sa.parent_id, c.lvl-1
from tree as sa
join c ON c.parent_id = sa.id
)
select c.lvl, tree.*
from tree, c
where tree.id = c.parent_id and c.parent_id != new.id
order by lvl desc) as ancestors
where tree.id = ancestors.id;
return null;
end
$$ language plpgsql;
create or replace trigger update_tree_trigger after insert or update
on tree
for each row
execute procedure update_tree_trigger_proc();
insert into tree(title, parent_id, sumvalue) values
('A', null, 0),
('AA', 1, 0),
('AB', 1, 0),
('AC', 1, 0),
('AAA', 2, 0),
('AAAA', 5, 5),
('AAAB', 5, 5),
('AAAC', 5, 5),
('AAB', 2, 5),
('AAC', 2, 5),
('ABA', 3, 5),
('ABB', 3, 5),
('ABC', 3, 5),
('ACA', 4, 5),
('ACB', 4, 5),
('ACC', 4, 5)
;
Result:
id parent_id title sumvalue
6 5 AAAA 5
7 5 AAAB 5
8 5 AAAC 5
9 2 AAB 5
10 2 AAC 5
11 3 ABA 5
12 3 ABB 5
13 3 ABC 5
14 4 ACA 5
15 4 ACB 5
16 4 ACC 5
5 2 AAA 179410
2 1 AA 8433535
3 1 AB 299839815
4 1 AC 10718331525
1 null A 21745115865
Expected result:
id parent_id title sumvalue
6 5 AAAA 5
7 5 AAAB 5
8 5 AAAC 5
9 2 AAB 5
10 2 AAC 5
11 3 ABA 5
12 3 ABB 5
13 3 ABC 5
14 4 ACA 5
15 4 ACB 5
16 4 ACC 5
5 2 AAA 15
2 1 AA 25
3 1 AB 15
4 1 AC 15
1 null A 55
Demo: https://dbfiddle.uk/Z4ZKz-wL
UPDATE:
I fixed trigger to:
create or replace function update_value_trigger_proc() returns trigger as $$
begin
update tree
set sumvalue = (
select coalesce(sum(sumvalue), 0)::bigint as sumvalue_sum from tree where parent_id = new.id
)
from tree t, (with recursive c as (
select
new.id as parent_id,
cast(0 as bigint) as lvl
union all
select sa.parent_id, c.lvl-1
from tree as sa
join c ON c.parent_id = sa.id
)
select c.lvl, tree.*
from tree, c
where tree.id = c.parent_id and c.parent_id != new.id
order by lvl desc) as ancestors
where tree.id = ancestors.id;
return null;
end
$$ language plpgsql;
Because we need to sum only one level below (already summed before). Result now:
id parent_id title sumvalue
6 5 AAAA 5
7 5 AAAB 5
8 5 AAAC 5
9 2 AAB 5
10 2 AAC 5
11 3 ABA 5
12 3 ABB 5
13 3 ABC 5
14 4 ACA 5
15 4 ACB 5
16 4 ACC 5
5 2 AAA 0
2 1 AA 0
3 1 AB 0
4 1 AC 0
1 null A 15
