Use Postgres RECURSIVE to create a view of descendants

Question

I am trying to create a postgres view, containing a kind of hash map of an hierarchical structure.

My hierarchy table looks something like:

------------------
| id | parent_id |
------------------
|  1 |      null |
|  2 |         1 |
|  3 |         1 |
|  4 |         2 |

For performance reasons i need, in my program code, a hashed represantation

1 => [2,3,4]; 2 => [4]; ...

I can get all children of 1 by this query:

WITH RECURSIVE
    h_tree(id, label)
  AS (
    SELECT
      id,
      label
    FROM hierarchy
    WHERE parent_id = 10000
    UNION ALL
    (
      SELECT
        h.id,
        h.label
      FROM h_tree v, hierarchy h
      WHERE h.parent_id = v.id
    )
  )
SELECT array_to_json(ARRAY_AGG(id)) AS children_ids
FROM h_tree

This query gives me a (json encoded) list of all children of "10000".

My question:

How do I wrap this in a form where the output of this query is

---------------------------------
| hierarchy_id | children_ids[] |
---------------------------------
|            1 |        [2,3,4] |

Thanks a lot!

EDIT

I made an error in my question. I need all descendants, thanks @pozsfor pointing that out.

Furthermore my example was flawed, I edited it.

Perhaps use Select label as hierarchy_ID, string_agg(id,',') From h_tree group by label instead of array_to_jason to combine multiple rows into one for each hierarchy_ID/label? I'm a bit confused becuase I don't see where hierarchy_ID is coming from unless it's label. — xQbert
– xQbert, Commented May 17, 2017 at 14:32
You would need a RECURSIVE query for all descendants. For just children, all you need is a simple JOIN (or rather, a simple SELECT if you don't need anything other than the children's id). F.ex. rextester.com/VGTKE62744 — pozs
– pozs, Commented May 17, 2017 at 15:02
Thanks, but i didn't make myself clear in the question, i need all descendants, not only the children. — huehnerhose
– huehnerhose, Commented May 17, 2017 at 15:53

Laurenz Albe · Accepted Answer · 2017-05-22 08:04:08Z

4

WITH RECURSIVE children AS
   (SELECT parent_id AS id, id AS child_id
    FROM hierarchy
    WHERE parent_id IS NOT NULL
    UNION
    SELECT children.id, h.id
    FROM children
      JOIN hierarchy h
         ON h.parent_id = children.child_id
   )
SELECT hierarchy.id,
       string_agg(children.child_id::text, ',')
FROM hierarchy
   LEFT JOIN children USING (id)
GROUP BY hierarchy.id;

┌────┬────────────┐
│ id │ string_agg │
├────┼────────────┤
│  1 │ 2,3,4      │
│  2 │ 4          │
│  3 │            │
│  4 │            │
└────┴────────────┘
(4 rows)

edited May 22, 2017 at 8:04

answered May 17, 2017 at 15:02

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

huehnerhose Over a year ago

thanks for your answer, but I hat an error in my question. Hopefully you can have another look :)

Laurenz Albe Over a year ago

I rewrote the answer for the changed question.

huehnerhose Over a year ago

Many Thanks! Works perfectly! (And fast as hell)

Collectives™ on Stack Overflow

Use Postgres RECURSIVE to create a view of descendants

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related