ASH | サーバ | セキュリティ | Linux | FreeBSD | DB | Web | CGI | Perl | Java | XML | プログラム | ネットワーク | 標準 | Tips集

PostgreSQLの使い方

トリガの使い方

　トリガ（TRIGGER）は、データベースに更新（insert/update/delete）があった時、あらかじめ指定した関数を呼び出す機能です。一つのレコードに対する更新によって、他のテーブルに対する更新や同じテーブルの別のレコードに対する更新が必要になる場合等に、それを自動的に行なうために使われます。 PostgreSQLでは、C言語またはPL/pgSQLで定義したユーザ定義関数を、トリガで指定することができます。 PL/pgSQLで定義したトリガ用の関数のcreate文と、トリガのcreate文を、下に示します。

reate function set_koushin_datetime() returns opaque as '
  begin
    new.koushin_datetime := ''now'';
    return new;
  end;
' language 'plpgsql';

create trigger access_upd before update on access_kanri for each row
  execute procedure  set_koushin_datetime();

　まずトリガ用の関数set_koushin_datetimeを定義し、その関数をaccess_kanriテーブルに対するupdateが行われる直前に実行されるトリガとして設定しています。

create function文の説明

　トリガ用の関数の返却値は、必ずopaqueにします。
　beginからend;の間に、実行文を書きます。上の例では、現在の日付時刻をnew.koushin_datetimeに設定し、newを返却しています。（'now'は特殊な定数で、現在を表します。PL/pgSQLでは、文字列は''で囲むことになっているので、''now''となります）
　newは更新後のレコードイメージを表し、newを返却するとは、koushin_datetimeに現在の日付時刻を設定した後のレコードイメージを返却することになります。

create trigger文の説明

　上で定義した関数set_koushin_datetime()を、access_kanriテーブルにupdateが実行される直前に実行されるように定義しています。
　before updateと指定しているので、関数set_koushin_datetime()から返却されるイメージのレコードでupdateされます。（after updateと指定した場合は、トリガ関数の返却値は無視される、とのことです）
　関数set_koushin_datetime()は、レコードのkoushin_datetimeに現在の日付時刻を設定するものです。 koushin_datetimeというdatetime型またはtimestamp型のカラムを持つテーブルであれば、どんなテーブルに対しても適用することができます。
　この関数をbefore updateのトリガとして設定すれば、レコードの最終更新日時がkoushin_datetimeに設定されるようになります。

SEQUENCEの使い方

　シーケンス（SEQUENCE）は、PostgreSQLで、自動的に連番を生成する仕組みです。
　シーケンスを使うには、まずcreate sequnence文で、シーケンスを作成しておく必要があります。 create sequnence文の構文は、

create sequence シーケンス名
	[ increment 増分値 ]
	[ minvalue 最小値 ]
	[ maxvalue 最大値 ]
	[ start 初期値 ]
	[ cycle ]

です。 increment以下の指定は、オプションであり、何も指定しなければ、最小値1から最大値2,147,483,647まで、1ずつ増加するシーケンスが作成されます。
　初期値は、startで指定されなければ、最小値になります。（増分値がプラスの場合。増分値がマイナスの時は最大値が初期値になるとのことですが、未確認です）
　cycleは、値をサイクリックに使うかどうかの指定です。これが指定された場合、最大値に達すると、次の値は、最小値になります。
　PostgreSQLでは、シーケンスを使うために、以下の三つの関数が用意されています。

nextval('シーケンス名')
　次のシーケンス値を取り出します。前回この関数を呼んだ時に返った値より、増分値だけ大きい値が返却されます。
currval('シーケンス名')
　現在のシーケンス値を取り出します。ただし、この関数を呼ぶ前に同じセッションで同じシーケンスに対してnextval('シーケンス名')を呼び出していないと、エラーになります。
setval('シーケンス名',値)
　シーケンスの値を指定した値でリセットします。

　シーケンスの機能を利用したものに、serial型というデータ型があります。 serial型は、自動的に連番を生成する、PostgreSQL独自のデータ型で、単独で一意にしたいカラムを定義するのに便利です。
　例えば、

SQL# create table table1(id serial, name text);

というように使います。
　上記のcreate table文を実行した後、psqlモードで\dコマンドを実行すると、table1というテーブルが作成されているだけでなく、table1_id_seqという名前のシーケンス（SEQUENCE）も作成されているのが、わかります。（create table文で自動的に作成されたシーケンスは、drop table文で自動的に削除はされないので、注意してください。別にdrop sequence文で削除する必要があります。）

serialを使っている表をcopyしたときの注意

　serialを使っている表をcopyして作った場合、sequenceに最大値を設定し直す必要があります。
　表でserial型のカラムを使うと、シーケンスが自動的に作成されます。その表にレコードを追加するたびに、シーケンスの値が１ずつ増加され、その値がserial型のカラムに設定されます。
　このような表を、copyして、別の表にデータを移した場合、そのまま新しい表にさらにレコードを追加していっては、いけません。 serial型のカラムにunique制約や主キー（primary key）制約が指定されていると、insert時に、duplicate keyエラーが発生する事があります。
　これは、copyでテーブルにデータを追加していく時、そのカラムに値を設定しているが、シーケンスの値は更新していないためです。
　例えば、

create table meibo(id serial primary key,name text);

と定義したテーブルにcopyでデータを設定した場合、copyの後で、meibo_id_seqというシーケンスに値を設定し直す必要があります。具体的には、サブクエリーを使って、

SQL# select setval('meibo_id_seq',(select max(id) from meibo));

というように、設定します。

interval の使い方

　例えば、table1に、date1という日付を表現するデータ型のカラムがあるとすると、こんな感じのＳＱＬ文で、日付データを、１年後の日付で更新することができます。

SQL# update table1 set date1 = date1 + interval '@ 1 year';

　１年前の日付にするには'-'を使います。１日分ずらすには、yearの代りにdayを使います。（@はなくても良いようです。）
　シーラカンス本のp.97、「図3.4.1 intervalを使った日付の計算例」では、i がdatetime型で、１日後の日付が得られるように書いてありますが、実際にやってみたところ、うまくいきませんでした。

SQL# select i + '1 day' from t2;

ERROR:  Unable to identify an operator '+' for types 'date' and 'unknown'
        You will have to retype this query using an explicit cast

　これは、以下のようにすると、うまくいきます。（実際にやってみた時の表名やカラム名は違いますが）

SQL# select i + interval '1 day' from t2;

　intervalというのは、本来、日付・時刻の差を扱うデータ型です。
　例えば、

SQL# create table hon (hhh interval);

のように、interval型のカラムを持つ表を定義することができます。

SQL# insert into hon values(timestamp '1999/12/31' - 'now');

　上のようにレコードを追加すると、次のようなデータが格納されます。

SQL# select * from hon;
       hhh
------------------
 467 14:46:05 ago
(1 row)

　これは、４６７日１４時間４６分５秒前を意味します。
　シーラカンス本で、intervalの入力形式の例として、10 years、3 months、1 dayというように、値が１かどうかによって、単位を表す単語も単数形／複数形が使い分けらわれています。厳密な使い分けが必要なのかどうか、yearsを例にして、実験してみました。　結論としては、値が１でもそうでなくても、year／yearsとも、受け付けられました。なお、yearaなどというでたらめな単位を使うと、

ERROR:  Bad interval external representation '1 yeara'

というように、エラーになりました。

select文の用途

　select文の一般的な用途は、データベースの表からレコードを検索することです。

SQL# select * from table1 where 検索条件;

で、table1に格納されているレコードの内、検索条件に合致するレコードの全てのカラムの値を取得します。* の位置にカラム名のリストを書けば、指定したカラムの値だけを取得します。この * の位置に書くものを「ターゲットリスト」と呼びます。
　ターゲットリストには、カラム名だけでなく、カラムの値を使った演算も書くことができます。　例えば、

SQL# select カラム名 * 2 from table1 where 検索条件;

のように指定すると、検索に合致したレコードの指定されたカラムの値を２倍にした値が得られます。
　また、ターゲットリストに関数を使った式を書くこともできます。例えば、

SQL# select count(*) from table1 where 検索条件;

と指定すると、検索に合致したレコード件数を得る事ができます。
　ターゲットリストに良く使われる関数には、max(カラム名)、avg(カラム名)、sum(カラム名)等があります。それぞれ条件に合致したレコードのカラムの最大値、カラムの平均値、カラムの合計値が得るのに使われます。

　以上は、データベースを検索した結果や、その結果に基づいた計算を行なった結果を得るものでした。このように、データベースの検索を行なう、というのが、select文の基本的な機能であることは、間違いありません。ところが、データベースの検索を行なわずに、select文を使うことがあります。
　例えば、setval()という関数を使うような場合です。setval()は、シーケンスの値を指定した値でリセットする関数です。このような関数をSQLで実行するには、select文を使って、

SQL# select setval('シーケンス名',値);

とするしかないようです。　また、関数の返却値を確認するために、selectで関数を動かしてみる、ということも、psqlモードではよく行なわれます。
　select文に、このような使い方もあることを理解しておくと、役に立つことがあると思います。

[ASHのホームページへ]