Exception handling postgresql

Question

Hello fellow programmers,

I have a question regarding exception handling. I have created a function for inserting values into a table with the columns email and username with a max character length of 16 for the email and 32 for the password.

Now I want to give out an error message like 'Password too long,16 Chars max', whenever the inserted email is longer than 16 Chars.

I tried it already with the 'Name_too_long' error code.

How could I do that wiht exceptions in Postgres 10.5 ? Thanks in advance.

EDIT: CODE that is semi functional

    CREATE OR REPLACE FUNCTION users_insert(_email character varying,_passwort character varying) RETURNS void 
    AS $BODY$
     BEGIN 
     INSERT INTO users(email,passwort,lastlogin)
     VALUES(_email,_passwort,CURRENT_TIMESTAMP);
     EXCEPTION 
     WHEN string_data_right_truncation 
     THEN RAISE NOTICE 'ERROR: INSERT TOO LONG';
     END;
    $BODY$
    LANGUAGE plpgsql ;

EDIT: Working Code, with 2 different error messages, for each error:

CREATE OR REPLACE FUNCTION public.users_insert(
    _email character varying,
    _passwort character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$

 BEGIN 
 IF LENGTH(_passwort) > 16
  THEN RAISE EXCEPTION USING errcode = 50001;
END IF;

 IF LENGTH(_email) > 32
  THEN RAISE EXCEPTION USING errcode = 22001;
  END IF;


 INSERT INTO users(email,passwort,lastlogin)
 VALUES(_email,_passwort,CURRENT_TIMESTAMP);

EXCEPTION 
 WHEN SQLSTATE '50001' THEN  
   RAISE NOTICE 'Password too long, 16 Chars max';

WHEN SQLSTATE '22001' THEN  
   RAISE NOTICE 'Email too long, 32 Chars max';


 END; 

$BODY$;

ALTER FUNCTION public.users_insert(character varying, character varying)
    OWNER TO postgres;

You may be interested in the Security.SE quoestion Should I have a maximum password length? — Haem
– Haem, Commented Nov 19, 2018 at 8:13
@KaushikNayak I have edited the code to my post. My Problem now is, that it gives out an error, but I cant "choose" between two different errors. I am trying to get one for a too long password insert and one for an too Long email insert. I want to create for each error a distinct message. — generic_user
– generic_user, Commented Nov 19, 2018 at 8:24

Kaushik Nayak · Accepted Answer · 2018-11-19 09:53:05Z

4

Use an IF condition to check for lengths of specific columns and raise/handle relevant exceptions.

CREATE OR REPLACE FUNCTION users_insert(_email character varying,
                                        _passwort character varying ) 
RETURNS void 
 AS $BODY$
  BEGIN 

  IF LENGTH(_passwort) > 16
    THEN RAISE EXCEPTION  USING errcode = 50001;
  END IF;

  INSERT INTO users(email,passwort,lastlogin)
    VALUES(_email,_passwort,CURRENT_TIMESTAMP);

  EXCEPTION 
   WHEN SQLSTATE '50001' THEN
       RAISE NOTICE  'Password too long,16 Chars max';
   WHEN OTHERS THEN
    raise notice '% %', SQLERRM, SQLSTATE;
 END;
$BODY$
LANGUAGE plpgsql ;

Testing

knayak$# PERFORM users_insert('[email protected]','password123passwod');
knayak$#
knayak$# END $$;
NOTICE:  Password too long,16 Chars max
DO
knayak=# DO $$
knayak$# BEGIN
knayak$#
knayak$# PERFORM users_insert('[email protected]','password');
knayak$#
knayak$# END $$;
NOTICE:  value too long for type character varying(30) 22001
DO

answered Nov 19, 2018 at 9:53

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

1 Comment

generic_user Over a year ago

Thank you very much, this is the answer I was looking for, I failed to create the If condition myself, due to a minor syntax mistake, which I couldn't figure out!

Collectives™ on Stack Overflow

Exception handling postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related