PL/PGSQL - 无法将 SELECT 语句中的array_agg赋值给变量

Question

我需要获取给定某个区域ID的所有子区域ID。在表areas中，有一个引用areas表本身的parent_id字段。现在可以有多个级别，例如具有 id 的区域1可以是具有 id2的区域的父级，而具有 id 的区域2可以是具有 id3的区域的父级，依此类推。因此，当我调用此函数时，我需要某个区域 id 的所有后代。

SELECT id, get_all_children(id) as children from areas where id = 1;

结果应该是

| id       | children       |
| -------- | -------------- |
| 1        | {2,3}          |

这是我到目前为止尝试过的

CREATE OR REPLACE FUNCTION get_all_children(ancestors INT[])
RETURNS INT[]
LANGUAGE plpgsql
AS
$$
DECLARE
childrenRet INT[];
BEGIN
SELECT
ARRAY_AGG(id::INT) INTO childrenRet
FROM areas
WHERE parent_id = ANY(ancestors);
IF childrenRet = '{}' THEN
RETURN childrenRet;
END IF;
RETURN ARRAY_CAT(get_all_children(childrenRet), childrenRet);

END;
$$;
SELECT id, get_all_children(id) as children from areas where id = 492;

但我得到的结果是

| id       | children       |
| -------- | -------------- |
| 492      | {3044}         |

我尝试将直接孩子归还为

CREATE OR REPLACE FUNCTION get_all_children(ancestors INT[])
RETURNS INT[]
LANGUAGE plpgsql
AS
$$
DECLARE
childrenRet INT[];
BEGIN
SELECT
ARRAY_AGG(id::INT) INTO childrenRet
FROM areas
WHERE parent_id = ANY(ancestors);
RETURN childrenRet;

END;
$$;
SELECT id, get_all_children(id) as children from areas where id = 492;

我仍然得到相同的结果。

现在我试过了

SELECT ARRAY_AGG(id) FROM areas WHERE parent_id = 492;

这是我得到的结果

array_agg
------------------------------------------------------------------------------------
{3044,3075,923,1470,774,1466,1473,1468,1467,3043,1471,1469,922,1472,3076,1474,920}

我猜问题就在这里，但我不知道如何处理它。

SELECT
ARRAY_AGG(id::INT) INTO childrenRet
FROM areas
WHERE parent_id = ANY(ancestors);

Answer 1

在递归找到所有子项后，您可以简单地将array_agg操作推迟到最后一个查询表达式，如下所示：

小提琴

-- Aggregate all of the recursively found descendants
WITH RECURSIVE cte01 (children, lev) AS (
SELECT a1.id, 0 FROM areas AS a1 WHERE a1.parent_id = 1 UNION ALL
SELECT a1.id, lev+1
FROM areas AS a1
JOIN cte01 AS c1
ON a1.parent_id = c1.children
)
SELECT array_agg(children ORDER BY lev, children) AS children FROM cte01
;

结果：

children
{2,3,492,774,920,922,923,1466,1467,1468,1469,1470,1471,1472,1473,1474,3043,3044,3075,3076}