Postgresql函数忽略return语句并继续执行下一个return



我试图创建一个返回表的postgres函数,但当我执行该函数时,return语句应该关闭或结束整个函数,但它一直忽略这个结束,然后继续。

该函数非常简单:如果字段为空,则只需要进行验证。我知道如果我放一个ELSE语句,问题就会得到解决,但我不知道为什么它会忽略RETURN,我很想知道除了ELSE

create or replace function fnRegisterUserWin(rUsername text, rFname text, rLname text,rRole text, rBrand text) returns table(id_users int, message text, is_failure_location text, error_fields text[])
language plpgsql
as $$
declare
sanitazedUsername text;
sanitazedFirstname text;
sanitazedLastname text;
sanitazedRole text;
sanitazedBrand text;
errorFields text;
begin
sanitazedUsername := str_clean(rUsername,true,true,true,true,true,true,true);
sanitazedFirstname := str_clean(rFname,true,true,true,true,true,true,true);
sanitazedLastname := str_clean(rLname,true,true,true,true,true,true,true);
sanitazedRole := str_clean(rRole,true,true,true,true,true,true,true);
sanitazedBrand := str_clean(rBrand,true,true,true,true,true,true,true);
errorFields := '';
if(empty2null(sanitazedUsername) is null OR empty2null(sanitazedFirstname) is null OR
empty2null(sanitazedLastname) is null OR empty2null(sanitazedRole) is null OR
empty2null(sanitazedBrand) is null) then
if(empty2null(sanitazedUsername) is null) then  errorFields := errorFields || chr(39) || 'Username' || chr(39); end if;
if(empty2null(sanitazedFirstname) is null) then errorFields := errorFields || ',' || chr(39) || 'Firstname' || chr(39); end if;
if(empty2null(sanitazedLastname) is null) then errorFields := errorFields || ',' || chr(39) || 'Lastname' || chr(39); end if;
if(empty2null(sanitazedRole) is null) then errorFields := errorFields || ',' || chr(39) || 'Role' || chr(39); end if;
if(empty2null(sanitazedBrand) is null) then errorFields := errorFields || ',' || chr(39) || 'Brand' || chr(39); end if;
return query select  0 as id_users, 'There are required fields that are empty, please complete them and try again. '::text as message,'Empty Fields'::text as is_failure_location,ARRAY[ltrim(',sa,aaa',',')]as errorFields;
end if;
return query execute 'select 0 as id_users, ' || chr(39) || 'There are required fields that are empty, please complete them and try again. ' || chr(39) || '::text as message,' || chr(39) || 'Empty Fields' || chr(39) || '::text as is_failure_location,ARRAY[' || ltrim(errorFields,',') ||']as errorFields';
end;
$$;
create function empty2null(text_i character varying)
returns character varying
language plpgsql
as $$
declare
text_p varchar;
begin
if text_i = ''
then text_p := null;
else text_p := text_i;
end if;
return text_p;
end;
$$;
alter function empty2null(varchar)
owner to postgres;

这是退货:

0有必填字段为空,请填写它们并再试一次。空字段{"名字"}

0必填字段为空,请填写并再试一次。空字段{名字}

执行两个return query语句是因为return query不退出函数。如果您想在if块结束时退出,可以添加一个return语句(单独(。

根据文件:

RETURN QUERY实际上并不是从函数返回的——它们只是将零行或更多行追加到函数的结果集。然后执行继续PL/pgSQL函数中的下一条语句。像执行连续的RETURN NEXT或RETURN QUERY命令时建立了结果集。最后的RETURN不应该有任何参数,导致控件退出函数(或者您可以让控件到达函数的末尾(。

最新更新