动态PostgreSQL函数返回1而非JSONB的修复求助

表结构概述 #

• company_users：存储公司所有用户，包含唯一键email、主键id、workspace_id。
• users_xyz（xyz为github、jira等服务名）：存储各服务用户，包含可空唯一字段email、可空唯一company_user_id、可空workspace_id，支持公司外用户加入服务。

需求目标 #

获取指定工作区的公司用户及其对应各服务的关联数据，返回符合指定结构的JSONB。

现有函数代码 #

CREATE OR REPLACE FUNCTION public.get_users_info(_workspace_id BIGINT, services TEXT[], _id BIGINT DEFAULT NULL)
RETURNS JSONB AS $$
DECLARE
  result_array JSONB;
  table_name TEXT;
  table_join_query TEXT := '';
  join_tables TEXT := '';
  union_query TEXT := '';
  union_tables TEXT := '';
  _service_name TEXT;
BEGIN
  -- Iterate over the services and build the join and union clauses
FOREACH _service_name IN ARRAY services
  LOOP
    table_name := 'users_' || _service_name;
    table_join_query := ' LEFT JOIN ' || table_name || ' ON cu.email = ' || table_name || '.email AND cu.workspace_id = ' || table_name || '.workspace_id';
    join_tables := join_tables || table_join_query;
    union_query := ' SELECT ''' || _service_name || ''' AS service_name,  account_id, user_state FROM ' || table_name || ' WHERE ' || table_name || '.email = cu.email AND ' || table_name || '.workspace_id = cu.workspace_id UNION ALL ';
    union_tables := union_tables || union_query;
  END LOOP;


  -- Remove the trailing UNION ALL from union_query
  union_query := LEFT(union_query, LENGTH(union_query) - 11);
  RAISE LOG 'Debug: union_query = %', union_query;
  -- Construct the main SQL query
  EXECUTE format('
    SELECT cu.id, cu.name, cu.email, cu.created_at, cu.active, (
      SELECT jsonb_agg(
        jsonb_build_object(
          ''service_name'', services.service_name,
          ''account_id'', services.account_id,
          ''user_state'', services.user_state
        )
      )
      FROM (
        %s
      ) AS services
    )
    FROM company_users cu %s
    WHERE cu.workspace_id = $1 AND ($2 IS NULL OR cu.id = $2) 
  ', union_query, join_tables) USING _workspace_id, _id INTO result_array;
  RAISE LOG 'Debug: result_array = %', result_array;
 RETURN result_array;
END;
$$ LANGUAGE plpgsql;

问题现象 #

函数返回1，而非期望的如下结构：

export interface UserData {
    id: number;
    name: string;
    userId: number;
    email: string;
    created_at: string;
    active: boolean;
    account_id: string;
    secondary_email?: string;
    services:{
          service_name: string;
          id: string;
          user_state: UserState;
        }[]
}

理想的JOIN与UNION语句 #

JOIN（实际无需使用，子查询已关联条件） #

LEFT JOIN users_github ug ON cu.email = ug.email AND cu.workspace_id = ug.workspace_id
LEFT JOIN users_asana ua ON cu.email = ua.email AND cu.workspace_id = ua.workspace_id
LEFT JOIN users_zoom uz ON cu.email = uz.email AND cu.workspace_id = uz.workspace_id

UNION #

SELECT 'github' AS service_name, ug.account_id, ug.user_state FROM users_github ug WHERE ug.email = cu.email AND ug.workspace_id = cu.workspace_id
UNION ALL
SELECT 'asana' AS service_name, ua.account_id, ua.user_state FROM users_asana ua WHERE ua.email = cu.email AND ua.workspace_id = cu.workspace_id

问题分析与修复方案 #

核心问题点 #

1. 变量误用：循环中union_query每次被覆盖为单个服务的查询语句，最终处理的不是累加后的所有服务的UNION语句（正确的累加变量是union_tables），导致子查询逻辑错误。
2. 结果打包错误：主查询直接返回多列字段，赋值给单个JSONB变量时，PostgreSQL会返回查询的行数（即匹配到1个用户时返回1），而非预期的JSON结构。
3. 冗余JOIN：主查询中的join_tables完全多余，子查询已经通过email和workspace_id关联了company_users，LEFT JOIN会导致不必要的表关联，甚至可能引发重复行问题。
4. 空值处理缺失：当用户无关联服务时，jsonb_agg会返回null，不符合数组结构的预期。

修复后的函数代码 #

CREATE OR REPLACE FUNCTION public.get_users_info(_workspace_id BIGINT, services TEXT[], _id BIGINT DEFAULT NULL)
RETURNS JSONB AS $$
DECLARE
  result_json JSONB;
  table_name TEXT;
  union_tables TEXT := '';
  _service_name TEXT;
BEGIN
  -- 构建所有服务的UNION ALL查询语句
  FOREACH _service_name IN ARRAY services
  LOOP
    table_name := 'users_' || _service_name;
    union_tables := union_tables || format(
      ' SELECT %L AS service_name, account_id, user_state FROM %I WHERE email = cu.email AND workspace_id = cu.workspace_id UNION ALL ',
      _service_name, table_name
    );
  END LOOP;

  -- 移除末尾的UNION ALL（如果有服务的话）
  IF union_tables != '' THEN
    union_tables := LEFT(union_tables, LENGTH(union_tables) - 11);
  ELSE
    -- 如果没有传入服务，返回空数组结构
    union_tables := ' SELECT NULL AS service_name, NULL AS account_id, NULL AS user_state WHERE FALSE ';
  END IF;

  -- 构建主查询，将结果打包为目标JSON结构
  EXECUTE format('
    SELECT jsonb_build_object(
      ''id'', cu.id,
      ''name'', cu.name,
      ''userId'', cu.id, -- 对应目标结构中的userId字段
      ''email'', cu.email,
      ''created_at'', cu.created_at::TEXT, -- 转为字符串匹配目标结构
      ''active'', cu.active,
      ''account_id'', (SELECT account_id FROM %I WHERE email = cu.email AND workspace_id = cu.workspace_id LIMIT 1), -- 取任意一个服务的account_id，可按需调整
      ''secondary_email'', NULL, -- 原表无此字段，设为NULL或根据实际逻辑填充
      ''services'', COALESCE(
        (
          SELECT jsonb_agg(
            jsonb_build_object(
              ''service_name'', s.service_name,
              ''id'', s.account_id, -- 目标结构中services的id对应account_id
              ''user_state'', s.user_state
            )
          )
          FROM (%s) AS s
        ), ''[]''::jsonb
      )
    )
    FROM company_users cu
    WHERE cu.workspace_id = $1 AND ($2 IS NULL OR cu.id = $2)
  ', table_name, union_tables) USING _workspace_id, _id INTO result_json;

  RETURN result_json;
END;
$$ LANGUAGE plpgsql;

修复说明 #

1. 修正变量逻辑：使用union_tables累加所有服务的UNION语句，确保子查询包含所有指定服务的数据。
2. 结果正确打包：用jsonb_build_object将所有字段组装成目标JSON结构，确保返回的是完整的JSONB对象而非行数。
3. 移除冗余JOIN：删除主查询中的LEFT JOIN逻辑，避免不必要的表关联。
4. 空值处理：用COALESCE(jsonb_agg(...), '[]'::jsonb)确保用户无关联服务时，services字段返回空数组而非null。
5. 字段映射：根据目标TypeScript结构，将cu.id映射为userId，将account_id映射为services中的id，并处理secondary_email字段（原表无此字段，设为NULL，可根据实际逻辑调整）。
6. 安全格式化：使用format函数的%L（字符串转义）和%I（标识符转义）避免SQL注入风险。

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