PolygonBase

Zukamiso's blog!

PL/pgSQLに入門した

2025-07-17


仕事で PostgreSQL の PL/pgSQL を使うことになったのだが、書いたことはないし、読めもしない。 さすがにこのままタスクを進められる気がしないので勉強のために調べてアウトプットとして残しておく。

PL/pg SQL はなんのために使うのか

とある時系列データを PostgreSQL に記録する機能の実装を担当することになった。 timestamp 付きのレコードがクライアントから送られてくるので、それをDBに保存するだけの割とシンプルな実装なのだが、問題は5分や1時間単位でダウンサンプリングする必要がある。 実装の方針としてもDBでできることはDBに寄せた方がいいという設計思想ではある。

ここで疑問になるのは、5分ごとのデータをどこかにバッファしておいて、それをアグリゲートして INSERT するのか、レコード自体は毎度 INSERT して5分間のデータが集まった段階でアグリゲートするのかという点が気になった。 結果的には INSERT のタイミングでアグリゲートして、集計単位時間ごとにレコードを保存することになった。

PL/pgSQL とは？

PostgreSQL の公式ドキュメントの概要には PL/pgSQLで作成した関数は、組み込み関数が使えるところであれば、どこでも使用できます。 とあるので、INSERT 時に関数を実行して集計することもできそう。

特徴

PL/pgSQL の特徴は次の通り。

  • PostgreSQL の拡張言語である
  • SQL の拡張であり、SQL の文法をベースにしている
  • 手続き型言語であり、変数や制御構造を持つ
  • 関数やトリガーを作成するために使用される

実際に PL/pgSQL の使い方

docker で PostgreSQL を立ち上げて、PL/pgSQL の関数を作成してみる。

docker run --name postgres -e POSTGRES_PASSWORD=postgres -d postgres
docker exec -it postgres psql -U postgres

PostgreSQL に接続できたら、PL/pgSQL の関数を作成するために適当なデータベースを作成する。

CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

作成されているか確認する。

SELECT proname FROM pg_proc WHERE proname = 'add_numbers';
   proname
-------------
 add_numbers
(1 row)

この関数は、2つの整数を受け取り、その合計を返す簡単な関数である。 関数を作成したら、実際に呼び出してみる。

SELECT add_numbers(3, 5);
-- 8 が返る
 add_numbers
-------------
           8

次に、PL/pgSQL の制御構造を使って、条件分岐やループを実装してみる。

CREATE OR REPLACE FUNCTION test_factorial(n INTEGER)
RETURNS INTEGER AS $$
DECLARE
    result INTEGER := 1;
BEGIN
    IF n < 0 THEN
        RAISE EXCEPTION 'Negative input not allowed';
    ELSIF n = 0 THEN
        RETURN 1;
    ELSE
        FOR i IN 1..n LOOP
            result := result * i;
        END LOOP;
        RETURN result;
    END IF;
END;
$$ LANGUAGE plpgsql;

この関数は、与えられた整数の階乗を計算するものである。 負の数が入力された場合は例外を発生させ、0の場合は1を返す。 それ以外の場合は、1からnまでの整数をループして、その積を計算する。 関数を作成したら、実際に呼び出してみる。

SELECT test_factorial(5);
-- 120 が返る
 test_factorial
----------------
            120
(1 row)

まとめ

PL/pgSQL は PostgreSQL で関数を実装することができる。 DB に処理を寄せることができるので、アプリケーション側の負荷を軽減したり、効率化を図ることができる。 また、SQL の拡張であり、手続き型言語であるため、変数や制御構造を使って柔軟な処理を実装できる。 データの集計はアプリケーション側で行うこともできるが、PL/pgSQL を使うことで、データベース内で効率的に処理を行うことができるので今後は積極的に使っていきたい。

Back to articles