插入到Postgres索引的SELECT上的数组中

Question

我正在使用Postgres CTE通过父子树递归。以下脚本将从根递归到叶，并附加到path末尾 (ARRAY(。

WITH RECURSIVE tree AS (
// get roots
SELECT entity_id, parent_id, ARRAY[entity_id] as path
FROM entity
WHERE parent_id is null
UNION ALL
// recursive step
SELECT c.entity_id, c.parent_id, path || c.entity_id
FROM tree t
JOIN entity c ON c.parent_id = t.entity_id
)
SELECT path 
FROM tree t 
WHERE entity_id NOT IN (SELECT DISTINCT parent_id FROM tree WHERE parent_id IS NOT NULL);

我不想在每个步骤都附加到path的末尾，而是想通过index列插入数组。是否可以在 SELECT 中执行此操作？

假设解决方案

SELECT  path[c.index] = c.entity_id
FROM tree t
JOIN entity c ON c.parent_id = t.entity_id

预期产出

| entity_id  | index       | parent_id |
|:-----------|------------:|:----------|
| a          |          3  | d         |
| b          |          5  | a         |
| c          |          1  | (none)    |
| d          |          2  | c         |

path = [c,d,a,(none),b]

Answer 1

该函数实现赋值arr[idx]:= elem并返回arr。如有必要，数组会自动扩展以容纳新元素。

create or replace function array_set_element(arr text[], elem text, idx int)
returns text[] language plpgsql as $$
begin
if cardinality(arr) < idx then
arr:= arr || array_fill(null::text, array[idx- cardinality(arr)]);
end if;
arr[idx]:= elem;
return arr;
end $$;

例：

select array_set_element('{a, b}'::text[], 'e', 5);
array_set_element 
------------------------
{a,b,NULL,NULL,e}
(1 row) 

在查询中使用该函数：

WITH RECURSIVE tree AS (
SELECT entity_id, parent_id, array_set_element('{}'::text[], entity_id, index) as path
FROM entity
WHERE parent_id is null
UNION ALL
SELECT c.entity_id, c.parent_id, array_set_element(path, c.entity_id, c.index)
FROM tree t
JOIN entity c ON c.parent_id = t.entity_id
)
SELECT path 
FROM tree t 
WHERE entity_id NOT IN (SELECT DISTINCT parent_id FROM tree WHERE parent_id IS NOT NULL);
path      
----------------
{c,d,a,NULL,b}
(1 row)