関数とプロシージャ

はじめに

PostgreSQLの関数に関してまとめてみました。
MySQLなど他のデータベースとは異なる箇所があるかもしれません。ご了承ください。

関数とプロシージャ

PostgreSQLでは、データベースに関数を定義することができます。
一般的にはストアドプロシージャと呼ばれます。
ストアドプロシージャでは、DBMSに備わっている便利な関数や機能を利用できるので、アプリケーション側での複雑な処理をDBMS側に任せることができます。
また、大量のデータをアプリケーション側へ渡さずにデータベースで処理できるので、ネットワークの負荷軽減なども期待できます。

関数とプロシージャの違い

PostgreSQLでは関数(FUNCTION)とプロシージャ(PROCEDURE)の2種類のストアドプロシージャを作成できます。
役割や定義はほぼ同じですが、以下のような違いがあります。

• 関数は戻り値を定義できるが、プロシージャはできない。
• 関数はSELECTで呼び出すが、プロシージャはCALLで呼び出す。
• 関数は定義内でトランザクションのCOMMITやROLLBACKができないが、プロシージャはできる。

関数定義

基本的な書式は以下です。

• 関数

CREATE FUNCTION 関数名(引数) RETURNS [SETOF] 戻り値 AS $$ 関数の記述
$$ LANGUAGE 使用言語;

関数の具体例

CREATE FUNCTION add_func(int, int) RETURNS bigint AS $$
SELECT ($1 + $2)::bigint /* 引数2つを足す */
$$ LANGUAGE SQL;
SELECT add_func(1,2); /* 関数呼び出し */

• プロシージャ

CREATE [ON REPLACE] PROCEDURE プロシージャ名(引数) AS $$ 関数の記述
$$ LANGUAGE 使用言語;

プロシージャの具体例

CREATE PROCEDURE insert_proc(int, text) AS $$
INSERT INTO tbl VALUES($1, $2) /* tblテーブルに引数の値をinsertする */
$$ LANGUAGE SQL;
CALL insert_proc(123, 'test'); /* プロシージャ呼び出し */

PL/pgSQLで記述する関数について

PL/pgSQLとは

PL/pgSQL（Procedural Language/PostgreSQL Structured Query Language）は、オープンソースのリレーショナルデータベースであるPostgreSQLのための手続き型言語です。この言語を使うことで、SQLのクエリに加え、より複雑な制御構造やエラーハンドリングなどを利用でき、PostgreSQLのデータベース内での高度なデータ処理が可能となります。

PL/pgSQLで記述する基本的な関数の構造は以下になります。

DECLARE
  変数の宣言
BEGIN
  関数の処理記述
END

上記の変数宣言についてもう少し詳しくみていきます。
変数宣言の基本的な書式は以下になります。

変数名 [CONSTANT] データ型 [NOT NULL] [{DEFAULT | := } 値など]

変数宣言の具体例

var_1 int DEFAULT 10; -- integer型でデフォルトを10とした変数var_1の宣言

var_2 CONSTANT text := 'Sample'; -- text型でSampleを固定値とした変数var_2の宣言

PL/pgSQLの関数の具体例

CREATE TABLE tbl (c1 int, c2 timestamp); tblテーブルを作成

CREATE OR REPLACE FUNCTION test_func(int, timestamp) RETURNS integer AS $$
DECLARE
  r timestamp;
  result int := 0;
BEGIN
  FOR r IN SELECT c2 FROM tbl WHERE c1 = $1 -- FOR文でc2の値を逐次rへ代入
  LOOP
    IF r < $2 -- 第2引数のtimestampの情報より古い日付かチェック
    THEN
      result := result + 1
    END IF; -- IFの終了
  END LOOP; -- LOOPの末端
  RETURN result; -- 戻り値を返却
END
$$
LANGUAGE plpgsql;