『入門向け』PostgreSQLでシンプルなプロシージャを作成する

はじめに

この記事はPostgreSQLのPL/pgSQLという言語で作成するプロシージャについて、難しい処理は行わないシンプルなプロシージャをベースに、それを少しずつ拡張しながらプロシージャの開発について説明していきます。

環境

この記事の内容はWindows 10で作成、動作確認しています。

OS: Windows 10
PostgreSQL 12.2

参考

シンプルなプロシージャ

最初に下記のシンプルなプロシージャで、プロシージャの構造について説明したいと思います。このプロシージャは、実行するとコンソール画面に『calling 'sample_proc()' at 2020-07-16 13:04:27.652065+09』のようなメッセージを表示するだけです。

/*
 * 1) プロシージャの宣言
 */
CREATE PROCEDURE sample_proc()
-- 2) 記述言語を指定
LANGUAGE plpgsql
-- 3) プロシージャ本体を囲む引用符を$$に指定
AS $$
/* 4) プロシージャ本体は BEGIN - ENDブロックで囲む */
BEGIN
  /*
   * 5) RAISE NOTICEはメッセージを標準出力に出力する
   */
  RAISE NOTICE 'calling ''sample_proc()'' at %.', now();
END
$$;

プロシージャの作成

プロシージャを実行するには、その前にCREATE PROCEDUREコマンドでプロシージャを作成する必要があります。

上記のコードをsample_proc.sqlという名前のソースファイルに保存します。次にpsqlでログインし、\iメタコマンドでこのソースファイルを読み込んでプロシージャを作成します。

> \i sample_proc.sql
CREATE PROCEDURE

コンソールにCREATE PROCEDUREと表示されればプロシージャの作成は成功です。

メタコマンドとは

メタコマンドとはSQLコマンドとは違う、PostgreSQLに対して指示を行うコマンドです。良く使うメタコマンドでは、テーブル、ビュー、シーケンスの一覧を表示する\d、データベースの一覧を表示する\lなどがあります。 \iメタコマンドは、指定したファイルを読み込んで、そのファイルに記述されているSQLコマンドを実行します。

プロシージャの構造

1) プロシージャの宣言

プロシージャを作成するにはCREATE PROCEDUREコマンドを使用します。その後に続くsample_procがプロシージャ名です。プロシージャが引数を取る場合は( )に引数のリストを指定しますが、引数がなければ空の( )のままです。

CREATE PROCEDURE sample_proc()
^^^^^^^^^^^^^^^^ ^^^^^^^^^^^
 |                |
 |                +--- プロシージャ名
 |
 +-------------------- プロシージャを作成するコマンド

このソースファイルを実行してプロシージャを作成すると1度目は成功しますが、2度目は以下のエラーで失敗します。

psql:sample_proc.sql:15: ERROR:  function "sample_proc" already exists with same argument types

このエラーメッセージの通り、すでにsample_procというプロシージャが作成されているため、2回目の作成がエラーになります。この場合は、ソースコードの先頭にプロシージャを削除するDROP PROCEDUREコマンドを追加すると、sample_procというプロシージャが作成されていれば削除し、次にプロシージャの作成が行われるのでこのエラーを回避できます。

DROP PROCEDURE IF EXISTS sample_proc();

/*
 * 1) プロシージャの宣言
 */
CREATE PROCEDURE sample_proc()

//...省略...

なお、この書き方の他にCREATE OR REPLACEを使う方法もあります。この記事ではこちらの方法でコードを書きます。

/*
 * 1) プロシージャの宣言
 */
CREATE OR REPLACE PROCEDURE sample_proc()

//...省略...

2) 記述言語

プロシージャの記述言語を指定します。PL/pgSQLの場合はplpgsqlとします。

3) プロシージャ本体の引用符

プロシージャ本体のコードは文字列リテラルとして記述します。通常文字列リテラルは単一引用符(')で囲みますが、単一引用符だとソースコードの記述に不便なことがある（シングルクォートやバックスラッシュのエスケープ）ので慣習的に$$を指定することが多いようです。

4) プロシージャ本体

BEGINブロック

プロシージャのコードは下記のようにBEGIN - ENDブロックで定義します。なおこのBEGINというキーワードはブロック構造を定義するためのもので、トランザクションの開始を表すBEGINとは別のものです。なのでBEGINブロックの開始はトランザクションの開始を意味しません。

BEGIN
  プロシージャのコード
END

サブブロック

BEGINブロックはネストすることができます。下記はプロシージャ本体のBEGINブロックに、2つのサブブロックが含まれているコードを表しています。なお、サブブロックの終了を示すENDにはセミコロンが必要ですが、プロシージャ本体を定義するENDのセミコロンは省略できます。

BEGIN

  BEGIN
    サブブロック
  END;

  BEGIN
    サブブロック
  END;

END

5) メッセージの出力

簡単にメッセージ出力の方法に触れておきます。PL/pgSQLではメッセージ出力にRAISE <level>構文を使用します。基本的なRAISEの構文は下記のようになります。levelは省略可能で省略した場合はEXCEPTIONになります。'format'にはメッセージを文字列リテラルで記述します。メッセージに式の結果や変数の値を埋め込みたい場合は%というプレースフォルダを使用します。 'format'の後にカンマ区切りでプレースフォルダに埋め込みたい値を記述します。

RAISE [level] 'format' [, expression [, ...] ]

この例ではNOTICEというレベルでメッセージを出力し、%の位置にnow()の結果を埋め込んでいます。

RAISE NOTICE 'calling ''sample_proc()'' at %.', now();

指定できるレベルには下記のものがあります。

DEBUG
LOG
INFO
NOTICE
WARNING
EXCEPTION (デフォルト)

レベルを省略した場合のデフォルトはEXCEPTIONですが、このレベルを指定するとエラーを発生させトランザクションを失敗させます。 EXCEPTION以外のレベルを指定した場合、優先度の異なるメッセージがクライアント（標準出力）やサーバーログに出力されます。

6) コメント

ブロックコメントは /* */ で囲みます。

1行コメントは、--で始めます。

/*
 * ブロックコメント
 */

-- 一行コメント

プロシージャの実行方法

callコマンドでプロシージャを実行します。

> call sample_proc();
NOTICE:  calling 'sample_proc()' at 2020-07-16 13:04:27.652065+09.
CALL

プロシージャの削除

DROP PROCEDUREコマンドでプロシージャを削除します。

> drop procedure sample_proc;
DROP PROCEDURE

シンプルなプロシージャに少し手を加えてみる

次に上記の例で扱ったプロシージャに、引数で指定された値をテーブルに追加するという処理を追加してみます。ここで扱うテーブルは下記のmemoという名前のテーブルです。

CREATE TABLE memo (
  id BIGSERIAL,
  title VARCHAR(1000) NOT NULL,
  description TEXT NULL,
  done BOOLEAN NULL,
  create_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
  update_at TIMESTAMP WITHOUT TIME ZONE NULL,
  PRIMARY KEY (id)
);

引数を取る

memoテーブルに追加する値を引数で取るようにします。引数はプロシージャ名の後に続く()の中に引数名とそのデータ型をペアで列挙します。この例では引数名にv_という接頭辞を付けて定義しています

CREATE OR REPLACE PROCEDURE sample_proc(
  v_title VARCHAR,
  v_description TEXT,
  v_done BOOLEAN)

プロシージャを実行するときは

> call sample_proc('new memo title', 'new memo description', false);

のようにして引数を与えます。

引数にデフォルト値を指定する

引数を省略した場合、デフォルト値を適用するにはDEFAULTを使用します。たとえばプロシージャの実行時に3番目の引数(v_done)が省略された場合、デフォルト値をfalseとするには下記のように記述します。

CREATE OR REPLACE PROCEDURE sample_proc(
  v_title VARCHAR,
  v_description TEXT,
  v_done BOOLEAN DEFAULT false)

3番目の引数は省略可能なので、以下のようにもプロシージャを実行することができます。この場合引数v_doneの値はfalseになります。

> call sample_proc('new memo title', 'new memo description');

テーブルにデータを追加する

データを追加するにはINSERT文を使いますが、プロシージャではSQL文を直接扱えるので下記のようにINSERT文を記述できます。

CREATE OR REPLACE PROCEDURE sample_proc(
  v_title VARCHAR,
  v_description TEXT,
  v_done BOOLEAN DEFAULT false)
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE NOTICE 'calling ''sample_proc()'' at %.', now();

  INSERT INTO memo (title, description, done) VALUES (v_title, v_description, v_done);

END
$$;

この時点でソースファイルからプロシージャを作成しなおして実行してみます。

> \i sample_proc.sql
CREATE PROCEDURE

> call sample_proc('memo title', 'memo description', false);
NOTICE:  calling 'sample_proc()' at 2020-07-16 17:03:01.048974+09.
CALL

psqlなどでデータが追加されたか確認してみます。

> select * from memo;
 id |     title      |             description             | done |         create_at          | update_at
----+----------------+-------------------------------------+------+----------------------------+-----------
  1 | memo title     | memo description                    | f    | 2020-07-16 17:03:01.048974 |
(1 行)

変数を宣言してみる

BEGINブロックにはDECLARE句という変数を宣言できる宣言部があり、ここで宣言した変数はプロシージャ本体で使用することができます。この例ではv_last_idというINTEGER型の変数を宣言し、memoテーブルにデータを追加した際に発番されたIDを格納するために使用しています。

DECLARE
  -- 変数の宣言
  v_last_id INTEGER := 0;
BEGIN
  RAISE NOTICE 'calling ''sample_proc()'' at %.', now();

  INSERT INTO memo (title, description, done) VALUES (v_title, v_description, v_done) RETURNING id INTO v_last_id;

  RAISE NOTICE 'new memo id(%)', v_last_id;

END;

このコードでプロシージャを再作成し実行すると

> call sample_proc('memo title', 'memo description', false);
NOTICE:  calling 'sample_proc()' at 2020-07-16 17:21:51.449391+09.
NOTICE:  new memo id(2)
CALL

のように出力されます。

型のコピー

v_last_idの変数宣言の部分は%TYPEを使用して型のコピーをするとテーブル定義の変更に強いコードになります。下記はmemoテーブルのidカラムと同じ型でv_last_id変数を宣言しています。

v_last_id memo.id%TYPE := 0;

%TYPEは引数の型にも使用できるので下記のように書き直すことができます。

CREATE OR REPLACE PROCEDURE sample_proc(
  v_title memo.title%TYPE,
  v_description memo.description%TYPE,
  v_done memo.done%TYPE DEFAULT false)

この状態でプロシージャを再作成すると

> \i sample_proc.sql
psql:sample_proc.sql:42: NOTICE:  type reference memo.title%TYPE converted to character varying
psql:sample_proc.sql:42: NOTICE:  type reference memo.description%TYPE converted to text
psql:sample_proc.sql:42: NOTICE:  type reference memo.done%TYPE converted to boolean
CREATE PROCEDURE

のメッセージが表示されます。

行型変数を使ってみる

%ROWTYPEを使って行型変数を宣言することができます。下記はmemoテーブルの行と同じ構造を持つ行型変数を宣言しています。

v_memo memo%ROWTYPE;

行型変数を使うとSELECTの結果を行型変数へ代入することができます。

SELECT * INTO v_memo FROM memo WHERE id = v_last_id;

この時点でのソースコード

ここまでの内容を反映したソースコードです。

CREATE OR REPLACE PROCEDURE sample_proc(
  v_title memo.title%TYPE,
  v_description memo.description%TYPE,
  v_done memo.done%TYPE DEFAULT false)
LANGUAGE plpgsql
AS $$
DECLARE
  v_last_id memo.id%TYPE := 0;
  v_memo memo%ROWTYPE;
BEGIN
  RAISE NOTICE 'calling ''sample_proc()'' at %.', now();

  INSERT INTO memo (title, description, done) VALUES (v_title, v_description, v_done) RETURNING id INTO v_last_id;

  RAISE NOTICE 'new memo id(%)', v_last_id;

  SELECT * INTO v_memo FROM memo WHERE id = v_last_id;

  RAISE NOTICE 'new memo title=%, description=%', v_memo.title, v_memo.description;

END
$$;

エラーを捕捉する処理を加える

プロシージャの処理中にエラーが起きた場合、そのエラーを捕捉して何らかの処理を行いたい場合はBEGINブロックのEXCEPTION句に、エラーの捕捉条件と行いたい処理を記述します。

エラーの捕捉条件はWHEN {エラー条件} THENのように記述します。{エラー条件}の部分には予め決められたエラーコード若しくは条件名を指定します。どのようなエラーコード、条件名が定義されているかは付録A PostgreSQLエラーコードに一覧が記載されています。

下記は0除算時に発生するエラーdivision_by_zeroを捕捉し、メッセージを表示する例です。

BEGIN

  -- 0除算を行う処理

EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'caught division_by_zero';
END;

複数のエラーを捕捉する

エラーの捕捉条件は下記のように複数記述することができます。

BEGIN

  -- 何らかのエラーが起きる処理

EXCEPTION
  WHEN no_data_found THEN
    -- エラー後処理A
  WHEN too_many_rows THEN
    -- エラー後処理B
END;

上記の例ではno_data_foundとtoo_many_rowsとで、別々のエラー後処理を行っていますが、同じエラー後処理で対応したい場合はORでつなげます。

BEGIN

  -- 何らかのエラーが起きる処理

EXCEPTION
  WHEN no_data_found OR too_many_rows THEN
    -- エラー後処理
END;

OTHERS

OTHERSという特殊なエラー条件があります。このエラー条件はQUERY_CANCELEDとASSERT_FAILUREを除く全てのエラーに合致します。なので何らかのエラーを捕捉したいといった場合は下記のように書けます。

BEGIN

  -- 何らかのエラーが起きる処理

EXCEPTION
  WHEN OTHERS THEN
    -- エラー後処理
END;

エラーを捕捉しなかった場合

プロシージャの処理中にエラーが起きた場合、

エラーを捕捉するEXCEPTION句を書いていない
WHEN {エラー条件} THENで指定したエラー条件が合致しなかった

などのときはプロシージャの処理は中断し、呼び出し元のトランザクションも中断します。

この時点でのソースコード

エラーの捕捉を反映したソースコードです。

CREATE OR REPLACE PROCEDURE sample_proc(
  v_title memo.title%TYPE,
  v_description memo.description%TYPE,
  v_done memo.done%TYPE DEFAULT false)
LANGUAGE plpgsql
AS $$
DECLARE
  v_last_id memo.id%TYPE := 0;
BEGIN
  RAISE NOTICE 'calling ''sample_proc()'' at %.', now();

  BEGIN
    INSERT INTO memo (title, description, done) VALUES (v_title, v_description, v_done) RETURNING id INTO v_last_id;
    RAISE NOTICE 'new memo id(%)', v_last_id;
  EXCEPTION
    WHEN not_null_violation THEN
      RAISE EXCEPTION 'title column must not null';
  END;

  DECLARE
    v_memo memo%ROWTYPE;
  BEGIN
    SELECT * INTO v_memo FROM memo WHERE id = v_last_id;
    RAISE NOTICE 'new memo title=%, description=%', v_memo.title, v_memo.description;
  EXCEPTION
    WHEN no_data_found THEN
      RAISE WARNING 'not found memo id(%)', v_last_id;
  END;

END
$$;

以上でシンプルなプロシージャの作成方法の説明は終わりです。

補足

追加チェック

PL/pgSQLにはソースコードの追加チェックを行う機能があります。開発時やテスト時にはplpgsql.extra_warningsとplpgsql.extra_errors変数の値をallにすることが推奨されています。この変数を有効にすることで、警告やエラーとなる箇所があればプロシージャのコンパイル時や実行時にメッセージが出力されるようになります。

SET plpgsql.extra_warnings TO 'all';
SET plpgsql.extra_errors TO 'all';

これらの変数には

none (デフォルト)
all
shadowed_variables
strict_multi_assignment
too_many_rows

の値を指定することができます。allは下3つをすべて指定することと同じ意味になります。

たとえば、プロシージャの下記の行を

SELECT * INTO v_memo FROM memo WHERE id = v_last_id;

このように修正して、実行してみます。（memoテーブルには複数行データが格納されています）

SELECT * INTO v_memo FROM memo;

実行結果の通りエラーは起きませんが、追加したデータとは違うデータが表示されています。

> \i sample_proc.sql
CREATE PROCEDURE

> call sample_proc('CCCCCC', 'DDDDDDDDDD');
NOTICE:  calling 'sample_proc()' at 2020-07-17 15:50:11.70216+09.
NOTICE:  new memo id(6)
NOTICE:  new memo title=AAAAAA, description=BBBBBBBBBB
CALL

ソースファイルの先頭にこの2行を追加して、再度実行してみます。

SET plpgsql.extra_warnings TO 'all';
SET plpgsql.extra_errors TO 'all';

このようにエラーが起きるようになりました。

> \i sample_proc.sql
CREATE PROCEDURE

> call sample_proc('EEEEEE', 'FFFFFFFFFF');
NOTICE:  calling 'sample_proc()' at 2020-07-17 15:58:38.629452+09.
NOTICE:  new memo id(7)
ERROR:  query returned more than one row
HINT:  Make sure the query returns a single row, or use LIMIT 1.
CONTEXT:  PL/pgSQL function sample_proc(character varying,text,boolean) line 22 at SQL statement

作成したプロシージャを確認する

プロシージャの実行時にエラーが起きると、どの行でエラーが起きたかがエラーメッセージに含まれることがあります。

ERROR:  query returned more than one row
HINT:  Make sure the query returns a single row, or use LIMIT 1.
CONTEXT:  PL/pgSQL function sample_proc(character varying,text,boolean) line 22 at SQL statement

しかし、この行番号(line 22 at SQL statement)はコンパイル後の行番号なので手元のソースファイルを見ても位置を特定しにくいです。この場合は\sfメタコマンドを使用して作成したプロシージャを確認することができます。

プロシージャが引数を取る場合は、プロシージャ名だけでなく引数の型のリストも必要です。

react_db=> \sf sample_proc(varchar,text,boolean)
CREATE OR REPLACE PROCEDURE public.sample_proc(v_title character varying, v_description text, v_done boolean DEFAULT false)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
  v_last_id memo.id%TYPE := 0;
-- 4) 関数本体は BEGIN - ENDブロックで囲む
BEGIN
  /*
   * 5) RAISE NOTICEはメッセージを標準出力に出力する
   */
  RAISE NOTICE 'calling ''sample_proc()'' at %.', now();

  BEGIN
    INSERT INTO memo (title, description, done) VALUES (v_title, v_description, v_done) RETURNING id INTO v_last_id;
    RAISE NOTICE 'new memo id(%)', v_last_id;
  EXCEPTION
    WHEN not_null_violation THEN
      RAISE EXCEPTION 'title column must not null';
  END;

  DECLARE
    v_memo memo%ROWTYPE;
  BEGIN
    SELECT * INTO v_memo FROM memo /*WHERE id = v_last_id*/;
    RAISE NOTICE 'new memo title=%, description=%', v_memo.title, v_memo.description;
  EXCEPTION
    WHEN no_data_found THEN
      RAISE WARNING 'not found memo id(%)', v_last_id;
  END;

END
$procedure$

メタコマンドに+を付けると行番号が付加されます。これで22行目がどこかを確認することができます。

> \sf+ sample_proc(varchar,text,boolean)
        CREATE OR REPLACE PROCEDURE public.sample_proc(v_title character varying, v_description text, v_done boolean DEFAULT false)
         LANGUAGE plpgsql
1       AS $procedure$
2       DECLARE
3         v_last_id memo.id%TYPE := 0;
4       -- 4) 関数本体は BEGIN - ENDブロックで囲む
5       BEGIN
6         /*
7          * 5) RAISE NOTICEはメッセージを標準出力に出力する
8          */
9         RAISE NOTICE 'calling ''sample_proc()'' at %.', now();
10
11        BEGIN
12          INSERT INTO memo (title, description, done) VALUES (v_title, v_description, v_done) RETURNING id INTO v_last_id;
13          RAISE NOTICE 'new memo id(%)', v_last_id;
14        EXCEPTION
15          WHEN not_null_violation THEN
16            RAISE EXCEPTION 'title column must not null';
17        END;
18
19        DECLARE
20          v_memo memo%ROWTYPE;
21        BEGIN
22          SELECT * INTO v_memo FROM memo /*WHERE id = v_last_id*/;
23          RAISE NOTICE 'new memo title=%, description=%', v_memo.title, v_memo.description;
24        EXCEPTION
25          WHEN no_data_found THEN
26            RAISE WARNING 'not found memo id(%)', v_last_id;
27        END;
28
29      END
30      $procedure$

rubytomato's “Getting Started”

Webアプリケーション開発の入門的な記事を投稿していきます。

『入門向け』PostgreSQLでシンプルなプロシージャを作成する

はじめに

シンプルなプロシージャ

プロシージャの作成

プロシージャの構造

1) プロシージャの宣言

2) 記述言語

3) プロシージャ本体の引用符

4) プロシージャ本体

BEGINブロック

サブブロック

5) メッセージの出力

6) コメント

プロシージャの実行方法

プロシージャの削除

シンプルなプロシージャに少し手を加えてみる

引数を取る

引数にデフォルト値を指定する

テーブルにデータを追加する

変数を宣言してみる

型のコピー

行型変数を使ってみる

この時点でのソースコード

エラーを捕捉する処理を加える

複数のエラーを捕捉する

OTHERS

エラーを捕捉しなかった場合

この時点でのソースコード

補足

追加チェック

作成したプロシージャを確認する