SQL REPL：互動式環境 • PostgreSQL 的藝術

PostgreSQL 內建的命令列工具 psql 既能寫腳本也能互動使用，功能強大：自動補全、readline 支援（歷史搜尋、現代快捷鍵）、輸入輸出重導、格式化輸出等。

PostgreSQL 新手常想找進階的視覺化查詢編輯工具，聽到答案是 psql 時一臉困惑；而多數進階使用者與專家想都不想，就是用 psql。本章教你真正欣賞這個小工具。

psql 入門#

psql 實作的是 REPL（read-eval-print loop）——學習與嘗試新事物時和電腦互動的最佳方式之一：探索 schema、探索資料集、或琢磨一條查詢。

我們通常只看到 SQL 查詢的最終完成形，很少看到通往它的過程。程式碼也一樣：你看到的是成品，不是作者反覆嘗試、逐步釐清問題的中間步驟。寫出完整而高效 SQL 的過程與寫程式相同——從最簡單的切入點開始迭代，而 REPL 環境讓你能輕鬆地在前一步的基礎上疊加。

psqlrc 設定#

先給出完整設定，本章其餘部分再逐項回頭解釋。存進 ~/.psqlrc（psql 啟動時讀取），你就有一個立即可用的環境：

\set PROMPT1 '%~%x%# '
\x auto
\set ON_ERROR_STOP on
\set ON_ERROR_ROLLBACK interactive

\pset null '¤'
\pset linestyle 'unicode'
\pset unicode_border_linestyle single
\pset unicode_column_linestyle single
\pset unicode_header_linestyle double
set intervalstyle to 'postgres_verbose';

\setenv LESS '-iMFXSx4R'
\setenv EDITOR '/Applications/Emacs.app/Contents/MacOS/bin/emacsclient -nw'

這裡用到三種設定指令：

\set [name [value ...]]：設定 psql 變數；多個值會串接；不給值則設為空值；用 \unset 取消。
\setenv name [value]：設定環境變數（不給值則取消）。此處用來設定 psql 所需的環境變數，例如 LESS——每個結果集都交給分頁器，但只在必要時才接管畫面。
\pset [option [value]]：控制查詢結果表格的輸出選項；value 的語意依選項而異，省略時多為切換或顯示目前設定。

交易與 psql 行為#

幾個改變 psql 行為的關鍵變數：

\set ON_ERROR_STOP on：如其名——前一條指令出錯時不再繼續執行後續指令。主要用於腳本（也可從命令列設定），但因為互動中也常用 \i、\ir 執行腳本，此選項仍然有用。
\set ON_ERROR_ROLLBACK interactive：改變 psql 的交易行為。設為 on 時，交易區塊內的語句出錯會被忽略、交易繼續；設為 interactive 時只在互動階段如此，讀腳本檔時不適用。其原理是 psql 在交易區塊內的每條指令前自動發出隱含的 SAVEPOINT，失敗時自動 rollback 到該 savepoint。

搭配 \set PROMPT1 '%~%x%# '，提示符會在交易進行中顯示一顆小星號，提醒你要收尾；反過來說，要做任何有副作用的操作（改資料或改 schema）時，沒看到星號就知道要先打 BEGIN。

比較兩種行為。ON_ERROR_ROLLBACK 為預設（off）時：

f1db# begin;
BEGIN
f1db*# select 1/0;
ERROR: division by zero
f1db!# select 1+1;
ERROR: current transaction is aborted, commands ignored until end of transaction block
f1db!# rollback;
ROLLBACK

出錯後星號變成驚嘆號，交易已被標記無效，PostgreSQL 只接受 commit 或 rollback——而且兩者的結果都是 ROLLBACK。設為 interactive 之後：

f1db# begin;
BEGIN
f1db*# select 1/0;
ERROR: division by zero
f1db*# select 1+1;
 ?column?
══════════
        2
(1 row)

f1db*# commit;
COMMIT

錯誤之後不但能繼續送出指令、仍在交易中，最後還能成功 COMMIT。

報表工具#

psql 有兩大用途：互動工具、以及腳本與報表工具。後者提供進階的格式化能力——透過 \pset format 可以把查詢結果直接輸出成 asciidoc 或 HTML。例如查詢某位車手姓氏的前 N 佳績，設定動態變數、只顯示資料列、輸出 HTML：

~ psql --tuples-only        \
       --set n=1            \
       --set name=Alesi     \
       --no-psqlrc          \
       -P format=html       \
       -d f1db              \
       -f report.sql

<table border="1">
  <tr valign="top">
    <td align="left">Alesi</td>
    <td align="left">Canadian Grand Prix</td>
    <td align="right">1995</td>
    <td align="right">1</td>
  </tr>
</table>

連線參數可用環境變數，也可直接用與應用程式碼相同的連線字串（copy/paste 即可測試，不必轉成 -d dbname -h hostname ... 語法）：

~ psql -d postgresql://dim@localhost:5432/f1db
~ psql -d "user=dim host=localhost port=5432 dbname=f1db"

report.sql 中使用 :'name' 變數語法——:name 會少掉字面值的引號，:'' 則能正確處理含空白的值；psql 也支援 :"variable" 雙引號記法，用於識別字（欄名、表名）當參數的動態 SQL：

  select surname, races.name, races.year, results.position
    from results
         join drivers using(driverid)
         join races using(raceid)
   where drivers.surname = :'name'
         and position between 1 and 3
order by position
   limit :n;

跑報表時用 --no-psqlrc 確保不載入互動用設定（UTF-8 花樣與 ON_ERROR_ROLLBACK 等）——批次或報表腳本通常不想要那些；倒是可以考慮設 ON_ERROR_STOP。

探索 Schema#

回到互動功能。psql 除了送出 SQL、顯示結果與伺服器通知（notification）、錯誤訊息之外，還提供一整組以反斜線開頭的客戶端命令，多數用於探索資料庫 schema——它們全都是對伺服器執行一或多條系統目錄（catalog)查詢實作的。也就是說，你可以藉由觀察這些查詢，學會怎麼查 PostgreSQL 的 catalog。

例如想報告資料庫大小卻不知道去哪找：文件說 \l+ 可以做到，打開 ECHO_HIDDEN 就能看到它背後的 SQL：

~# \set ECHO_HIDDEN true
~# \l+
********* QUERY **********
SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       ...
       CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
            THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
            ELSE 'No Access'
       END as "Size",
       ...
FROM pg_catalog.pg_database d
  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
ORDER BY 1;
**************************

於是「只要資料庫名稱與磁碟大小」就簡單了：

  SELECT datname,
         pg_database_size(datname) as bytes
    FROM pg_database
ORDER BY bytes desc;

書裡不重印公開文件——去把 psql 的手冊整頁讀完，裡面有很多你不知道的寶藏。

互動式查詢編輯器#

前面設定的 EDITOR 環境變數，就是 psql 執行 \e 等視覺編輯命令時呼叫的程式：它會把上一條查詢（或空白內容）放進暫存檔用你的編輯器打開，編輯結束後立即執行該查詢。用 emacs 或 vim 的人在終端機裡會非常開心；psql 在本機執行時，也可以把 EDITOR 設成你慣用的 IDE。