PostgreSQL: Run CTE and then Recursive query on the CTE

February 1, 2022

I am trying to run a recursive query in PostgreSQL. I need it to find all people that belong under a team. The query is the following:

WITH TAB AS (
SELECT id as user_id, team_id
from users

union

select user_id,team_id
from team_units
),
RECURSIVE recuree AS (
    SELECT user_id, team_id
    from TAB
    
    UNION
    
    SELECT E.user_id, E.team_id
    from TAB E
    JOIN recuree S on E.team_id = S.team_id)

SELECT * FROM recuree

I read that RECURSIVE must always followed from WITH.
But how can that happen since the TAB table doesn’t actually exist?

Thank you in advance.

>Solution :

You might need to use WITH RECURSIVE, so you can try to use a subquery to instead TAB

RECURSIVE need to use UNION ALL instead of UNION

WITH RECURSIVE recuree AS (
    SELECT user_id, team_id
    from (
        SELECT id as user_id, team_id
        from users
        union
        select user_id,team_id
        from team_units
    ) t1
    UNION ALL
    SELECT E.user_id, E.team_id
    from (
        SELECT id as user_id, team_id
        from users
        union
        select user_id,team_id
        from team_units
    ) E
    JOIN recuree S on E.team_id = S.team_id
)
SELECT * 
FROM recuree

otherwise, you can try to create a view first

CREATE VIEW v_TAB AS 
SELECT id as user_id, team_id
from users
union
select user_id,team_id
from team_units;

then do CTE RECURSIVE

WITH RECURSIVE recuree AS (
    SELECT user_id, team_id
    from v_TAB
    UNION ALL
    SELECT E.user_id, E.team_id
    from v_TAB E
    JOIN recuree S on E.team_id = S.team_id
)
SELECT * 
FROM recuree;