PostgreSQLで手続 き言語を動かす PL/pgSQL入門 目黒 聖

自己紹介 • 目黒聖（めぐろ・たかし）（Twitter:@tameguro） • 都内某SI勤務のSE • PostgreSQLの設計・導入・保守などをやっています • PostgreSQL歴はだいたい14～5年くらい • 日本PostgreSQLユーザ会の正会員になりました

ある日の待機時間…… 障害に備えて深夜待機し てるけど、何も起こらな いし暇だなあ…… とはいえ手元には PostgreSQLしかないし、 PostgreSQLで○×ゲームで もするか……

○×ゲームといえども…… • 指定した位置に○と×を交互に記入 • すでに○または×が記入された位置には新しく記入することは できない • 縦横斜めのどれか1列に、先に同じマークを記入した方が勝ち • 記入する場所がなくなり、どちらも1列も揃えていない場合は 引き分け • 画面に現在の○×の状況を表示 • 少なくともこのくらいのルールがあるが、これらすべてを単純 なSQLで実現するのは相当難しい…… • SQLでプログラミングができれば……

PL/pgSQL

SQLで手続き処理を書く言語 – PL/pgSQL • SQLはプログラミング言語ではなく問い合わせ言語であり、SQL を拡張し、手続き処理をできるようにしたのがPL/pgSQL • PostgreSQLで手続き処理を作成する際に最もよく使用される言 語であり、ストアドファンクション・ストアドプロシージャの 作成にも使用される • PLはProcedural Language（手続き型言語）の略 • OracleのPL/SQLを参考にしており、書き方がよく似ている • 実はPL/pgSQL 以外にもPostgreSQLで手続き処理を書く言語はあ りますが、デフォルトでは使用できず、拡張機能として作成さ れているものもあるため、今回は説明を省きます

ストアドファンクション／プロシージャ • DB内に関数（function）や手続（procedure）をプログラミング して保存（stored）しておき、必要時に呼び出すことで決まっ た処理をさせることができます • 主要なRDBMS製品には搭載されており、PostgreSQLにも機能が 存在し、PL/pgSQLで作成されます • 特に有名で、使用されていることが多いのはOracle • 特に、昔からOracleを使い倒しているところでは、必ずと言っていいほ ど使われている印象です • バッチ処理だけではなくオンライン処理もストアドで書かれていて、 アプリ側にはデータに関する処理がほとんどないところもありました

ストアドファンクション／プロシージャ • ストアドファンクション • SQLで実行する関数を作成する • ○×ゲームもストアドファンクションで作成しました • PostgreSQL 10まではこれしかありませんでした • ストアドプロシージャ • SQLから呼び出す関数ではなく一連の手続きを定義する • PostgreSQL 11から実装されました • 機能や定義の方法だけではなく、呼び出し方もストアドファンクショ ンとは異なります

PostgreSQLで○×ゲーム（デモ）

PL/pgSQL

ソース全文について  CREATE FUNCTIONを含めた全文を知りたい方は、以下のURLにて公開しています。 資料に全文を載せられないので、見ながらどこの話をしているのか確認してい ただけると理解に役に立つと思います。 https://qiita.com/tameguro/items/957b2bbc143c2ba7187f

準備：○×ゲーム用テーブルの作成 CREATE TABLE marubatsu_field ( row_id integer NOT NULL CHECK (1 <= row_id AND row_id <= 3), column_id integer NOT NULL CHECK (1 <= column_id AND column_id <= 3), mark char(1), PRIMARY KEY (row_id, column_id) ); 1レコードが○×のマス一つを表しています。 mark (○か×か) column_id (列番号) row_id (行番号) null 1 1 ○ 2 1 null 3 1 × 1 2 null 2 2 … 3 2 1 ○ 1 × 2 3 ○×ゲーム表示イメージ

CREATE FUNCTION  CREATE FUNCTION構文の基本。オプションはたくさんありますが、よく使うも のを抜き出すと以下のようになります。 CREATE OR REPLACE FUNCTION marubatsu(rownum integer, columnnum integer) RETURNS text AS $$ 関数本文 ←ここをPL/pgSQLで記述する $$ LANGUAGE plpgsql;

CREATE FUNCTIONの要素  CREATE FUNCTION：ストアドファンクションを作成するCREATE文。「CREATE OR REPLACE」とすることで、既存の関数を上書きできます。UPDATEはありません。  関数名：関数の名前です。○×ゲームでは「marubatsu」としています。既存 の名前と重複しても、引数が異なる場合は別の関数として作成されます。  引数：○×ゲームでは「rownum」 と「columnnum」の2つのintegerを引数にし ています。引数の名前は実は必須ではありませんが、関数本文の中で変数とし て使用できるので便利です。引数のデータ型の指定は必須です。  戻り値：戻り値が1つでも「RETURNS」。戻り値はデータ型を指定します。 ○×ゲームではtext型を戻り値としています。戻り値が不要な関数の場合、void を指定します。  LANGUAGE：ストアドファンクションをプログラミングする言語です。 PL/pgSQLの場合は「plpgsql」と指定します。

CREATE FUNCTIONの要素  関数本文：PL/pgSQLの文法に則って処理を記述します。SQLの文字列として引 用符で囲む必要があります。通常の文字列としてシングルクォーテーションで 囲んでも問題ありませんが、関数本文中にシングルクォーテーションが出てき た場合エスケープが必要となり本文が見にくくなります。そのため多くの場合、 「$$（ドル引用符）」で囲み、本文中でシングルクォーテーションのエスケー プを不要にします。 表示したい文字列： L'état, c'est moi シングルクォーテーションで囲む場合： 'L''état, c''est moi' ドル引用符で囲む場合： $$L'état, c'est moi$$

PL/pgSQLの構造  PL/pgSQLは構造がはっきりしています。メジャーなプログラミング言語に慣れ ている人には面倒かもしれません。  宣言部：変数を宣言する。変数が不要な場合、記述は不要。  実行部：実際に行う処理を記述する。  例外処理部：実行部で発生した例外を 処理する記述をする。 例外が発生しない、またはそのまま 例外を呼び出し元に返してもいい場合 記述は不要。  ブロックは入れ子にすることも可能。 例外の処理を限定的にできる。

○×ゲームの宣言部 DECLARE row_count integer; init_row_num CONSTANT integer := 3; init_column_num CONSTANT integer := 3; sente_mark marubatsu_field.mark%TYPE; gote_rec RECORD; display_cur CURSOR FOR SELECT row_id, column_id, COALESCE(mark, ' ') AS mark FROM marubatsu_field ORDER BY row_id, column_id; display_text text := '';

○×ゲームの宣言部  原則、変数は宣言部で宣言しなければ実行部で使用することはできません。 （後で出てきますが例外もたくさんあります。）  変数の宣言にはデータ型が必要です。使用できるデータ型は、PostgreSQLでカ ラムのデータ型を決めるときに使うデータ型の他に、RECORDやCURSORなど PL/pgSQLでしか使わないようなデータ型も存在します。  CONSTANTを付けることにより定数として扱えます。  データ型を明示せず、「テーブル名.カラム名%TYPE」で、そのカラムと同じ データ型と宣言可能です。仮にカラムのデータ型が変更になった場合にソース の修正箇所を減らせます。  宣言と同時に値を代入するには「:=」を使用します。

○×ゲームの実行部（引数チェック） BEGIN -- 実行部の開始 -- 引数チェック IF rownum < 1 OR rownum > 3 THEN RAISE EXCEPTION '行指定の数字が有効範囲外を指定して います。'; ELSIF columnnum < 1 OR columnnum > 3 THEN RAISE EXCEPTION '列指定の数字が有効範囲外を指定して います。'; END IF;

○×ゲームの実行部（引数チェック）  原則、変数は宣言部で宣言しなければ実行部で使用することはできません。と 言いましたが、「rownum」と「columnnum」は宣言部では宣言していませんが、 引数の名前は宣言部で宣言しなくても実行部で使用できます。  条件分岐としてはIFまたはCASE文が使えます。  IF 条件文 THEN 処理; ELSIF 条件 THEN 処理; END IF; とつなげます。セミコロンを付ける位置に気をつけてください。PL/pgSQLでは 文の最後、ブロックの最後にセミコロンを付けるのが原則です。  2つ以上の条件はANDやORでつなげられます。  RAISEはPL/pgSQLで使用し、メッセージを報告し、エラーを発生することができ ます。RAISE EXCEPTIONでいきなり例外を発生させることになります。

○×ゲームの実行部（テーブル初期化） -- 行が初期化行数じゃなければ初期化 SELECT COUNT(*) INTO row_count FROM marubatsu_field; IF row_count != (init_row_num * init_column_num) THEN DELETE FROM marubatsu_field; FOR i IN 1 .. init_row_num LOOP FOR j IN 1 .. init_column_num LOOP INSERT INTO marubatsu_field (row_id, column_id) VALUES (i, j); END LOOP; END LOOP; END IF;

○×ゲームの実行部（テーブル初期化）  「SELECT カラム名 INTO 変数名」で、テーブルをSELECTした結果を変数に格納 できます。ただし、この取り出し方では1行のみ取得可能で、2行目以降は破棄 されます。ここではCOUNT(*)の結果を取得しており絶対に1行しか返らないこ とがわかっているので問題ありません。  FOR文には様々な書き方がありますが、基本は FOR ループカウンタ変数 IN カウント開始値 .. カウント終了値 LOOP 処理; END LOOP; と書きます。「..」も構文の一つです。  原則、変数は宣言部で宣言しなければ実行部で使用することはできません。と 言いましたが、FOR文のループカウンタ変数は宣言無しで使用可能です。ただ し、ループカウンタ変数はFOR文の中でしか使用できません。

○×ゲームの実行部（テーブル初期化）  DELETEやINSERTは通常のSQLを実行するときと同じように書くことができます。  DELETEでレコードの有無にかかわらずいったん全部削除し、INSERTで○×ゲー ムのマス目に該当するレコードを作成します。  INSERTの「i」「j」は変数です。PL/pgSQLでは、変数は適切な位置に書くことで 自動で展開されます。  PHPのように頭に$をつけるなど、「これが変数だ！」とわかる記法は特にあり ません。テーブル名やカラム名と見分けがつきにくくなるため、変数は「v_」 から始めるなど規約を定めるといいでしょう。

○×ゲームの実行部（先手の手番） -- 先手が選んだ位置に○を入れる SELECT mark INTO sente_mark FROM marubatsu_field WHERE row_id = rownum AND column_id = columnnum; IF sente_mark IS NOT NULL THEN -- すでにマークが入っていたらメッセージ RAISE EXCEPTION '指定された位置はすでにマークされていま す。'; ELSE UPDATE marubatsu_field SET mark = '○' WHERE row_id = rownum AND column_id = columnnum; END IF;

○×ゲームの実行部（先手の手番）  SELECT結果を代入しているsente_mark変数のデータ型は、宣言部で marubatsu_fieldテーブルのmark列のデータ型と同じであると宣言していま す。これにより、万が一mark列のデータ型が変更されてもこのSQLを修正する 可能性は低くなります。  NULLかどうかの判定は、通常のSQLと同じく「IS NULL」や「IS NOT NULL」で判 定します。「 = NULL」や「!= NULL」ではエラーにはなりませんが、正しく判定 されません。  UPDATEは通常のSQLを実行するときと同じように書くことができます。

○×ゲームの実行部（後手の手番） -- 後手がマークされていないところをランダムで選択して×を入れる BEGIN -- サブブロック開始 SELECT * INTO gote_rec FROM marubatsu_field WHERE mark is null ORDER BY random() limit 1; IF NOT FOUND THEN -- 全部埋まっていたら例外を発生させる RAISE EXCEPTION SQLSTATE 'ABCDE' USING MESSAGE = 'もう書き込める場所があ りません。'; ELSE UPDATE marubatsu_field SET mark = '×' WHERE row_id = gote_rec.row_id AND column_id = gote_rec.column_id; END IF; EXCEPTION -- 全部埋まっていた場合に発生した例外をキャッチしてメッセージを表示 WHEN SQLSTATE 'ABCDE' THEN RAISE NOTICE '%', SQLERRM; END; -- サブブロック終了

○×ゲームの実行部（後手の手番）  実行部や例外処理部の中にBEGIN～END;を入れサブブロックとすることができ ます。  主に、サブブロック内で例外が発生しても予期した例外であればきちんと処理 をして、サブブロック外の処理を続行するような場合に使用します。  ORDER BY rondom() LIMIT 1でランダムにソートして1行のみ取得することでラン ダムに1行取得を実現しています。この方法自体は普通のSQLでも実行できます。  「FOUND」は特殊な変数です。定義不要で使用でき、直前のSELECTでレコード が取得できた場合はtrue、レコードが1件もない場合はfalseになります。Linuxコ マンドの「$?」とかWindowsコマンドの「%errorlevel%」と同じようなイメージ です。  gote_recはRECORD型の変数です。RECORD型は宣言した時点では内部構造が決 まっておらず、代入された時点でそのレコードと同じ構造に定義されます。  「レコード型変数.カラム名」の形で取得したカラムの値を参照できます。

○×ゲームの実行部（後手の手番例外処 理）  RAISE EXCEPTIONで例外を発生させ、EXCEPTIONブロックで例外をキャッチして います。  SQLSTATEはHTTPステータスコードのSQL版のようなもので、様々な値が規定さ れていますが、今回は「ABCDE」という独自のSQLSTATEを指定して例外を発生 させています。  WHEN SQLSTATE '～' で指定したSQLSTATEの例外の処理を記述できます。逆に言 うと、それ以外の例外はキャッチできません。できるだけ例外をキャッチした い場合、WHEN OTHERSという指定でほとんどの例外をキャッチできます。  SQLERRMは特殊な変数です。宣言無しで使用でき、エラーメッセージが格納さ れます。  例外処理部を持つブロックは処理性能が遅くなります。むやみに例外処理をし ないようにしましょう。

○×ゲームの実行部（表示テキスト） -- 全てのレコードを取得して、表示用のテキストを作成する FOR rec IN display_cur LOOP CASE rec.column_id WHEN 1 THEN display_text := display_text || ' ' || rec.mark; WHEN 2 THEN display_text := display_text || ' | ' || rec.mark; WHEN 3 THEN display_text := display_text || ' | ' || rec.mark || ' ' || chr(10); END CASE; END LOOP; -- 表示用のテキストを返す RETURN display_text; END; -- 実行部の最後

○×ゲームの実行部（表示テキスト）  CURSORを使用して、marubatsu_fieldテーブルから全レコードを取得しています。  CURSORは変数宣言時にSQLも指定し、そのSQLを実行した場合のカーソルを変 数にします。他のプログラムでいうところのResultSetのように使うと思ってく ださい。  CURSORとFOR文を組み合わせることで、カーソルのオープンやクローズを明記 せずに全レコードを取得できます。  CASE文で条件分岐する場合の書き方は通常のSQLのCASE式とほぼ同じです。終 わりが「END CASE;」になるくらい。  RETURN文で戻り値を返すことで関数は終了です。CREATE FUNCTIONの 「RETURNS」と間違えないでください。

勝敗判定  実は勝敗判定処理は組み込んでいません。（○×なら目視ですぐに確認できる ので……）  試しに組み込んでみてはいかがでしょうか？

呼び出し方  CREATE FUNCTIONで作成するのは関数です。COUNT()やMAX()などの関数と同じ と思ってください。  呼び出し方もそれらの関数と同じです。つまり呼び出し方は次のようになりま す。 SELECT marubatsu(1, 2);  ちなみにINSERTでもUPDATEでも、関数が使えるSQLなら呼び出しは可能ですが、 SELECTで実行されるのが一般的です。  SELECTで関数を呼び出して、関数の中でINSERTしたりUPDATEしたりしているの は違和感があるといえばありますが…。

その他  ○×ゲームを題材に、変数の宣言・代入・参照、条件分岐、SQLの実行・結果 の取得、繰り返し処理、例外処理などを説明いたしました。通常のプログラミ ングでできることの多くができることがわかっていただけたと思います。  その他にも構文がたくさんありますので、すべてを知りたい方はマニュアルを、 基本的な要点を知りたい方は拙著『わたしとぼくのPL/pgSQL』を御覧ください。 （たぶん）世界で唯一のPL/pgSQLだけを説明した本。Kindle他、電子書籍プラットフォー ムで発売中。 2019年発売なので内容が古くなってるかな、と思いきや、本当の本当に基本的なところを 書いているので現在でも使えます。 ただちょっと基本的すぎてボリュームが少ないかもしれません……。

PL/pgSQL

No content

ストアドのメリット • SQLの処理の中に複雑な処理を組み込むことができる • 同じRDBMS製品であれば、バージョンアップやOSの移行をして も互換性が維持されることが多く、移植に有利 • PostgreSQL上で動くものなので、Windowsで動くならLinuxでも、RDSで も動く • 処理をDBに任せることで、APサーバのリソースを節約できる • 呼び出し側は簡潔な記述で同じ処理を呼び出すことができる • 呼び出し側は処理の結果のみを受け取ることができ、受け取っ たデータに複雑な処理をする必要がない

ストアドを使わない場合の処理 シンプルなSQLだと… 大量のデータ シンプルなデータが欲しければ 複雑で⾧文のSQLが必要 大量のデータの 処理が負担 DBサーバ ネットワークの負荷が増大 システム全体の負荷も増大

ストアドを使った場合の処理 ストアドを呼び出すだけの シンプルなSQL ストアドによる処理 DBサーバ ネットワークの負荷軽減 DBサーバの負荷増大 システム全体の負荷を考慮する必要あり 結果のみの軽量 データ

ストアドのユースケース • 運用や保守で必要となる面倒なSQLをPL/pgSQLで書いておき、 必要なときに使うのが実用的かもしれません。 • 全テーブルのレコード件数一覧とか、ユーザが持つ権限の一覧とか、 一定期間内に実行されたSQLと回数と実行時間とか。 • 「Oracleからの移行でPL/SQLの処理を移植したい」というとき にも出番があります。 • しかし、大規模で複雑な処理をPL/pgSQLで記述するのは、少な くとも現段階ではまだ機能が追いついていないと思います。 • 開発に最適といえるエディタも特になく、複雑になればなるほどデ バッグも大変です……。

できないこと  ストアドファンクションでできないことの代表として、関数内でのトランザク ションがあります。基本的にはCOMMITもROLLBACKもできません。 SELECT 関数名(引数);  言われてみれば↑こういう関数の中でトランザクションが始まったり終わった りするのは変です。  トランザクションの中で関数を使ったSQLを実行することはできますが、関数 で例外が発生した場合、そのトランザクションはアボートされ関数外で行った 処理もロールバックされます。

No content

No content

ストアドプロシージャ  関数ではなく手続きを作成する。言語はPL/pgSQLで記述でき、プログラム部分 の書き方はストアドファンクションとほぼ同じ。  内部でトランザクションを開始・終了できる。  正確にはプロシージャの開始時にトランザクションが開始され、COMMITま たはROLLBACKをしてトランザクションが終了すると自動で新しいトランザ クションが開始される。  そもそもストアドファンクションとは呼び出し方が異なる。 CALL プロシージャ名(引数);

CREATE PROCEDURE  FUNCTIONではなくPROCEDURE。  ストアドプロシージャには戻り値がなく、RETURNS句もありません。 処理本文中にRETURNも不要です。 CREATE OR REPLACE PROCEDURE 関数名 ( [ [ 引数名 ] 引数のデータ型 [, ...] ] ) RETURNS 戻り値のデータ型 AS $$ 手続き本文 ←ここをPL/pgSQLで記述する $$ LANGUAGE plpgsql; ←PL/pgSQLであることを明示する

PL/pgSQL