How to use user defined exception - PostgreSQL Function

Question

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 [22023]: 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?

Code:

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;

Erwin Brandstetter · Accepted Answer · 2021-08-04T23:05:50.433

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 are equivalent:
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.
Just use RAISE with all desired details. Not need to trap an error first.

SQL injection

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:

https://xkcd.com/327/

Handle user input properly. See:

SQL injection in Postgres functions vs prepared queries

Function

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$;

db<>fiddle here

Note the use of format() to properly quote name and value, and defend against SQL injection.

Call:

SELECT myfunc('usr', 'pw'); -- PW obviously too short ...

Produces:

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');

Produces:

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

How to use user defined exception - PostgreSQL Function

1 Answers1

Trapping and raising errors

SQL injection

Function