sql - Recursive query with sum in Postgres -

sql - Recursive query with sum in Postgres -

i must store lot of projects in db. each project can have kid projects. construction looks tree:

project / | \ projectchild1 projectchild2 [...] projectchild[n] / | projectchildofchild1 projectchildofchild2

the level of tree unknow. i'm thinking create table this:

table projects:

project_id id_unique primary_key project_name text project_value numeric project_parent id_unique

in case, column project_parent store id of parent project, if exists.

for application need retrieve total value of project, need sum values of every project kid , root project.

i know need utilize recursivity, don't know how in postgres.

this simplified version of @a_horse's correct answer (after give-and-take op in comments). works any (reasonably finite) number of levels in recursion.

total cost given project_id with recursive cte ( select project_id project_parent, project_value projects project_id = 1 -- come in id of base of operations project here ! union select p.project_id, p.project_value cte bring together projects p using (project_parent) ) select sum(project_value) total_value cte;

to total cost projects @ once:

for projects @ once with recursive cte ( select project_id, project_id project_parent, project_value projects project_parent null -- base of operations projects union select c.project_id, p.project_id, p.project_value cte c bring together projects p using (project_parent) ) select project_id, sum(project_value) total_value cte grouping 1 order 1;

sql fiddle (with right test case).

sql postgresql recursion common-table-expression