postgreSQL その31 トリガ、ルール、スキーマ

postgreSQL その30 インデックスのつづき

postgreSQL その8 課題リストは2018/4/30時点でのオイラの所見

[P.248]

■トリガ

【概要】

トリガとは、特定のテーブルに対して挿入/更新/削除が行われた場合に、既定のユーザ定義関数（プロシージャ）を発動させる機能。

トリガの発動タイミングは下記のような場合に指定できる。

　・行ごと、SQLごとを指定する

　　トリガの発動を、処理対象の行ごとに行うか、あるいはSQLごとに行うかを指定できる

　・UPDATE/INSERT/DELETE/TRUNCATEの種別を指定する

　・トリガの発動を、更新の種類で指定できる

　　INSERTとUPDATEのときだけトリガを発動させる、などの指定が可能

　・更新対象の列を指定する

　　特定の列の更新をトリガの発動の対象にする、といった指定が可能

　・実際に更新されたかどうかを指定する

　　実際に値が更新されたらトリガを発動させる、といった指定が可能。

　　特定の列を対象にすることも可能

　・更新の前後を指定する

　　更新処理の前後のどちらでトリガを発動させるかを指定できる。　

　　更新処理の前でトリガを発動させる場合、更新結果を参照することはできない。

【トリガの作成】

[書式]

create trigger トリガ名 { before | after } { update | insert | delete | truncate } on テーブル名

[for] [each] { row | statement } ] execute procedure 関数名;

更新前後のどちらで発動するかを[before]もしくは[after]で指定する。

更新種別としては、updateなどを指定する。　更新種別は、orでつなげて複数指定することも可能。　updateを指定した場合に限り、「update of 列名」という書式で、特定の列が更新されたときにトリガを発動させることができる。　特定の列が更新されたかどうかは、newとoldというキーワードを使って判別する。　oldは更新前、newは更新後の値をそれぞれ参照するときに使う。

updateのトリガは双方を使えるが、insertはnewのみ、deleteはold飲み使える。行ごとの発動かSQL文ごとの発動かは、「row」、「statement」で指定できる。　トリガの実際の処理となる関数は、最後の「関数名」に指定する。

次の例では、トリガで発動させる関数をtrg_f()として、いくつかの種類のトリガを指定する。

[例]

create table tb1 (c1 int, c2 text, c3 text);

・tb1へのupdateとinsert処理の実行後に、行ごとにトリガを起動

create trigger trg_1 after update or insert on tb1 for each row execute procedure trg_f();

・tb1のc1列の値を実際に変更したupdateの実行後に、行ごとにトリガを起動

create trigger trg_2 after update of c1 on tb1 for each row when (old.c1 is distinct from new.c1) execute procedure trg_f();

【トリガの削除】

[書式]

drop trigger トリガ名 on テーブル名;

■ルール

【概要】

ルールは、ある条件のSQLが発行された場合、それを別のSQLで置き換えたり、別の処理を付け加えたりしたい場合に使用する。　たとえば、あるテーブルへのINSERT処理を別のテーブルへのINSERTに置き換えたり、あるテーブルへのSELECTを別のテーブルへのSELECTに置き換えたりすることができる。

PostgreSQLのビューは、このルールという仕組みを利用する。

ビューが使用される際は、ルールによりビューを定義したSQLへ置き換えられる。

【ルールの作成】

[書式]

create rule ルール名 as on イベント名 to テーブル名 do [ instead | also ] SQL文;

「イベント名」には、「テーブル名」に対して実施されるINSERT/SELECT/UPDATE/DELETEのいずれかを指定する。　本来のSQLの処理を置き換える処理を定義したい場合は

「DO INSTEAD」を、追加で実施したい処理を定義したい場合は「DO ALSO」を指定する。

また、「イベント名」にINSERTかUPDATEを指定した場合には、「SQL文」でNEWというキーワードを使用できる。　これは、本来実施されるSQLでINSERTあるいはUPDATEされる新規の値を参照したい場合に使用する。

次の例では、tb1テーブルへのINSERT時に、tbl_logテーブルへ同じデータとINSERT時のタイムスタンプを挿入するルールを作成する。

[例]

create table tb12 (c1 int);
create table tb12_log (c1 int,c2 timestamp);

・ルールを作成。tb12へinsertされた値を、new c1でtb12_logにも入れる

create rule tb12_ins as on insert to tb12 do also insert into tb12_log values (new.c1, now());

・tb12にデータを挿入

insert into tb12 values(100);

・tb12へ挿入した値と挿入時のタイムスタンプがtb12_logに格納されているか確認

select * from tb12_log;
c1 | c2
-----+----------------------------
100 | 2018-05-22 19:01:30.036252
(1 row)

【ルールの削除】

[書式]

drop rule ルール名 on テーブル名;

■スキーマ

スキーマは名前空間とも呼ばれ、テーブルや関数などのオブジェクトが含まれる。

データベースの中に複数のスキーマが存在しており、各スキーマの中にテーブルやインデックスが含まれているイメージとなる。　PostgreSQLでは、デフォルトでpublicスキーマが作成されており、通常はこのpublicスキーマの中にテーブルやインデックスが作成される。

【スキーマの作成】

[書式]

create schema スキーマ名 [authorization データベースユーザ名];

create schema [authorization データベースユーザ名];

AUTHORIZATIONを使用すると、スキーマの所有者を指定できる。

省略した場合は、発行したデータベースユーザ名を所有者としてスキーマが作成される。

次の例では、schemaという名前のスキーマを作成している。

[例]

select user;
user
----------
postgres
(1 row)

create schema test authorization punisuke;

create schema authorization punisuke;
create schema unko_schema;

【スキーマへのテーブルの作成とスキーマ検索パス】

特定のスキーマにテーブルを作成するには、次のようにテーブル名をスキーマ名で修飾する。　

インデックスはスキーマ名で修飾することができず、インデックスを付与するテーブルと同じスキーマに自動的に作成される。

[例]

testdb=# \c testdb punisuke
Password for user punisuke:
You are now connected to database "testdb" as user "punisuke".
testdb=> select user;
user
----------
punisuke
(1 row)

testdb=> \dn
List of schemas
Name | Owner
-------------+----------
public | postgres
punisuke | punisuke
test | punisuke
testsch | postgres
testschema | postgres
unko_schema | postgres
(6 rows)

testdb=> set search_path to public;
SET
testdb=> select current_schema;
current_schema
----------------
public
(1 row)

testdb=> set search_path to testschema;
SET

testdb=> select current_schema;
current_schema
----------------
testschema
(1 row)

→自分が所有者でないスキーマもSETできる

testdb=> create table punisuke_tb1 (c1 int, c2 text);
CREATE TABLE

→自分が所有者でないスキーマの中にテーブルを作成できる
testdb=> insert into testschema.punisuke_tb1 values (1,'あいう');

INSERT 0 1

→カレントスキーマでスキーマ名を修飾せずにテーブルを作成したらカレントスキーマの中にテーブルが作成される

→自分が所有者でないスキーマの中にテーブルに更新系SQLを実行できる

testdb=> select * from testschema.punisuke_tb1;
c1 | c2
----+--------
1 | あいう
(1 row)
→自分が所有者でないスキーマの中にテーブルにSQLを実行できる

→自分が所有者でないスキーマの中のテーブルにインデックスを作成できる

異なるスキーマ間で同じ名前のテーブルや関数を作成できる。

このように、複数のスキーマに渡って存在する同名のテーブルへのアクセスする際には、

①テーブルや関数をスキーマ名で修飾する方法

②スキーマ検索パスを設定する方法

の２つの方法がある。

検索パスを使うと、どのスキーマのオブジェクトに優先的にアクセスするかを指定できる。

スキーマ検索パスの指定方法には、

(1) postgresql.confのsearch_pathパラメータに指定する方法

(2) SETを使って、search_pathパラメータを動的に変更する方法

がある。

search_pathのデフォルト値は「$user,public」となっており、現在のデータベースユーザ名と同じ名前のスキーマにあるオブジェクトを優先的に使用するようになっている。（$userは現在のデータベースオブジェクトを表すエイリアス）

setを使ったsearch_pathパラメータの変更方法は下記の通り

[書式]

set search_path to スキーマ名;

スキーマ名はカンマ区切りで複数を指定することができる。$userのみダブルクォートで囲む。

標準的に使われる関数はすべてpublicスキーマにあるため、setで変更する場合はpublicを設定し忘れないように注意する。

次の例では、異なるスキーマに作成した２つのテーブルに対してSQLを発行している。

[例]

testdb=> create table punisuke.punisuke_tb1 (c1 int, c2 text);
CREATE TABLE
testdb=> insert into punisuke.punisuke_tb1 values (1,'かきく');
INSERT 0 1
testdb=> select current_schema;
current_schema
----------------
testschema
(1 row)

testdb=> select * from punisuke_tb1;
c1 | c2
----+--------
1 | あいう
(1 row)

testdb=> select * from punisuke.punisuke_tb1;
c1 | c2
----+--------
1 | かきく
(1 row)

testdb=> set search_path to punisuke;
SET
testdb=> select current_schema;
current_schema
----------------
punisuke
(1 row)

testdb=> select * from punisuke_tb1;
c1 | c2
----+--------
1 | かきく
(1 row)