PostgreSQL WITH RECURSIVE 배열로 데이터 전달하기

WITH RECURSIVE category_hierarchy AS (
    -- Base case: Select root nodes
    SELECT 
        id,
        name,
        parent_id,
        ARRAY[name] AS path, -- Start hierarchy path as an array
		ARRAY[x,y,width,height] AS rectData, -- Start hierarchy path as an array
        1 AS level
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive case: Append child nodes to hierarchy
    SELECT 
        c.id,
        c.name,
        c.parent_id,
        ch.path || c.name, -- Append child name to the path
		ch.rectData || ARRAY[c.x,c.y,c.width,c.height] AS rectData, -- Append child name to the path
        ch.level + 1 AS level
    FROM categories c
    INNER JOIN category_hierarchy ch ON c.parent_id = ch.id
)

-- Flatten hierarchical path into separate columns
SELECT 
    id,
    parent_id,
    path[1] AS level1,
    path[2] AS level2,
    path[3] AS level3,
    path[4] AS level4,
    path[5] AS level5,
    rectData[1:4] AS rectData1, -- Extracts first row (1:1) and first 4 columns (1:4)
    rectData[5:8] AS rectData2, -- Extracts second row
    rectData[9:12] AS rectData3, -- Extracts third row
    rectData[13:16] AS rectData4, -- Extracts fourth row
    rectData[17:20] AS rectData5  -- Extracts fifth row
FROM category_hierarchy
ORDER BY path;