PostgreSQL：如何基于多组ID筛选同时归属所有组的用户

Got it, let's sort out this issue where your function is returning users in any of the input groups instead of all of them. The core problem with your current code is that the JOIN acts like an OR condition—any user who has at least one match with the input groups gets included. We need to adjust this to verify a user is present in every input group.

Key Improvements We'll Make #

• Remove the unnecessary temporary table (it adds overhead and isn't needed here)
• Use a grouping + count approach to enforce "all groups" matching
• Keep the solution efficient, even with large datasets or many input groups

Modified Function Code #

CREATE OR REPLACE FUNCTION fn_get_users_filtered(p_in_filters json)
RETURNS TABLE (user_id integer)
LANGUAGE plpgsql
AS $$
DECLARE
  filter_count integer;
BEGIN
  -- First, get the total number of unique group IDs in the input
  SELECT COUNT(DISTINCT j.code)
  INTO filter_count
  FROM json_to_recordset(p_in_filters->'data') as j(code int);

  -- Handle edge case: no filters provided (return all unique users, adjust if needed)
  IF filter_count = 0 THEN
    RETURN QUERY SELECT DISTINCT user_id FROM user_groups;
    RETURN;
  END IF;

  -- Return only users who belong to ALL input groups
  RETURN QUERY
  SELECT u.user_id
  FROM user_groups u
  JOIN json_to_recordset(p_in_filters->'data') as j(code int)
    ON u.group_id = j.code
  GROUP BY u.user_id
  HAVING COUNT(DISTINCT u.group_id) = filter_count;
END;
$$;

How This Works #

1. Count Input Groups: We first calculate how many unique group IDs are in the input JSON. This gives us the threshold a user must meet to be included.
2. Group & Validate: We join user_groups with the input groups, then group results by user_id. The HAVING clause checks if the number of unique groups the user belongs to (from the matched entries) equals the total input group count—this ensures the user is in every requested group.
3. Edge Case Handling: If no filters are passed, we return all unique users (you can replace this with RETURN; to return nothing if that's your preferred behavior).

Testing the Function #

• When passing '{"data": [{"code":1},{"code":2}]}', the function returns only user_id 1 (the only user in both groups 1 and 2).
• When passing '{"data": [{"code":2},{"code":4}]}', it returns user_id 1 and user_id 2 (both are in groups 2 and 4).

Efficiency Notes #

• Ditching the temporary table cuts down on disk I/O and memory usage, boosting performance.
• Using COUNT(DISTINCT) guards against duplicate entries in user_groups (even if your sample data doesn't have this, it's a safe practice).
• The JOIN + GROUP BY approach is optimized by PostgreSQL's query planner, especially if you have indexes on user_groups.user_id and user_groups.group_id.

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