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[i];     
      END LOOP;
    END;    
  END LOOP;
END;
$$ LANGUAGE plpgsql;

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

Answers


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


Need Your Help

Android My application works on 2.3 fine but doesn't start even on 4.0

android android-4.0

How can I fix problems on Ice Cream Sandwich? also I am not good at reading logcat If anyone can guide me about how to read the actual problem on which line of my code I will be appreciate it..

How to set padding to columns in table layout dynamically

android android-layout tablelayout

I am trying to create a table layout with buttons dynamically. I am able to get the table layout with buttons. bt i need padding between buttons. How i can get programatically.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.