sql - 确定路径的子计数

Question

我有一个表，其“路径”列有值，我想更新表的“child_count”列，以便获得以下输出。

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

我目前的解决方案——效率太低了——使用如下存储过程:

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;

有人知道做同样事情的单个 UPDATE 语句吗？

Answer 1

也许像这样的东西可以解决问题:

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