All we need is an easy explanation of the problem, so here it is.
Can you please provide syntax for to use user defined exception in PostgreSQL function?
Suppose I want to handle below user defined exception.
SQL Error : ERROR: password is too short.
There are multiple SQLSTATE error code but not able to find what is the SQLSTATE code for this error. I used above which is 22023 but not resolved.
We have below code we are able to manage unique violation related exception but not able to manage for "password is too short". could you help me with the syntax?
begin EXECUTE 'ALTER USER ' || $1 || ' WITH PASSWORD '''|| $2||'''' ; EXCEPTION WHEN "Password is too short" THEN RAISE DETAIL 'Please check your password'; INSERT INTO pwdhistory (usename,password,changed_on) values($1,md5($2),now()); EXCEPTION WHEN unique_violation THEN RAISE DETAIL 'Password already used earlier. Please try again with another password.'; end;
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Trapping and raising errors
To trap errors, use only one
EXCEPTION clause in PL/pgSQL code. It can have multiple
WHEN clauses. (But you seem to need only one. See below.)
You can work with error codes as with condition names. The list of error codes can be found in the manual.
Also consider the Postgres manual page on Trapping Errors in PL/pgSQL:
The condition names can be any of those shown in Appendix A. A
category name matches any error within its category. […] Also, an
error condition can be specified by
SQLSTATEcode; for example these
WHEN division_by_zero THEN ... WHEN SQLSTATE '22012' THEN ...
So you can trap errors and (re-)raise them with different or additional details.
Raising your own errors is a different matter.
RAISE with all desired details. Not need to trap an error first.
Unrelated to your core question, your displayed code is wide open to SQL injection.
EXECUTE 'ALTER USER ' || $1 || ' WITH PASSWORD '''|| $2||'''' ;
Do not use this.
Would be fun to call your function with:
SELECT my_func('user1', 'pw1234567''; DELETE FROM pwdhistory; --');
(Or worse.) Boom. See:
Handle user input properly. See:
Your function might look like this (assuming current Postgres 13):
CREATE OR REPLACE FUNCTION myfunc(_usename text, _password text) RETURNS void LANGUAGE plpgsql AS $func$ DECLARE _min_password_length int := 8; -- specify min length here BEGIN IF length(_password) >= _min_password_length THEN EXECUTE format('ALTER USER %I WITH PASSWORD %L', _usename, _password); ELSE -- also catches NULL -- raise custom error RAISE EXCEPTION 'Password too short!' USING ERRCODE = '22023' -- 22023 = "invalid_parameter_value' , DETAIL = 'Please check your password.' , HINT = 'Password must be at least ' || _min_password_length || ' characters.'; END IF; INSERT INTO pwdhistory (usename, password, changed_on) VALUES ($1 , md5($2) , now()); EXCEPTION -- trap existing error and re-raise with added detail WHEN unique_violation THEN -- = error code 23505 RAISE unique_violation USING DETAIL = 'Password already used earlier. Please try again with a different password.'; END $func$;
Note the use of
format() to properly quote name and value, and defend against SQL injection.
SELECT myfunc('usr', 'pw'); -- PW obviously too short ...
ERROR: Password too short! DETAIL: Please check your password. HINT: Password must be at least 8 characters. CONTEXT: PL/pgSQL function pg_temp_5.foo(text,text) line 8 at RAISE SQL state: 22023
SELECT myfunc('usr', 'repeated_pw');
ERROR: unique_violation DETAIL: Password already used earlier. Please try again with a different password. CONTEXT: PL/pgSQL function pg_temp_5.foo(text,text) line 21 at RAISE SQL state: 23505
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂