CODEWITHSUNDEEP
postgresql
Luis Cardoza Bird
Luis Cardoza Bird

Reputation: 1472

Postgresql Function ignore return statement and goes on to the next return

I was trying to create a postgres function that returns a table, but when i execute the function, the return statement it's supposed to close or end the whole function, however it's keep ignoring that ending, and goes on.

The function is quite simple: it just take a validation if the fields are empty. I know that if i put an ELSE statement the problem is gonna be solved, however i don't know why it ignore the RETURN and im curious to know if there is any way other than an ELSE to fix the issue

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;

this is the return:

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}

Upvotes: 5

Views: 3109

Answers (1)

JGH
JGH

Reputation: 17866

The two return query statements are executed because return query does not exit the function. If you want to quit at the end of the if block, you can add a return statement (alone).

As per the 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).

Upvotes: 11

Related Questions