PostgreSQL返回SETOF类型函数报错：RETURN cannot have a parameter求解决

我编写了如下PostgreSQL PL/pgSQL代码，但运行时抛出错误：
RETURN cannot have a parameter in function returning set

代码如下：

DROP FUNCTION IF EXISTS public.get_services_connected_for_workspace;
DROP FUNCTION IF EXISTS public.get_services_connected_data;

CREATE OR REPLACE FUNCTION public.get_services_connected_for_workspace(_workspace_id BIGINT)
 RETURNS SETOF services_connected_response
 LANGUAGE plpgsql
AS $function$
DECLARE
  results services_connected_response[];
  services_connected_row services_connected_response;
BEGIN
  SELECT ARRAY[
    ('github', COALESCE(service_github.auto_invite, false), services_connected.service_github IS NOT NULL, false, COALESCE(service_github.last_synced, NULL), COALESCE(service_github.sync_status, NULL)),
    ('zoom', COALESCE(service_zoom.auto_invite, false), services_connected.service_zoom IS NOT NULL, false, COALESCE(service_zoom.last_synced, NULL), COALESCE(service_zoom.sync_status, NULL)),
    ('jira', COALESCE(service_jira.auto_invite, false), services_connected.service_jira IS NOT NULL, false, COALESCE(service_jira.last_synced, NULL), COALESCE(service_jira.sync_status, NULL)),
    ('docusign', COALESCE(service_docusign.auto_invite, false), services_connected.service_docusign IS NOT NULL, false, COALESCE(service_docusign.last_synced, NULL), COALESCE(service_docusign.sync_status, NULL)),
    ('asana', COALESCE(service_asana.auto_invite, false), services_connected.service_asana IS NOT NULL, false, COALESCE(service_asana.last_synced, NULL), COALESCE(service_asana.sync_status, NULL)),
    ('google', COALESCE(service_google.auto_invite, false), services_connected.service_google IS NOT NULL, true, COALESCE(service_google.last_synced, NULL), COALESCE(service_google.sync_status, NULL))
  ]
  INTO results
  FROM services_connected
  LEFT JOIN service_github ON services_connected.service_github = service_github.id
  LEFT JOIN service_jira ON services_connected.service_jira = service_jira.id
  LEFT JOIN service_zoom ON services_connected.service_zoom = service_zoom.id
  LEFT JOIN service_asana ON services_connected.service_asana = service_asana.id
  LEFT JOIN service_docusign ON services_connected.service_docusign = service_docusign.id
  LEFT JOIN service_google ON services_connected.service_google = service_google.id
  WHERE services_connected.workspace_id = _workspace_id;
  RETURN results;
END;
$function$;

CREATE OR REPLACE FUNCTION public.get_services_connected_data()
 RETURNS SETOF services_connected_response
 LANGUAGE plpgsql
AS $function$
DECLARE
  _workspace_id BIGINT := ((current_setting('request.jwt.claims'::text, TRUE))::JSON ->> 'workspace_id')::BIGINT;
BEGIN
   RETURN QUERY
    SELECT *
    FROM public.get_services_connected_for_workspace(_workspace_id);
END;
$function$;

错误原因 #

get_services_connected_for_workspace函数声明返回SETOF services_connected_response（即返回一组行数据），但你在函数末尾用了RETURN results;——这里的results是数组类型，而RETURN 直接带参数的语法只适用于返回单个值的函数，对于返回集合的函数，这种写法不符合PL/pgSQL的语法规则，因此触发了该错误。

修复方案 #

方案1：遍历数组返回每一行 #

保留数组变量，通过循环遍历数组中的每个元素，用RETURN NEXT逐个返回：

DROP FUNCTION IF EXISTS public.get_services_connected_for_workspace;
DROP FUNCTION IF EXISTS public.get_services_connected_data;

CREATE OR REPLACE FUNCTION public.get_services_connected_for_workspace(_workspace_id BIGINT)
 RETURNS SETOF services_connected_response
 LANGUAGE plpgsql
AS $function$
DECLARE
  results services_connected_response[];
  services_connected_row services_connected_response;
BEGIN
  SELECT ARRAY[
    ('github', COALESCE(service_github.auto_invite, false), services_connected.service_github IS NOT NULL, false, COALESCE(service_github.last_synced, NULL), COALESCE(service_github.sync_status, NULL)),
    ('zoom', COALESCE(service_zoom.auto_invite, false), services_connected.service_zoom IS NOT NULL, false, COALESCE(service_zoom.last_synced, NULL), COALESCE(service_zoom.sync_status, NULL)),
    ('jira', COALESCE(service_jira.auto_invite, false), services_connected.service_jira IS NOT NULL, false, COALESCE(service_jira.last_synced, NULL), COALESCE(service_jira.sync_status, NULL)),
    ('docusign', COALESCE(service_docusign.auto_invite, false), services_connected.service_docusign IS NOT NULL, false, COALESCE(service_docusign.last_synced, NULL), COALESCE(service_docusign.sync_status, NULL)),
    ('asana', COALESCE(service_asana.auto_invite, false), services_connected.service_asana IS NOT NULL, false, COALESCE(service_asana.last_synced, NULL), COALESCE(service_asana.sync_status, NULL)),
    ('google', COALESCE(service_google.auto_invite, false), services_connected.service_google IS NOT NULL, true, COALESCE(service_google.last_synced, NULL), COALESCE(service_google.sync_status, NULL))
  ]
  INTO results
  FROM services_connected
  LEFT JOIN service_github ON services_connected.service_github = service_github.id
  LEFT JOIN service_jira ON services_connected.service_jira = service_jira.id
  LEFT JOIN service_zoom ON services_connected.service_zoom = service_zoom.id
  LEFT JOIN service_asana ON services_connected.service_asana = service_asana.id
  LEFT JOIN service_docusign ON services_connected.service_docusign = service_docusign.id
  LEFT JOIN service_google ON services_connected.service_google = service_google.id
  WHERE services_connected.workspace_id = _workspace_id;

  -- 遍历数组，逐个返回元素
  FOREACH services_connected_row IN ARRAY results LOOP
    RETURN NEXT services_connected_row;
  END LOOP;
  
  RETURN; -- 无参数的RETURN用于结束函数
END;
$function$;

CREATE OR REPLACE FUNCTION public.get_services_connected_data()
 RETURNS SETOF services_connected_response
 LANGUAGE plpgsql
AS $function$
DECLARE
  _workspace_id BIGINT := ((current_setting('request.jwt.claims'::text, TRUE))::JSON ->> 'workspace_id')::BIGINT;
BEGIN
   RETURN QUERY
    SELECT *
    FROM public.get_services_connected_for_workspace(_workspace_id);
END;
$function$;

方案2：直接用RETURN QUERY返回行（更高效简洁） #

不需要先把结果转成数组，直接用RETURN QUERY返回构造的行数据，这是PL/pgSQL返回集合的推荐写法，避免数组转换的额外开销：

DROP FUNCTION IF EXISTS public.get_services_connected_for_workspace;
DROP FUNCTION IF EXISTS public.get_services_connected_data;

CREATE OR REPLACE FUNCTION public.get_services_connected_for_workspace(_workspace_id BIGINT)
 RETURNS SETOF services_connected_response
 LANGUAGE plpgsql
AS $function$
BEGIN
  RETURN QUERY
  SELECT 
    ('github', COALESCE(service_github.auto_invite, false), services_connected.service_github IS NOT NULL, false, COALESCE(service_github.last_synced, NULL), COALESCE(service_github.sync_status, NULL))::services_connected_response,
    ('zoom', COALESCE(service_zoom.auto_invite, false), services_connected.service_zoom IS NOT NULL, false, COALESCE(service_zoom.last_synced, NULL), COALESCE(service_zoom.sync_status, NULL))::services_connected_response,
    ('jira', COALESCE(service_jira.auto_invite, false), services_connected.service_jira IS NOT NULL, false, COALESCE(service_jira.last_synced, NULL), COALESCE(service_jira.sync_status, NULL))::services_connected_response,
    ('docusign', COALESCE(service_docusign.auto_invite, false), services_connected.service_docusign IS NOT NULL, false, COALESCE(service_docusign.last_synced, NULL), COALESCE(service_docusign.sync_status, NULL))::services_connected_response,
    ('asana', COALESCE(service_asana.auto_invite, false), services_connected.service_asana IS NOT NULL, false, COALESCE(service_asana.last_synced, NULL), COALESCE(service_asana.sync_status, NULL))::services_connected_response,
    ('google', COALESCE(service_google.auto_invite, false), services_connected.service_google IS NOT NULL, true, COALESCE(service_google.last_synced, NULL), COALESCE(service_google.sync_status, NULL))::services_connected_response
  FROM services_connected
  LEFT JOIN service_github ON services_connected.service_github = service_github.id
  LEFT JOIN service_jira ON services_connected.service_jira = service_jira.id
  LEFT JOIN service_zoom ON services_connected.service_zoom = service_zoom.id
  LEFT JOIN service_asana ON services_connected.service_asana = service_asana.id
  LEFT JOIN service_docusign ON services_connected.service_docusign = service_docusign.id
  LEFT JOIN service_google ON services_connected.service_google = service_google.id
  WHERE services_connected.workspace_id = _workspace_id;
END;
$function$;

CREATE OR REPLACE FUNCTION public.get_services_connected_data()
 RETURNS SETOF services_connected_response
 LANGUAGE plpgsql
AS $function$
DECLARE
  _workspace_id BIGINT := ((current_setting('request.jwt.claims'::text, TRUE))::JSON ->> 'workspace_id')::BIGINT;
BEGIN
   RETURN QUERY
    SELECT *
    FROM public.get_services_connected_for_workspace(_workspace_id);
END;
$function$;

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