How to update hierarchical data in the same table

Question

I have two tables. The first table contains some data changes by year and territory_id.

territory_id      year  count_sth
430000000         2015  130
431000000         2015  15
433200000         2015  75
433600000         2015  40
431000000         2014  13
433200000         2014  20
...

Second table contains relationship between territory_id in the form of parent-child.

territory_id      parent_id     desc_ru
430000000         0             'abc'
431000000         430000000     'sdf'    
433200000         430000000     'jkl'
433210000         433200000     'bnm'
...

I want to set the value of column count_sth (table1) as the sum of values of the childrens group by year. It should be updated automatically. Each time when we update count_sth, value of count_sth where territory_id is parent of some territory will be equal to sum of values count_sth of its childs. How to do it? I tried to use function returns trigger to update this (table1) table.

CREATE OR REPLACE FUNCTION table__byyear()
RETURNS trigger AS
$func$
  BEGIN

UPDATE table__byyear f
SET    count_sth= A.a1
    from (select b.year, b.sum(count_sth) as a1, t.parent_id
          from table__byyear b join admterr t
          on b.territory_id = t.territory_id 
             GROUP by b.year, t.parent_id) A
where f.territory_id = A.parent_id and f.year = A.year
AND   count_sth IS DISTINCT FROM A.a1;

RETURN NULL;
END
$func$  LANGUAGE plpgsql;


CREATE TRIGGER table__byyear_terr
BEFORE INSERT OR UPDATE ON table__byyear 
FOR EACH ROW EXECUTE PROCEDURE table__byyear();

But here, this UPDATE trigger runs another UPDATE on the same table, which will fire the trigger again, so we get infinite recursion. Point me in the right direction.

score 1 · Answer 1 · answered Nov 27 '15 at 09:28

You can use the following statement to update a row and all the parent rows at once:

with recursive hier as (
  -- a row
  select t.territory_id, 
         t.parent_id,
         b.year
  from   table__byyear b,
         admterr t
  where  t.territory_id = 433210000 --<< change me
  and    b.year = 2015              --<< change me
  and    t.territory_id = b.territory_id
  union all
  -- parent rows
  select t.territory_id,
         t.parent_id,
         prev.year
  from   hier prev,
         admterr t
  where  t.territory_id = prev.parent_id
)
update table__byyear
set    count_sth = count_sth + 100 --<< change me
where  (territory_id, year) in (select territory_id, year from hier);

Given this statement, you may want to create a function...

create function update_hier(
  territory_id integer,
  year integer,
  delta numeric
)
returns void
as $$
  with ...
  update table__byyear
  set    count_sth = count_sth + $3
  where  (territory_id, year) in (select territory_id, year from hier);
$$ language sql;

...and even create a view with instead-of-update trigger to call this function.

How to update hierarchical data in the same table

1 Answers1