我得到以下错误在我的代码
在“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;
我是写代码的新手,所以我不确定我做错了什么。
有没有人知道我为什么会出现这个错误?
1条答案
按热度按时间dwbf0jvd1#
错误消息应用于块的第一行
需要
FOREACH
循环来迭代数组值的元素,请参见循环遍历数组。