Таблицы geo_objects(id, parent_id) geo_object_attributes(geo_objects_id, name) geo_objects связана с таблицей geo_object_attributes geo_objects_id= id запрос, который рекурсивно находит все гео-объекты по дереву и выводит наименование всех гео-объектов дерева в одну строку postgresql

WITH RECURSIVE cte AS (
    WITH RECURSIVE tree AS (
        SELECT *, ARRAY[]::INTEGER[] AS path
        FROM geo_objects
        WHERE id = 1
        UNION ALL
        SELECT o.*, path || o.id
        FROM geo_objects o
        JOIN tree ON o.parent_id = tree.id
    )
    SELECT id, path
    FROM tree
    WHERE path IS NOT NULL
)
SELECT cte.id, geo_object_attributes.name
FROM cte
LEFT JOIN geo_object_attributes ON cte.id = geo_object_attributes.geo_object_id
ORDER BY cte.path;