собственно сам запрос

WITH RECURSIVE category_tree(id, name, depth) AS (
SELECT id, "sCaption", ARRAY[id]
FROM "BGT_Item"
WHERE "idParent" IS NULL
UNION ALL
SELECT "BGT_Item".id, "BGT_Item"."sCaption", depth || "BGT_Item".id
FROM category_tree
JOIN "BGT_Item" ON "BGT_Item"."idParent"=category_tree.id
WHERE NOT "BGT_Item".id = ANY(depth)
)

SELECT name,array_length(depth,1) as depth FROM category_tree ORDER BY id;