I need to fetch all the children area ids given a certain area id. In the table areas, there's a parent_id field that references the areas table itself. Now there can be more than multiple levels for example area with id 1 can be a parent of area with id 2 and area with id 2 can be a parent of area with id 3 and so on. So when I call this function I need all the descendants of a certain area id.

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

The result should be

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

Here is what I have tried so far

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;

But the result I get is

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

I tried returning the direct children as

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;

I still get the same result.

Now I tried

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

This is the result I get

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

I'm guessing the problem is here but I can't figure out how to deal with it.

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

CodePudding user response：

You can simply postpone the array_agg operation to the last query expression, after having found all the children recursively, like this:

The fiddle

-- 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
;

The result:

We can do the same thing in the form of a function as well:

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;
$$;

Updated fiddle

Example:

SELECT get_all_children(1) as children;

The result: