I'm trying to create check login procedure with postgresql where if the password already exist-CodePudding

CREATE FUNCTION checkUsername (Iusername VARCHAR(50), Iuserpassword VARCHAR(50), result INT=0)
RETURNS VARCHAR(50) AS $$
BEGIN 
    SET nocount ON;
        IF EXISTS (SELECT * FROM users WHERE username=Iusername AND userpassword=Iuserpassword)
            SET result = 1
        ELSE 
            SET result = 0
    
        END IF;
END;$$LANGUAGE plpgsql;

CodePudding user response：

Besides the missing return statement required in a plpgsql function, and there being no set statement your function has additional errors and/or apparent misconceptions. First off there is no need to declare the size of a formal parameter, it is just discarded ignored and discarded anyway. Speaking of parameters why do you provide an return parameter (result) of type integer then define the function to return a varchar. Finally problems with the IF and assignment statements. The True branch requires the THEN keyword and each statement within the then/else branches requires statement terminator (;). Correcting for each the code becomes:

create or replace
function checkusername (
         iusername varchar 
       , iuserpassword varchar
       , result varchar default '0')     -- type and value to match returns declaration 
  returns varchar      
as $$
begin 
    if exists (select * 
                 from users where username=iusername 
                  and userpassword=iuserpassword
              )
    then                                    -- added 
        result = '1';                       -- added  's and ;
    else 
        result = '0';                       -- added  's and ;                                           
    end if;
    
    return result;                          -- added 
end;  
$$ language plpgsql;

However, this can be reduced considerably. Postgres fully supports the BOOLEAN data type. Since EXISTS returns a boolean value, there is no need to test it, just directly return its result. This also then permits a SQL function (of 1 statement):

create or replace 
function checkusername (
         iusername varchar
       , iuserpassword varchar
       )  
 returns boolean
 language sql
as $$
    select exists (select null 
                     from users 
                    where username=iusername 
                      and userpassword=iuserpassword
                  ); 
$$;

See demo (with variations on function);