Postgresql 函数忽略返回语句并继续下一个返回

Question

我试图创建一个返回表的 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 There are required fields that are empty, please complete them and try again. Empty Fields {'Firstname'}

0 There are required fields that are empty, please complete them and try again. Empty Fields {Firstname}

Answer 1

执行这两条return query语句是因为return query没有退出函数。如果你想在 if block 的末尾退出，你可以添加一个 return 语句(单独)。

根据 doc :

RETURN QUERY do not actually return from the function — they simply append zero or more rows to the function's result set. Execution then continues with the next statement in the PL/pgSQL function. As successive RETURN NEXT or RETURN QUERY commands are executed, the result set is built up. A final RETURN, which should have no argument, causes control to exit the function (or you can just let control reach the end of the function).