PostgreSQL | SQLite＋MySQL経験者向け、30日で習得するPostgreSQL：プロレベル運用 － Day24 ストアド関数

Day24 後半のゴール

「“ただの関数”から“一応ちゃんとしたロジック”へステップアップする」

前半で「PL/pgSQL の関数を定義して呼ぶ」までは行けました。
後半では、もう一歩だけ踏み込んで「IF」「ループ」「エラー処理」「権限」という、実務で必ず触る要素を押さえます。
ここを越えると、「ちょっとした業務ロジックなら PL/pgSQL で書ける」という感覚に近づきます。

IF文で“条件分岐”を書く

「状況によって返す値や動きを変える」

PL/pgSQL でも、普通のプログラミング言語と同じように IF が書けます。
まずは一番シンプルな形から見てみましょう。

CREATE OR REPLACE FUNCTION classify_score(p_score integer)
RETURNS text
LANGUAGE plpgsql
AS $$
BEGIN
  IF p_score >= 80 THEN
    RETURN 'A';
  ELSIF p_score >= 60 THEN
    RETURN 'B';
  ELSE
    RETURN 'C';
  END IF;
END;
$$;

CREATE OR REPLACE FUNCTION classify_score(p_score integer)
RETURNS text
LANGUAGE plpgsql
AS $$
BEGIN
  IF p_score >= 80 THEN
    RETURN 'A';
  ELSIF p_score >= 60 THEN
    RETURN 'B';
  ELSE
    RETURN 'C';
  END IF;
END;
$$;

この関数は、点数を渡すと A / B / C を返すだけのものです。
でも、この中に PL/pgSQL の条件分岐の基本が全部入っています。

IF 条件 THEN
ELSIF 条件 THEN
ELSE
END IF;

という形で、「上から順に条件を評価して、最初にマッチしたところを実行する」動きです。

実際の呼び出しはこうなります。

SELECT classify_score(85);  -- 'A'
SELECT classify_score(70);  -- 'B'
SELECT classify_score(40);  -- 'C'

SELECT classify_score(85);  -- 'A'
SELECT classify_score(70);  -- 'B'
SELECT classify_score(40);  -- 'C'

重要なのは、「SQLだけでは書きづらい“条件の枝分かれ”を、PL/pgSQL の IF で素直に書ける」という感覚です。
CASE 式でも似たことはできますが、複雑になってくると PL/pgSQL のほうが読みやすくなります。

ループで“複数行に対する処理”を書く

「1行ずつ処理したいときの FOR」

次に、「複数行を1行ずつ処理したい」ケースを考えます。
SQL は「まとめて処理する」のが得意ですが、どうしても「1行ずつ何かしたい」場面もあります。

例として、「特定ユーザーの注文を全部なめて、合計金額を計算する関数」を書いてみます。

CREATE OR REPLACE FUNCTION calc_user_total_amount(p_user_id bigint)
RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
  v_order record;
  v_total integer := 0;
BEGIN
  FOR v_order IN
    SELECT amount
      FROM orders
     WHERE user_id = p_user_id
  LOOP
    v_total := v_total + v_order.amount;
  END LOOP;

  RETURN v_total;
END;
$$;

CREATE OR REPLACE FUNCTION calc_user_total_amount(p_user_id bigint)
RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
  v_order record;
  v_total integer := 0;
BEGIN
  FOR v_order IN
    SELECT amount
      FROM orders
     WHERE user_id = p_user_id
  LOOP
    v_total := v_total + v_order.amount;
  END LOOP;

  RETURN v_total;
END;
$$;

ここでのポイントを分解します。

DECLARE で、v_order record; と v_total integer := 0; を宣言しています。
record 型は「行1件分」を入れられる柔らかい型で、SELECT の結果をそのまま受けるときによく使います。
v_total は合計値を入れる変数で、:= 0 で初期値を 0 にしています。

FOR v_order IN SELECT … LOOP 〜 END LOOP;
この形が、PL/pgSQL の「カーソルループ」の基本形です。
SELECT の結果セットを1行ずつ取り出して、v_order に入れながらループします。

ループの中で、v_total := v_total + v_order.amount; として、
各行の amount を足し込んでいます。

最後に RETURN v_total; で合計を返します。

実際には、この例は SQL だけでも書けます。

SELECT SUM(amount) FROM orders WHERE user_id = p_user_id;

SELECT SUM(amount) FROM orders WHERE user_id = p_user_id;

なので、「ループを使うべきかどうか」はよく考える必要があります。
ただ、「PL/pgSQL でループを書ける」という事実を一度体験しておくことが大事です。

例外処理で“想定外”に名前を付ける

「エラーをそのまま投げるか、自分で扱うか」

DBの中でロジックを書く以上、「うまくいかないとき」をどう扱うかも考えないといけません。
PL/pgSQL では、EXCEPTION ブロックで例外処理が書けます。

さきほどの「フルネームを返す関数」を、
「ユーザーが存在しないときはエラーではなく NULL を返す」ようにしてみます。

CREATE OR REPLACE FUNCTION get_user_full_name_safe(p_user_id bigint)
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
  v_full_name text;
BEGIN
  SELECT last_name || ' ' || first_name
    INTO v_full_name
    FROM users
   WHERE id = p_user_id;

  RETURN v_full_name;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
$$;

CREATE OR REPLACE FUNCTION get_user_full_name_safe(p_user_id bigint)
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
  v_full_name text;
BEGIN
  SELECT last_name || ' ' || first_name
    INTO v_full_name
    FROM users
   WHERE id = p_user_id;

  RETURN v_full_name;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
$$;

ここでのポイントは、EXCEPTION ブロックです。

BEGIN 〜 EXCEPTION 〜 END;
という形で、「通常の処理」と「エラー時の処理」を分けています。

NO_DATA_FOUND は、「SELECT INTO で1行も見つからなかったとき」に発生する例外です。
それをキャッチして、RETURN NULL; に置き換えています。

これにより、呼び出し側は「存在しないユーザーIDを渡しても、エラーではなく NULL が返ってくる」という挙動になります。

例外処理を入れるかどうかは設計次第ですが、
「DB側で“これはエラーではなく普通のケース”とみなしたい状況」に名前を付けてあげるイメージです。

セキュリティと権限の視点から見たストアド関数

「“誰の権限で動くか”はかなり重要」

ストアド関数は、「DBの中で動くコード」です。
ということは、「そのコードがどの権限で動くか」がセキュリティ的に重要になります。

PostgreSQL には、大きく分けて二つの実行モードがあります。

呼び出したユーザーの権限で動く（デフォルト）
関数を定義したユーザーの権限で動く（SECURITY DEFINER）

デフォルトでは、「関数を呼んだ人が持っている権限の範囲でしか、テーブルにアクセスできない」ようになっています。
これは安全側の挙動です。

一方で、「アプリユーザーには直接テーブルを触らせたくないけど、関数経由なら特定の操作を許したい」というケースがあります。
例えば、「残高を更新するのはこの関数だけにしたい」といった場合です。

そのときに使うのが、SECURITY DEFINER です。

CREATE OR REPLACE FUNCTION secure_update_balance(...)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
  ...
$$;

CREATE OR REPLACE FUNCTION secure_update_balance(...)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
  ...
$$;

SECURITY DEFINER を付けると、「関数を作ったユーザーの権限」で関数が実行されます。
つまり、「呼び出し側はテーブルに直接権限がなくても、関数経由なら更新できる」という状態を作れます。

これは強力ですが、同時に危険でもあります。
関数の中で「何を許すか」「何をさせないか」を慎重に設計しないと、
意図しないデータ操作の入り口になりかねません。

セキュリティの観点からは、

アプリユーザーには最低限の権限だけを与える
重要な更新は、SECURITY DEFINER の関数経由に限定する
その関数の中で、入力チェックや条件分岐をきちんと行う

という設計がよく使われます。

「どこまでPL/pgSQLに寄せるか」を決める視点

「テストしやすさ・変更しやすさ・責務の場所」

最後に、PL/pgSQL を実務で使うときに、いつも頭の片隅に置いておいてほしい視点をまとめます。

一つ目は、テストしやすさです。
アプリコードは、ユニットテストやCIで自動テストしやすいですが、
PL/pgSQL はテスト環境のDBを用意して、SQLで叩いて…という手間がかかります。
「頻繁に変わるロジック」は、アプリ側に置いたほうが楽なことが多いです。

二つ目は、変更のしやすさです。
アプリのデプロイフローが整っているなら、
「アプリを直すほうが早い」場面も多いです。
逆に、「DBだけを更新したい」「複数言語のアプリから共通で使いたい」ロジックは、PL/pgSQL に寄せる価値があります。

三つ目は、責務の場所です。
「DBに近い責務」（データ整合性、集計、複数テーブルをまたぐ更新など）は、
PL/pgSQL に置くとスッキリすることが多いです。
「UIやワークフローに近い責務」は、アプリ側に置いたほうが自然です。

あなたに今、完璧な答えは要りません。
大事なのは、「PL/pgSQL という選択肢があり、どんなときに使うと気持ちいいか」を少しずつ体で覚えていくことです。

Day24 後半のまとめ

PL/pgSQL では IF や ELSIF、ELSE を使って条件分岐を書き、FOR ループと SELECT ... INTO を組み合わせて複数行を1行ずつ処理でき、EXCEPTION ブロックで「ユーザーがいないときは NULL を返す」など“想定外のケース”に名前を付けて扱えるようになる。
さらに、ストアド関数は「誰の権限で動くか」が重要で、デフォルトは呼び出しユーザーの権限だが、SECURITY DEFINER を使えば「関数定義者の権限で動く安全な入り口」を作れる一方で、その中身の設計を誤るとセキュリティホールにもなりうるため、「DBに近いロジックだけを慎重にPL/pgSQLに寄せ、頻繁に変わるビジネスロジックはアプリ側に置く」というバランス感覚を持つことが、Day24 後半の着地点になる。