Select query on a Hierarchy Table PostgreSQL

Question

I was trying to make a recursive select query in postgreSQL, I tried to read a few articles but I couldn't really get the solution out of them... either it's multi-table hierarchy or on another dialect to which then I can't translate to Postgres.

This is what I've come up with upon now but it seems to retrieve only one column so something is not quite working

WITH RECURSIVE authorities(role_id, role_name, fk_above_role) AS (
SELECT role_id,role_name,fk_above_role
FROM roles
WHERE role_id=1
UNION ALL
SELECT h.id_role, h.role_name, h.fk_above_role
FROM roles h
WHERE h.role_id= h.fk_above_role
)
SELECT * FROM authorities LIMIT 2;

I've set the limit to 2 for just to try. So basically let's say I've got a table made like so

role_id   |    role_name    |   fk_above_role
1              HRManager        null
2              Recruiter        1

In this case the recursive query I made, was to retrieve all the HRManager sub roles, how can I do this? I'll need to get eventually also the recruiter sub roles that are considered also HRManager sub_roles. What am I doing wrong?

user330315 · Accepted Answer · 2019-11-27 09:27:52Z

2

You need to join to the recursive CTE in the recursive part of the UNION:

WITH RECURSIVE authorities(role_id, role_name, fk_above_role) AS (
  SELECT role_id,role_name,fk_above_role
  FROM roles
  WHERE role_id = 1
  UNION ALL
  SELECT child.id_role, child.role_name, child.fk_above_role
  FROM roles child
     JOIN authorities parent on child.fk_above_rold = parent.role_id
)
SELECT * 
FROM authorities LIMIT 2;

answered Nov 27, 2019 at 9:27

user330315

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

1 Comment

L_Cleo Over a year ago

Thank you so much, I've been wasting already so much time on this problem. Saved my life, asa I can I will accept the answer

Collectives™ on Stack Overflow

Select query on a Hierarchy Table PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related