自分は PostgreSQL に Postico などの DB クライアントで接続することが多いのですが、今回改めて psql の機能について学んでみました。
psql とは PostgreSQL のターミナル型フロントエンドです。 対話的に問い合わせを入力し、それを PostgreSQL に対して発行して、結果を確認することができます。 また、ファイルまたはコマンドライン引数から入力を読み込むことも可能です。 さらに、psql は、スクリプトの記述を簡便化したり、様々なタスクを自動化したりする、いくつものメタコマンドとシェルに似た各種の機能を備えています。
psql の便利な機能
psql では変数を扱うことができ、SQL に値を差し込むことができます。
:変数名 とすると、その変数の値で置換されます。
また、psql にはメタコマンドがたくさんあります。メタコマンドを使うと便利なスクリプトを作成できます。
今回は以下のメタコマンドを使ってみました。
-
\echo: 変数などを標準出力に出力する -
\gexec: 問い合わせの出力の各行の各列を SQL 文として実行する -
\gset: 問い合わせの出力を psql 変数に格納する -
\if、\elif、\else、,\endif: 条件分岐させる -
\prompt: 変数に代入するテキストを入力するようにユーザを促す
アカウントごと、日付ごとのいいね数を一覧表示させる例を考えます。
CREATE TABLE account_summaries (
account_name TEXT NOT NULL
,like_count BIGINT NOT NULL DEFAULT 0
,summarized_at TIMESTAMPTZ NOT NULL
);
-- テストデータの準備
INSERT INTO account_summaries
SELECT
'account_hoge'
,round((random() * 1000)::NUMERIC, 0)
,t1.value
FROM
generate_series('2022-01-01'::TIMESTAMPTZ, '2022-12-01'::TIMESTAMPTZ, '1day'::INTERVAL) AS t1(value)
;
INSERT INTO account_summaries
SELECT
'account_fuga'
,round((random() * 100000)::NUMERIC, 0)
,t1.value
FROM
generate_series('2022-01-01'::TIMESTAMPTZ, '2022-11-01 '::TIMESTAMPTZ, '1day'::INTERVAL) AS t1(value)
;
以下のような形式で、「指定した期間」のアカウントごとのいいね数を出力したいのですが、カラムの数やカラム名が変わってしまうため通常の SQL だと出力できません。
| account_name | 2022-10-01 | 2022-10-02 | 2022-10-03 | 2022-10-04 |
|---|---|---|---|---|
| account_hoge | 813 | 19 | 141 | 497 |
| account_fuga | 28214 | 91272 | 95464 | 42615 |
そこで、psql のメタコマンドを使ってスクリプトを書いてみました。
\prompt で開始日時、終了日時を受け取り、その期間のいいね数を上のような形式で一覧表示させます。
\prompt '開始日時（YYYY-MM-DD）: ' summarize_start_at
\prompt '終了日時（YYYY-MM-DD）: ' summarize_end_at
\echo '開始日時:' :summarize_start_at '、終了日時:' :summarize_end_at
\prompt '実行しますか？（y/N）' answer
SELECT :'answer' = 'y' AS is_yes
\gset
\if :is_yes
\else
\q
\endif
SELECT
'
SELECT
t1.account_name
' || string_agg('
,MAX(CASE WHEN date_trunc(''day'', t1.summarized_at) = ''' || TO_CHAR(t1.value, 'YYYY-MM-DD') || ''' THEN t1.like_count ELSE NULL END) AS "' || TO_CHAR(t1.value, 'YYYY-MM-DD') || '"
', '') || '
FROM
public.account_summaries AS t1
GROUP BY
t1.account_name
ORDER BY
3
'
FROM
generate_series(
(:'summarize_start_at')::TIMESTAMPTZ
,(:'summarize_end_at')::TIMESTAMPTZ
,'1day'::INTERVAL
) AS t1(value)
\gexec
実行結果:
# \i list_account_summaries.sql
開始日時（YYYY-MM-DD）: 2022-10-12
終了日時（YYYY-MM-DD）: 2022-10-20
開始日時: 2022-10-12 、終了日時: 2022-10-20
実行しますか？（y/N）y
account_name | 2022-10-12 | 2022-10-13 | 2022-10-14 | 2022-10-15 | 2022-10-16 | 2022-10-17 | 2022-10-18 | 2022-10-19 | 2022-10-20
--------------+------------+------------+------------+------------+------------+------------+------------+------------+------------
account_hoge | 767 | 817 | 210 | 80 | 919 | 649 | 77 | 570 | 500
account_fuga | 3588 | 55343 | 3818 | 59556 | 53648 | 45980 | 1244 | 13320 | 21429
(2 rows)
psql を使うと、ストアドプロシージャと比べて以下のことが便利かもしれないと思いました。
- psql ではユーザ入力を求めることができる。
- psql ではシェルスクリプトを実行できる。（今回は試していない）
逆に不便だと思ったことは以下です。
- 変数を扱いにくい（基本的に値の文字列が置換されるだけ）
- ループさせるコマンドがない