\setでpsql変数を定義する
psqlの\setメタコマンドは、クエリ内で:変数名として参照できる変数を定義する。
testdb=# \set myvar 'hello'
testdb=# \echo :myvar
hello
クエリ中では:'変数名'(引用符付き)、:"変数名"(識別子として)の2つの展開方法がある。
testdb=# SELECT :'myvar' AS as_string;
as_string
-----------
hello
(1 row)
testdb=# \set col name
testdb=# SELECT :"col" FROM users LIMIT 1;
name
-------
alice
(1 row)
:'myvar'はSQLの文字列リテラルとして展開され、:"col"は列名やテーブル名などの識別子として展開される。値をそのまま埋め込む:myvarは数値やSQL断片を組み立てる場合に使う。
未定義の変数を参照すると構文エラーになる。
testdb=# SELECT * FROM users WHERE id = :undefined_var;
ERROR: syntax error at or near ":"
LINE 1: SELECT * FROM users WHERE id = :undefined_var;
\setを引数なしで実行すると、ユーザーが定義した変数に加えてAUTOCOMMITやVERSIONなどpsqlが内部的に使う特殊変数も含めた一覧を表示する。
testdb=# \set
AUTOCOMMIT = 'on'
...
DBNAME = 'testdb'
...
VERSION = 'PostgreSQL 14.17 (Homebrew) on aarch64-apple-darwin24.2.0, ...'
myvar = 'hello'
\unsetで変数を削除する
\unsetで定義済みの変数を削除する。削除後は再び未定義の変数として扱われる。
testdb=# \unset myvar
testdb=# \echo :myvar
:myvar
\echoは未定義の変数をエラーにせず、:変数名という文字列のまま出力する。SQL文中で未定義変数を参照した場合との違いに注意する。
\setenvでシェルの環境変数を設定する
\setenvはpsqlプロセスの環境変数を設定する。設定した環境変数は\!で実行するシェルコマンドや、PSQL_EDITORのようなpsql自身が参照する環境変数の変更に使う。
testdb=# \setenv MY_ENV_VAR hello_env
testdb=# \! echo "shell sees: $MY_ENV_VAR"
shell sees: hello_env
psql変数(\setで定義するもの)とは別の仕組みであり、:での参照はできない。
\getenvでシェルの環境変数をpsql変数に取り込む
\getenvは\setenvの逆で、環境変数の値をpsql変数に取り込む。PostgreSQL 15以降のpsqlクライアントで使える機能であり、それより古いバージョンではinvalid commandエラーになる。
testdb=# \getenv myenvvar MY_ENV_VAR
testdb=# \echo :myenvvar
hello_env
環境変数MY_ENV_VARの値がmyenvvarというpsql変数に格納される。存在しない環境変数を指定した場合、psql変数は変更されない。
testdb=# \getenv nouser NO_SUCH_VAR
testdb=# \echo :nouser
:nouser
接続情報をシェルスクリプト側から環境変数で渡し、psqlスクリプト内でその値を使って分岐したい場合などに使う。
\promptで対話的に値を入力させる
\promptはメッセージを表示してユーザーからの入力を1行受け取り、psql変数に格納する。
testdb=# \prompt 'Enter a value: ' myval
Enter a value: test input
testdb=# \echo :myval
test input
バッチ実行時に標準入力から値を流し込むこともできる。確認用のプロンプトを出しつつ、スクリプトから値を渡す場合に使う。
$ echo "test input" | psql -d testdb -f script.sql
パスワードのように画面に表示したくない値を入力させる専用のオプションは\promptにはない。そうした値は環境変数と\getenvの組み合わせで扱うか、psql起動時のパスワードプロンプト(-W)のような別の仕組みを使う。
具体例: 接続先ごとに異なる値を使い分ける
\getenvとスクリプトファイルを組み合わせると、実行環境ごとに異なる値を使うSQLスクリプトを1つにまとめられる。
-- migrate.sql
\getenv target_schema TARGET_SCHEMA
SELECT count(*) FROM :target_schema.users;
$ TARGET_SCHEMA=app psql -d testdb -f migrate.sql
$ TARGET_SCHEMA=report psql -d testdb -f migrate.sql
環境変数を切り替えるだけで、同じスクリプトを複数のスキーマに対して実行できる。
