PostgreSQL サーチパスの設定方法

特にWindowsのローカル環境ではまりやすいのだが、スキーマ修飾子をつけないとSQLがうまく使えないような状況がある。
SQLを書くたびに "SELECT * FROM foo.orders"のように書かないといけないような状態である。
これは現在のログインユーザーとそのテーブル等のスキーマ作成時のユーザーが異なる場合に発生するのである。

なんでこうなるかというと、PostgreSQLでスキーマ修飾子を省略してよいデフォルト状態は
1.User名とスキーマ名が同じ
2.Publicに存在するテーブル
というケースであるのだが、該当しない場合だ。多くは1のスキーマ名が他のユーザ名で作成されていて、自分のIDではSELECT権限等あっても、検索できないケースだ。
これはpostgresql.conf内の "search_path"オプションに記載がある。

#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------

# - Statement Behavior -

#search_path = '"$user",public' # schema names

こんな感じ。
例によってコメントアウトされていてもデフォルトとしては有効なので、publicスキーマと$userスキーマについて有効である。
この"サーチパス"はPostgreSQLにおけるpath設定であり、ここに設定があればパスが通るのだ。

ではこれを変更するためにはどうすればよいのか？

1.永続的に全ユーザーに対して適用

postgresql.confの上記を変更すれば、全ユーザーについて追加したスキーマは自動的に検索対象パスになる。

search_path = 'foo,"$user",public' # schema names

こんな感じ。
これでfooスキーマについてはどのユーザーでクエリを行う場合でもスキーマ名を書かなくてよろしい。
左側から優先されるので、同一テーブル名があった場合、"foo"のテーブルが優先的に選択されることになる。

2.永続的に特定ユーザーに対して適用

さすがに誰でもが利用するとなるとちょっと、、という場合は特定のユーザーだけ対象とすることも可能である。

ALTER USER [ユーザ名] SET search_path TO [スキーマ名];

これで特定ユーザが特定スキーマに対してパスを通すことが可能となる。

3.一時的に特定セッションに対して適用

もちろん、一時的な設定も可能である。この場合はいつもどおりのSETを使用する。

SET search_path TO [スキーマ名];

である。

名前空間とスキーマのおかげで、名称のユニークさを担保しやすくなっているのだが、時として不便になることもある。
こうしたときはこんな技で回避、ということですな。