psqlプログラミング
これは PostgreSQL Advent Calendar 2018 9日目の記事です。
みなさん、PostgreSQLマニュアルを読んでますか？
PostgreSQLマニュアルは新聞と同じく小学生の時に読み終わってはいけません。更新され続けていますので、読み続ける必要があります。
psqlの機能もまた更新され続けています。
更新というだけでなく気づくとプログラミング出来る環境に変わりつつあります。
これまでシェルスクリプトから必要な時にpsqlを呼び出し結果を受け取る処理を書いたことがある人もいると思いますが、psqlの機能向上によりpsql内で処理出来るようになってきています。
ということで、psqlのスクリプトについて書いていきます。
今回は基本的に psql -f "psqlスクリプト"やpsqlにログインしている状態で\i "psqlスクリプト"での実行を前提にしています。
一部制限がありますが、psqlからSQLや\スラッシュのメタコマンドが実行する場合とほぼ同じと考えて良いでしょう。
マニュアルを読んで頂ければ、詳しく書いてありますが、使用する機能をざっと紹介していきます。
\setコマンド
psqlの変数をセット出来ます。あらかじめセットされている変数があり、変更するとpsqlの動作に影響がある変数が存在します（PROMPT等）。
\set 変数名 値
セットした変数は\echo で見ることが出来ます。また、単に\setと打つとセットしてある変数名一覧が表示されます。
\echo 変数名
\set
AUTOCOMMIT = 'on'
COMP_KEYWORD_CASE = 'preserve-upper'
DBNAME = 'noborus'
ECHO = 'none'
ECHO_HIDDEN = 'off'
ENCODING = 'UTF8'
....
\setコマンドは強烈で、変数というよりも書き換え機能と言った方がよいレベルです。
セットした変数は、ほぼ、どこでも :変数名 で値に書き換わります。なので、以下どれも使用可能です。
\set ONE 1
SELECT * FROM my_table WHERE n = :ONE;
\set TABL my_table
SELECT * FROM :TABL;
-- スペースが含まれる文字列は''で囲みます。
\set SEL 'SELECT * FROM test;'
:SEL
例外として、シングルクォート('')内の文字列は書き換わりません。そうするとシングルクオート内の書き換えが出来ないので、:'変数名'を使うと'値'に書き換わる機能が用意されています。
\set CNAME noborus
SELECT * FROM my_table WHERE c = ':CNAME'; -- NG
SELECT * FROM my_table WHERE c = :'CNAME'; -- OK
さらに、SQLに直接逆引用符(``)を使用して、コマンドの実行結果を埋め込むことは出来ませんが、\スラッシュのメタコマンドでは使用することが出来ます。そのため、一旦変数にセットすれば、コマンドの実行結果をSQLに使用できます。
\set content `cat my_file.txt`
INSERT INTO my_table VALUES (:'content');
逆引用符(``)では１つの文字列としてセットするため、文字列が1行である必要はありません。そして、想像しているよりもセットできる文字列長はずっと大きいです。
\set psql_BASE64 `base64 bin/psql`
\gsetコマンド
\gsetコマンドは、直前に実行したSQLの結果を変数にセットします。ただし返された行が１行だけだった場合にのみセットできます。列名分だけ変数にセットされます。
SELECT c FROM my_table LIMIT 1;
\gset
\echo :c
noborus
\if, \elif, \else, \endif コマンド
いわゆるif文です。ただし\if の中では式は書けないので、式を計算した後に真偽値を変数を入れ、その変数を渡すといった使い方をします。
（ちなみに\if関連のコマンドは、バージョン10からです）。
SELECT 1=1 as ex;
\gset
\if :ex
\echo "OK"
\else
\echo "NG"
\endif
これらとSQL文を組み合わせて、psqlプログラムを書いていきます。
使用例
例として書いたのは、メモリの使用量(freeの結果)を実行する度にテーブルに追加していくプログラムです。普通はテーブルの定義とINSERT文をそれぞれ書かないとなりませんが、ここでは CREATE TABLE AS SELECT OR INSERT INTO TABLE SELECT(テーブルがなければSELECTの結果から作成し、テーブルがあればSELECTの結果からINSERTする)的なことをやってみます。
なぜ、こんなことをやっているかというと、CREATE TABLE は IF NOT EXISIT があるので、無ければ作成するのはSQLだけでも出来るようになっていますが、AS SELECTを付けた場合に既にテーブルがあった場合にその結果は捨てられてしまいます。
なので、普通のAS SELECTでは書けない条件ですが、それをなんとかするためです。
SELECT文を使いまわして両方出来てしまえば、こんな楽なことはないからです（ちょっと苦しい）。
作成したpsqlプログラムは以下です。
\set mem `free|grep ^Mem`
\set mem_select 'SELECT now() as time, mem[2] AS "total", mem[3] AS "used", mem[4] AS "free", mem[5] AS "buff/cache" , mem[6] AS "available" FROM (SELECT * FROM regexp_split_to_array(:''mem'',''\\s+'') AS "mem") AS d;'
SELECT EXISTS(SELECT 1 FROM pg_tables WHERE tablename = 'mem') as is_mem;
\gset
\if :is_mem
SELECT false as tr;
\gset
BEGIN;
INSERT INTO mem :mem_select
SELECT true as tr;
\gset
COMMIT;
\if :tr
\else
DROP TABLE mem;
CREATE TABLE mem AS :mem_select
\endif
\else
CREATE TABLE mem AS :mem_select
\endif
上記をファイルに保存して、 psql -f memory_monitor.psqlや psql の中で \i memory_monitor.psqlで実行します。
プログラムを解説していきます。
まず set mem `free|grep ^Mem`でMemoryの行だけを変数memにセットしています。コマンドの実行結果が複数行にまたがっていてもセットは出来るので、そこから切り出せばSwapの行も入れることは可能です（ここでは省略）。
次に、現在の時刻も含めて、変数にセットした文字列をregex_split_arrayにより、配列に変換し、その配列から、それぞれの列に切り出しています。このmem_selectは、単体で実行可能です。
:mem_select
time | total | used | free | buff/cache | available
-------------------------------+----------+---------+--------+------------+-----------
2018-12-08 18:11:25.092434+09 | 16296012 | 5655248 | 523420 | 1098396 | 10117344
(1 row)
その後SELECT EXISTS(SELECT 1 FROM pg_tables WHERE tablename = 'mem') as is_mem;により is_mem変数にテーブルが存在するか？でtrue or falseをセットしています。
テーブルが存在していれば、（少しとばして）memテーブルに INSERT INTO mem :mem_select でINSERT TABLE SELECTを実行しています。テーブルが存在していなければ、CREATE TABLE mem AS :mem_selectでテーブルを作成して結果を入れています。
さらにおまけとして、（少し戻って）BEGINでトランザクションを開始して、もし（テーブルの定義が違う場合に）INSERTに失敗した場合にトランザクションが失敗していることを判定して、テーブルを削除（危険！）して、テーブルを作成し直しています。
以上により、あらかじめテーブルが作成されているか気にすることなく実行出来るようになりました。
実行すれば、以下のように追加されています。
time | total | used | free | buff/cache | available
-------------------------------+----------+---------+--------+------------+-----------
2018-12-08 18:02:57.301394+09 | 16296012 | 5687988 | 497148 | 1097412 | 10110876
2018-12-08 18:02:59.077667+09 | 16296012 | 5687204 | 492616 | 1102704 | 10116192
2018-12-08 18:02:59.844954+09 | 16296012 | 5687484 | 492336 | 1102704 | 10116192
手動実行？
「メモリモニター的に書いておきながら手動で実行するのか？」と疑問に思いますよね？
psqlで構文が拡張されてもループ系の構文はないので、繰り返し実行的なことは出来ないんです。残念。
generate_series()で複数行を作成して、実行文を作成して\gexecで実行すれば...と考えましたが、\gexecでは \メタコマンドを実行出来ないんですよね〜
しかし\i で実行したプログラムの中で \i を実行することは出来るので、最後に
SELECT pg_sleep(1);
\i memory_monitor.psql
を付け加えて、再帰ロードすれば...いつかリソースを食いつぶしますが、数回なら...
おわりに
いかがだったでしょうか？ちょっと強引な例でしたが、今まで別の言語と組み合わせなければ出来なかったようなことが、psqlプログラムで出来るようになっていることがわかってもらえたのではないかと思います。
さて、とても参考になるPostgreSQLマニュアルですが、Version 11の翻訳プロジェクトの参加者を募集してます。興味がありましたら、どしどしご参加下さい。
参考
PostgreSQL 10がやってくる！(その7) psql新機能 - \if, \elseif, \else, \endif