postgresql自定义函数实现三个数组存在相同数据,且在第四个数组中不存在的数据

-- 使用postgresql 语言写一个函数，实现以下功能：
-- 1 有管理权限用户数组、列表权限用户数组、查看权限用户数组、无权限用户数组四个用户数组
-- 2 当无权限用户数组存在用户数据时，如果管理权限用户数组，列表权限用户数组，查看权限用户数组中存在相同的用户数据，并且和无权限用户数组的用户数据不同，则返回其他三个数组中相同的数据
-- 3 举个例子： 无权限用户数组存在A、B用户数据，管理权限用户数组存在A、B、C用户，列表权限用户数组存在A、B、C用户，查看权限用户数组存在A、B、C用户，则返回C用户数据

CREATE OR REPLACE FUNCTION find_permission_conflicts(
    admin_users text[],
    manager_users text[],
    viewer_users text[],
    no_permission_users text[]
)
RETURNS text[] AS
$$
DECLARE
    conflicts text[];
    no_permission_set text[];
BEGIN
    -- 将无权限用户数组转换为集合
    no_permission_set := ARRAY(SELECT unnest(no_permission_users));

    -- 查找管理、列表和查看数组的交集
    conflicts := ARRAY(
        SELECT user_id
        FROM unnest(admin_users) AS user_id
        WHERE user_id = ANY(manager_users) AND user_id = ANY(viewer_users)
        EXCEPT
        SELECT unnest(no_permission_set)
    );

    RETURN conflicts;
END;
$$
LANGUAGE plpgsql;
-- 举例子
SELECT find_permission_conflicts(
      ARRAY['A', 'B', 'C', 'D'],
    ARRAY['A', 'B', 'C', 'D'],
    ARRAY['A', 'B', 'C' ,'D'],
    ARRAY['A', 'B']
)AS conflicts;

-- 实战
SELECT array_length( find_permission_conflicts(
    (SELECT array(select json_array_elements_text('{"users":["1685947296799670271","1685947296799670272","1685947296799670273","1685947296799670274"],"roles":[],"groups":[],"organizations":[]}'::json->'users'))),
    (SELECT array(select json_array_elements_text('{"users":["1685947296799670271","1685947296799670272","1685947296799670273","1685947296799670274"],"roles":[],"groups":[],"organizations":[]}'::json->'users'))),
    (SELECT array(select json_array_elements_text('{"users":["1685947296799670271","1685947296799670272","1685947296799670273","1685947296799670274"],"roles":[],"groups":[],"organizations":[]}'::json->'users'))),
    (SELECT array(select json_array_elements_text('{"users":["1685947296799670271","1685947296799670272"],"roles":[],"groups":[],"organizations":[]}'::json->'users')))
),1) AS conflicts;


SELECT find_permission_conflicts(
    (SELECT array(select json_array_elements_text('{"users":["1685947296799670271","1685947296799670272","1685947296799670273","1685947296799670274"],"roles":[],"groups":[],"organizations":[]}'::json->'users'))),
    (SELECT array(select json_array_elements_text('{"users":["1685947296799670271","1685947296799670272","1685947296799670273","1685947296799670274"],"roles":[],"groups":[],"organizations":[]}'::json->'users'))),
    (SELECT array(select json_array_elements_text('{"users":["1685947296799670271","1685947296799670272","1685947296799670273","1685947296799670274"],"roles":[],"groups":[],"organizations":[]}'::json->'users'))),
    (SELECT array(select json_array_elements_text('{"users":["1685947296799670271","1685947296799670272"],"roles":[],"groups":[],"organizations":[]}'::json->'users')))
) AS conflicts;