はじめに

今日は個人的にちょっと気になっているPostgreSQL 10の新機能を紹介します。

psqlで条件分岐

今回紹介する機能は、\if, \elseif, \else, \endifというもので、要するにpsqlに読ませるSQLスクリプトファイル内で条件分岐を可能にするというものです。

構文

構文は、各種スクリプト言語などでも使っているようなものです。毎度のことながら、else if相当をelifとか省略して書くのが個人的には気持ち悪いのですがｗ
(構文解析する立場になるとやりやすいんだろうなあとは思う)

\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はやっぱり面白いよなあと思うのです。
これからも、この機能を使ってみて、何か面白そうなことが出来たら、また投稿したいと思います。