Postgres函数错误或接近数组错误

Question

我得到以下错误在我的代码

在"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;

我是写代码的新手，所以我不确定我做错了什么。我代码中唯一的数组恰好是results services_auth_data[] := '{}';和services Text[]

有人知道为什么我得到这个错误吗?

Answer 1

错误信息适用于块的第一行

FOR service_name IN ARRAY services LOOP

需要FOREACH循环遍历数组值的元素，参见遍历数组。