简介
在上一章中，我们介绍了使用SQL在PostgreSQL中存储和访问数据的基础知识。现在我们将讨论SQL的一些更高级的功能，这些功能可以简化管理并防止数据丢失或损坏。最后，我们将介绍一些PostgreSQL扩展。
假设您的应用程序特别关注天气记录和城市位置的组合列表，但您不想每次需要时都键入查询。您可以在查询上创建一个视图，该视图为查询提供了一个名称，您可以像引用普通表一样引用该名称：
CREATE VIEW myview AS
SELECT name, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
SELECT * FROM myview;充分利用视图是良好 SQL 数据库设计的关键方面。视图允许您将表结构的细节封装在一致的界面后面，这些细节可能会随着应用程序的发展而改变。
视图几乎可以用于任何可以使用真实表的地方。在其他视图上构建视图并不罕见。
外键
weather中的和cities表。考虑以下问题：您想确保没有人可以在表中插入没有匹配条目的行。这称为维护数据的*引用完整性*。在简单的数据库系统中，这将通过首先查看表以检查是否存在匹配记录，然后插入或拒绝新记录来实现（如果有的话）。这种方法存在许多问题并且非常不方便，因此PostgreSQL可以为您完成此操作。weathercitiescities``weather 新的表格声明如下：
CREATE TABLE cities (
name varchar(80) primary key,
location point
);
CREATE TABLE weather (
city varchar(80) references cities(name),
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".外键的行为可以根据您的应用程序进行微调。本教程中我们不会超出这个简单示例的范围，正确使用外键肯定会提高数据库应用程序的质量，因此强烈建议您了解它们。
交易
事务是所有数据库系统的基本概念。事务的要点是将多个步骤捆绑成一个全有或全无的操作。步骤之间的中间状态对其他并发事务不可见，并且如果发生某些故障导致事务无法完成，则所有步骤都不会影响数据库。
例如，假设有一个银行数据库，其中包含各种客户帐户的余额以及各分行的总存款余额。假设我们要记录从 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.00 美元，而这笔钱并没有从 Alice 那里扣除，那当然不行。如果 Alice 被扣除了款项而 Bob 却没有被记入账户，那么 Alice 也不会长期成为满意的客户。我们需要保证，如果操作过程中出现问题，到目前为止执行的任何步骤都不会生效。将更新分组到事务中可以为我们提供这种保证。事务被称为原子事务：从其他事务的角度来看，它要么完全发生，要么根本不发生。
我们还希望保证，一旦事务完成并被数据库系统确认，它确实已被永久记录，即使随后不久发生崩溃也不会丢失。例如，如果我们记录 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有时称为事务块。
笔记
一些客户端库会自动发出
BEGIN和COMMIT命令，因此您可能无需询问即可获得事务块的效果。请查看您正在使用的接口的文档。
通过使用保存点，可以更精细地控制事务中的语句。保存点允许您有选择地丢弃事务的某些部分，同时提交其余部分。使用 定义保存点后SAVEPOINT，如果需要，您可以使用 回滚到保存点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 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子句精确确定查询的行如何拆分以供窗口函数处理。子句PARTITION BY中的子句OVER将行划分为共享相同表达式值的组或分区PARTITION BY。对于每一行，窗口函数都会跨与当前行属于同一分区的行进行计算。
ORDER BY您还可以使用within控制窗口函数处理行的顺序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)如这里所示，该函数使用子句定义的顺序为当前行分区中的rank每个不同值生成一个数值排名。不需要明确的参数，因为其行为完全由子句决定。ORDER BY``ORDER BY``rank``OVER
窗口函数考虑的行是查询子句生成的“虚拟表”FROM中的行，这些行由其WHERE、GROUP BY和HAVING子句（如果有）过滤。例如，由于不符合条件而被删除的行WHERE不会被任何窗口函数看到。查询可以包含多个窗口函数，这些窗口函数使用不同的子句以不同的方式对数据进行切分OVER，但它们都作用于此虚拟表定义的同一行集合。
我们已经看到，ORDER BY如果行的顺序不重要，则可以省略。也可以省略PARTITION BY，在这种情况下，只有一个分区包含所有行。
窗口函数还有另一个重要概念：对于每一行，在其分区内都有一组行，称为其窗口框架。某些窗口函数仅对窗口框架的行起作用，而不是整个分区。默认情况下，如果ORDER BY提供了，则框架包含从分区开头到当前行的所有行，加上根据子句与当前行相等的任何后续行ORDER BY。当ORDER BY省略时，默认框架由分区中的所有行组成。[5]下面是一个使用的示例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)ORDER BY上面，由于子句中没有OVER，窗口框架与分区相同，由于缺少，分区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,
elevation int, -- (in ft)
state char(2)
);
CREATE TABLE non_capitals (
name text,
population real,
elevation int -- (in ft)
);
CREATE VIEW cities AS
SELECT name, population, elevation FROM capitals
UNION
SELECT name, population, elevation FROM non_capitals;就查询而言，这可以正常工作，但是当您需要更新多行时，它会变得很难看。
更好的解决方案是这样的：
CREATE TABLE cities (
name text,
population real,
elevation int -- (in ft)
);
CREATE TABLE capitals (
state char(2) UNIQUE NOT NULL
) INHERITS (cities);在这种情况下，行capitals 继承了**其父行的所有列（name、population和elevation）。列的类型为，这是PostgreSQL的原生可变长度字符串类型。表有一个附加列 ，显示其州缩写。在PostgreSQL中，一个表可以从零个或多个其他表继承。cities``name``text``capitals``state
例如，以下查询查找海拔超过 500 英尺的所有城市（包括州首府）的名称：
SELECT name, elevation
FROM cities
WHERE elevation > 500;返回
name | elevation
-----------+-----------
Las Vegas | 2174
Mariposa | 1953
Madison | 845
(3 rows)另一方面，以下查询查找所有非州首府且海拔超过 500 英尺的城市：
SELECT name, elevation
FROM ONLY cities
WHERE elevation > 500; name | elevation
-----------+-----------
Las Vegas | 2174
Mariposa | 1953
(2 rows)此处的ONLYbeforecities表示查询应仅针对cities表运行，而不针对cities继承层次结构中的下一级表运行。我们已经讨论过的许多命令 — SELECT、UPDATE和DELETE— 都支持此ONLY表示法。
尽管继承经常有用，但它尚未与唯一约束或外键集成，这限制了它的实用性。
本教程介绍中未涉及PostgreSQL的许多功能，这些功能面向SQL的新用户。本书的其余部分将更详细地讨论这些功能。