628
PostgreSQL教程（三）：高級特性
本文檔為PostgreSQL 9.6.0文檔，本轉載已得到原譯者彭煜瑋授權。
在之前的章節裏我們已經涉及了使用SQL在PostgreSQL中存儲和訪問數據的基礎知識。現在我們將要討論SQL中一些更高級的特性，這些特性有助於簡化管理和防止數據丟失或損壞。最後，我們還將介紹一些PostgreSQL擴展。
本章有時將引用PostgreSQL教程（二）中的例子並對其進行改變或改進以便於閱讀本章。在這裏就不在贅述。
回想一下Section 2.6中的查詢。假設天氣記錄和城市為止的組合列表對我們的應用有用，但我們又不想每次需要使用它時都敲入整個查詢。我們可以在該查詢上創建一個視圖，這會給該查詢一個名字，我們可以像使用一個普通表一樣來使用它：
CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
SELECT * FROM myview;
對視圖的使用是成就一個好的SQL數據庫設計的關鍵方麵。視圖允許用戶通過始終如一的接口封裝表的結構細節，這樣可以避免表結構隨著應用的進化而改變。
視圖幾乎可以用在任何可以使用表的地方。在其他視圖基礎上創建視圖也並不少見。
回想第2章中的weather和cities表。考慮以下問題：我們希望確保在cities表中有相應項之前任何人都不能在weather表中插入行。這叫做維持數據的引用完整性。在過分簡化的數據庫係統中，可以通過先檢查cities表中是否有匹配的記錄存在，然後決定應該接受還是拒絕即將插入weather表的行。這種方法有一些問題且並不方便，於是PostgreSQL可以為我們來解決：
新的表定義如下：
CREATE TABLE cities (
city varchar(80) primary key,
location point
);
CREATE TABLE weather (
city varchar(80) references cities(city),
temp_lo int,
temp_hi int,
prcp real,
date date
);
現在嚐試插入一個非法的記錄：
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".
外鍵的行為可以很好地根據應用來調整。我們不會在這個教程裏更深入地介紹，讀者可以參考Chapter 5中的信息。正確使用外鍵無疑會提高數據庫應用的質量，因此強烈建議用戶學會如何使用它們。
事務是所有數據庫係統的基礎概念。事務最重要的一點是它將多個步驟捆綁成了一個單一的、要麼全完成要麼全不完成的操作。步驟之間的中間狀態對於其他並發事務是不可見的，並且如果有某些錯誤發生導致事務不能完成，則其中任何一個步驟都不會對數據庫造成影響。
例如，考慮一個保存著多個客戶賬戶餘額和支行總存款額的銀行數據庫。假設我們希望記錄一筆從Alice的賬戶到Bob的賬戶的額度為100.00美元的轉賬。在最大程度地簡化後，涉及到的SQL命令是：
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
UPDATE branches SET balance = balance - 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
UPDATE branches SET balance = balance + 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');
這些命令的細節在這裏並不重要，關鍵點是為了完成這個相當簡單的操作涉及到多個獨立的更新。我們的銀行職員希望確保這些更新要麼全部發生，或者全部不發生。當然不能發生因為係統錯誤導致Bob收到100美元而Alice並未被扣款的情況。Alice當然也不希望自己被扣款而Bob沒有收到錢。我們需要一種保障，當操作中途某些錯誤發生時已經執行的步驟不會產生效果。將這些更新組織成一個事務就可以給我們這種保障。一個事務被稱為是原子的：從其他事務的角度來看，它要麼整個發生要麼完全不發生。
我們同樣希望能保證一旦一個事務被數據庫係統完成並認可，它就被永久地記錄下來且即便其後發生崩潰也不會被丟失。例如，如果我們正在記錄Bob的一次現金提款，我們當然不希望他剛走出銀行大門，對他賬戶的扣款就消失。一個事務型數據庫保證一個事務在被報告為完成之前它所做的所有更新都被記錄在持久存儲（即磁盤）。
事務型數據庫的另一個重要性質與原子更新的概念緊密相關：當多個事務並發運行時，每一個都不能看到其他事務未完成的修改。例如，如果一個事務正忙著總計所有支行的餘額，它不會隻包括Alice的支行的扣款而不包括Bob的支行的存款，或者反之。所以事務的全做或全不做並不隻體現在它們對數據庫的持久影響，也體現在它們發生時的可見性。一個事務所做的更新在它完成之前對於其他事務是不可見的，而之後所有的更新將同時變得可見。
在PostgreSQL中，開啟一個事務需要將SQL命令用BEGIN和COMMIT命令包圍起來。因此我們的銀行事務看起來會是這樣：
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
-- etc etc
COMMIT;
如果，在事務執行中我們並不想提交（或許是我們注意到Alice的餘額不足），我們可以發出ROLLBACK命令而不是COMMIT命令，這樣所有目前的更新將會被取消。
PostgreSQL實際上將每一個SQL語句都作為一個事務來執行。如果我們沒有發出BEGIN命令，則每個獨立的語句都會被加上一個隱式的BEGIN以及（如果成功）COMMIT來包圍它。一組被BEGIN和COMMIT包圍的語句也被稱為一個事務塊。
Note: 某些客戶端庫會自動發出BEGIN和COMMIT命令，因此我們可能會在不被告知的情況下得到事務塊的效果。具體請查看所使用的接口文檔。
也可以利用保存點來以更細的粒度來控製一個事務中的語句。保存點允許我們有選擇性地放棄事務的一部分而提交剩下的部分。在使用SAVEPOINT定義一個保存點後，我們可以在必要時利用ROLLBACK TO回滾到該保存點。該事務中位於保存點和回滾點之間的數據庫修改都會被放棄，但是早於該保存點的修改則會被保存。
在回滾到保存點之後，它的定義依然存在，因此我們可以多次回滾到它。反過來，如果確定不再需要回滾到特定的保存點，它可以被釋放以便係統釋放一些資源。記住不管是釋放保存點還是回滾到保存點都會釋放定義在該保存點之前的所有其他保存點。
所有這些都發生在一個事務塊內，因此這些對於其他數據庫會話都不可見。當提交整個事務塊時，被提交的動作將作為一個單元變得對其他會話可見，而被回滾的動作則永遠不會變得可見。
記住那個銀行數據庫，假設我們從Alice的賬戶扣款100美元，然後存款到Bob的賬戶，結果直到最後才發現我們應該存到Wally的賬戶。我們可以通過使用保存點來做這件事：
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
-- oops ... forget that and use Wally's account
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Wally';
COMMIT;
當然，這個例子是被過度簡化的，但是在一個事務塊中使用保存點存在很多種控製可能性。此外，ROLLBACK TO是唯一的途徑來重新控製一個由於錯誤被係統置為中斷狀態的事務塊，而不是完全回滾它並重新啟動。
一個窗口函數在一係列與當前行有某種關聯的表行上執行一種計算。這與一個聚集函數所完成的計算有可比之處。但是與通常的聚集函數不同的是，使用窗口函數並不會導致行被分組成為一個單獨的輸出行--行保留它們獨立的標識。在這些現象背後，窗口函數可以訪問的不僅僅是查詢結果的當前行。
下麵是一個例子用於展示如何將每一個員工的薪水與他/她所在部門的平均薪水進行比較：
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
depname | empno | salary | avg
-----------+-------+--------+-----------------------
develop | 11 | 5200 | 5020.0000000000000000
develop | 7 | 4200 | 5020.0000000000000000
develop | 9 | 4500 | 5020.0000000000000000
develop | 8 | 6000 | 5020.0000000000000000
develop | 10 | 5200 | 5020.0000000000000000
personnel | 5 | 3500 | 3700.0000000000000000
personnel | 2 | 3900 | 3700.0000000000000000
sales | 3 | 4800 | 4866.6666666666666667
sales | 1 | 5000 | 4866.6666666666666667
sales | 4 | 4800 | 4866.6666666666666667
(10 rows)
最開始的三個輸出列直接來自於表empsalary，並且表中每一行都有一個輸出行。第四列表示對與當前行具有相同depname值的所有表行取得平均值（這實際和一般的avg聚集函數是相同的函數，但是OVER子句使得它被當做一個窗口函數處理並在一個合適的行集合上計算。）。
一個窗口函數調用總是包含一個直接跟在窗口函數名及其參數之後的OVER子句。這使得它從句法上和一個普通函數或聚集函數區分開來。OVER子句決定究竟查詢中的哪些行被分離出來由窗口函數處理。OVER子句中的PARTITION BY列表指定了將具有相同PARTITION BY表達式值的行分到組或者分區。對於每一行，窗口函數都會在當前行同一分區的行上進行計算。
我們可以通過OVER上的ORDER BY控製窗口函數處理行的順序（窗口的ORDER BY並不一定要符合行輸出的順序。）。下麵是一個例子：
SELECT depname, empno, salary,
rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;
depname | empno | salary | rank
-----------+-------+--------+------
develop | 8 | 6000 | 1
develop | 10 | 5200 | 2
develop | 11 | 5200 | 2
develop | 9 | 4500 | 4
develop | 7 | 4200 | 5
personnel | 2 | 3900 | 1
personnel | 5 | 3500 | 2
sales | 1 | 5000 | 1
sales | 4 | 4800 | 2
sales | 3 | 4800 | 2
(10 rows)
如上所示，rank函數在當前行的分區內按照ORDER BY子句的順序為每一個可區分的ORDER BY值產生了一個數字等級。rank不需要顯式的參數，因為它的行為完全決定於OVER子句。
一個窗口函數所考慮的行屬於那些通過查詢的FROM子句產生並通過WHERE、GROUP BY、HAVING過濾的"虛擬表"。例如，一個由於不滿足WHERE條件被刪除的行是不會被任何窗口函數所見的。在一個查詢中可以包含多個窗口函數，每個窗口函數都可以用不同的OVER子句來按不同方式劃分數據，但是它們都作用在由虛擬表定義的同一個行集上。
我們已經看到如果行的順序不重要時ORDER BY可以忽略。PARTITION BY同樣也可以被忽略，在這種情況下隻會產生一個包含所有行的分區。
這裏有一個與窗口函數相關的重要概念：對於每一行，在它的分區中的行集被稱為它的窗口幀。 很多（但不是全部）窗口函數隻作用在窗口幀中的行上，而不是整個分區。默認情況下，如果使用ORDER BY，則幀包括從分區開始到當前行的所有行，以及後續任何與當前行在ORDER BY子句上相等的行。如果ORDER BY被忽略，則默認幀包含整個分區中所有的行。 下麵是使用sum的例子：
SELECT salary, sum(salary) OVER () FROM empsalary;
salary | sum
--------+-------
5200 | 47100
5000 | 47100
3500 | 47100
4800 | 47100
3900 | 47100
4200 | 47100
4500 | 47100
4800 | 47100
6000 | 47100
5200 | 47100
(10 rows)
如上所示，由於在OVER子句中沒有ORDER BY，窗口幀和分區一樣，而如果缺少PARTITION BY則和整個表一樣。換句話說，每個合計都會在整個表上進行，這樣我們為每一個輸出行得到的都是相同的結果。但是如果我們加上一個ORDER BY子句，我們會得到非常不同的結果：
SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
salary | sum
--------+-------
3500 | 3500
3900 | 7400
4200 | 11600
4500 | 16100
4800 | 25700
4800 | 25700
5000 | 30700
5200 | 41100
5200 | 41100
6000 | 47100
(10 rows)
這裏的合計是從第一個（最低的）薪水一直到當前行，包括任何與當前行相同的行（注意相同薪水行的結果）。
窗口函數隻允許出現在查詢的SELECT列表和ORDER BY子句中。它們不允許出現在其他地方，例如GROUP BY、HAVING和WHERE子句中。這是因為窗口函數的執行邏輯是在處理完這些子句之後。另外，窗口函數在普通聚集函數之後執行。這意味著可以在窗口函數的參數中包括一個聚集函數，但反過來不行。
如果需要在窗口計算執行後進行過濾或者分組，我們可以使用子查詢。例如：
SELECT depname, empno, salary, enroll_date
FROM
(SELECT depname, empno, salary, enroll_date,
rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
FROM empsalary
) AS ss
WHERE pos < 3;
上述查詢僅僅顯示了內層查詢中rank低於3的結果。
當一個查詢涉及到多個窗口函數時，可以將每一個分別寫在一個獨立的OVER子句中。但如果多個函數要求同一個窗口行為時，這種做法是冗餘的而且容易出錯的。替代方案是，每一個窗口行為可以被放在一個命名的WINDOW子句中，然後在OVER中引用它。例如：
SELECT sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
繼承是麵向對象數據庫中的概念。它展示了數據庫設計的新的可能性。
讓我們創建兩個表：表cities和表capitals。自然地，首都也是城市，所以我們需要有某種方式能夠在列舉所有城市的時候也隱式地包含首都。如果真的聰明，我們會設計如下的模式：
CREATE TABLE capitals (
name text,
population real,
altitude int, -- (in ft)
state char(2)
);
CREATE TABLE non_capitals (
name text,
population real,
altitude int -- (in ft)
);
CREATE VIEW cities AS
SELECT name, population, altitude FROM capitals
UNION
SELECT name, population, altitude FROM non_capitals;
這個模式對於查詢而言工作正常，但是當我們需要更新一些行時它就變得不好用了。
更好的方案是：
CREATE TABLE cities (
name text,
population real,
altitude int -- (in ft)
);
CREATE TABLE capitals (
state char(2)
) INHERITS (cities);
在這種情況下，一個capitals的行從它的父親cities繼承了所有列（name、population和altitude）。列name的類型是text，一種用於變長字符串的本地PostgreSQL類型。州首都有一個附加列state用於顯示它們的州。在PostgreSQL中，一個表可以從0個或者多個表繼承。
例如，如下查詢可以尋找所有海拔500尺以上的城市名稱，包括州首都：
SELECT name, altitude
FROM cities
WHERE altitude > 500;
它的返回為：
name | altitude
-----------+----------
Las Vegas | 2174
Mariposa | 1953
Madison | 845
(3 rows)
在另一方麵，下麵的查詢可以查找所有海拔高於500尺且不是州首府的城市：
SELECT name, altitude
FROM ONLY cities
WHERE altitude > 500;
name | altitude
-----------+----------
Las Vegas | 2174
Mariposa | 1953
(2 rows)
其中cities之前的ONLY用於指示查詢隻在cities表上進行而不會涉及到繼承層次中位於cities之下的其他表。很多我們已經討論過的命令 — SELECT、UPDATE 和DELETE — 都支持這個ONLY記號。
Note: 盡管繼承很有用，但是它並未和唯一約束或外鍵繼承，這也限製了它的可用性。
PostgreSQL中有很多特性在這個麵向SQL新用戶的教程中並未觸及。有關這些特性的更多詳情將在本書的後續部分進行討論。
最後更新：2017-08-16 14:32:12