Determining child count of path
- by sqlnewbie
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?
