我需要獲取給定特定區域 ID 的所有兒童區域 ID。在 table 中areas，有一個parent_id參考areastable 本身的欄位。現在可以有多個級別，例如帶有 id 的區域1可以是帶有 id的區域的父級，帶有 id 的2區域2可以是帶有 id 的區域的父級3等等。所以當我呼叫這個函式時，我需要某個區域 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);
uj5u.com熱心網友回復：
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
;
結果：
| 孩子們 |
|---|
| {2,3,492,774,920,922,923,1466,1467,1468,1469,1470,1471,1472,1473,1474,3043,3044,3075,3076} |
我們也可以以函式的形式做同樣的事情：
CREATE OR REPLACE FUNCTION get_all_children(start_id int)
RETURNS INT[]
LANGUAGE plpgsql
AS
$$
DECLARE
childrenRet INT[];
BEGIN
WITH RECURSIVE cte01 (children, lev) AS (
SELECT a1.id, 0 FROM areas AS a1 WHERE a1.parent_id = start_id 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 INTO childrenRet FROM cte01
;
RETURN childrenRet;
END;
$$;
更新的小提琴
例子：
SELECT get_all_children(1) as children;
結果：
| 孩子們 |
|---|
| {2,3,492,774,920,922,923,1466,1467,1468,1469,1470,1471,1472,1473,1474,3043,3044,3075,3076} |
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/383298.html
標籤：sql PostgreSQL的 递归 plpgsql
上一篇：Rspec不顯示郵件正文中的內容