Determining child count of path

I have a table whose 'path' column has values and I would like to update the table's 'child_count' column so that I get the following output.

 path   | child_count 
--------+-------------
        |           5
 /a     |           3
 /a/a   |           0
 /a/b   |           1
 /a/b/c |           0
 /b     |           0

My present solution - which is way too inefficient - uses a stored procedure as follows:

CREATE FUNCTION child_count() RETURNS VOID AS $$
DECLARE
  parent VARCHAR; 
BEGIN   
  FOR parent IN
    SELECT path FROM my_table
  LOOP
    DECLARE
      tokens VARCHAR[] := REGEXP_SPLIT_TO_ARRAY(parent, '/');
      str VARCHAR := '';
    BEGIN
      FOR i IN 2..ARRAY_LENGTH(tokens, 1)
      LOOP
        UPDATE my_table
          SET child_count = child_count + 1
        WHERE path = str;
        str := str || '/' || tokens[开发者_Python百科i];     
      END LOOP;
    END;    
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Anyone knows of a single UPDATE statement that does the same thing?

Maybe something like this does the trick:

UPDATE my_table a
   SET child_count = SELECT count(path) FROM my_table b WHERE b.path LIKE a.path || '/%';

in TSQL:

update a
set a.child_count = (select count(1) - 1 from my_table as b where b.path like (a.path + '%'))
from my_table as a

Managing trees in sql is not a simple subject. I would recommend you trying to find a good library doing that. Counting direct and indirect descendants is only a small part of what you might want to do with your tree. And storing your "children count" in the database is maybe not the best idea, since the tree can change in the future.

I'm not sure it will fit your development environment, but there is a nice gem for rails called ancestry, that does that just fine (it uses a materialized path as well). But that's ruby, and if I understand you correctly, you want to implement that in postgresql. I would then recommend you buying the book Trees and hierarchies in SQL for Smarties, of Joe Celko.

Update:

Here is a postgresql additional module you might want to have a look at: http://www.postgresql.org/docs/current/static/ltree.html