はじめに
デフォルトではグローバル設定として以下の優先順位でサーチパスが設定されています。このデフォルト値を変更するにはpostgresql.confを書き換えることでグローバル設定のデフォルト値を変更できます。今回はユーザ毎にサーチパスを変更したくなった場合の対応方法を解説します。
-- 以下の意味は、ログインユーザと同じ名前のスキーマが存在しない限り、
-- publicスキーマへのアクセスになります。
myposdb=> show search_path;
search_path
-----------------
"$user", public
(1 行)
-- 以下でも確認可能です
--
myposdb=# select current_setting('search_path');
current_setting
-----------------
"$user", public
(1 行)
myposdb=#alter user でユーザ毎にサーチパスを変更できます
postgreSQLではスキーマ修飾をせずにオブジェクトを指定した場合、サーチパス（スキーマ検索パス）で指定する順番でオブジェクトを探します。この動作を認識しておかないと意図せず異なるスキーマのオブジェクトを参照および更新してしまう事があるので注意が必要です。
サーチパスの確認
現状サーチパス確認SQL
select * from ( select usename,unnest(useconfig) search_path from pg_user ) t where search_path like '%search%';
postgres=# select * from ( select usename,unnest(useconfig) search_path from pg_user ) t
postgres-# where search_path like '%search%';
usename | search_path
---------+----------------------------------------
udonman | search_path="$user", public, prjschema
sooni | search_path="$user", public, prjschema
(2 行)
postgres=#サーチパスの変更
変更SQL
以下例題として”relapi”スキーマを追加していますが、この時点で存在しないスキーマでも指定可能です。既にログインしているユーザのサーチパスを変更した場合、変更内容は次のログインから有効になりますのでご注意ください。
alter user udonman set search_path = "$user",relapi,public ;
変更例
ユーザ毎のサーチパスはpg_user.useconfigを参照すれば確認できますがこちらの項目は配列になっているのでunnest()関数で、配列から「行集合」に変換した後like検索する必要があります。
-- 現状の確認
postgres=# select * from ( select usename,unnest(useconfig) unnest from pg_user ) t where unnest like '%search%';
usename | unnest
---------+--------------------------------------
sooni | search_path=sooni, public, prjschema
udonman | search_path="$user", public
(2 rows)
postgres=#
-- udonmanユーザのサーチパスを変更
postgres=# alter user udonman set search_path = "$user",relapi,public
postgres-# ;
ALTER ROLE
-- 変更された事を確認する
postgres=# select * from ( select usename,unnest(useconfig) unnest from pg_user ) t where unnest like '%search%';
usename | unnest
---------+--------------------------------------
sooni | search_path=sooni, public, prjschema
udonman | search_path="$user", relapi, public
(2 rows)
postgres=#
