postgresql - postgreSQL 中不存在函数.. 为什么？

Question

需要你的帮助，不明白为什么我会收到以下错误，我不是专业的 postgresql 开发人员..

如您所见，函数已创建，那么为什么函数不存在呢？

create or replace function loginAttempt (u_email character varying, u_password character varying, date_time timestamptz, OUT attempt smallint) returns smallint AS $$
   BEGIN
        INSERT INTO login_attempts (typed_password, date_time, attempt_nu, email) VALUES (u_password, date_time, attempt_nu, email);
        IF attempt = 3 THEN INSERT INTO warnings (u_email,u_password) VALUES (u_email,u_password);
        END IF;
   END;
$$ LANGUAGE plpgsql;


  select loginattempt ('Jon.Jones88@gmail.com','+_@kjhfdb987', now(), 1);

ERROR: function loginattempt(unknown, unknown, timestamp with time zone, integer) does not exist LINE 1: select loginattempt ('Jon.Jones88@gmail.com','+_@kjhfdb987',... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 8

Answer 1

您已将最后一个参数定义为 OUT 参数，这意味着您不能为其传递值。

你需要使用:

select loginattempt ('Jon.Jones88@gmail.com','+_@kjhfdb987', now());

因为您没有写入参数 attempts 我看不出有什么理由将它定义为 out 参数。如果需要，您可以简单地返回该值:

create or replace function loginAttempt (u_email character varying, u_password character varying, u_date_time timestamptz, u_attempt smallint) 
  returns smallint 
AS $$
BEGIN
  INSERT INTO login_attempts (typed_password, date_time, attempt_nu, email) 
  VALUES (u_password, u_date_time, u_attempt, u_email);
  IF u_attempt = 3 THEN 
    INSERT INTO warnings (u_email,u_password) VALUES (u_email,u_password);
  END IF;
  return u_attempt;
END;
$$ LANGUAGE plpgsql;

由于值 1 被假定为整数，因此您需要在调用函数时转换该值:

select loginattempt ('Jon.Jones88@gmail.com','+_@kjhfdb987', now(), 1::smallint);

在线示例:https://rextester.com/YNIQ55561