PostgreSQL自定义函数出现syntax error at or near "ARRAY"错误求助

问题描述 #

我是编程新手，编写了PostgreSQL建表语句及自定义函数get_services_auth_data，执行时出现错误：syntax error at or near "ARRAY"。代码中涉及数组的部分为results services_auth_data[] := '{}'和函数参数services TEXT[]，需要找出错误原因。

原代码如下：

DROP TABLE IF EXISTS "public"."services_auth_data";

DROP FUNCTION IF EXISTS public.get_services_auth_data;

create table "public"."services_auth_data" (
    "service_name" text not null,
    "token" text not null,
    "admin_email" text, 
    "org" text
);

CREATE OR REPLACE FUNCTION get_services_auth_data(services TEXT[])
RETURNS SETOF services_auth_data AS $$
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;
  w_id BIGINT := ((current_setting('request.jwt.claims'::TEXT, TRUE))::JSON ->> 'workspace_id')::BIGINT;
  service_result services_auth_data;
  results services_auth_data[] := '{}';
  _token text;
  _org text;
  _admin_email text; 
  service_name text;
BEGIN
  FOR service_name IN ARRAY services LOOP
    IF service_name = 'github' THEN
      SELECT service_github.org, service_github.token INTO _org, _token FROM service_github WHERE id = w_id;
      service_result.org = _org;
      service_result.token = _token;
      service_result.service_name = service_name;
      results := array_append(results, service_result);
    ELSIF service_name = 'jira' THEN
      SELECT service_jira.admin_email, service_jira.token INTO _admin_email, _token FROM service_jira WHERE id = w_id;
      service_result.admin_email = _admin_email;
      service_result.token = _token;
      service_result.service_name = service_name;
      results := array_append(results, service_result);
    ELSIF service_name = 'zoom' THEN
      SELECT service_zoom.token INTO _token FROM service_zoom WHERE id = w_id;
      service_result.token = _token;
      service_result.service_name = service_name;
      results := array_append(results, service_result);
    ELSE
      RAISE EXCEPTION 'Invalid service name or missing params: %', service_name;
    END IF;
  END LOOP;
  
  return query select * from unnest(results);
END;
$$ LANGUAGE plpgsql;

错误原因 #

错误出现在FOR service_name IN ARRAY services LOOP这一行。PostgreSQL的PL/pgSQL中，遍历数组不能用FOR ... IN ARRAY ...的语法，正确的数组遍历方式有两种：

1. 使用FOREACH关键字直接遍历数组元素
2. 通过SELECT UNNEST()将数组转为结果集后用FOR ... IN ...遍历

修正后的代码 #

方式一：使用FOREACH（推荐） #

把原循环部分替换为FOREACH service_name IN ARRAY services LOOP，完整代码如下：

DROP TABLE IF EXISTS "public"."services_auth_data";

DROP FUNCTION IF EXISTS public.get_services_auth_data;

create table "public"."services_auth_data" (
    "service_name" text not null,
    "token" text not null,
    "admin_email" text, 
    "org" text
);

CREATE OR REPLACE FUNCTION get_services_auth_data(services TEXT[])
RETURNS SETOF services_auth_data AS $$
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;
  w_id BIGINT := ((current_setting('request.jwt.claims'::TEXT, TRUE))::JSON ->> 'workspace_id')::BIGINT;
  service_result services_auth_data;
  results services_auth_data[] := '{}';
  _token text;
  _org text;
  _admin_email text; 
  service_name text;
BEGIN
  FOREACH service_name IN ARRAY services LOOP
    IF service_name = 'github' THEN
      SELECT service_github.org, service_github.token INTO _org, _token FROM service_github WHERE id = w_id;
      service_result.org = _org;
      service_result.token = _token;
      service_result.service_name = service_name;
      results := array_append(results, service_result);
    ELSIF service_name = 'jira' THEN
      SELECT service_jira.admin_email, service_jira.token INTO _admin_email, _token FROM service_jira WHERE id = w_id;
      service_result.admin_email = _admin_email;
      service_result.token = _token;
      service_result.service_name = service_name;
      results := array_append(results, service_result);
    ELSIF service_name = 'zoom' THEN
      SELECT service_zoom.token INTO _token FROM service_zoom WHERE id = w_id;
      service_result.token = _token;
      service_result.service_name = service_name;
      results := array_append(results, service_result);
    ELSE
      RAISE EXCEPTION 'Invalid service name or missing params: %', service_name;
    END IF;
  END LOOP;
  
  return query select * from unnest(results);
END;
$$ LANGUAGE plpgsql;

方式二：使用SELECT UNNEST #

如果坚持用FOR循环，可将循环行改为：

FOR service_name IN SELECT UNNEST(services) LOOP

补充说明 #

• FOREACH是PL/pgSQL中专门用于遍历数组的语法，写法更简洁直接
• UNNEST()函数的作用是将数组拆分为单行的结果集，适合用在FOR ... IN SELECT ...的场景中

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