はじめに
PL/pgSQLはPostgreSQLデータベースシステム用の読み込み可能な手続き言語です。
PL/pgSQLを使うことで複数の問い合わせをひとまとめに実行することが出来るため、通信のオーバーヘッドを節約でき性能の向上が期待できます。
一方で、PL/pgSQLに対するエディタのサポートは乏しく、実行するまで構文エラーにすら気付かない可能性があります。PostgreSQLのドキュメントにも
PL/pgSQLで開発する1つの良い方法は、関数を作成するのに自分の好きなテキストエディタを使い、もう1つのウィンドウでpsqlを使用して関数を読み込ませて試験を行うことです。
と書かれており、実際に動かしながら開発するのが確実な手法であるようです。（出展：PostgreSQL 12.4文書 42.12. PL/pgSQLによる開発向けのヒント）
これではあまりにも開発効率が悪いと思って調査したところ、plpgsql_checkというPL/pgSQLの静的解析ツールを発見したので、導入方法とその実用性について記事にまとめます。
前提条件
本記事では環境構築にDockerを使用します。検証に使用したマシンのバージョンは以下の通りです。
$ docker --version
Docker version 20.10.7, build f0df350
環境構築
今回は以下のような構成を作ります。
.
├── Dockerfile
└── initdb
└── add_extension.sql
まずはDockerfileです。
PostgreSQLの公式のイメージにplpgsql_checkをインストールするだけの内容です。
FROM postgres:12
RUN apt update \
&& apt install -y postgresql-$PG_MAJOR-plpgsql-check
続いて、PostgreSQLに拡張機能をインストールするためのクエリです。
CREATE EXTENSION IF NOT EXISTS "plpgsql_check";
イメージをビルドします。
docker build -t plpgsql-check:12 .
コンテナを起動します。
docker run --rm -d \
--name postgres \
-e POSTGRES_PASSWORD=password \
-v $PWD/initdb:/docker-entrypoint-initdb.d \
plpgsql-check:12
psqlを開いて拡張機能を確認し、以下のように表示されればインストール成功です。
$ docker exec -it postgres psql -U postgres
psql (12.7 (Debian 12.7-1.pgdg100+1))
Type "help" for help.
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
---------------+---------+------------+--------------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
plpgsql_check | 1.17 | public | extended check for plpgsql functions
(2 rows)
動かしてみる
READMEの通りに最初のストアドプロシージャの検査を行います。
最初に、以下のクエリを実行してテーブルと関数を作成します。
CREATE TABLE t1(a int, b int);
CREATE OR REPLACE FUNCTION public.f1()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE r record;
BEGIN
FOR r IN SELECT * FROM t1
LOOP
RAISE NOTICE '%', r.c; -- there is bug - table t1 missing "c" column
END LOOP;
END;
$function$;
この f1 という関数は t1 テーブルの c というカラムを参照しようとしていますが、 t1 に c というカラムは存在しません。
この関数を実行すると
postgres=# select f1();
f1
----
(1 row)
のように表示され、正直問題があるのかよく分かりません。
この関数にplpgsql_checkをかけてみると
postgres=# \x
Expanded display is on.
postgres=# select * from plpgsql_check_function_tb('public.f1()');
-[ RECORD 1 ]---------------------------
functionid | f1
lineno | 6
statement | RAISE
sqlstate | 42703
message | record "r" has no field "c"
detail |
hint |
level | error
position |
query |
context | SQL statement "SELECT r.c"
のように表示され、6行目で r というレコードが c という存在しないフィールドを参照していることが分かります。
ストアドプロシージャの行数は \sf+ 関数名 で知ることが出来ます。
postgres=# \sf+ f1
CREATE OR REPLACE FUNCTION public.f1()
RETURNS void
LANGUAGE plpgsql
1 AS $function$
2 DECLARE r record;
3 BEGIN
4 FOR r IN SELECT * FROM t1
5 LOOP
6 RAISE NOTICE '%', r.c; -- there is bug - table t1 missing "c" column
7 END LOOP;
8 END;
9 $function$
解析結果は上のようなテーブル形式の他、textやxml, json形式にすることが出来ます。
- テキスト形式
postgres=# select * from plpgsql_check_function('f1()', fatal_errors := false);
plpgsql_check_function
-------------------------------------------------------
error:42703:6:RAISE:record "r" has no field "c"
Context: SQL statement "SELECT r.c"
warning extra:00000:2:DECLARE:never read variable "r"
(3 rows)
- XML形式
postgres=# select * from plpgsql_check_function('f1()', format:='xml');
plpgsql_check_function
----------------------------------------------------
<Function oid="16410"> +
<Issue> +
<Level>error</Level> +
<Sqlstate>42703</Sqlstate> +
<Message>record "r" has no field "c"</Message>+
<Stmt lineno="6">RAISE</Stmt> +
<Context>SQL statement "SELECT r.c"</Context> +
</Issue> +
</Function>
(1 row)
- JSON形式
postgres=# select * from plpgsql_check_function('f1()', format:='json');
plpgsql_check_function
--------------------------------------------------
{ "function":"16410", +
"issues":[ +
{ +
"level":"error", +
"message":"record \"r\" has no field \"c\"",+
"statement":{ +
"lineNumber":"6", +
"text":"RAISE" +
}, +
"context":"SQL statement \"SELECT r.c\"", +
"sqlState":"42703" +
} +
+
] +
}
(1 row)
その他READMEには様々な使用方法や制限について書かれているので、より詳細に知りたい方はリポジトリをご参照ください。
まとめ
plpgsql_checkを使用することで、何も使用しない状態に比べると開発効率を上げることは出来そうです。
CIに組み込むなどして、自動で検査が行えるといち早く問題に気が付くことが出来るのではないかと思います。