如何使用使用者定義的異常 - PostgreSQL 函式

您能否提供在 PostgreSQL 函式中使用使用者定義異常的語法？

假設我想處理以下使用者定義的異常。

SQL Error [22023]: ERROR: password is too short.

有多個 SQLSTATE 錯誤程式碼，但無法找到此錯誤的 SQLSTATE 程式碼。我在上面使用的是 22023 但沒有解決。

我們有以下程式碼，我們能夠管理唯一的違規相關異常，但無法管理“密碼太短”。你能幫我語法嗎？

程式碼：

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;

擷取和引發錯誤

要擷取錯誤，請在 PL/pgSQL 程式碼中僅使用一個子句。 EXCEPTION它可以有多個WHEN子句。（但你似乎只需要一個。見下文。）

您可以像使用條件名稱一樣使用錯誤程式碼。錯誤程式碼列表可以在手冊中找到。

還要考慮關於PL/pgSQL 中的Trapping Errors的 Postgres手冊頁：

條件名稱可以是附錄 A中顯示的任何名稱。類別名稱匹配其類別中的任何錯誤。

$$ … $$此外，可以通過SQLSTATE程式碼指定錯誤條件；例如，這些是等價的：

WHEN division_by_zero THEN ...
WHEN SQLSTATE '22012' THEN ...

因此，您可以擷取錯誤並（重新）使用不同或附加的詳細資訊提出錯誤。

提出自己的錯誤是另一回事。

只需RAISE與所有需要的細節一起使用。不需要先擷取錯誤。

SQL注入

與您的核心問題無關，您顯示的程式碼對 SQL 注入是開放的。

EXECUTE 'ALTER USER ' || $1 || ' WITH PASSWORD '''|| $2||'''' ;

不要使用這個。

呼叫你的函式會很有趣：

SELECT my_func('user1', 'pw1234567''; DELETE FROM pwdhistory; --');

（或更糟。）繁榮。看：

• https://xkcd.com/327/

正確處理使用者輸入。看：

• Postgres 函式中的 SQL 注入與準備好的查詢

功能

您的函式可能如下所示（假設目前的 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<>在這裡擺弄

注意使用format()正確引用名稱和值，並防止 SQL 注入。

稱呼：

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

引用自：https://dba.stackexchange.com/questions/297529