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?