function does not exists in postgreSQL .. Why ?

Question

Need your help please , can't understand why i got the following error , i am not a professional postgresql developer ..

As you can see the function created , so why the function not exist occurred ?

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 ('[email protected]','+_@kjhfdb987', now(), 1);

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

What is your Postgres version (select version(); will tell you) — user330315
– user330315, Commented Oct 22, 2018 at 6:42

score 5 · Accepted Answer · 2018-10-22 07:02:28Z

5

You have defined the last parameter as an OUT parameter, that means you can't pass a value for it.

You need to use:

select loginattempt ('[email protected]','+_@kjhfdb987', now());

As you are not writing to the parameter attempts I don't see a reason to define it as an out parameter to begin with. You can simply return the value if you need it:

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;

As the value 1 is assumed to be an integer, you need to cast that value when calling the function:

select loginattempt ('[email protected]','+_@kjhfdb987', now(), 1::smallint);

Online example: https://rextester.com/YNIQ55561

edited Oct 22, 2018 at 7:02

answered Oct 22, 2018 at 6:50

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Maks.Burkov Over a year ago

Why i got 2 unknown argument types in mine function loginattempt as you can see from the error message ?

user330315 Over a year ago

@Maks.Burkov: you need to cast the 1 to a smallint. And you should also make sure your function parameters do not have the same name as your columns to avoid confusion

Collectives™ on Stack Overflow

function does not exists in postgreSQL .. Why ?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related