はじめてのPostgreSQL 9.1.5文書第三章

PostgreSQL 9.1.5文書の第三章を読んだ。なかなか難しい……
環境：Ubuntu 12.04 LTS 64bit / PostgreSQL 9.1.9 - pgvm

3.2 ビュー

問い合わせをビューとして作成すると、テーブル参照のような形で結果を参照することができる。

mydb=# CREATE VIEW myview AS
mydb-#   SELECT city, temp_lo, temp_hi, prcp, date, location
mydb-#       FROM weather, cities
mydb-#       WHERE city = name;
CREATE VIEW
mydb=# SELECT * FROM myview;

エイリアスみたいな感じかな。

3.3 外部キー

citiesテーブルに一致する項目がない行はweatherテーブルに挿入できないようにしたい場合。データの参照整合性の保全とか言うみたい。

mydb=# CREATE TABLE cities (
mydb(#   city varchar(80) primary key,
mydb(#   location point
mydb(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "cities_pkey" for table "cities"
CREATE TABLE
mydb=# CREATE TABLE weather (
mydb(#   city varchar(80) references cities(city),
mydb(#   temp_lo int,
mydb(#   temp_hi int,
mydb(#   prcp real,
mydb(#   date date
mydb(# );
CREATE TABLE

weatherテーブルの作成に指定したreferencesがキモなのかな。

mydb=# INSERT INTO weather VALUES('Berkeley', 45, 53, 0.0, '1994-11-28');
ERROR:  insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
DETAIL:  Key (city)=(Berkeley) is not present in table "cities".
STATEMENT:  INSERT INTO weather VALUES('Berkeley', 45, 53, 0.0, '1994-11-28');
ERROR:  insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
DETAIL:  Key (city)=(Berkeley) is not present in table "cities".

citiesテーブルに'Berkeley'の行ないよーってことかな。

試しにcitiesに'Berkeley'の行を入れてから実行してみると……

mydb=# INSERT INTO cities VALUES('Berkeley', point(10, 10));
INSERT 0 1
mydb=# INSERT INTO weather VALUES('Berkeley', 45, 53, 0.0, '1994-11-28');
INSERT 0 1

挿入できた！なるほどー。

3.4 トランザクション

トランザクションは複数の手順を単一の操作にまとめ上げること。エラーが発生した場合はトランザクションは完結しない。
他のトランザクションからはそのトランザクションが完結するまで不可視……ってのは終わるまで待ってるということなのか、同時に走ってる場合は実行前のデータで処理されるってことなのか……
ってかトランザクションってなんかモードみたいなのがあった気がする。

BEGIN;
UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice';
COMMIT;

BEGINでトランザクションを開始、COMMITで更新の完了をする。途中で問題があって更新の破棄をする場合はROLLBACKをする。

セーブポイントを使うと、トランザクション内の文をより細かい粒度で制御できるみたい。

mydb=# BEGIN;
BEGIN
mydb=# INSERT INTO cities VALUES('Niigata', point(20, 20));
INSERT 0 1
mydb=# SAVEPOINT insert_niigata;
SAVEPOINT
mydb=# INSERT INTO cities VALUES('Tokyo', point(30, 30));
INSERT 0 1
mydb=# ROLLBACK TO insert_niigata;
ROLLBACK
mydb=# COMMIT;
COMMIT
mydb=# SELECT * FROM cities;
   city   | location
----------+----------
 Berkeley | (10,10)
 Niigata  | (20,20)
(2 rows)

'Niigata'を挿入したあと、'Tokyo'を挿入してるけどROLLBACK TOで'Niigata'を挿入した直後に戻ってからCOMMITしているので'Tokyo'は挿入されてない。
こういう使い方するのかー、なるほど。

3.5 ウィンドウ関数

意味がわからない……あとで調べる。

3.6 継承

OOPと同じく、テーブルを継承するらしい。

mydb=# DROP TABLE weather;
DROP TABLE
mydb=# DROP TABLE cities;
DROP TABLE
mydb=# CREATE TABLE cities (
mydb(#   name text,
mydb(#   population real,
mydb(#   altitude int
mydb(# );
CREATE TABLE
mydb=# CREATE TABLE capitals(
mydb(#   state char(2)
mydb(# ) INHERITS (cities);
CREATE TABLE
mydb=# SELECT * FROM capitals;
 name | population | altitude | state
 ------+------------+----------+-------
 (0 rows)

citiesを継承したcapitalsはstate以外のカラムを持ってる。

mydb=# SELECT name, altitude FROM ONLY cities WHERE altitude > 500;

FROM ONLYにすることでcitiesテーブルだけを参照するみたい。ということはONLYを付けないと継承元も参照するってことなのかな。
あと、継承すると一意性制約もしくは外部キーが使えないみたい。

これで一応チュートリアルの一章から三章は終わり。
読み進めるのがいいのか、一応勉強してみたほうがいいのか……
ひと通り先に読んでみたほうがいいかな。