PostgreSQL | スキーマ | スキーマ検索パスを設定する

Tags:

• PostgreSQL
• Database

スキーマ名を省略してテーブルなどのオブジェクトを指定する場合に、実際にはどのスキーマ内のオブジェクトなのかを探すために使用されるスキーマ検索パスの設定方法と使い方について説明する。

スキーマ検索パスとは

テーブルなどのオブジェクトはスキーマに作成される。スキーマごとに同じ名前のテーブルを作成できるため、どのテーブルかを明確にするには スキーマ名.テーブル名 のように指定する必要がある。

たとえば、schemaA スキーマに staff テーブルがあり、schemaB スキーマにも同じ名前の staff テーブルがある場合、単に staff テーブルを指定すると、どちらのスキーマの staff テーブルなのかわからない。

select * from staff;

テーブルを明確に指定するには、schemaA.staff や schemaB.staff のように スキーマ名.テーブル名 で指定する必要がある。

select * from schemaA.staff;

毎回スキーマ名を入力するのは手間なので、スキーマ名を省略するためにどのスキーマを使うかを設定できる。この設定がスキーマ検索パスである。スキーマ検索パスには複数のスキーマを設定しておくことができる。

スキーマ名1, スキーマ名2 ...

たとえば、スキーマを省略してテーブル名だけを記述すると、スキーマ検索パスの先頭から順に、各スキーマにそのテーブル名が存在するかどうかを確認する。一致するテーブルが見つかると、そのスキーマ内のテーブル名として判断される。

それでは具体的に、スキーマ検索パスの使い方と設定方法について見てみる。

スキーマ検索パスの現在の設定値を確認する

スキーマ検索パスに設定されている値を確認するには、次のように実行する。

devkuma=# show search_path;
   search_path
-----------------
 "$user", public
(1개 행)


devkuma=#

次のように表示された。

 "$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 ロールで devkuma データベースに接続している。スキーマ検索パスはデフォルト状態なので、次のようになっている。

"$user", public

devkuma データベースにはロール名と同じ postgres スキーマが作成されていないため、スキーマ検索パスで最初に実在するスキーマは public である。したがって、スキーマを省略してテーブルを作成すると、public スキーマにテーブルが作成される。

次のように実行してテーブルを作成する。

devkuma=# create table memo (id integer, memo text);
CREATE TABLE
devkuma=#

テーブルが作成された。確認のため、psql メタコマンドである \dt を実行してみる。

devkuma=# \dt
      릴레이션(relation) 목록
 스키마 | 이름 |  종류  |  소유주
--------+------+--------+----------
 public | memo | 테이블 | postgres
(1개 행)


devkuma=#

public スキーマに、先ほど作成した memo テーブルが作成されていることを確認できる。

スキーマ検索パスを変更する

スキーマ検索パスを現在のセッション中だけ一時的に変更するには、SET コマンドを使用する。

SET search_path TO スキーマ名1, スキーマ名2, ...;

たとえば、スキーマ検索パスを myschema, public に設定するには、次のように実行する。

devkuma=# set search_path to myschema, public;
SET
devkuma=#

スキーマ検索パスが変更された。確認のため、show search_path を実行してみる。

devkuma=# show search_path;
   search_path
------------------
 myschema, public
(1개 행)


devkuma=#

スキーマ検索パスが指定した値に変更されていることを確認できる。

–

次に、スキーマ検索パスを永続的に変更する場合は、ALTER ROLE コマンドを使用する。

ALTER ROLE user SET search_path = スキーマ名1, スキーマ名2 ...;

たとえば、スキーマ検索パスを myschema, public に設定するには、次のように実行する。

devkuma=# alter role postgres set search_path to myschema, public;
ALTER ROLE
devkuma=#

スキーマ検索パスを変更した。確認のため、一度 PostgreSQL との接続を終了し、再度接続して show search_path を実行してみる。

devkuma=# alter role postgres set search_path to myschema, public;
ALTER ROLE
devkuma=# \q

C:\Users\kimkc>psql -U postgres
postgres 사용자의 암호:
psql (12.2)
도움말을 보려면 "help"를 입력하십시오.

postgres=# show search_path;
   search_path
------------------
 myschema, public
(1개 행)


postgres=#

スキーマ検索パスが指定した値に変更されていることを確認できる。

デフォルト値に戻すには、次のように実行する。

alter role postgres set search_path to default;

–

すべてのユーザーのデフォルトスキーマ検索パスを変更するには、PostgreSQL 設定ファイルである postgresql.conf を修正する。postgresql.conf ファイルの場所などについては、postgresql.conf ファイルの設定方法 を参照する。

postgresql.conf ファイルをテキストエディタで開き、次の場所を探す。

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

# - Statement Behavior -

#client_min_messages = notice		# values in order of decreasing detail:
					#   debug5
					#   debug4
					#   debug3
					#   debug2
					#   debug1
					#   log
					#   notice
					#   warning
					#   error
#search_path = '"$user", public'	# schema names
#row_security = on
#default_tablespace = ''		# a tablespace name, '' uses the default

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

現在の search_path 設定はコメントになっているため、先頭の # を削除する。そして、デフォルトのスキーマ検索パスとして設定したい値を指定する。ここでは次のように設定する。

search_path = 'myschema, clientschema'    # schema names

変更したら postgresql.conf ファイルを保存し、PostgreSQL を再起動する。

その後 PostgreSQL に接続し、show search_path を実行してみる。

C:\Users\kimkc>psql -U postgres
postgres 사용자의 암호:
psql (12.2)
도움말을 보려면 "help"를 입력하십시오.

postgres=# show search_path;
   search_path
------------------
 myschema, clientschema
(1개 행)


postgres=#

スキーマ検索パスのデフォルト値が変更されていることを確認できる。

–

ここまで、スキーマ検索パスの設定と使い方について説明した。