我创建了此存储过程,以便在组织 ID 在数据库中时返回row_version。
CREATE OR REPLACE FUNCTION sote.validate_row_version(a BIGINT, b BIGINT)
RETURNS text AS $$
DECLARE
ret RECORD;
v_error_stack text;
BEGIN
SELECT row_version INTO ret
FROM sote.organizations
WHERE organization_id = a;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE EXCEPTION 'Organization Id (%) was not found', myOrganizationID;
GET STACKED DIAGNOSTICS v_error_stack = PG_EXCEPTION_CONTEXT;
RETURN to_json(v_error_stack);
RETURN '0';
END;$$ LANGUAGE plpgsql;
当我运行此命令时,
SELECT sote.validate_row_version(2,4);
我收到以下错误。 我不明白为什么。任何帮助都会很棒。
ERROR: control reached end of function without RETURN
有 2 个问题:
-
您的 select 语句不会引发异常,除非您指定
SELECT
...INTO STRICT
-
您的
RETURN '0'
语句是异常处理程序的一部分,永远不会执行。
所以你可以把你的函数重写为
CREATE OR REPLACE FUNCTION sote.validate_row_version(a BIGINT, b BIGINT)
RETURNS text AS $$
DECLARE
ret RECORD;
v_error_stack text;
BEGIN
SELECT row_version INTO STRICT ret
FROM sote.organizations
WHERE organization_id = a;
RETURN '0';
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE EXCEPTION 'Organization Id (%) was not found', myOrganizationID;
GET STACKED DIAGNOSTICS v_error_stack = PG_EXCEPTION_CONTEXT;
RETURN to_json(v_error_stack);
END;$$ LANGUAGE plpgsql;
您需要移动RETURN '0'
,以便在EXCEPTION
处理程序之前执行它:
CREATE OR REPLACE FUNCTION sote.validate_row_version(a BIGINT, b BIGINT)
RETURNS text AS $$
DECLARE
ret RECORD;
v_error_stack text;
BEGIN
SELECT row_version INTO ret
FROM sote.organizations
WHERE organization_id = a;
RETURN '0';
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE EXCEPTION 'Organization Id (%) was not found', myOrganizationID;
GET STACKED DIAGNOSTICS v_error_stack = PG_EXCEPTION_CONTEXT;
RETURN to_json(v_error_stack);
END;$$ LANGUAGE plpgsql;
下面的代码EXCEPTION
都是异常块的一部分,除非发生异常,否则不会执行。
祝你好运。