postgresql - Is it possible to make a recursive SQL query?

Question

I have a table similar to this:

CREATE TABLE example (
  id integer primary key,
  name char(200),
  parentid integer,
  value integer);

I can use the parentid field to arrange data into a tree structure.

Now here's the bit I can't work out. Given a parentid, is it possible to write an SQL statement to add up all the value fields under that parentid and recurse down the branch of the tree ?

UPDATE: I'm using posgreSQL so the fancy MS-SQL features are not available to me. In any case, I'd like this to be treated as a generic SQL question.

BTW, I'm very impressed to have 6 answers within 15 minutes of asking the question! Go stack overflow!

Answer 1

Here is an example script using common table expression:

with recursive sumthis(id, val) as (
    select id, value
    from example
    where id = :selectedid
    union all
    select C.id, C.value
    from sumthis P
    inner join example C on P.id = C.parentid
)
select sum(val) from sumthis

The script above creates a 'virtual' table called sumthis that has columns id and val. It is defined as the result of two selects merged with union all.

First select gets the root (where id = :selectedid).

Second select follows the children of the previous results iteratively until there is nothing to return.

The end result can then be processed like a normal table. In this case the val column is summed.