Count children of a parent in recursive CTE

I use this query to find comments by a parent id:

WITH RECURSIVE cte (id, content, path, parent_id, depth)  AS (
SELECT  id,
content,
array[id] AS path,
parent_id,
1 AS depth
FROM    comment
WHERE   id = 1

UNION ALL

SELECT  comment.id,
comment.content,
cte.path || comment.id,
comment.parent_id,
cte.depth + 1 AS depth
FROM    comment
JOIN cte ON comment.parent_id = cte.id
WHERE depth < 3
)
SELECT id, content, path, parent_id, depth FROM cte
ORDER BY path LIMIT 200;

It works well except I'm limiting the depth to 3. How can I find out if the current row has more children and if so get the count of them so I can display the number, ex. "Load X more".

In this fiddle, id 4 and 5 have children, so I want to count them.

To count all children, there is no way around following each path to the last element. You can then show rows down to some depth while counting the rest:

WITH RECURSIVE cte AS (
SELECT id, parent_id, content, array[id] AS path
FROM   comment
WHERE  id = 1

UNION ALL
SELECT c.id, c.parent_id, c.content, cte.path || c.id
FROM   comment c
JOIN   cte ON c.parent_id = cte.id
)
SELECT DISTINCT ON (path[1:2])
id, parent_id, content, path
, count(*) OVER (PARTITION BY path[1:2]) - 1 AS children
FROM   cte
ORDER  BY path[1:2], path <> path[1:2]
LIMIT  200;  -- arbitrary limit, unrelated to the question

SQL fiddle.

path[1:2] is the array slice from the 1st up to the 2nd element. Details in the manual.

This works for a depth of 2. Replace all occurrences of 1:2 with 1:3 to do the same for a depth of 3 etc.

The main feature is to "group" by the first n elements of the path. Apply the technique laid out in this related question:

The last ORDER BY expression path <> path[1:2] produces a boolean result that is only true for the root element of the group (where the whole path is equal to the leading elements), and FALSE sorts before TRUE. You could simply use path instead - not sure which performs better.