PL/pgSQLを使い、PostgreSQLのクエリ内で条件分岐して例外発生を起こす

動機

• 現在のスキーマの数が5以下なら、新しいスキーマnew_schemaを作りたい。
• 複数のクライアントからDBが更新される可能性があるので、競合状態になるのを防ぎたい。

処理のイメージは以下。
🟥 を実現するために PL/pgSQLを使いたい。

START TRANSACTION
  - CREATE SCEMA new_schema;
  - (🟥) Raise error if num(schemas) > 5
COMMIT

PL/pgSQL を使うとよい

(通常のpostgresのクエリではなく) PL/pgSQL を使用することで以下の構文を使用できる。

• ① if 文
• ② raise exception 文

👉 良さそう。

PL/pgSQLを試してみる

【PL/pgSQL入門】PL/pgSQLを勉強したのでまとめてみた がわかりやすい。
function と procedure の二種類があるが、今回は procedure を使用する。

やってみる。

postgresqlの起動

$ brew services start postgresql

# defaultのDBに繋がる。
$ psql -d postgres

スニペットを実行してテストデータを作成する。

作成後の確認。

$ \dn

      List of schemas
     Name     |   Owner    
--------------+------------
 public       | daiki-kudo
 test_plpgsql | daiki-kudo
(2 rows)

プロシージャーの作成、実行、削除のサンプルを実行してみた。

-- 定義
CREATE OR REPLACE PROCEDURE test_plpgsql.sample1_02() AS $$ 
DECLARE 
BEGIN   
    RAISE INFO 'HELLO WORLD！！';
END;    
$$ LANGUAGE plpgsql; 

-- 実行
CALL test_plpgsql.sample1_02();
INFO:  HELLO WORLD！！
CALL

-- 削除
DROP PROCEDURE p;
DROP PROCEDURE

PL/pgSQL で本題の処理を作成する

Raise error if num(schemas) > 5 を作る。

CREATE OR REPLACE PROCEDURE p() AS $$ 
DECLARE
  schema_cnt int;
BEGIN
  SELECT COUNT(nspname) INTO schema_cnt FROM pg_catalog.pg_namespace; -- schema_cnt に代入。
  IF schema_cnt > 5 THEN
    RAISE EXCEPTION 'Schema cannot be created any more!';
  END IF;
END;
$$ LANGUAGE plpgsql; 

CALL p();

完成。乙です。