PL/pgSQLの基本と使い所

OSC京都2025で発表したスライドです。

Transcript

1. PL/pgSQL の基本と使い所 目黒 聖 PL/pgSQL の基本と使い所 1
2. 自己紹介 目黒聖（めぐろ・たかし） （Twitter:@tameguro ） 日本PostgreSQL ユーザ会の正会員 都内某SI 勤務のSE PostgreSQL の設計・導入・保守などをやっています PostgreSQL 歴はだいたい15 年くらい PL/pgSQL の基本と使い所 2
3. 手続き言語？ SQL は一般的なプログラミング言語と異なり宣言型言語に分類される RDB はSQL を採用しているが、実は主要RDBMS には手続き言語が存在する RDBMS 言語 備考 Oracle PL/SQL Oracle を活用しているところではかなり使われている SQL Server T-SQL PostgreSQL ではPL/pgSQL が標準装備 PL/pgSQL の基本と使い所 3
4. PL/pgSQL とは SQL を拡張し、手続き処理をできるようにしたのがPL/pgSQL PostgreSQL で手続き処理を作成する際に最もよく使用される言語 PL はProcedural Language （手続き型言語）の略 Oracle のPL/SQL を参考にしており、書き方がよく似ている PL/SQL からの移行候補にもなりやすい（ただし一筋縄ではいかない） PL/pgSQL の基本と使い所 4
5. 今回はPL/pgSQL の細かい文法の説明ではなく、 どういうことができて、どういうところで使う のが便利かを説明したいと思います PL/pgSQL の基本と使い所 5
6. PL/pgSQL の基本構造 DECLARE 宣言部 BEGIN 実行部 EXCEPTION 例外処理部 END PL/pgSQL の基本と使い所 6
7. ブロック 必須 説明 宣言部 × 実行部・例外処理部で使用する変数を宣言する 原則、宣言しなければ変数は使用できないが、 変数を使用しない場合は不要 実行部 ◦ メインの処理を記述する 最悪ここだけあればいい 例外処理部 × 実行部で例外が発生した場合にここの処理に移行する 例外処理を行わない場合は不要 PL/pgSQL の基本と使い所 7
8. PL/pgSQL でできること もともとSQL の拡張として設計されているのでSQL でできることは基本できま す PL/pgSQL の基本と使い所 8
9. 単純なSQL の実行 CREATE TABLE prefs ( pref_code int not null, pref_name text not null, primary key (pref_code) ); INSERT INTO prefs VALUES (11, '埼玉県'); 関数の実行 length(v_pref_name); PL/pgSQL の基本と使い所 9
10. その他、一般的なプログラミング言語が備えている機能は大体備えています 変数 条件分岐 繰り返し 例外処理 など PL/pgSQL の基本と使い所 10
11. 変数 PostgreSQL のデータ型は全部対応しています DECLARE v_pref_name text; v_pref_name_length int; BEGIN SELECT pref_name INTO v_pref_name FROM prefs WHERE pref_code = 11; v_pref_name_length := length(v_pref_name); END PL/pgSQL の基本と使い所 11
12. 条件分岐 IF 文の条件判定はWHERE 句の条件判定と同じ書き方ができます IF v_pref_name = '埼玉県' OR v_pref_name = '千葉県' THEN RAISE NOTICE '埼玉県、または千葉県です'; ELSE RAISE NOTICE '埼玉県でも千葉県でもありません'; END IF PL/pgSQL の基本と使い所 12
13. 繰り返し 単純なループだけではなく、問い合わせ結果からもループできます FOR v_pref_rec IN SELECT * FROM prefs LOOP RAISE NOTICE '%', v_pref_rec.pref_name; END LOOP; PL/pgSQL の基本と使い所 13
14. 例外処理 実行中に発生した例外をキャッチすることができます BEGIN PERFORM 10 / 0; EXCEPTION WHEN division_by_zero THEN RAISE NOTICE '0除算が実行されました！'; END PL/pgSQL の基本と使い所 14
15. つまり何ができるかというと、何でもできます！ PL/pgSQL の基本と使い所 15
16. PL/pgSQL で作成するもの 次の3 つを作成するために使います ストアド・ファンクション ストアド・プロシージャ トリガ PL/pgSQL の基本と使い所 16
17. ストアド・ファンクション DB 内に保存される（stored ）関数（function ） 引数と戻り値がある 作る関数はsum() とかmax() とかと同じ「関数」なので、SQL から実行され る SQL の関数が使えるところならどこでも使える トランザクションはコミットできない sum() 実行中にトランザクションが終わって始まってたとかいうこと がありえないのと同じ PL/pgSQL の基本と使い所 17
18. ストアド・ファンクションの使い所 複雑で長大なSQL を毎回実行しなければならないような処理をストアド・ファ ンクションにして、呼び出しを簡単・確実にする、というのが多い 条件が複雑な集計 SQL で書くと長くて複雑になり、メンテが大変な計算 PostgreSQL のシステムカタログを参照して状況を確認できるユーティリテ ィー関数 など PL/pgSQL の基本と使い所 18
19. ストアド・ファンクションの作り方 CREATE OR REPLACE FUNCTION 関数名(引数データ型) RETURNS 戻り値データ型 AS $$ 関数本文　←ここをPL/pgSQLで記述する $$ LANGUAGE plpgsql; PL/pgSQL の基本と使い所 19
20. ストアド・ファンクションの使い方 SQL の関数が使えるところならどこでも使えます SELECT foo(); INSERT INTO foo_table VALUES (foo()); 長大なSQL の可変部分を引数にして、関数でラップして使い勝手をよくするの もあり PL/pgSQL の基本と使い所 20
21. ストアド・プロシージャ PostgreSQL 11 から実装された（比較的）新しい機能 10 まではストアド・ファンクションで代用していました DB 内に保存される（stored ）手続き（procedure ） 処理をまとめたもので、戻り値がない CALL という専用コマンドで実行する 関数とは異なるため、SQL からは実行できない トランザクションをコミットできる PL/pgSQL の基本と使い所 21
22. ストアド・プロシージャの使い所 SQL からは実行できないが、トランザクションが使えるという利点を活かし、 バッチ処理に向いている 日次の集計バッチ 定期的に実行されるデータの洗替 定期的な名寄せ など PL/pgSQL の基本と使い所 22
23. ストアド・プロシージャの作り方 ファンクションと異なり戻り値がないためRETURNS は不要 CREATE OR REPLACE PROCEDURE プロシージャ名(引数データ型) AS $$ プロシージャ本文　←ここをPL/pgSQLで記述する $$ LANGUAGE plpgsql; PL/pgSQL の基本と使い所 23
24. ストアド・プロシージャの使い方 バッチ処理はたいていユーザが手動で実行するのではなく、スケジューラが自 動で実行すると思います 気になるのは作成したバッチが成功したか失敗したかではないでしょうか 大きく分けて、成否の判定には2 つの方法があります プログラミング言語からプロシージャをCALL し、結果を受け取る シェルからpsql を実行してCALL を実行し、結果をシェルに返す 前者は言語次第でやり方が変わるので、後者の方法を説明します PL/pgSQL の基本と使い所 24
25. ストアド・プロシージャの結果をシェルに返す psql コマンドの結果はSQL と無関係 通常通りにpsql で実行した場合、SQL やCALL の結果がシェルに戻りません psql コマンド自体の結果を受け取ってしまうからです psql 内で実行したSQL の結果が何であれ、正常にpsql が終了していればシェル には正常終了が返ります PL/pgSQL の基本と使い所 25
26. PL/pgSQL の基本と使い所 26
27. ON_ERROR_STOP 変数をON にする そこで、psql 内で特殊な変数ON_ERROR_STOP をON にしておくと、プロシージ ャが異常終了したらpsql の結果として「3 」がシェルに返り、シェルでも異常 が起きたことを検知できるようになります PL/pgSQL の基本と使い所 27
28. # psqlを実行。 psql <<EOT \set ON_ERROR_STOP on CALL foo(); EOT # $?でpsqlコマンドの結果を取得 RESULT=$? PL/pgSQL の基本と使い所 28
29. PL/pgSQL の基本と使い所 29
30. CALL のOUT 変数をシェルに返す シェルでは標準出力を変数にそのまま格納できることを利用して、psql で処理 の結果を標準出力に出力することでシェルに結果を通知するという方法もあり ます PL/pgSQL の基本と使い所 30
31. プロシージャの例 OUT 引数に結果を格納する CREATE PROCEDURE bar(OUT result int) AS $$ BEGIN result := 1; END $$ LANGUAGE plpgsql; PL/pgSQL の基本と使い所 31
32. psql から実行する \gset は結果をpsql の変数に格納するコマンド \echo は変数の値を出力するコマンド # CALL bar(null) \gset # \echo :result PL/pgSQL の基本と使い所 32
33. シェルから実行する -At でpsql の不要な出力を抑制 \echo の結果がRESULT 変数に格納される RESULT=$(psql -At <<EOT CALL bar(null) \gset \echo :result EOT ) PL/pgSQL の基本と使い所 33
34. トリガ テーブルやビューに特定の操作が行われた場合に実行されるファンクショ ンを規定する PL/pgSQL でトリガを作成するのではなく、ストアド・ファンクションを あらかじめ作成しておき、テーブルと紐づけたのがトリガ 制約トリガ、イベントトリガなどの種類がある SQL やCALL で直接実行せず、特定のタイミングでしか実行できないが、ス トアド・ファンクションと組み合わせることで思ったより多くのことが実 現できる PL/pgSQL の基本と使い所 34
35. トリガの使い所 テーブルが更新された時に、必ず実行したい処理をトリガにすることで、アプ リケーションから更新した場合でも、手動でSQL を実行した場合でも、データ の整合性を保つことができます。 店舗ごとの売上テーブルが更新されるたびにサマリテーブルを集計し直し たデータで更新する 手動で更新されないように更新処理を無効にする テーブルを更新されるたびに監査ログテーブルにレコードを追加する など PL/pgSQL の基本と使い所 35
36. トリガの作り方 トリガとは別に、トリガ関数を作成する -- 引数なし、戻り値はtrigger CREATE OR REPLACE FUNCTION 関数名() RETURNS trigger AS $$ 関数本文　←ここをPL/pgSQLで記述する $$ LANGUAGE plpgsql; トリガとトリガ関数が分かれているので、異なるテーブルに同じ関数を紐づけ たりもできる PL/pgSQL の基本と使い所 36
37. トリガの作り方 トリガ関数とテーブルを紐づけてトリガを作成する CREATE TRIGGER トリガ名 BEFORE INSERT ON テーブル名 FOR EACH ROW EXECUTE FUNCTION トリガ関数名(); BEFORE 、AFTER やINSERT やUPDATE など、どのタイミングで何のSQL を実行し たタイミングで起動するかも細かく設定できる PL/pgSQL の基本と使い所 37
38. PL/pgSQL のメリット アプリケーションの負荷軽減 ネットワークの負荷軽減 バージョン間・プラットホーム間互換 PL/pgSQL の基本と使い所 38
39. アプリケーション・ネットワークの負荷 必要なデータにするまで何度もSQL を実行しデータの加工を行う PL/pgSQL の基本と使い所 39
40. アプリケーション・ネットワークの負荷軽減 DB で全部行うのでアプリケーションの処理とネットワークを流れるデータは 最小限 PL/pgSQL の基本と使い所 40
41. プラットフォームを問わず動作 PostgreSQL 上で動作するためプラットフォームにあまり依存しない PL/pgSQL の基本と使い所 41
42. PL/pgSQL のデメリット アプリとDB の役割分担があやふやになる PL/pgSQL で解決できる問題は解決済み？ 大規模開発に向かない 存在感の無さ PL/pgSQL の基本と使い所 42
43. アプリとDB の役割分担？？ データベースの責任はデータを問題なく保存すること アプリが必要なデータはアプリしか知らない データは提供しても、必要な形に加工するのはアプリの責任 そもそも誰がPL/pgSQL のプログラミングをする？ DB オブジェクトとして捉えれば、DB の範疇 アプリケーションの一種として捉えれば、アプリの範疇 PL/pgSQL の基本と使い所 43
44. そもそも問題は解決済み？ 今どきのインフラはそこまで貧弱じゃない SQL でできることが増えて、手続き処理を書かなくてもSQL 一発で必要な データが取得できる PL/pgSQL の基本と使い所 44
45. 大規模開発に向かない PL/pgSQL 開発に適したエディタが特にない デバッグ環境構築が容易ではない 関数の再利用がしにくく、インタフェースと実装に分けられず、大人数で 開発するのに向かない Oracle PL/SQL にはパッケージという機能があり、比較的分担しやす い PL/pgSQL の基本と使い所 45
46. 存在感がない！ DB に保存され目につかなくなる メンバの移動でよく忘れられる メンテや移行漏れに注意 ストアド・ファンクションは見た目が普通の関数と変わらない 自作関数かどうかわからない人もいる そもそも関数を自作できることを知らない人もいる トリガは自分で実行していることもわからない 「このテーブルにINSERT したらこうなる」ものだと思い込む人もいる PL/pgSQL の基本と使い所 46
47. メリットとデメリットがありますが、自分で作 成しないとしても、使っている現場はありますの で、なんか不思議な動きするな～と思ったらス トアドの存在を思い出してください PL/pgSQL の基本と使い所 47
48. DB 上で手続き処理のプログラミングができるこ とで広がる可能性があります！ぜひ使ってみてく ださい PL/pgSQL の基本と使い所 48