Postgres优化：将多表查询合并为单查询实现非空列值入文本数组

你可以通过单次查询services_connected表，结合条件判断直接构造连接服务数组，彻底替代原来的多次重复查询。以下是两种可行的优化方案：

方案一：使用UNION ALL结合array_agg #

将每个服务的判断逻辑放入子查询，通过UNION ALL收集符合条件的服务名，最后用array_agg聚合为数组：

-- 替换原来的多个IF EXISTS块
SELECT array_agg(service)
INTO connected
FROM (
    SELECT 'google' AS service FROM services_connected wt WHERE wt.workspace_id = w_id AND google IS NOT NULL
    UNION ALL
    SELECT 'github' AS service FROM services_connected wt WHERE wt.workspace_id = w_id AND github IS NOT NULL
    UNION ALL
    SELECT 'zoom' AS service FROM services_connected wt WHERE wt.workspace_id = w_id AND zoom IS NOT NULL
    UNION ALL
    SELECT 'jira' AS service FROM services_connected wt WHERE wt.workspace_id = w_id AND jira IS NOT NULL
    UNION ALL
    SELECT 'notion' AS service FROM services_connected wt WHERE wt.workspace_id = w_id AND notion IS NOT NULL
) AS services;

方案二：使用CASE构造数组并移除空值 #

直接构造包含条件判断的数组，再用array_remove过滤掉空值，写法更简洁：

-- 替换原来的多个IF EXISTS块
SELECT array_remove(
    ARRAY[
        CASE WHEN google IS NOT NULL THEN 'google' END,
        CASE WHEN github IS NOT NULL THEN 'github' END,
        CASE WHEN zoom IS NOT NULL THEN 'zoom' END,
        CASE WHEN jira IS NOT NULL THEN 'jira' END,
        CASE WHEN notion IS NOT NULL THEN 'notion' END
    ],
    NULL
) INTO connected
FROM services_connected wt
WHERE wt.workspace_id = w_id;

注意事项 #

• 如果services_connected表中同一个workspace_id对应多条记录，建议在查询中添加聚合函数（比如MAX()）确保逻辑正确，例如：

  SELECT array_remove(
      ARRAY[
          CASE WHEN MAX(google) IS NOT NULL THEN 'google' END,
          CASE WHEN MAX(github) IS NOT NULL THEN 'github' END,
          -- 其他服务同理
      ],
      NULL
  ) INTO connected
  FROM services_connected wt
  WHERE wt.workspace_id = w_id
  GROUP BY wt.workspace_id;
  

• 两种方案都只需要访问一次services_connected表，减少了数据库IO操作，提升了代码执行效率。

以下是完整的优化后代码：

DECLARE 
  email TEXT := ((current_setting('request.jwt.claims'::TEXT, TRUE))::JSON ->> 'email');
  workspace_name TEXT := ((current_setting('request.jwt.claims'::TEXT, TRUE))::JSON ->> 'hd');
  name TEXT := ((current_setting('request.jwt.claims'::TEXT, TRUE))::JSON ->> 'name');
  is_admin BOOLEAN := ((current_setting('request.jwt.claims'::TEXT, TRUE))::JSON ->> 'isAdmin')::BOOLEAN;
  onboarding_complete BOOLEAN;
  w_id bigint;
  connected TEXT[];
  result initial_user_data%ROWTYPE;
BEGIN
  SELECT workspaces.id, workspaces.onboarding_complete INTO w_id, onboarding_complete FROM workspaces WHERE domain_name = workspace_name;
  
  -- Assign values to the result record type
  result.email := email;
  result.name := name;
  result.workspace_name := workspace_name;
  result.is_admin := is_admin;
  result.onboarding_complete := onboarding_complete;
  result.workspace_id := w_id;
  
  -- 替换为单次查询生成服务数组
  SELECT array_remove(
      ARRAY[
          CASE WHEN google IS NOT NULL THEN 'google' END,
          CASE WHEN github IS NOT NULL THEN 'github' END,
          CASE WHEN zoom IS NOT NULL THEN 'zoom' END,
          CASE WHEN jira IS NOT NULL THEN 'jira' END,
          CASE WHEN notion IS NOT NULL THEN 'notion' END
      ],
      NULL
  ) INTO connected
  FROM services_connected wt
  WHERE wt.workspace_id = w_id;
  
  -- Assign the array of services to the result variable
  result.services_connected := connected;
  
  RETURN result;
END;

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