how to write select statement inside if in PostgreSQL-CodePudding

I'm trying to write select statement inside if in PostgreSQL but getting error like ERROR: syntax error at or near "SELECT" please help me to solve this error LINE 39: SELECT random()*power( 10, 8 ) INTO STRICT W_OTPNUM;

BEGIN
              if p_mobile_no=usersdefotp_mobile_no 
                then 
                P_OTP :='123456'
                else
                SELECT random()*power( 10, 8 ) INTO STRICT W_OTPNUM;
                P_OTP :=LPAD(W_OTPNUM::TEXT, 6, '0');
                INSERT INTO usersdefotp(P_OTP)
                end if
        EXCEPTION
           WHEN NO_DATA_FOUND THEN
              P_OTP := NULL;
          END;

CodePudding user response：

You can use declarations in a Procedure as an Array on PostgreSQL, like this one:

do $$
declare
   randoms text[];
begin
  -- store data
  randoms = ARRAY(SELECT random()*power( 10, 8 ) INTO STRICT W_OTPNUM;
                P_OTP :=LPAD(W_OTPNUM::TEXT, 6, '0'));

   if something
   then
     do something
   else
     return format("%s", randoms)
   end if;
end   
$$;

That way you can store your select and then use him when needed to return;