はじめての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を付けないと継承元も参照するってことなのかな。
あと、継承すると一意性制約もしくは外部キーが使えないみたい。
これで一応チュートリアルの一章から三章は終わり。
読み進めるのがいいのか、一応勉強してみたほうがいいのか……
ひと通り先に読んでみたほうがいいかな。