スキーマ検索パスを設定する
スキーマ名を省略してテーブルなどのオブジェクトを指定した場合、実際にどのスキーマの中のオブジェクトなのかを探すためスキーマ検索パスが使用されます。ここでは PostgreSQL でスキーマ検索パスの設定方法と使い方について解説します。
(Last modified: )
スキーマ検索パスとは
テーブルなどのオブジェクトはスキーマの中に作成され、スキーマ毎に同じ名前のテーブルを作成することができるので、どのテーブルかを明確に指定するには スキーマ名.テーブル名 のような形式で指定する必要があります。
例えば schemaA スキーマの中に staff テーブルがあり、 schemaB スキーマの中にも同じ名前の staff テーブルがあった場合、単に staff テーブルと指定したらどちらのスキーマの中の staff テーブルなのか分かりません。
select * from staff;
テーブルを明確に指定するには schemaA.staff や schemaB.staff などのように スキーマ名.テーブル名 のように指定する必要があります。
select * from schemaA.staff;
ただ毎回スキーマ名を記述するのは手間もかかるため、スキーマ名を省略した場合はどのスキーマなのかを設定しておくことができます。これがスキーマ検索パスです。スキーマ検索パスには複数のスキーマを設定しておくことができます。
スキーマ名1, スキーマ名2, ...
例えばスキーマが省略されてテーブル名だけが記述された場合、スキーマ検索パスの先頭から順にスキーマの中にテーブル名が存在するかどうかを調べていきます。一致したテーブルが見つかったら、そのスキーマの中のテーブル名と判断します。
では具体的にスキーマ検索パスの使い方と設定方法について確認していきます。
スキーマ検索パスの現在の設定値を確認する
スキーマ検索パスに設定されている値を確認するには次のように実行してください。
show search_path;
次のような値を取得しました。
"$user", public
スキーマ検索パスにはスキーマをカンマで区切って記述していきます。最初に記載されている "$user" は現在のロール名と同じ名前のスキーマを表します。現在は postgres ロールで接続しているため、次のように記述されていたのと同じ扱いになります。
postgres, public
スキーマ検索パスの使われ方
スキーマ検索パスは、 SELECT などの既存のテーブルを探す場合と、 CREATE などの新しいテーブルを作成する場合で使われ方が少し異なります。
最初にテーブルを探す場合です。テーブルからデータを取得する場合などにスキーマ名を省略してテーブル名だけを記述すると、スキーマ検索パスに記載されているスキーマを最初から順にしたテーブルが存在するかどうかを確認していきます。
例えばスキーマ検索パスが次のようになっていた場合で考えてみます。
schemaA, schemaB, schemaC
ここで例えば次のようにスキーマを省略してテーブル名を指定し、データを取得しようとしたとします。
select * from mytbl;
最初に schemaA スキーマの中に mytbl があるかどうかをチェックします。見つかった場合は schemaA.mytbl としてデータを取得します。見つからなかった場合は schemaB スキーマの中に mytbl があるかどうかをチェックします。これをスキーマ検索パスに設定されているスキーマの数だけ繰り返します。
最後まで見つからなかった場合はエラーとなります。スキーマ検索パスに記載されていないスキーマが他にあったとしてもチェックはされません。
-- --
次にテーブルを作成する場合です。スキーマ名を省略してテーブル名を記述した場合、スキーマ検索パスに記載されているスキーマが実在するかどうかを確認していきます。実在するスキーマが見つかった場合はそのスキーマでテーブルを作成します。
例えばスキーマ検索パスが次のようになっていた場合で考えてみます。
schemaA, schemaB, schemaC
schemaA から順に確認していき、実際に存在するスキーマが見つかった場合にはそのスキーマが現在のスキーマとなります。
ここで次のようにスキーマ名を省略してテーブルを作成すると、現在のスキーマにテーブルが作成されます。
create table mytbl (...);
-- --
それではテーブルを作成する方を実際に試してみます。現在スーパーユーザーである postgres ロールにて mydb データベースに接続しています。スキーマ検索パスはデフォルトの状態なので次のようになっています。
"$user", public
mydb データベースにはロール名と同じ postgres スキーマが作成されていませんので、スキーマ検索パスの中で最初に実在するスキーマは public となります。そのため、テーブルを作成する場合にスキーマを省略した場合は public スキーマの中にテーブルが作成されます。
では次のように実行してテーブルを作成してください。
create table memo (id integer, memo text);
テーブルが作成されました。確認のために psql メタコマンドの \dt を実行してみます。
\dt
public スキーマの中に先ほど作成した memo テーブルが作成されていることが確認できました。
スキーマ検索パスを変更する
スキーマ検索パスを現在のセッション中だけ一時的に変更したい場合には SET コマンドを使って変更します。
SET search_path TO スキーマ名1,スキーマ名2,...;
例えばスキーマ検索パスを myschema,public に設定したい場合は次のように実行します。
set search_path to myschema,public;
スキーマ検索パスが変更されました。確認のために show search_path を実行してみます。
スキーマ検索パスが指定した値に変更されていることが確認できました。
-- --
次にスキーマ検索パスを恒久的に変更したい場合には ALTER ROLE コマンドを使って変更します。
ALTER ROLE user SET search_path = スキーマ名1,スキーマ名2,...;
例えばスキーマ検索パスを myschema,public に設定したい場合は次のように実行します。
alter role postgres set search_path to myschema,public;
スキーマ検索パスを変更しました。確認のために一度 PostgreSQL との接続を終了してからあらためて接続を行い show search_path を実行してみます。
スキーマ検索パスが指定した値に変更されていることが確認できました。
なおデフォルトの値を参照するように戻したい場合には、次のように実行してください。
alter role postgres set search_path to default;
-- --
全てのユーザーのデフォルトのスキーマ検索パスを変更する場合は、 PostgreSQL の設定ファイルである postgresql.conf ファイルを変更します。( postgresql.conf ファイルの場所などについては「postgresql.confファイルの設定方法」を参照してください)。
postgresql.conf ファイルをテキストエディタで開いたあと次のような場所を探してください。
#------------------------------------------------------- # CLIENT CONNECTION DEFAULTS #------------------------------------------------------- #search_path = '"$user", public' # schema names
現在 search_path に関する設定はコメントとなっているので先頭の # を削除してください。その後でデフォルトのスキーマ検索パスとして設定したい値を設定してください。今回は次のように設定しました。
search_path = 'myschema, clientschema' # schema names
変更が終わりましたら postgresql.conf ファイルを保存してください。その後で PostgreSQL を再起動してください。
それではあらためて PostgreSQL と接続を行い show search_path を実行してみます。
スキーマ検索パスのデフォルトの値が変更されていることが確認できました。
-- --
スキーマ検索パスの設定と使い方について解説しました。
( Written by Tatsuo Ikura )
著者 / TATSUO IKURA
これから IT 関連の知識を学ばれる方を対象に、色々な言語でのプログラミング方法や関連する技術、開発環境構築などに関する解説サイトを運営しています。