PostgreSQL ストアドプロシージャ実例チュートリアル

はじめに

PostgreSQL のストアドプロシージャは重要な役割を担っており、データベース管理において多くのメリットがあります。このチュートリアルでは、PostgreSQL でストアドプロシージャを使い、SQL 操作をより効率的かつ安全に処理する方法を解説します。本記事は、PostgreSQL の高度な機能を活用してデータベース管理スキルを高めたいソフトウェアエンジニアやデータベース管理者を対象としています。

本題に入る前に、私たちのオープンソースの PostgreSQL GUI、Beekeeper Studio をご紹介させてください。ストアドプロシージャや関数をサイドバーに一覧表示するので、データベースの中身をいつでも確認できます。PostgreSQL では無料でご利用いただけます。こちらからダウンロードできます。

ストアドプロシージャとは？

例に入る前に、ストアドプロシージャの概念を理解しておくことが重要です。PostgreSQL におけるストアドプロシージャとは、一連の SQL コマンドを含む、事前にコンパイルされた実行可能オブジェクトです。主な役割は、挿入、更新、削除、作成といったデータベース操作を実行することです。特に複数のステップや複雑なロジックを伴う操作に便利で、トランザクション管理の向上やデータベースの脆弱性の低減につながります。

注: このチュートリアルの例とコマンドは、PostgreSQL 11 以降のバージョンに対応しています。

前提条件

このチュートリアルでは、以下がインストールおよび設定済みである必要があります。

1. ローカルマシンまたはサーバーにインストールされた PostgreSQL。
2. SQL コマンドを実行するためのコマンドラインツール、または Beekeeper Studio などの GUI ツール。

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

PostgreSQL では従来、値を返すルーチンを定義するために FUNCTION キーワードが使われてきました。PostgreSQL の関数は処理を実行でき、スカラー値またはテーブルといった値や集合を必ず返します。また、SQL クエリの中で使用することもできます。

PostgreSQL 11 で導入された PROCEDURE は、値を返さないルーチンを定義するために使われます。プロシージャは、データを直接返さずにデータベースの状態を変更するといった処理に利用できます。プロシージャは CALL 文で呼び出され、SELECT 文の一部として呼び出されることが多い関数とは異なります。

PROCEDURE を使ってテーブルを更新する

まずは PostgreSQL でシンプルなストアドプロシージャを作成してみましょう。ここでは従業員の給与を更新するプロシージャを作成します。

CREATE OR REPLACE PROCEDURE update_employee_salary(p_id INT, p_salary NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
   UPDATE employees SET salary = p_salary WHERE employee_id = p_id;
END;
$$;

上記のコードでは、update_employee_salary がプロシージャ名で、従業員 ID（p_id）と新しい給与（p_salary）の 2 つのパラメータを受け取ります。

PostgreSQL でストアドプロシージャを呼び出す

作成したプロシージャを呼び出すには、以下のように CALL コマンドを使用します。

CALL update_employee_salary(1, 50000);

上記のコマンドにより、ID 1 の従業員の給与が 50000 に更新されます。

応用: 条件分岐を含むプロシージャ

プロシージャでは、条件文やエラー処理などのより複雑なロジックを使って、高度な処理を実行することもできます。

このプロシージャは、指定された条件に基づいて従業員の部署を更新するもので、プロシージャ内での制御構造の使い方を示しています。

CREATE OR REPLACE PROCEDURE update_employee_department(employee_id INT, new_department_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    IF new_department_id IS NOT NULL THEN
        UPDATE employees
        SET department_id = new_department_id
        WHERE id = employee_id;
    ELSE
        RAISE EXCEPTION 'Department ID cannot be null.';
    END IF;
END;
$$;

期待される出力:

CREATE PROCEDURE

FUNCTION を使ってデータを取得する

PROCEDURE とは対照的に、PostgreSQL の FUNCTION は戻り値が必要な処理に適しています。特に、データの取得や SQL クエリの一部として行う計算に便利です。ここでは、特定の条件に基づいて情報を取得する関数の例を作成します。

この関数は、ID に基づいて従業員の詳細を取得します。関数がデータを返し、そのデータを SQL クエリで直接利用できることを示します。

CREATE OR REPLACE FUNCTION get_employee_details(employee_id INT)
RETURNS TABLE(first_name VARCHAR, last_name VARCHAR, department_id INT) AS $$
BEGIN
    RETURN QUERY 
    SELECT first_name, last_name, department_id
    FROM employees
    WHERE id = employee_id;
END;
$$ LANGUAGE plpgsql;

期待される出力:

CREATE FUNCTION

関数を呼び出す

この FUNCTION でデータを取得するには、テーブルをクエリするのと同じように SELECT 文を使用します。

SELECT * FROM get_employee_details(1);

期待される出力:

ここでの出力は ID 1 の従業員の詳細です。従業員が存在する場合は、名、姓、部署 ID が表示されます。該当する従業員が存在しない場合、結果は空になります。

 first_name | last_name | department_id 
------------+-----------+---------------
 John       | Doe       | 1
(1 row)

まとめ

このチュートリアルでは、PROCEDURE と FUNCTION を使って PostgreSQL でストアドプロシージャを作成する方法と、その呼び出し方を学びました。ストアドプロシージャの使い方を理解することは、コードの整理、セキュリティ、実行速度の向上に役立ちます。今回の例はシンプルですが、より複雑な処理でも考え方は同じです。

PostgreSQL のストアドプロシージャは、ロジックをカプセル化し、処理を効率的かつ安全に実行するための堅牢な手段です。ストアドプロシージャをデータベース設計に組み込むことで、より保守しやすく信頼性の高いデータベースアプリケーションを実現できます。本ガイドを出発点として、PostgreSQL の手続き型機能が提供する幅広い可能性を探ってみてください。