pgsql的函数

1.string_agg.字段拼接在一起

select string_agg(role_id, ',') from portal_role

 

 

 

 

2.字符串分隔成table

select regexp_split_to_table('1,2,3', ',')

 

3.字符串分隔成数组

select regexp_split_to_array('1,2,3', ',')

 

4.树结构封装

WITH RECURSIVE T (user_id, user_name, cn_name) AS (
    SELECT
        sua.user_id,
        sua.user_name,
        sua.cn_name,
        sua.MANAGER_ACCOUNT_ID
    FROM
        SYSTEM_USER sua
    WHERE
        sua.user_id = 'cf2dcc613c544ab6a10aefe069a78dce'
    UNION ALL
        SELECT
            sub.user_id,
            sub.user_name,
            sub.cn_name,
            sub.MANAGER_ACCOUNT_ID
        FROM
            SYSTEM_USER sub
        JOIN T ON sub.MANAGER_ACCOUNT_ID = T .user_id
) SELECT
    user_id as userId,
    manager_account_id AS pId,
    user_name AS userName,
    cn_name
FROM
    T

 

 

 

5.逗号分隔的字符串转table。并使用in方法判断

        SELECT
            string_agg (r.role_name, ',') role_name
        FROM
            t_role r
        WHERE
                r.role_id IN (
                SELECT
                    UNNEST (string_to_array(#{roleIds}, ','))
            )