PostgreSQL递归查询报错解决：MariaDB迁移后的递归引用问题

问题背景 #

原SQL语句在MariaDB中可正常执行，但在PostgreSQL中触发报错：

ERROR: recursive reference to query "userrolehierarchy" must not appear within its non-recursive term

需求为获取用户有权访问的sac.sys_table_column_pair，涉及用户-角色-组-权限的多层关联关系。

报错原因 #

PostgreSQL对递归CTE（WITH RECURSIVE）的语法约束更严格：必须严格划分为非递归初始项和递归迭代项两部分，仅允许在递归项中引用CTE自身。原SQL将多个非递归关联逻辑与递归逻辑通过UNION并列，违反了这一约束。

修复后的SQL语句 #

WITH RECURSIVE UserRoleHierarchy AS (
    -- 非递归初始集：所有直接/间接通过组关联的用户-角色映射
    SELECT DISTINCT sur.sys_role_id, sur.sys_user_id
    FROM sys_user_to_sys_role sur
    UNION
    SELECT DISTINCT sgrn.sys_role, sug.sys_user
    FROM sys_user_to_sys_group sug
    JOIN sys_group_to_sys_role sgrn ON sug.sys_group = sgrn.sys_group
    
    UNION ALL -- 连接非递归初始集与递归迭代集
    
    -- 递归迭代集：处理角色的层级关联（组父级角色、角色包含的子角色）
    SELECT DISTINCT sub.sys_role_id, sub.sys_user_id
    FROM (
        -- 逻辑1：角色所属组的父组关联的角色
        SELECT sgrn.sys_role AS sys_role_id, urh.sys_user_id
        FROM UserRoleHierarchy urh
        JOIN sys_group sg ON urh.sys_role_id = sg.sys_id
        JOIN sys_group_to_sys_role sgrn ON sg.sys_id = sgrn.sys_group
        WHERE sg.sys_group_parent_id IS NOT NULL
        
        UNION
        
        -- 逻辑2：当前角色包含的子角色
        SELECT surc.sys_contains_role_id AS sys_role_id, urh.sys_user_id
        FROM UserRoleHierarchy urh
        JOIN sys_user_role_contains surc ON urh.sys_role_id = surc.sys_role_id
    ) sub
)
SELECT DISTINCT
    sac.sys_id,
    sac.sys_operation,
    sac.sys_type,
    sac.sys_table_filter,
    sac.sys_script,
    sac.sys_description,
    sac.sys_table_column_pair,
    urh.sys_user_id,
    sr.sys_name1 AS role_name
FROM UserRoleHierarchy urh
JOIN sys_access_control_to_sys_role sactr ON urh.sys_role_id = sactr.sys_role_id
JOIN sys_access_control sac ON sactr.sys_access_control_id = sac.sys_id
JOIN sys_role sr ON urh.sys_role_id = sr.sys_id
ORDER BY
    sac.sys_table_column_pair,
    role_name;

关键调整说明 #

1. 拆分递归结构：明确将初始的用户-角色映射（直接关联、组关联）作为非递归部分，将需要迭代的层级关联逻辑（组父角色、角色包含子角色）合并为递归部分，通过UNION ALL连接两部分。
2. 统一列格式：在递归部分用子查询合并两种逻辑，确保输出列与非递归部分完全一致（sys_role_id和sys_user_id），符合PostgreSQL对递归CTE列一致性的要求。
3. 保留去重逻辑：在各子查询中保留DISTINCT，避免递归过程中产生重复的用户-角色映射。

内容的提问来源于stack exchange，提问作者user17281101