はじめに
今日は個人的にちょっと気になっているPostgreSQL 10の新機能を紹介します。
psqlで条件分岐
今回紹介する機能は、\if, \elseif, \else, \endifというもので、要するにpsqlに読ませるSQLスクリプトファイル内で条件分岐を可能にするというものです。
構文
構文は、各種スクリプト言語などでも使っているようなものです。毎度のことながら、else if
相当をelif
とか省略して書くのが個人的には気持ち悪いのですがw
(構文解析する立場になるとやりやすいんだろうなあとは思う)
\if expression
-- any
\elif expression
-- any
\else
-- any
\endif
上記のうち、elif
とelse
は省略可能です。
また、\if
~\endif
はネストもできます。
expressionにはbooleanの変数または定数が設定できます。もっとも定数を指定した場合には実質上、条件分岐にはならなくなりますが。
変数というのはpsql内で使える変数のことで、SQLの検索結果を\gset コマンドで設定できたりします。
つまり、\if
や\elif
に設定するexpressoin
には、SQLによるboolean型の検索結果を設定できるわけで、これを応用することで、テーブルの設定値によって、psqlのスクリプトを分岐可能にできる、ということになります。
ただし!psql変数に設定させるためのSQLは正確に1行の結果のみを返却する必要があるので注意。
具体例
具体例を見ていったほうが分かりやすいと思うので、以下、つらつらと例を上げてみます。
SELECT true AS machida_is_kanagawa
\gset
\echo :machida_is_kanagawa
\if :machida_is_kanagawa
SELECT 'Machida is Kanagawa' as "Fact";
\else
SELECT 'Machida is Tokyo' as "Fact";
\endif
最初のSQL文を実行すると、結果はmachida_is_kanagawa列にtrueを設定した行が1行返却されます。
で、次の\gset
によってmachida_is_kanagawaという変数にtrueがセットされた状態になります。
次の行の\echo :machida_is_kanagawa
では、設定された変数を参照(参照時には先頭に:
をつける)します。trueが入っているのでt
と表示されます。
で、\if
で、その変数kanagawa_is_machida
を評価すると真になるので、\if
と\else
の間のブロックにあるスクリプトSELECT 'Machida is Kanagawa' as "Fact";
が実行されます。
実行例はこうなります。
$ psql -U postgres postgres -f sample-01.sql
t
Fact
---------------------
Machida is Kanagawa
(1 row)
変数に日本語を使う
ここで使える変数はいわゆるSQL識別子扱いのようなので、引用すれば日本語でも問題なく使えたりします。
$ cat sample-02-true.sql
SELECT true AS "町田は神奈川"
\gset
\echo :町田は神奈川
\if :町田は神奈川
SELECT '町田は神奈川' as "我々の世界線";
\else
SELECT '町田は東京' as "別の世界線";
\endif
実行結果はこうなります。
$ psql -U postgres postgres -f sample-02.sql
t
我々の世界線
--------------
町田は神奈川
(1 row)
SQL検索結果を使った分岐
もう少し実用的な例を考えてみた。
例えばあるSQLスクリプトを実行するときに、特定のアプリケーションが動作しているとアカン、というケースもあるんじゃないのかと。そういうときに、この分岐機能と``\q```コマンドを組合せてアカン状態のときには、即座にスクリプトを中断するようなケースを試してみる。
SQLスクリプトはこんな感じ。
$ cat sample-04.sql
SELECT (count(*) <> 0) AS running FROM pg_stat_activity WHERE application_name = 'pgbench'
\gset
\if :running
\echo "pgbench動作中なのでスクリプトを終了します"
\q
\else
\echo "pgbenchは動作していないのでスクリプトを実行します"
\endif
\echo "バッチ処理を動かします(以下略)"
pgbenchが動作していないときにはこうなります。
$ psql -U postgres postgres -f sample-04.sql
"pgbenchは動作していないのでスクリプトを実行します"
"バッチ処理を動かします(以下略)"
pgbenchが動作している場合には最初に実行したSQL結果のrunningが真になるので、\if
側のブロックが実行されます。このブロックには\q
コマンドが書かれているので、\endif
以降の処理は実行されずスクリプトを抜けます。
$ psql -U postgres postgres -f sample-04.sql
"pgbench動作中なのでスクリプトを終了します"
pgbenchが動作していない場合には最初に実行したSQL結果のrunningが偽になるので、\elif
側のブロックが実行されます。このブロックには\q
コマンドが書かれていないので、\endif
以降の処理も継続して実行されます。
expressionに数式を指定してみる
そういえば構文を見る限りでは、\if
や\elif
にはexpressionを指定すると書かれている。
ということは、比較式なんかもかけるのかな、と思い以下のようなスクリプトを書いてみた。
$ cat sample-06-expressoin.sql
SELECT random() as random_num
\gset
\if :random_num < 0.1
\echo 町田は東京 (:random_num)
\elif :random_num < 0.4
\echo 町田は相模原 (:random_num)
\else
\echo 町田は神奈川 (:random_num)
\endif
上記のように、random()
の結果を変数に入れて、その結果の比較式を\if
等に書いてみた。
で、実行してみると・・・
$ psql -U postgres postgres -f sample-06-expressoin.sql
psql:sample-06-expressoin.sql:3: unrecognized value "0.0398230962455273 < 0.1" for "\if expression": boolean expected
psql:sample-06-expressoin.sql:5: unrecognized value "0.0398230962455273 < 0.4" for "\elif expression": boolean expected
町田は神奈川 (0.0398230962455273)
(´・ω・`) 残念ながら比較式を記述すると、それ全体を変数と解釈しようとし、そんなのねーよと怒られてしまい、その評価式は偽と扱われてしまうようだ。
えーーー!expressionって書いてあったじゃーん!
これについては、beta版が出たら再検証して、再現するようならbug report上げるべきなのかな(ドキュメント修正、ということになるのかもしれないが)。
上記のような動作を期待する場合、以下のように評価対象とするboolean値をSQLの中で生成しないといけないようだ。これはちょいとビミョー。
$ cat sample-06.sql
SELECT random_num, (random_num < 0.1) as tokyo, (random_num < 0.4) as sagamihara FROM (SELECT random() as random_num) as t
\gset
\if :tokyo
\echo 町田は東京 (:random_num)
\elif :sagamihara
\echo 町田は相模原 (:random_num)
\else
\echo 町田は神奈川 (:random_num)
\endif
これを何回か実行すると、こんな結果が得られる。
$ psql -U postgres postgres -f sample-06.sql
町田は神奈川 (0.786175288259983)
$ psql -U postgres postgres -f sample-06.sql
町田は相模原 (0.160528630949557)
$ psql -U postgres postgres -f sample-06.sql
町田は東京 (0.0387250757776201)
$ psql -U postgres postgres -f sample-06.sql
町田は神奈川 (0.632859560195357)
$
まとめ
- booleanを返却するSQL検索結果を元に、条件分岐をSQLスクリプト内に書けるようになった。
-
\if
や\elif
に指定できるのはbooleanの変数のみ。式はここには書けない。 - 町田は神奈川。
おわりに
今回説明した\if, \elseif, \else, \endifの機能によって、これまでSQLの実行結果を元にシェル側で条件分岐させて、複数のSQLスクリプトファイルにしていたケースを、シェルを作成せずに、SQLスクリプトだけで記述できるようになりそうです。
psql固有の機能として、ここまで本当に必要なのか?という疑問を持つ人も多いかもしれませんが、毎バージョン、そういう面白い機能を追加するpsqlはやっぱり面白いよなあと思うのです。
これからも、この機能を使ってみて、何か面白そうなことが出来たら、また投稿したいと思います。