CODEWITHSUNDEEP
postgresqlexception
generic_user
generic_user

Reputation: 35

Exception handling postgresql

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;

Upvotes: 2

Views: 10158

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

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

Upvotes: 4

Related Questions