CODEWITHSUNDEEP
postgresql
Maks.Burkov
Maks.Burkov

Reputation: 626

Got syntax error can't understand what is wrong

Need you help please tell me what i am doing wrong in that function ? According to docs examples all should work .. docs 

create or replace function loginValidator(nickname varchar, email varchar, u_password varchar) returns boolean as $$
DECLARE
    checked boolean := false; n_regex varchar; e_regex varchar; p_regex varchar;
BEGIN 

 BEGIN;

  select nickname_r into n_regex from regex;
  select email_r into e_regex from regex;
  select password_r into p_regex from regex;

  IF n_regex ~ nickname AND e_regex ~ email AND p_regex ~ u_password THEN checked := true;

 COMMIT;

 return checked;
END;
$$ language plpgsql;

ERROR: syntax error at or near ";" LINE 6: BEGIN; ^ SQL state: 42601 Character: 222

Upvotes: 1

Views: 95

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175686

You are missing END IF. I would rewrite it as:

create or replace function loginValidator(nickname varchar,
                                          email varchar,
                                          u_password varchar)
returns boolean 
as
$$
DECLARE
    checked boolean := false; n_regex varchar; e_regex varchar; p_regex varchar;
BEGIN 
  -- removed BEGIN
  select nickname_r, email_r, password_r into n_regex, e_regex, p_regex from regex;

  IF n_regex ~ nickname AND e_regex ~ email AND p_regex ~ u_password 
  THEN checked := true;
  END IF;    -- added END IF;
  -- removed COMMIT;

  RETURN checked;
END;
$$ language plpgsql;

or even simpler:

create or replace function loginValidator(nickname varchar,
                                          email varchar,
                                          u_password varchar)
returns boolean 
as
$$
BEGIN 

  RETURN  (select COUNT(*) 
          from regex
          WHERE nickname_r ~ nickname
            AND  email_r ~ email
            AND password_r ~ u_password)::boolean;

END;
$$ language plpgsql;

I hope that you are not stroring passwords as clear text.

Upvotes: 1

Related Questions