【PostgreSQL入門】ストアドプロシージャとファンクションの作り方・使い方まとめ

#SQL

記事をシェア：

コピー

こんにちは、関口です。

SQLに慣れてくると、「同じ処理を何度も書くのが面倒…」「アプリからの処理をまとめて管理したい」と感じることはありませんか？

そんなときに役立つのが、ストアドプロシージャやストアドファンクションです。PostgreSQLでもこれらを活用することで、パフォーマンスの最適化や処理の共通化、セキュリティ強化など、さまざまなメリットが得られます。

この記事では、PostgreSQLでのストアドの作り方・使い方を、実際のSQLコード付きでわかりやすく解説します！

検証環境は以下になります。

PostgreSQL: 12.3

ストアドとは？

ストアドプロシージャやストアドファンクションは、データベースに対する複数の命令をまとめたプログラムです。主に以下の2種類があります。

ストアドプロシージャ
戻り値のない関数。「CREATE PROCEDURE」で作成する
ストアドファンクション
戻り値のある関数。「CREATE FUNCTION」で作成する

ストアドを使用するメリット

パフォーマンスの向上
ストアドは格納時に解析されるため、SQLを都度実行するよりも高速に動作します。
保守性の向上
ビジネスロジックをアプリケーションから分離できるため、変更時には関連するストアドだけを修正すれば対応可能です。
セキュリティの向上
ストアド経由のアクセスに制限することで、不用意な操作を防ぐことができます。

上記のようなメリットがありますが、ストアドが増えるとデータベースサーバの負荷も増加してしまうため、作りすぎには注意が必要です。

ストアドプロシージャを作成する

まず、練習用のテーブルを下記CREATE TABLEで作成します。

XXCREATE TABLE stored_training(
  id SERIAL NOT NULL, -- ID（シーケンス）
  title VARCHAR(255) NOT NULL, -- タイトル
  content TEXT NOT NULL, -- 内容
  date_from DATE, -- 開始日
  date_to DATE -- 終了日
);

レコードを追加するストアドプロシージャを作成します。タイトルと内容を引数として渡し、開始日と終了日は登録日から算出した値が設定されるように処理を作成します。

CREATE PROCEDURE sp_insert_data(v_title CHARACTER VARYING, v_content TEXT)
AS $$
DECLARE -- 変数を宣言できる。今回は使用しない
BEGIN -- 処理開始
  INSERT INTO stored_training VALUES (NEXTVAL('stored_training_id_seq'), v_title, v_content, CAST(NOW() AS DATE), CAST(NOW() AS DATE) + interval '3 month'); -- レコード追加
END; -- 処理終了
$$
LANGUAGE plpgsql; -- 言語を指定

作成したストアドプロシージャを実行します。

CALL sp_insert_data('練習１', 'ストアドプロシージャを使ってレコードを追加しました。');

実行した結果は以下の通りです。

postgres=# SELECT * FROM stored_training;
 id | title  |                       content                        | date_from  |  date_to
----+--------+------------------------------------------------------+------------+------------
  1 | 練習１ | ストアドプロシージャを使ってレコードを追加しました。 | 2024-09-15 | 2024-12-15
(1 行)

ストアドファンクションを作成する

次にストアドファンクションを作成してみましょう。練習用のテーブルは先ほど作成したものを使用します。IDを指定して、終了日を文字列で取得する関数を作成します。

CREATE FUNCTION sf_get_date_to_str(v_id INTEGER)
RETURNS CHARACTER VARYING -- 戻り値の型を宣言
AS $$
DECLARE -- 変数の宣言
  cnt BIGINT; -- COUNT用の変数
  str CHARACTER VARYING; -- 戻り値用の変数
BEGIN -- 処理開始
  -- 引数のIDに対するレコードの存在をCOUNTで確認
  SELECT COUNT(id) INTO cnt FROM stored_training WHERE id = v_id;

  if cnt > 0 THEN
    -- レコードが存在する場合、終了日を文字列にして返却
    SELECT TO_CHAR(date_to, 'YYYY年MM月DD日') INTO str FROM stored_training WHERE id = v_id;
  ELSE
    -- レコードが存在しない場合、存在しない旨の文言を返却
    str := '対象データなし';
  END IF;

  RETURN str;
END; -- 処理終了
$$
LANGUAGE plpgsql; -- 言語を指定

作成したストアドファンクションを実行します。

SELECT sf_get_date_to_str(1); -- レコードありのケース
SELECT sf_get_date_to_str(2); -- レコードなしのケース

実行した結果は以下の通りです。

postgres=# SELECT sf_get_date_to_str(1);
 sf_get_date_to_str
--------------------
 2024年12月15日
(1 行)

postgres=# SELECT sf_get_date_to_str(2);
 sf_get_date_to_str
--------------------
 対象データなし
(1 行)

ストアドを更新・削除する

ストアドを更新する場合は、「CREATE」の後ろに「OR REPLACE」を付けます。ORと書いている通り、新規作成の場合にも使用できます。

CREATE OR REPLACE PROCEDURE sp_insert_data(v_title CHARACTER VARYING, v_content TEXT)
AS $$
DECLARE
BEGIN
  -- 省略
END;
$$ LANGUAGE plpgsql;

削除する場合は、下記です。

-- ストアドプロシージャの場合
DROP PROCEDURE sp_insert_data(v_title CHARACTER VARYING, v_content TEXT);

-- ストアドファンクションの場合
DROP FUNCTION sf_get_date_to_str(v_id INTEGER);

今回作成したような引数がある関数の場合は引数を設定します。引数がない関数場合は括弧の中には何も設定しません。