【PostgreSQL】ストアドファンクション・ストアドプロシジャ違いと用途を徹底比較

はじめに
使用環境
ストアドファンクション（Stored Function）とは？
ストアドプロシジャ（Stored Procedure）とは？
ストアドファンクション・ストアドプロシジャの作成と実行
ストアドファンクション・ストアドプロシジャの実行
有効活用できる場面
1. ストアドファンクション
2. ストアドプロシジャ
まとめ

はじめに

PostgreSQLを使った業務システム開発において、ストアドプロシジャ（PROCEDURE）とストアドファンクション（FUNCTION）は、処理の再利用性や保守性を高める重要な要素です。

本ブログでは、「psql」と「pgAdmin4」を使って、簡単な「本の在庫管理システム」を題材に、両者の違いや使い方を紹介します。

使用環境

Windows 11 Pro 24H2
PostgreSQL 17.6
pgAdmin4 9.6
Chrome 134.0.6998.205

ストアドファンクション（Stored Function）とは？

ストアドファンクションは、値を返す処理を定義するSQLの関数です。たとえば「在庫数を取得する」「売上を計算する」といった処理を関数として定義し、SELECT文などから呼び出すことができます。

特徴
- RETURNS で戻り値を定義
- SELECT や PERFORM で呼び出し
- 単一の値や集計結果を返す用途に最適
基本構文

CREATE [OR REPLACE] FUNCTION 関数名(引数 型, ...)
RETURNS 戻り値の型
LANGUAGE plpgsql
AS $$
DECLARE
  -- 変数宣言（任意）
BEGIN
  -- 処理内容
  RETURN 戻り値;
END;
$$;

ストアドプロシジャ（Stored Procedure）とは？

ストアドプロシジャは、複数ステップの処理や副作用を伴う業務ロジックをまとめた手続き型の処理です。たとえば「在庫を更新し、ログを記録する」といった一連の処理を1つのプロシジャにまとめることができます。

特徴
- 戻り値はなし（ただしOUTパラメータは使える）
- CALL で呼び出し
- BEGIN, COMMIT, ROLLBACK によるトランザクション制御が可能
- 複雑な業務処理に適している
基本構文

CREATE [OR REPLACE] PROCEDURE プロシジャ名(引数 型, ...)
LANGUAGE plpgsql
AS $$
DECLARE
  -- 変数宣言（任意）
BEGIN
  -- 処理内容（複数ステップ可）
  -- トランザクション制御も可能
END;
$$;

ストアドファンクション・ストアドプロシジャの作成と実行

テーブルの作成

先ず以下のテーブルを作成します。

booksテーブル
在庫数テーブル。在庫数を入力しておき、ストアドファンクションから参照されます。

CREATE TABLE books (
  book_id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  stock INT DEFAULT 0
);

stock_logs
在庫数の変化を記録するログテーブルです。ストアドプロシジャによって更新されます。

CREATE TABLE stock_logs (
  log_id SERIAL PRIMARY KEY,
  book_id INT REFERENCES books(book_id),
  change INT,
  log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ストアドファンクションの作成

在庫数を取得するストアドファンクションを作成します。

記号：$$は、「ドルクオート（dollar-quoting）」と呼ばれる構文で、関数やプロシジャの本体（つまりPL/pgSQLコード）を囲むための区切り記号です。

-- 関数を作成または置き換える（既存の関数があれば上書き）
CREATE OR REPLACE FUNCTION get_stock(book_title TEXT)
-- 戻り値の型を指定（在庫数：整数）
RETURNS INT AS $$
-- 変数宣言部：在庫数を一時的に保持する変数
DECLARE
  current_stock INT;
BEGIN
  -- booksテーブルから、指定されたタイトルの在庫数を取得し、変数に格納
  SELECT stock INTO current_stock
  FROM books
  WHERE title = book_title;
  -- 取得した在庫数を関数の戻り値として返す
  RETURN current_stock;
END;
-- PL/pgSQL言語で記述された関数であることを指定
$$ LANGUAGE plpgsql;