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
SQLSTATE code; 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)
LANGUAGE plpgsql AS
_min_password_length int := 8; -- specify min length here
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.';
INSERT INTO pwdhistory
(usename, password, changed_on)
VALUES ($1 , md5($2) , now());
-- trap existing error and re-raise with added detail
WHEN unique_violation THEN -- = error code 23505
USING DETAIL = 'Password already used earlier. Please try again with a different password.';
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');
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
$2, but not the function header declaring those parameters. Please always include the function header.
CREATE FUNCTION ...And your version of Postgres, obviously. Finally, I have read the question a couple of times, and it's still not clear to me what the question is, exactly. Please clarify.