sql - PostgreSQL: conditional statements in functions -

i've been trying create basic user authentication system within postgresql 9.4, have been coming unstuck. users table looks this:

-- users table create table users (   user_id serial not null primary key,    first_name text not null,   last_name text not null,   email text not null,   password text not null,    failed_login_attempts int not null default 0     constraint positive_login_attempts check (failed_login_attempts >= 0),    last_failed_login_attempt timestamp null,    unique(email),    created_at timestamp not null default current_timestamp,   updated_at timestamp not null,   deleted_at timestamp null ); 

these functions work fine:

-- check see if user exists create or replace function user_exists (auth_email varchar(254)) returns setof users $$   select *   users   email = auth_email $$ language sql;  -- authenticates user against system create or replace function authenticate_user (auth_email varchar(254), auth_password varchar(72)) returns setof users $$   select *   users   email = auth_email   , password = crypt(auth_password, password)) $$ language sql; 

but then, when try , combine these, fall flat on face. in semi-pseudocode, i'd this:

-- login function create or replace function user_login (auth_email varchar(254), auth_password varchar(72)) returns setof users $$   if exists (select count(*) user_exists(auth_email))     if exists (select count(*) authenticate_user (auth_email, auth_password))       -- set failed_login_attempts value 0       -- set last failed login attempt null       -- return user details     else       -- increment failed_login_attempts value       -- set last failed login attempt current time       -- return nothing     end if;   else     -- return nothing   end if; $$ language sql; 

is possible? going down entirely wrong lines?

the purpose of 'failed login attempts' set incrementally longer cooling off periods - eg failed attempts:

1. 1 second
2. 2 seconds
3. 4s
4. 8s
5. 16s
6. ...

is required functions written using sql language? there solution plpgsql procedure if accept plpgsql.

create or replace function user_login (auth_email varchar(254), auth_password varchar(72))  returns setof users  $$ declare      found_user users; begin     select u.*      users u     u.email=auth_email     found_user;      -- check password here using algorithm     if found_user.password = auth_password         return next found_user;         return;     end if;      update users set           failed_login_attempts = failed_login_attempts + 1         , last_failed_login_attempt = now()     user_id = found_user.user_id; end; $$ language plpgsql;