postgresql Postgres函数错误位于或接近数组错误

kcwpcxri  于 2023-04-11  发布在  PostgreSQL
关注(0)|答案(1)|浏览(158)

我得到以下错误在我的代码
在“ARRAY”处或附近出现语法错误

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;

我是写代码的新手,所以我不确定我做错了什么。
有没有人知道我为什么会出现这个错误?

dwbf0jvd

dwbf0jvd1#

错误消息应用于块的第一行

FOR service_name IN ARRAY services LOOP

需要FOREACH循环来迭代数组值的元素,请参见循环遍历数组。

相关问题