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

SQL PostgreSQL
スポンサーリンク

Day24 前半のゴール

「“SQLだけのDB”から“ロジックを持つDB”をイメージできるようになる」

Day24 は、いよいよ「ストアド関数」と「PL/pgSQL」です。
ここまで、SQLを書いてデータを操作する世界でしたが、今日からは「DBの中にロジックを書く」世界に足を踏み入れます。

前半のゴールはこうです。
ストアド関数とは何かを、自分の言葉で説明できる。
PL/pgSQL が「PostgreSQL専用のミニ言語」だと理解する。
簡単なストアド関数を1つ書いて、呼び出す流れをイメージできる。

まずは、「なんでわざわざDBの中に関数を書くのか」からいきます。

ストアド関数とは何か

「“DBの中に住んでいる関数”」

ストアド関数（stored function）は、一言で言うと「データベースの中に保存されている関数」です。
アプリケーションコード（Python, PHP, Java…）の関数と似ていますが、住んでいる場所が違います。

アプリ側の関数
アプリケーションサーバのコードとして動く。
DBにはSQLを投げるだけ。

ストアド関数
PostgreSQLの中に定義され、DBサーバ側で実行される。
SQLから SELECT my_func(...) のように呼び出せる。

イメージとしては、「DBの中に小さなプログラムを置いておいて、SQLから呼び出す」という感じです。

これが何に効くかというと、

複雑な集計や更新ロジックを、DB側にまとめておける。
同じ処理を何度も書かずに、関数として再利用できる。
アプリからは「関数を呼ぶ」だけで済むので、インターフェースがシンプルになる。

というメリットがあります。

PL/pgSQLとは何か

「PostgreSQL専用の“SQLが書きやすいミニ言語”」

PostgreSQL では、ストアド関数を書くためにいくつかの言語が使えますが、
一番基本になるのが「PL/pgSQL」です。

PL/pgSQL は、

SQL を書きやすいように設計された、PostgreSQL専用の手続き型言語
IF や FOR、変数、例外処理などが書ける
中から普通に SELECT / INSERT / UPDATE / DELETE を実行できる

という特徴を持っています。

イメージとしては、「SQLにちょっとした制御構文（if, for）がくっついたもの」です。
Python や JavaScript ほど自由ではないけれど、
「DBの中でやりたいこと」を書くには十分な機能があります。

まずは“超シンプルな関数”を書いてみる

「足し算関数で、構文の雰囲気を掴む」

いきなり複雑なことをやると心が折れるので、
まずは「2つの整数を足して返すだけ」の関数を書いてみます。

CREATE OR REPLACE FUNCTION add_two_numbers(a integer, b integer)
RETURNS integer
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN a + b;
END;
$$;
SQL

この1本の関数の中に、PL/pgSQLの基本要素が全部入っています。
1行ずつ分解して見ていきましょう。

PL/pgSQL関数の基本構造を分解する

「CREATE FUNCTION〜RETURNS〜LANGUAGE〜AS〜BEGIN〜END」

さっきの関数を、パーツごとに見ていきます。

CREATE OR REPLACE FUNCTION add_two_numbers(a integer, b integer)
RETURNS integer
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN a + b;
END;
$$;
SQL

関数ヘッダ部分

CREATE OR REPLACE FUNCTION add_two_numbers(a integer, b integer)
RETURNS integer
LANGUAGE plpgsql
SQL

ここでやっていることは、

関数名：add_two_numbers
引数：a integer, b integer
戻り値の型：RETURNS integer
使用言語：LANGUAGE plpgsql

を宣言しています。

CREATE OR REPLACE にしておくと、同じ名前の関数がすでにあっても上書きできます。
開発中はこれが便利です。

本体部分

AS $$
BEGIN
  RETURN a + b;
END;
$$;
SQL

AS $$ ... $$ の中が、PL/pgSQL の本体です。
$$ は「ここからここまでが関数の中身ですよ」という区切り記号だと思ってください（シェルのクォート対策でもあります）。

BEGIN ... END; の中が、実際に実行される処理です。
ここでは RETURN a + b; として、引数 a と b の和を返しています。

この「ヘッダ＋BEGIN〜END」の形が、PL/pgSQL関数の基本形です。

関数を呼び出してみる

「SQLから“普通にSELECTするだけ”」

作った関数は、SQLからこう呼び出せます。

SELECT add_two_numbers(3, 5);
SQL

結果はこうなります。

 add_two_numbers
-----------------
               8
(1 row)

つまり、「関数は“値を返すもの”として、SELECT の中で使える」ということです。
他の式と組み合わせることもできます。

SELECT add_two_numbers(10, 20) * 3;
SQL

この感覚が大事で、「PL/pgSQL関数は、SQLの世界に“自分専用の関数”を増やすイメージ」です。

例題：ユーザーのフルネームを返す関数

「“ちょっとしたロジック”をDB側に寄せる」

もう少し“DBっぽい”例をやってみましょう。
users テーブルがこうだとします。

CREATE TABLE users (
  id         bigserial PRIMARY KEY,
  first_name text NOT NULL,
  last_name  text NOT NULL
);
SQL

ここで、「ユーザーIDを渡すと、姓 名 のフルネームを返す関数」を作ってみます。

CREATE OR REPLACE FUNCTION get_user_full_name(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;
END;
$$;
SQL

ここで新しく出てきたポイントを深掘りします。

DECLAREとSELECT INTO

「PL/pgSQLの“変数”と“結果の受け取り方”」

さっきの関数の中で、こういう部分がありました。

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;
END;
SQL

DECLARE

DECLARE ブロックでは、「関数の中で使う変数」を宣言します。

ここでは、

v_full_name text;
SQL

として、「v_full_name という text 型の変数」を1つ用意しています。
命名ルールは自由ですが、PL/pgSQLでは「v_〜」のように変数だと分かる名前を付けることが多いです。

SELECT … INTO

普通のSQLでは、SELECT ... は「結果セット」を返しますが、
PL/pgSQLの中では、SELECT ... INTO 変数 という形で「結果を変数に代入」できます。

SELECT last_name || ' ' || first_name
  INTO v_full_name
  FROM users
 WHERE id = p_user_id;
SQL

この1文で、

users テーブルから、指定された id の行を1件探す。
その行の last_name と first_name を連結した文字列を作る。
その文字列を v_full_name 変数に代入する。

という処理をしています。

そのあとで、

RETURN v_full_name;
SQL

として、その変数の値を関数の戻り値として返しています。

関数を実際に呼んでみるイメージ

「SELECT get_user_full_name(1) のように使える」

この関数は、SQLからこう呼び出せます。

SELECT get_user_full_name(1);
SQL

結果は例えばこうです。

 get_user_full_name
--------------------
 山田 太郎
(1 row)

さらに、他のクエリの中でも使えます。

SELECT
  id,
  get_user_full_name(id) AS full_name
FROM users;
SQL

これで、「全ユーザーのIDとフルネーム」を一気に取ることができます。

ここでのポイントは、

「フルネームの作り方（姓＋スペース＋名）」というロジックを、
アプリ側ではなくDB側の関数に閉じ込めている

ということです。

もし将来、「スペースではなく ‘・’ でつなぎたい」「順番を 名 姓 にしたい」といった仕様変更があっても、
関数の中身を1箇所変えれば、関数を使っている全てのクエリに反映されます。

PL/pgSQLをどこまで使うか、という現実的な話

「“なんでもDBに書く”でも“全部アプリに任せる”でもない」

ここで一度、少し俯瞰しておきます。

PL/pgSQL は強力ですが、「なんでもかんでもDB側に書けばいい」というものではありません。
逆に、「全部アプリ側でやるからストアド関数は一切使わない」という極端なスタンスも、もったいないです。

現実的には、こういう分け方が多いです。

ビジネスロジックの大部分
→ アプリケーションコード（テストしやすい・デプロイしやすい層）に書く。

DBに近いロジック（集計・バリデーション・共通の更新処理など）
→ PL/pgSQL の関数としてまとめておき、SQLから再利用する。

例えば、

「注文を確定する処理（在庫チェック→ステータス更新→履歴追加）」
「特定の条件での集計ロジック」
「複雑なバリデーション（DB内の他のテーブルも参照する）」

などは、PL/pgSQL に寄せるとスッキリすることが多いです。

Day24 前半では、「PL/pgSQLは“DBに近いロジックをまとめるための道具”」という位置づけをイメージできれば十分です。

Day24 前半のまとめ

ストアド関数は「DBの中に保存され、SQLから呼び出せる関数」であり、PL/pgSQLはその関数を書くための「PostgreSQL専用の手続き型ミニ言語」で、CREATE FUNCTION〜RETURNS〜LANGUAGE plpgsql〜AS $$ BEGIN ... END; $$; という形で定義し、SELECT my_func(...) のように呼び出す。
PL/pgSQL では DECLARE で変数を宣言し、SELECT ... INTO 変数 でクエリ結果を受け取り、RETURN で値を返せるため、「ユーザーIDからフルネームを作る」といった“DBに近いロジック”を関数として閉じ込めておき、SQLから再利用できる――ここまでの構造とイメージが掴めていれば、Day24 前半としてはとても良いスタートラインに立てている。

次へ
12
スポンサーリンク
タイトルとURLをコピーしました