PostgreSQL教程 -part2高级特性

在前面几章里，我们介绍了使用SQL存储和 访问在PostgreSQL里的数据的 基本方法。我们现在将讨论一些 SQL更高级的特性， 这些特性可以简化管理和避免数据的丢失或损坏。 最后，我们将看看一些PostgreSQL的扩展。

回头看看Section 2.6里的查询。 假设你的应用对天气记录和城市位置的组合列表特别感兴趣， 而你又不想每次键入这些查询。 那么你可以在这个查询上创建一个视图， 它给这个查询一个名字，你可以像普通表那样引用它:

自由地运用视图是设计好的SQL数据库的一个关键要素。 视图允许我们把表结构的细节封装起来，这些表可能随你的应用进化而变化， 但这些变化却可以隐藏在一个一致的接口后面。

视图几乎可以在一个真正的表可以使用的任何地方使用。 在其它视图上面再创建视图也并非罕见。
外键
回忆一下Chapter 2里的weather表 和cities表。 考虑下面的问题： 你想确保没有人可以在weather表里插入一条 在cities表里没有匹配记录的数据行。 这就叫维护表的参照完整性。 在简单的数据库系统里， 实现(如果也叫实现)这个特性的方法通常是先看看 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';-- 等等COMMIT;

PostgreSQL实际上把每个SQL语句当做在一个事务中执行来看待。 如果你没有发出BEGIN命令， 那么每个独立的语句都被一个隐含的BEGIN和(如果成功的话)COMMIT包围。 一组包围在BEGIN和COMMIT之间的语句 有时候被称做事务块。
我们可以通过使用保存点的方法，在一个事务里更加精细地控制其中的语句。 保存点允许你选择性地抛弃事务中的某些部分，而提交剩下的部分。 在用SAVEPOINT定义了一个保存点后，如果需要， 你可以使用ROLLBACK TO回滚到该保存点。 则该事务在定义保存点到ROLLBACK TO之间的所有数据库更改都被抛弃， 但是在保存点之前的修改将被保留。

在回滚到一个保存点之后，这个保存点仍然保存着其定义， 所以你可以回滚到这个位置好几次。当然，如果你确信你不需要再次回滚到一个保存点， 那么你可以释放它，这样系统可以释放一些资源。 要记住：释放或者回滚到一个保存点都会自动释放在其后定义的所有保存点

所有这些都发生在一个事务块内部，所以所有这些都不可能被其它事务会话看到。 当且仅当你提交了这个事务块，这些提交了的动作才能以一个单元的方式被其它会话看到， 而回滚的动作完全不会再被看到。
记得我们的银行数据库吗? 假设我们从Alice的帐户上消费$100.00， 然后给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 accountROLLBACK 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值。 对于每一行，窗口函数通过同一个分区作为当前行的行进行计算。

虽然无论什么样的顺序的行avg将产生相同的结果，但并这不是对于所有的 窗口函数。当需要时，你可以使用 OVER内的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)

如下所示，在由ORDER BY定义子句的顺序中， rank功能在每个不同ORDER BY 值的当前行分区产生一个数值排名。 rank需要没有明确的参数，因为它完全取决于OVER子句。
如下所示，在由ORDER BY定义子句的顺序中， rank功能在每个不同ORDER BY 值的当前行分区产生一个数值排名。 rank需要没有明确的参数，因为它完全取决于OVER子句。

窗口函数的行是通过查询FROM子句”virtual table”产生的， 如果有的话，过滤WHERE, GROUP BY和HAVING子句。 例如，行删除，因为它不符合没有任何窗口函数WHERE条件。查询可以包含多个窗口的功能，通过不同的OVER子句不同的方式分割数据，但是他们所有的行动在这个虚拟表中定义的同一行的集合。

我们已经看到了，如果行排序并不重要，ORDER BY可以省略。 在一个分区包含所有行的情况下，也可以省略PARTITION BY。
还有一个重要的与窗口功能相关的概念： 对于每一行，是有其分区范围内的行集，又称为它的window frame。 许多（但不是全部）窗口功能，只作用于窗框行上，而不是整个分区。 默认情况下，如果使用ORDER BY，那么这个frame包含从分区开始到当前的所有行， 以及那些当前行后面的，根据ORDER BY规则等于当前行的所有行， 如果不使用ORDER BY，那么，frame默认包含分区中的所有行。 [1] 下面是一个使用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时即整个表)相同; 换句话说，每一次sum求和都是使用表中所有的salary，所以我们得到了每个输出 行的相同结果。 但是，如果我们添加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_dateFROM  (SELECT depname, empno, salary, enroll_date,          rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos     FROM empsalary  ) AS ssWHERE 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);

继承