9.15. 聚集函数

聚集函数 从一套输入值里计算一个结果。 Table 9-37 显示了内建聚集函数。 聚集函数的特殊语法在 Section 4.2.7里解释。请参考 Part I 获取附加的介绍性信息。

Table 9-37. 聚集函数
函数        参数类型        返回类型        描述
avg(expression)         所有输入值的均值（算术平均）        smallint, integer, bigint, real, double precision, numeric, 或 interval.         对于任何整数类型输入，结果都是 numeric 类型。 对于任何浮点输入，结果都是 double precision 类型。 否则和输入数据类型相同。         所有输入值的平均（算术平均）。
bit_and(expression)         smallint, integer, bigint, 或者 bit         和参数数据类型相同         所有非空输入值的按位 AND （与），如果没有，则为空
bit_or(expression)         smallint, integer, bigint, 或者 bit         和参数数据类型相同         所有非空输入值的按位 OR （或），如果没有，则为空
bool_and(expression)         bool         bool         如果所有输入值都是真，则为真，否则为假
bool_or(expression)         bool         bool         如果至少有一个输入值为真，则为着，否则为假
count(*)                 bigint        输入值的个数
count(expression)        任意        bigint        计算那些 expression 非 NULL 的输入的个数。
every(expression)         bool         bool         等效于 bool_and
max(expression)        任何数值，字串或者日期/时间类型        与参数同类型        所有输入值中， expression 的最大值
min(expression)        任何数值，字串或者日期/时间类型        与参数同类型        所有输入值中， expression 的最小值
stddev(expression)         smallint, integer, bigint, real, double precision, 或 numeric.         浮点数参数时是double precision， 否则 numeric。         输入值的标准采样偏差（sample standard deviation）
sum(expression)        smallint, integer, bigint, real, double precision, numeric, 或者 interval         对 smallint 或 integer输入 输出类型为 bigint。对于bigint输入输出类型为 numeric， 浮点数输入的结果是 double precision。 否则和输入数据类型相同。         所有输入值的 expression 的总和
variance(expression)         smallint, integer, bigint, real, double precision, 或者 numeric.         浮点数参数是 double precision， 否则是 numeric。         输出值的采样方差（标准采样偏差的平方）。

请注意除了 count 以外， 这些函数在没有选中行时返回 NULL。 尤其要指出的是对零输入行进行 sum 将返回 NULL， 而不是我们预期的零。 必要时可以用 coalesce 把 NULL 替换成零。

    注意: 布尔聚集 bool_and 和 bool_or 对应标准的 SQL 聚集 every 和 any 或者 some。对于 any 和 some， 标准语法里面似乎有些内置的歧义：

    SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;

    这里的 ANY 既可以被认为是引出一个子查询，也可以认为是一个聚集（如果查询表达式返回 1 行电话）。 因此标准的名字无法用于这些聚集。

    注意: 习惯了使用其它 RDBMS 产品的用户可能会对 PostgreSQL 里面的一些聚集函数的性能特点感到奇怪，特别是对整个表进行聚集的时候 （换句话说，不声明 WHERE 子句的时候）。 比如，一个下面这样的查询：

    SELECT min(col) from sometable;

    将会被 PostgreSQL 以对整个表进行顺序扫描的形式进行执行。 而其它数据库系统可能会把这样的查询优化成使用某个字段上的索引的方式（如果有索引的话）。 在 PostgreSQL 里类似的还有， 如果对整个表进行处理的话，聚集函数 max() 和 count() 总是要求一次顺序扫描。

    PostgreSQL 无法很容易地实现这样的 优化，因为它还允许用户定义的聚集查询。因为 min()， max()，和 count() 都是使用一个聚集函数的普通 API 定义的，因此在某些场合里， 这些函数的执行并不需要规定"特殊转换"。

    幸运的是，对于 min() 和 max()， 我们有些简单的方法来绕开。比如下面显示的查询就等效于上面的查询， 但是，如果在目标字段上有索引，那么它可以利用一个 B+ 树索引来优化。

    SELECT col from sometable ORDER BY col ASC LIMIT 1;

    类似的查询（通过把上面查询的 ASC 替换成 DESC 就行了）也可以用在 max() 的地方。

    糟糕的是，在对全表操作的时候，没有类似简单的查询可以用于改进 count() 的性能。

9.16. 子查询表达式

本节描述 PostgreSQL 里面能用的 SQL 兼容的子查询表达式。所有本节中成文的表达式都返回布尔值（真/假）结果。
9.16.1. EXISTS

EXISTS ( subquery )

EXISTS 的参数是一个任意的SELECT语句， 或者说子查询。系统对子查询进行运算以判断它是否返回行。 如果它至少返回一行，那么 EXISTS 的结果就为"真"； 如果子查询没有返回行，那么 EXISTS 的结果是"假"。

子查询可以引用来自周围的查询的变量，这些变量在该子查询的任何一次计算中都起常量的作用。

这个子查询通常只是运行到能判断它是否可以生成至少一行为止， 而不是等到全部结束。在这里写任何有副作用的子查询都是不明智的（比如调用序列函数）；这些副作用是否发生是很难判断的。

因为结果只取决于是否会返回行，而不取决于这些行的内容， 所以这个子查询的输出列表通常是无关紧要的。一个常用的编码习惯是用下面的形式写所有的EXISTS测试 EXISTS(SELECT 1 WHERE ...)。不过这条规则有例外， 比如那些使用 INTERSECT 的子查询。

下面这个简单的例子类似在col2上的一次内联接，但是它为每个 tab1的行生成最多一个输出，即使存在多个匹配tab2的行也如此∶

SELECT col1 from tab1
    WHERE EXISTS(SELECT 1 from tab2 WHERE col2 = tab1.col2);

9.16.2. IN

expression IN (subquery)

IN 的这种形式的右手边是一个圆括弧括起来的子查询， 它必须只返回一个字段。左手边表达式对子查询的结果的每一行进行一次计算和比较。 如果找到任何等于子查询行的情况，那么 IN 的结果就是"真"。 如果没有找到相等行，那么结果是"假"（包括子查询没有返回任何行的特殊例子）。

请注意如果左手边表达式生成 NULL，或者没有相等的右手边数值， 并且至少有一个右手边行生成 NULL，那么 IN 构造的结果将是 NULL，而不是假。 这个行为是遵照 SQL 处理布尔和 NULL 值组合时的规则定的。

和 EXISTS 一样，假设子查询将被完成运行完全是不明智的。

row_constructor IN (subquery)

这种形式的 IN 的左手边是一个行构造器， 如 Section 4.2.11 里面所述， 右手边是一个圆括弧括起来的子查询，它必须返回和左手边返回的行中表达式所构成的完全一样多的字段。 左手边表达式就子查询结果的每一行进行计算很比较。如果找到任意相等的子查询行，则 IN 的结果为"真"。如果没有找到相等行， 那么结果为"假"（包括子查询不返回行的特殊例子）。

通常，表达式或者子查询行里的 NULL 是按照 SQL 布尔表达式的一般规则进行组合的。 如果两个行对应的成员都是非空并且相等，那么认为这两行相等；如果任意对应成员为非空且不等，那么该两行不等； 否则这样的行比较的结果是未知（NULL）。如果所有行的结果要么是不等， 要么是 NULL，并且至少有一个 NULL，那么 IN 的结果是 NULL。
9.16.3. NOT IN

expression NOT IN (subquery)

右手边是一个用圆括弧包围的子查询，它必须返回一个字段。左手边表达式就子查询结果的每一行进行计算和比较。 如果只找到不相等的子查询行（包括子查询不返回行的特殊情况），那么NOT IN 的结果是"真"。 如果找到任何相等行，则结果为"假"。

请注意如果左手边表达式返回空，或者如果没有等于右手边的值，并且至少一个右手边行生成空， 那么 NOT IN 构造的结果将是空，而不是真。这是根据 SQL 对布尔和空值组合的一般规则制定的。

和 EXISTS 一样，假设子查询会完全结束是不明智的。

row_constructor NOT IN (subquery)

NOT IN 的这种形式左手边是一个行构造器， 如 Section 4.2.11 里所述。其右手边是一个圆括弧括起来的子查询， 它必须返回和左手边表达式返回的行一样多的字段。 左手边表达式对子查询的结果的每一行进行一次计算和比较。 如果只出现不等于子查询行的情况，那么 NOT IN 的结果就是"真"。 （包括子查询没有返回任何行的特殊例子）。如果找到相等行，那么结果是"假"。

和通常一样，表达式或者子查询行里的 NULL 是按照 SQL 布尔表达式的一般规则进行组合的。 如果两个行对应的成员都是非空并且相等，那么认为这两行相等；如果任意对应成员为非空且不等，那么该两行不等； 否则这样的行比较的结果是未知(NULL)。如果所有行的结果要么是不等， 要么是 NULL，并且至少有一个 NULL，那么 NOT IN 的结果是 NULL。
9.16.4. ANY/SOME

expression operator ANY (subquery)
expression operator SOME (subquery)

这种形式的 ANY 右手边是一个圆括弧括起来的子查询， 它必须返回一个字段。左手边表达式将使用给出的 operator对子查询的结果的每一行进行一次计算和比较。 目前如果获得任何真值结果，那么 ANY 的结果就是"真"。 如果没有找到真值结果，那么结果是"假"（包括子查询没有返回任何行的特殊例子）。

SOME 是 ANY 的同意词。 IN 等效于 = ANY。

请注意如果没有任何成功并且至少有一个右手边行为该操作符结果生成 NULL， 那么 ANY 构造的结果将是 NULL，而不是 FALSE。 这个行为是遵照 SQL 处理布尔和 NULL 值组合时的规则定的。

和 EXISTS 一样，假设子查询将被完成运行完全是不明智的。

row_constructor operator ANY (subquery)
row_constructor operator SOME (subquery)

的这种形式的左手边是一个行构造器，如 Section 4.2.11 里描述的那样。 右手边是一个圆括弧括起来的子查询， 它必须返回和左手边列表给出的表达式一样多的列。左手边表达式将使用给出的 operator对子查询的结果的每一行进行一次计算和比较。 目前，系统只允许使用 = 或 <> 操作符处理逐行的 ANY 构造。 如果分别找到相等或者不相等的行，那么 ANY 的结果就是 "真"。如果没有找到这样的行，那么结果是"假"（包括子查询没有返回任何行的特殊例子）。

通常，表达式或者子查询行里的 NULL 是按照 SQL 布尔表达式的一般规则进行组合的。 如果两个行对应的成员都是非空并且相等，那么认为这两行相等；如果任意对应成员为非空且不等，那么该两行不等； 否则这样的行比较的结果是未知(NULL)。如果至少有一个 NULL 行结果， 那么 ANY 的结果将是TRUE 或者 NULL。
9.16.5. ALL

expression operator ALL(subquery)

ALL 的这种形式的右手边是一个圆括弧括起来的子查询， 它必须只返回一列。左手边表达式将使用给出的 operator对子查询的结果的每一行进行一次计算和比较。该操作符必须生成布尔结果。 如果所有行的结果都为"真"，（包括子查询没有返回任何行的特殊例子）。 那么 ALL 的结果就是"真"。如果没有存在任何假值结果，那么结果是"假"。

NOT IN 等效于 <> ALL。

请注意如果没有运算失败但是至少有一个右手边行为该操作符的结果生成 NULL 值， 那么 ALL 构造的结果将是 NULL，而不是 TRUE。 这个行为是遵照 SQL 处理布尔和 NULL 值组合时的一般规则定的。

和 EXISTS 一样，假设子查询将被完成运行完全是不明智的。

row_constructor operator ALL (subquery)

ALL 的这种形式的左手边是一个行构造器，如 Section 4.2.11 所述。 右手边是一个圆括弧括起来的子查询，它必须返回和左手边行给出的表达式一样多的字段。 左手边表达式将使用给出的 operator对子查询的结果的每一行进行一次计算和比较。 目前系统只允许使用 = 和 <> 操作符处理逐行的 ALL 查询。 如果所有子查询都是相等或者不相等的行，那么 ALL 的结果就是"真"。（包括子查询没有返回任何行的特殊例子）。 如果分别有任何不相等或者相等的行，那么结果是"假"。

通常，表达式或者子查询行里的 NULL 是按照 SQL 布尔表达式的一般规则 进行组合的。如果两个行对应的成员都是非空并且相等，那么认为这两行 相等；如果任意对应成员为非空且不等，那么该两行不等； 否则这样的行比较的结果是未知(NULL)。如果至少有一个 NULL 行结果， 那么 ALL 的结果就不可能是TRUE；它将会是 FALSE 或者 NULL。
9.16.6. 逐行比较

row_constructor operator (subquery)

左手边是一个行构造器，如 Section 4.2.11 所述。 右手边是一个圆括弧括起来的子查询，该查询必须返回很左手边表达式数目完全一样的字段。 另外，该子查询不能返回超过一行的数量。（如果它返回零行，那么结果就是 NULL。） 左手边逐行与右手边的子查询结果行，或者右手边表达式列表进行比较。目前，只允许使用 = 和 <> 操作符进行逐行比较。如果两行分别是相等或者不等，那么结果为"真"。

通常，表达式或者子查询行里的 NULL 是按照 SQL 布尔表达式的一般规则进行组合的。 如果两个行对应的成员都是非空并且相等，那么认为这两行相等；如果任意对应成员为非空且不等，那么该两行不等； 否则这样的行比较的结果是未知（NULL）。

9.17. 行和数组比较

本节描述一个特殊的构造，用于在组和数值之间进行多重比较。这些形式语法上和前面一节的子查询形式相关，但是不涉及子查询。 这种形式涉及的 数组子表达式是 PostgreSQL 的扩展； 其它的是 SQL 兼容的。所有本节记录的表达式形式都返回布尔（Boolean）结果（真/假）。
9.17.1. IN

expression IN (value[, ...])

右手边是一个圆括弧包围的标量列表。如果左手边的表达式结果等于任何右手边表达式中的一个，结果为"真"。它是下面的方式的缩写

expression = value1
OR
expression = value2
OR
...

请注意如果左手边表达式生成空，或者没有相等的右手边数值并且至少有一个右手边的表达式生成空，那么 IN 构造的结果将为空， 而不是假。这是与 SQL 处理布尔和空值在一起的问题的时候的一般规则一致的。
9.17.2. NOT IN

expression NOT IN (value[, ...])

右手边是一个圆括弧包围的标量列表。如果左手边的表达式结果不等于所有右手边表达式的值，结果为"真"。它是下面的方式的缩写

expression <> value1
AND
expression <> value2
AND
...

请注意如果左手边表达式生成空，或者没有相等的右手边数值并且至少有一个右手边的表达式生成空，那么 NOT IN 构造的结果将为空， 而不是假。这是与 SQL 处理布尔和空值在一起的问题的时候的一般规则一致的。

    提示: x NOT IN y 在所有场合都等效于 NOT (x IN y)。 但是，在处理空值的时候，用 NOT IN 比用 IN 更容易迷惑新手。 最好用正逻辑来表达你的条件。

9.17.3. ANY/SOME （数组）

expression operator ANY (array expression)
expression operator SOME (array expression)

右手边是一个圆括弧包围的表达式，它必须生成一个数组值。左手边表达式使用给出的操作符 operator，对数组的每个元素进行计算和比较，这个操作符必须生成布尔结果。 如果获取了任何真值结果，那么 ANY 的结果是 "true"。 如果没有找到真值结果（包括数组只有零元素的特例），那么结果是 "false"。

SOME 是 ANY 的同义词。
9.17.4. ALL (array)

expression operator ALL (array expression)

右手边是一个圆括弧包围的表达式，它必须生成一个数组值。左手边表达式使用给出的操作符 operator，对数组的每个元素进行计算和比较，这个操作符必须 生成布尔结果。如果所有比较都生成真值结果，那么 ALL 的结果是 "true"（包括数组只有零元素的特例）。 如果有任何假值结果，那么结果是 "false"。
9.17.5. 逐行比较

row_constructor operator row_constructor

两遍都是一个行构造器；如 Section 4.2.11 里所述。 两个行必须有同等数目的字段。两边都被计算并且逐行比较。 目前，只允许用 = 和 <> 操作符进行 逐行比较。如果两行相等，结果是 "true"，否则是假。

通常，表达式或者子查询行里的 NULL 是按照 SQL 布尔表达式的一般规则进行组合的。 如果两个行对应的成员都是非空并且相等，那么认为这两行相等； 如果任意对应成员为非空且不等，那么该两行不等； 否则这样的行比较的结果是未知(NULL)。

row_constructor IS DISTINCT FROM row_constructor

这些构造类似一个 <> 行比较， 但是它对空值输入不生成空。而是任何空值都认为是不等于（区别于）任何非空值， 并且任何两个空都认为是相等的（没有区别）。因此，这个的结果将不是真就是假，绝不会是空。

row_constructor IS NULL
row_constructor IS NOT NULL

这个构造测试一行的数值时空还是非空。一个行值只有在至少有一个字段不为空的时候，才是非空。

9.18. 返回集合的函数

本节描述那些可能返回多于一行的函数。目前这个类型的函数只有序列号生成函数， 如 Table 9-38 所述。

Table 9-38. 序列号生成函数
函数        参数类型        返回类型        描述
generate_series(start, stop)        int 或 bigint        setof int 或 setof bigint （与参数类型相同）        生成一个数值序列，从 start 到 stop，步进为一。
generate_series(start, stop, step)        int or bigint        setof int or setof bigint (same as argument type)        生成一个数值序列，从 start 到 stop，步进为 step。

如果 step 是正数，而 start 大于 stop， 那么返回零行。想法，如果 step 是负数，start 小于 stop， 则返回零行。如果是 NULL 输入，也产生零行。step 为零则是一个错误。 下面是一些例子：

select * from generate_series(2,4);
generate_series
-----------------
               2
               3
               4
(3 rows)

select * from generate_series(5,1,-2);
generate_series
-----------------
               5
               3
               1
(3 rows)

select * from generate_series(4,3);
generate_series
-----------------
(0 rows)

select current_date + s.a as dates from generate_series(0,14,7) as s(a);
   dates
------------
2004-02-05
2004-02-12
2004-02-19
(3 rows)

9.19. 系统信息函数

Table 9-39 显示了几个抽取会话及系统信息的函数。

Table 9-39. 会话信息函数
名字        返回类型        描述
current_database()        name        当前数据库的名字
current_schema()        name        当前模式的名字
current_schemas(boolean)        name[]        在搜索路径中的模式名字
current_user        name        目前执行环境下的用户名
inet_client_addr()        inet        连接的远端址
inet_client_port()        int4        连接的远端端口
inet_server_addr()        inet        连接的本地地址
inet_server_port()        int4        连接的本地端口
session_user        name        会话用户名
user        name        等于 current_user
version()        text        PostgreSQL 版本信息

session_user 通常是初始化当前数据库联接的用户， 不过超级用户可以用 SET SESSION AUTHORIZATION 修改这个设置。 current_user 是用于权限检查的用户标识。通常， 它总是等于会话用户，但是在将来可能有 "setuid" 函数和其他它是等于会话用户， 但是它在函数执行的过程中随着属性 SECURITY DEFINER 的改变而改变。 在 Unix 的说法里，那么会话用户是"真实用户"，而当前用户是"有效用户"。

    注意: current_user，session_user， 和 user 在 SQL里有特殊的语意状态： 调用时结尾不要跟着园括号。

current_schema 返回在搜索路径前面的模式名字 （如果搜索路径是空则返回 NULL）。 如果创建表或者其它命名对象时没有声明目标模式，那么它将是用于这些对象的模式。 current_schemas(boolean) 返回一个在搜索路径中出现的所有模式的名字的数组。 布尔选项决定象 pg_catalog 这样的隐含的包含的系统模式是否包含在返回地搜索路径中。

    注意: 搜索路径可以通过运行时设置更改。所用的命令是：

    SET search_path TO schema [, schema, ...]

inet_client_addr 返回当前客户端的 IP 地址，inet_client_port 返回它的端口号。 inet_server_addr 返回服务器接收当前连接用的 IP 地址，而 inet_server_port 返回对应的端口号。 如果连接是通过 Unix 域套接字进行的，那么所有这些函数都返回 NULL。

version() 返回一个描述 PostgreSQL服务器的版本的字串。

Table 9-40 列出那些 允许用户在程序里查询对象访问权限的函数。 参阅 Section 5.7 获取更多有关权限的信息。

Table 9-40. 访问权限查询函数
名字        返回类型        描述
has_table_privilege(user, table, privilege)         boolean        用户是否有访问表的权限
has_table_privilege(table, privilege)         boolean        当前用户是否有访问表的权限
has_database_privilege(user, database, privilege)         boolean        用户是否有访问数据库的权限
has_database_privilege(database, privilege)         boolean        当前用户是否有访问数据库的权限
has_function_privilege(user, function, privilege)         boolean        用户是否有访问函数的权限
has_function_privilege(function, privilege)         boolean        当前用户是否有访问函数的权限
has_language_privilege(user, language, privilege)         boolean        用户是否有访问语言的权限
has_language_privilege(language, privilege)         boolean        当前用户是否有访问语言的权限
has_schema_privilege(user, schema, privilege)         boolean        用户是否有访问模式的权限
has_schema_privilege(schema, privilege)         boolean        当前用户是否有访问模式的权限
has_tablespace_privilege(user, tablespace, privilege)         boolean        用户是否有访问表空间的权限
has_tablespace_privilege(tablespace, privilege)         boolean        当前用户是否有访问表空间的权限

has_table_privilege 判断一个用户是否可以用某种特定的方式访问一个表。 该用户可以通过名字或者 ID （pg_user.usesysid） 来声明，如果省略该参数，则使用 current_user。 该表可以通过名字或者 OID 声明。（因此，实际上有六种 has_table_privilege 的变体，我们可以通过它们的参数数目和类型来区分它们。） 如果用名字声明，那么在必要时该名字可以是模式修饰的。 所希望的权限类型是用一个文本字串来声明的，它们必须得出下面的几个数值之一： SELECT，INSERT，UPDATE， DELETE，RULE，REFERENCES，或 TRIGGER。（当然，字串的大小写没什么关系。） 一个例子∶

SELECT has_table_privilege('myschema.mytable', 'select');

has_database_privilege 检查一个用户是否能以特定方式访问一个数据库。 它的可能参数类似 has_table_privilege。 需要的权限类型必须得出 CREATE，TEMPORARY， 或者 TEMP （它等效于 TEMPORARY）。

has_function_privilege 检查一个用户是否能以 特定方式访问一个函数。其可能参数类似 has_table_privilege。 我们声明一个函数用的是文本字串，儿不是 OID，允许的输入和 regprocedure 数据类型一样（参阅 Section 8.12）。当前可得的访问权限类型必须得出 EXECUTE。 一个例子：

SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');

has_language_privilege 检查一个用户是否可以以某种特定的方式访问一个过程语言。 其可能参数类似 has_table_privilege。 可用的访问权限类型必须得出 USAGE。

has_tablespace_privilege 检查一个用户是否可以以特定方式访问一个表空间。 其可能参数类似 has_table_privilege。用户需要的访问权限必须计算得出 CREATE。

has_schema_privilege 检查一个用户是否 可以以某种特定的方式访问一个模式。其可能的参数类似 has_table_privilege。 当前可得的访问权限类型必须得出 CREATE 或者 USAGE。

要评估一个用户是否在权限上持有赋权选项，给权限键字附加 WITH GRANT OPTION；比如 'UPDATE WITH GRANT OPTION'。

Table 9-41 显示了那些判断一个对象是否在当前模式搜索路径中可见的函数。 如果一个表所在的模式在搜索路径中，并且没有同名的表出现在搜索路径的更早的地方，那么就说这个表视可见的。 它等效于表可以不带明确模式修饰进行引用。比如，要列出所有可见表的的名字：

SELECT relname from pg_class WHERE pg_table_is_visible(oid);

Table 9-41. 模式可视性查询函数
名字        返回类型        描述
pg_table_is_visible(table_oid)         boolean        该表是否在搜索路径中可见
pg_type_is_visible(type_oid)         boolean        该类型是否在搜索路径中可见
pg_function_is_visible(function_oid)         boolean        该函数是否在搜索路径中可见
pg_operator_is_visible(operator_oid)         boolean        该操作符是否在搜索路径中可见
pg_opclass_is_visible(opclass_oid)         boolean        该操作符表是否在搜索路径中可见
pg_conversion_is_visible(conversion_oid)         boolean        转换是否在搜索路径中可见

pg_table_is_visible 执行表检查（或者视图， 或者视任何其它类型的 pg_class 记录） pg_type_is_visible， pg_function_is_visible， pg_operator_is_visible， pg_opclass_is_visible，和 pg_conversion_is_visible 分别为类型（和域），函数，操作符， ，操作符表和转换执行同样的检查。对于函数和操作符，如果在搜索路径中没有同名 并且同样参数数据类型的对象出现在路径中靠前的位置，那么 该对象就是可见的。对于操作符表，则同时考虑名字和相关的索引访问方法。

所有这些函数都需要对象 OID 标识要检查的对象。如果你想通过名字测试一个对象， 那么使用 OID 别名类型 (regclass，regtype，regprocedure， 或者 regoperator) 比较方便，比如

SELECT pg_type_is_visible('myschema.widget'::regtype);

请注意用这种方法测试一个未经修饰的名字没什么意义 — 如果一个名字可以被识别， 那它首先得是可见的。

Table 9-42. 系统表信息函数
名字        返回类型        描述
pg_get_viewdef(view_name)        text        获取视图的CREATE VIEW命令(废弃了)
pg_get_viewdef(view_name, pretty_bool)        text        获取视图的 CREATE VIEW (废弃了)
pg_get_viewdef(view_oid)        text        为视图获取CREATE VIEW命令
pg_get_viewdef(view_oid, pretty_bool)        text        为视图获取CREATE VIEW命令
pg_get_ruledef(rule_oid)        text        为规则获取CREATE RULE命令
pg_get_ruledef(rule_oid, pretty_bool)        text        为规则获取CREATE RULE命令
pg_get_indexdef(index_oid)        text        为索引获取CREATE INDEX命令
pg_get_indexdef(index_oid, column_no, pretty_bool)        text        为索引获取 CREATE INDEX 命令， 如果 column_no 不为零，则是只获取一个索引字段的定义
pg_get_triggerdef(trigger_oid)        text        为触发器获取 CREATE [ CONSTRAINT ] TRIGGER
pg_get_constraintdef(constraint_oid)        text        获取一个约束的定义
pg_get_constraintdef(constraint_oid, pretty_bool)        text        获取一个约束的定义
pg_get_expr(expr_text, relation_oid)        text        反编译一个表达式的内部形式，假设其中的任何 Vars 都引用第二个参数指出的关系
pg_get_expr(expr_text, relation_oid, pretty_bool)        text        反编译一个表达式的内部形式，假设其中的任何 Vars 都引用第二个参数指出的关系
pg_get_userbyid(userid)        name        获取给出的 ID 的用户名
pg_get_serial_sequence(table_name, column_name)        text        获取一个 serial 或者 bigserial 字段使用的序列名字
pg_tablespace_databases(tablespace_oid)        setof oid        获取在指定表空间（OID表示）中拥有对象的一套数据库的 OID 的集合

pg_get_viewdef()，pg_get_ruledef()， pg_get_indexdef()，pg_get_triggerdef，和pg_get_constraintdef() 分别从一个视图，规则，索引，触发器或者约束上重新构造创建它们的命令。 （请注意这里是一个反编译的重新构造，而不是该命令的原文。） pg_get_expr 反编译一个独立表达式的内部形式， 比如说一个字段的缺省值。在检查系统表的内容的时候很有用。 这些函数大多数都有两个变种，其中一个可以选择对结果的"漂亮的打印"。 漂亮打印的格式更容易读，但是缺省的格式更有可能被将来的 PostgreSQL 版本用同样的方法解释；如果是用于转储，那么尽可能避免使用漂亮打印。 给漂亮打印参数传递 false 生成的结果和那个没有这个参数的变种生成的结果完全一样。

pg_get_userbyid 抽取给出的一个用户 ID 号对应的用户名。 pg_get_serial_sequence 抓取与 serial 或者 bigserial 字段相关联的序列名字。 这个名字经过了合适的格式化，可以传递给序列函数（参阅 Section 9.12）。 如果字段没有附着序列，那么返回 NULL。

pg_tablespace_databases 允许我们检查一个表空间的使用状况。 它讲返回一套数据库的 OID 集合，这些数据库都是在该表空间中保存有对象的数据库。 如果这个函数返回数据行，那么它就是非空的，因此不能删除。要显示填充了该表空间的特定对象， 你需要把 pg_tablespace_databases 标出的数据库标识与 pg_class 表连接进行查询。

在 Table 9-43 显示的函数 将原来用 COMMENT 命令存储的评注抽取出来。 如果没有找到匹配声明的参数的评注，则返回 NULL。

Table 9-43. 注释信息函数
名字        返回类型        描述
obj_description(object_oid, catalog_name)        text        获取一个数据库对象的评注
obj_description(object_oid)        text        获取一个数据库对象的评注(废弃)
col_description(table_oid, column_number)        text        获取一个表字段的评注

两个参数形式的 obj_description 返回一个数据库对象的评注， 该对象是通过其 OID 和其包含系统表的名字声明的。 比如，obj_description(123456,'pg_class') 将返回 OID 为 12345 的表的评注。一个参数的 obj_description 只要求对象 OID。它现在已经废弃了，因为我们不再保证 OID 在不同的系统表之间是唯一的；因此可能会返回错误的评注。

col_description 返回一个表的字段的评注， 它是通过它的表和字段号的 OID 来声明的。 我们不能将 obj_description 用于表字段， 因为字段没有自己的 OID

9.20. 系统管理函数

Table 9-44 显示了那些可以用于查询以及修改运行时配置参数的函数。

Table 9-44. 配置设置函数
名字        返回类型        描述
current_setting(setting_name)         text        当前设置的值
set_config(setting_name, new_value, is_local)         text        设置参数并返回新值

current_setting 用于以查询形式获取 setting_name 设置的当前数值。它和SQL命令 SHOW 是等效的。 比如：

SELECT current_setting('datestyle');

current_setting
-----------------
ISO, MDY
(1 row)

set_config 将参数 setting_name 设置为 new_value。如果 is_local 设置为 true，那么新数值将只应用于当前事务。 如果你希望新的数值应用于当前会话，那么应该使用 false。 它等效于 SQL 命令 SET。比如：

SELECT set_config('log_statement_stats','off', false);

set_config
------------
off
(1 row)

在 Table 9-45 里显示的函数向其它服务器进程发送控制信号。 这些函数的使用限制为超级用户。

Table 9-45. 后端信号函数
名字        返回类型        描述
pg_cancel_backend(pid)         int        取消一个后端的当前查询

如果成功，这些函数返回 1，如果没有成功则返回 0。 活跃的后端的进程 ID （pid）可以从 pg_stat_activity 视图的 procpid 字段获取，或者通过用 ps 命令列示服务器上的 postgres 进程看到。

在 Table 9-46 里显示的函数帮助我们进行在线备份。 这些函数仅限超级用户使用。

Table 9-46. 备份控制函数
名字        返回类型        描述
pg_start_backup(label_text)         text        设置执行在线备份
pg_stop_backup()         text        完成执行在线备份

pg_start_backup 接受一个参数，这个参数可以是任意用户为备份定义的标签。 （通常这是备份转储文件存放所在的名字。）这个函数向数据库集群的数据目录写入一个备份标签文件， 然后以文本方式返回备份的起始 WAL 偏移。（用户不需要注意这个结果值，提供他只为了万一需要的场合。）

pg_stop_backup 删除 pg_start_backup 创建的标签文件， 并且在 WAL 归档区里创建一个备份历史文件。这个历史文件包含给予 pg_start_backup 的标签， 备份的起始与终止 WAL 偏移量，以及备份的起始和终止时间。返回值是备份的终止 WAL 偏移 （同样也可能没有什么用）。

有关正确使用这些函数的细节，参阅 Section 22.3。

Chapter 10. 类型转换

Table of Contents
10.1. 概述
10.2. 操作符
10.3. 函数
10.4. 值存储
10.5. UNION，CASE 和 ARRAY构造

SQL 语句可能（有意无意地）要求在同一表达式里混合不同的数据类型。 PostgreSQL 在计算混合类型表达式方面有许多扩展性很强的功能。

在大多数情况下，用户不需要明白类型转换机制的细节。但是，由 PostgreSQL 所进行的隐含的类型转换会对查询的结果产生影响，必要时这些影响又可以用明确的类型转换进行剪裁利用。

本章介绍 PostgreSQL类型转换的传统和机制。关于特定的类型和函数及操作符的进一步信息，请参考Chapter 8 和 Chapter 9里的相关章节。
10.1. 概述

SQL 是强类型语言。也就是说，每一数据都与一个决定其行为和可行用法的数据类型相联。 PostgreSQL 有一个可扩展的数据类型系统，该系统比其他 SQL 实现实现更具通用性和灵活性。因而，PostgreSQL中大多数类型转换的特性是由通用规则来管理的，而不是由专门搜索方法来分析，以此令混合类型表达式有实际意义，即便是用户定义的类型也如此。

PostgreSQL 扫描/分析器只将词法元素分解成五个基本种类：整数（integers），浮点数（floating-point numbers）， 字符串（strings），名字（names）和关键字（keywords）。大多数扩展的类型首先表征为字符串（strings）。 SQL 语言的定义允许将类型名声明为字符串，这个机制被 PostgreSQL 用于令分析器沿着正确的方向运行。例如，下面查询

SELECT text 'Origin' AS "label", point '(0,0)' AS "value";

label  | value
--------+-------
Origin | (0,0)
(1 row)

有两个文本常量，类型分别为 text 和 point。如果没有为字串文本声明类型，（该文本）先被初始化成一个拥有存储空间的 unknown（未知）类型，该类型将在后面描述的晚期阶段分析。

在 PostgreSQL 分析器里，有四种基本的 SQL 元素需要独立的类型转换规则：

函数调用

    多数 PostgreSQL 类型系统是建筑在一套丰富的函数上的。函数调用可以有一个或多个参数。因为 PostgreSQL 允许函数重载，所以函数名自身并不唯一地标识将要调用的函数 — 分析器必须以函数提供的参数的类型为基础选择正确的函数。
操作符

    PostgreSQL 也允许使用左目或右目操作符（单目操作符，一个参数），允许表达式里使用双目操作符（两个参数）。
值存储

    SQL INSERT和UPDATE 语句将表达式结果放入表中。语句中的表达式类型必须和的目标列的类型一致或者是（可能需要）转换成一致的。
UNION，CASE 和 ARRAY 构造

    因为联合SELECT语句中的所有查询结果必须在一列里显示出来，所以每个SELECT 子句中的元素类型必须相互匹配并转换成一套统一类型。类似，一个CASE构造的结果表达式必须转换成统一的类型，这样CASE表达式自身作为整体有一种已知输出类型。同样的要求也存在于 ARRAY 构造中。

系统表存储有关哪种数据类型之间的转换（叫 casts ）是合法的，以及如何执行这些转换的信息。额外的转换可以由用户通过 CREATE CAST 命令增加。（通常和定义一种新的数据类型一起完成。内置类型的类型转换集已经经过仔细的雕琢了，因此最好不要去更改它们。）

分析器中还有一个搜索器用于更好地猜测 SQL 标准类型的确切特性。分析器里定义了几种类型范畴： boolean，numeric，string， bitstring，datetime，timespan， geometric，network，和用户定义（user-defined）。除用户定义类型外，每种类型都有一种首选类型用于解决类型定义歧义的问题。对于用户定义的类型，其自身就是自己的首选类型，所以那些含混不清的表达式（在分析结果中有多种可能的表达式）如果有多个内置类型的时候大多可以正确分析，但如果有多个用户定义类型可选，则会抛出错。

所有类型转换规则都是建立在下面几个基本原则上的：

    *

      隐含转换决不能有奇怪的或不可预见的输出。
    *

      用户定义类型，因为分析器对其没有预先的认识，在类型级别中应该级别较"高"。在混合类型的表达式里，内部类型总是应该转换成用户定义类型。（当然只是在必须转换的时候）。
    *

      用户定义类型是不相关的。目前，PostgreSQL 除了用于内部数据类型的硬代码搜索器和以现有函数为基础的隐含类型关系外，没有任何可用于处理类型间关系的信息。
    *

      如果一个查询不需要隐含的类型转换，分析器或执行器不应该进行更多的额外操作。这就是说，任何一个类型匹配，格式清晰的查询不应该在分析器里耗费更多的时间，也不应该向查询中引入任何不必要的隐含类型转换调用。

      另外，如果一个查询通常使用某个函数进行隐含类型转换，而用户定义了一个有正确参数的函数，解释器应该使用新函数取代原先旧函数的隐含操作。

10.2. 操作符

一次操作符调用的操作数类型是按照下面的过程解析的。 请注意这个过程受被调用操作符的优先级的间接影响。 参阅 Section 4.1.6 获取更多信息。

操作符类型解析

   1.

      从 pg_operator 系统表中选出要考虑的操作符。如果使用了一个不带修饰的操作符名（常见的状况），那么认为该操作符是那些在当前的搜索路径中名字和参数个数正确的函数（参阅 Section 5.8.3）。如果给出一个带修饰的操作符名，那么只考虑指明的模式中的操作符。
         1.

            如果搜索路径中找到了多个相同参数类型的操作符，那么只考虑最早出现在路径中的。但是不同参数类型的操作符将以相同的基础进行考虑，而不管它们在路径中的位置如何。
   2.

      检查一个操作符是否刚好接受输入参数类型。如果存在一个（在考虑的操作符中，可能只存在一个精确匹配的），则用之。
         1.

            如果一个双目操作符调用中的一个参数是 unknown，则在本次检查中假设其与另一个参数类型相同。其他涉及 unknown 的情况绝不会在这一步找到匹配。
   3.

      寻找最优匹配。
         1.

            抛弃那些输入类型不匹配并且也不能强制转换（使用隐含转换函数）成匹配的候选操作符。 unknown 文本在这种情况下被认为是可以转换成任何东西。如果只剩下一个候选项，用之；否则继续下一步。
         2.

            遍历所有候选操作符，保留那些输入类型有最多准确匹配的。（在这种情况下，域被看作和他们的基本类型相同。）如果没有完全准确匹配的操作符，保留所有候选。如果只有一个，用之，否则继续下一步。
         3.

            遍历所有候选操作符，保留那些需要类型转换时最多位置接受（输入数据类型的类型范畴的）首选类型的。如果没有接受首选选类型的操作符，则保留所有候选。如果只有一个，用之，否则继续下一步。
         4.

            如果任何输入参数是 unknown，检查剩下的候选操作符对应参数位置的类型范畴。如果任何候选操作符接受string类型，则在那些位置选string类型（这个假设认为字串是合适的，因为 unknown 类型文本确实象字串）。否则，如果所有剩下的候选操作符接受相同的类型，选择该类型；否则抛出一个错误，因为在没有更多线索的条件下不能导出正确的选择。现在抛弃不接受选定的类型表的候选操作符；然后，如果任意候选操作符在某个给定的参数位置接受一个优选类型，则抛弃那些在该参数位置接受非优选类型的候选操作符。
         5.

            如果只剩下一个操作符，用之。如果还有超过一个的候选操作符或是没有候选操作符，则产生一个错误。

下面是一些例子。

Example 10-1. 指数操作符类型解析

在分类里只有一个指数操作符，它以 double precision 作为参数。扫描器给下面查询表达式的两个参数赋予 integer 的初始类型：

SELECT 2 ^ 3 AS "exp";

exp
-----
   8
(1 row)

分析器对两个参数都做类型转换，查询等效于：

SELECT CAST(2 AS double precision) ^ CAST(3 AS double precision) AS "exp";

Example 10-2. 字串连接操作符类型分析

一种类字符串的语法既可以用于字符串也可以用于复杂的扩展类型。包含不明类型的字串使用可能的候选操作符匹配。

有一个未声明的参数的例子：

SELECT text 'abc' || 'def' AS "text and unknown";

text and unknown
------------------
abcdef
(1 row)

本例中分析器寻找一个两个参数都是 text 的操作符。因为有一个这样的操作符，它认为另一个参数的类型是 text。

这里是联接未声明类型：

SELECT 'abc' || 'def' AS "unspecified";

unspecified
-------------
abcdef
(1 row)

本例中对类型任何初始提示，因为查询中没有声明任何类型。因此，分析器查找所有参数可以同时接受字符串类和位串类的候选操作符。因为在可用时字串类是首选，所以选择该表，于是字串的优选类型， text，作为解析未知类型文本的声明类型。

Example 10-3. 绝对值和取反操作符类型分析

PostgreSQL 操作符表里面有几条记录用于前缀操作符 @，所有这些都是为各种数值类型实现绝对值操作的。其中有一条用于类型 float8，它是数值表中的优选类型。因此，在面对非数值输入的时候，PostgreSQL 会使用该类型∶

select @ text '-4.5' as "abs";
abs
-----
4.5
(1 row)

在这里系统在应用选定的操作符之前执行类一次text到float8的转换。我们可以验证它是float8而不是其它什么类型：

select @ text '-4.5e500' as "abs";

ERROR:  Input '-4.5e500' is out of range for float8

另一方面，前缀操作符 ~ （按位取反） 只为整数数据类型定义，而不是为float8定义的。因此，如果我们用 ~ 做类似实验，就有：

SELECT ~ '20' AS "negation";

ERROR:  operator is not unique: ~ "unknown"
HINT:  Could not choose a best candidate operator. You may need to add explicit
type casts.

这是因为系统无法决定好几个可能的 ~ 操作符中应该用哪个。我们可以用明确地类型转换来帮它：

SELECT ~ CAST('20' AS int8) AS "negation";

negation
----------
      -21
(1 row)

10.3. 函数

函数调用的参数类型是用下面的步骤来解析的。

函数参数类型解析

   1.

      从 pg_proc 系统表中选择要考虑的函数。如果使用了未经修饰的名字，那么则认为该函数是那些在当前搜索路径中可见的，拥有正确名字和参数个数的函数（参阅 Section 5.8.3）。如果给出了一个带修饰的函数名，那么只考虑在声明的模式中的函数。
         1.

            如果在搜索路径中找到多个函数有相同的参数类型，那么只考虑出现在搜索路径最前面的那个。但是有不同参数类型的函数都以相同的态度看待，而不管它们在搜索路径中的位置。
   2.

      找一个刚好接受完全一致的输入参数类型的函数。如果存在这么一个（在考虑的函数集中可能只存在一个），用之。（涉及到 unknown 类型的情况下在本步骤不会找到任何匹配。）
   3.

      如果没有找到准确的匹配，则看看函数调用是否明显需要一个简单的类型转换。如果函数调用只有一个参数并且函数名与某些数据类型的（内部）名称相同，那么就会出现这种情况。另外，该函数的参数必须是一个未知类型的文本或者与命名数据类型二进制兼容。如果符合这些条件，则该函数参数在不做任何实际函数调用的情况下转换成这个命名的数据类型。
   4.

      查找最优的匹配
         1.

            抛弃那些输入类型不匹配并且也不能强制转换（使用隐含转换）成匹配的候选操作符。 unknown 文本在这种情况下被认为是可以转换成任何东西。如果只剩下一个候选项，用之；否则继续下一步。
         2.

            遍历所有候选函数，保留那些在输入类型上有最多准确匹配的。（在这种场合下域被认为和其基本类型一样。）如果没有一个有准确匹配，则保留全部。如果只剩下一个，用之；否则继续下一步。
         3.

            遍历所有候选函数，保留那些在最多需要类型转换的参数位置上接受优选类型的函数。如果没有哪个候选函数接受优选类型，则保留全部。如果只剩下一个，用之；否则继续下一步。
         4.

            如果任何输入参数是unknown，检查剩下的候选函数对应参数位置的类型表。如果任何候选函数接受string类型，则在那些位置选 string 类型（这个假设认为 string 是合适的，因为 unknown 类型文本确实象 string）。否则，如果所有剩下的候选函数接受相同的类型，选择该类型；否则抛出一个错误，因为在没有更多线索的条件下不能导出正确的选择。现在抛弃不接受选定的类型表的候选函数；然后，如果任意候选函数在某个给定的参数位置接受一个优选类型，则抛弃那些在该参数位置接受非优选类型的候选函数。
         5.

            如果只剩下一个函数，用之。如果还有超过一个的候选函数或是没有候选函数，则产生一个错误。

请注意，"最佳匹配" 规则对操作符和对函数的类型分析都是一样的。下面是一些例子。

Example 10-4. 圆整函数参数类型解析

只有一个round函数有两个参数。（第一个参数是numeric，第二个是 integer。）所以下面的查询自动把第一个类型为 integer 的参数转换成 numeric：

SELECT round(4, 4);

round
--------
4.0000
(1 row)

实际上它被分析器转换成：

SELECT round(CAST (4 AS numeric), 4);

因为带小数点的数值常量初始时被赋予 numeric 类型，因此下面的查询将不需要类型转换，并且可能会略微高效一些：

SELECT round(4.0, 4);

Example 10-5. 子字串函数类型解析

有好几个 substr 函数，其中一个接受类型 text 和 integer。如果用一个未声明类型的字串常量调用它，系统将选择接受优选 string类型（也就是类型 text）的候选函数。

SELECT substr('1234', 3);

substr
--------
     34
(1 row)

如果该字符串声明为类型 varchar，就像大多数从表中取来的数据一样，分析器将试着将其转换成 text：

SELECT substr(varchar '1234', 3);

substr
--------
     34
(1 row)

被分析器转换后实际上变成：

SELECT substr(CAST(varchar '1234' AS text), 3);

substr
--------
     34
(1 row)

    Note: 分析器从pg_cast中了解到 text 和 varchar 是二进制兼容的，意思是说其中一个可以传递给一个接受另外一个的函数而不需要做任何物理转换。因此，在这种情况下，实际上没有做任何明确的类型转换。

而且，如果以 integer为参数调用函数，分析器将试图将其转换成 text：

SELECT substr(1234, 3);

substr
--------
     34
(1 row)

实际上是这样执行的

SELECT substr(CAST(1234 AS text), 3);

substr
--------
     34
(1 row)

这种自动转换能够成功是因为存在一个从 integer 到 text 的隐含转换可以调用。