PostgreSQL函数和运算符（三）

gznz12345

7.14 序列相关函数


本节描述用来处理序列对象的函数。可以使用命令CREATE SEQUENCE来创建序列对象。序列对象通常用于为表中的数据行产生一个唯一的标识符。表7-34列出了用来操作序列对象的函数，多个用户可以同时使用这些函数操作同一个序列对象。
表7-34. 序列函数

函数名	返回值类型	描述
currval(regclass)	bigint	返回当前会话中指定的序列的最近一次nextval调用得到的值
lastval()	bigint	返回当前会话中最近一次nextval调用得到的值
nextval(regclass)	bigint	让序列前进到下一个值并返回这个值
setval(regclass, bigint)	bigint	设定序列对象的当前值
setval(regclass, bigint, boolean)	bigint	设定序列对象的当前值和is_called 标志位

上面的函数的某些参数的类型是regclass，它代表序列对象的 OID，可以直接用序列名作为参数(序列名要用两个单引号括起来)，而不必去查找序列的 OID，系统会自动将序列名转换成它的OID。序列名一般会被系统自动转换成小写的形式。但如果序列名用两个双引号括起来，系统就不会将序列名转化成小写的形式。例如：
nextval('foo') 引用序列foo
nextval('FOO') 引用序列foo
nextval('"Foo"') 引用序列Foo

序列名前面也可以用模式来修饰。例如：
nextval('myschema.foo') 引用序列myschema.foo
nextval('"myschema".foo') 引用序列myschema.foo
nextval('foo') 在会话的模式搜索路径中查找序列foo
序列函数的参数也可以是表达式和常量。
下面详细介绍每个序列函数:
（1）nextval
让序列前进到下一个值并返回这个值。系统会自动保证这个操作的原子性，即使有多个会话同时对一个序列执行这个函数，每个会话得到的序列值都是不一样的。
（2）currval
返回当前会话中指定的序列的最近一次nextval调用得到的值。如果当前会话从未对指定的序列调用函数nextval，系统将会报错。
（3）lastval
返回当前会话中最近一次nextval调用得到的值。这个函数与currval很相似，但它并没有任何参数，它返回当前会话最近一次nextval调用得到的值，这个nextval调用可能操作数据库中的任何一个序列对象。如果当前会话没有对任何序列对象调用过函数nextval，系统将会报错。
（4）setval
这个函数有两种调用形式：setval(sequence regclass, new_value bigint)和setval(sequence regclass, new_value bigint,is_called boolean)。
第一种调用形式只有两个参数，序列对象的计数器将被设置成指定的值new_value，序列的is_called标志位也将被设为true。下次对序列调用nextval将得到new_value的下一个值。在这个函数执行结束以后，对序列调用currval将得到new_value。
第二种调用形式有三个参数，如果第三个参数的值是true，它的效果与第一种调用形式是相同的。如果第三个参数的值是false，在这个函数执行结束以后，下次对这个序列调用nextval将得到new_value。另外，在这个函数执行结束以后，对序列调用currval将得到它的原来的值，而不是new_value。
这个函数返回的结果是它的第二个参数new_value的值。

参见命令CREATE SEQUENCE得到序列对象的详细信息。
注意：nextval操作从来不会被回滚，即使调用nextval的事务被回滚，它发出的nextval操作得到的序列值以后不会其它事务重新使用。setval操作也不会被回滚。




7.15 条件表达式


本节描述PostgreSQL中的条件表达式。如果应用程序的要求超过了条件表达式的能力，可以写一个存储过程来达到目的。


7.15.1 CASE


SQL CASE表达式是一个一般条件表达式，类似于其它语言中的if/else语句：
CASE WHEN condition THEN result
[WHEN ...]
[ELSE result]
END
condition是一个返回布尔结果的表达式，如果它的值为true，则CASE表达式的值就是跟在condition后面的result。如果它的值是false,则继续搜索下面的WHEN子句。如果没有一个WHEN子句中的condition为真，则CASE表达式的值就是ELSE子句中的result。如果省略了ELSE子句并且没有一个WHEN子句中的condition为真，CASE表达式的值就是null。例如：
SELECT * FROM test;

a
---
1
2
3


SELECT a,
CASE WHEN a=1 THEN 'one'
WHEN a=2 THEN 'two'
ELSE 'other'
END
FROM test;

a | case
---+-------
1 | one
2 | two
3 | other
所有的result表达式的数据类型必须相同或者属于同一个数据类型类别而且能够被转换成同一个数据类型。
下面是一个简单一些的CASE表达式的语法，它类似于c语言中的switch语句：
CASE expression
WHEN value THEN result
[WHEN ...]
[ELSE result]
END
expression被求值以后，将它的结果与每个WHEN子句中的value比较，如果相等，就返回WHEN子句中的result作为CASE表达式的值。如果没找到匹配的WHEN子句，就返回ELSE子句中的result作为CASE表达式的值（没有ELSE子句则返回null）。
上面的例子可以用新的语法写成下面的样子：

SELECT a,
CASE a WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'other'
END
FROM test;

a | case
---+-------
1 | one
2 | two
3 | other
CASE表达式不会对任何不影响最终结果的表达式求值。例如：
SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;
在这个例子中，如果x等于0，则以false作为CASE表达式的值，不会再对表达式y/x求值，所以数据库不会发出“除数为零”这样的错误。


7.15.2 COALESCE


COALESCE(value [, ...])
函数COALESCE返回它的参数中第一个不是空值的参数。如果所有的参数都为空值，它将返回一个空值。通常，在显示查询结果时，可以用一个默认值来表示空值，例如：
SELECT COALESCE(description, short_description, '(none)') ...
像CASE表达式一样，COALESCE不会对那些不影响最终结果的参数求值，也就是说，第一个不是空值的参数的右边的所有的参数都不会被求值。它的作用类似与其它数据库中的NVL 和IFNULL。


7.15.3 NULLIF


NULLIF(value1, value2)
如果value1 和value2相等，NULLIF返回一个空值，否则它返回value1。例如：
SELECT NULLIF(value, '(none)') ...


7.15.4 GREATEST and LEAST


GREATEST(value [, ...])
LEAST(value [, ...])
函数GREATEST 和LEAST选择参数列表中的最大值和最小值。所有的表达式的值的数据类型必须相同或者属于同一个数据类型类别而且能够被转换成同一个数据类型，这个类型也是最终的结果的类型。参数列表中的空值将被忽略。如果所有的参数都是空值，则这两个函数将返回空值。
GREATEST 和LEAST并不是SQL标准中的函数，它是PostgreSQL的扩展特性。



7.16 数组函数和运算符


表7-35列出了数组函数和运算符。
表7-35. 数组运算符

运算符	描述	例子	结果
=	相等	ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3]	t
<>	不相等	ARRAY[1,2,3] <> ARRAY[1,2,4]	t
<	小于	ARRAY[1,2,3] < ARRAY[1,2,4]	t
>	大于	ARRAY[1,4,3] > ARRAY[1,2,4]	t
<=	小于或等于	ARRAY[1,2,3] <= ARRAY[1,2,3]	t
>=	大于或等于	ARRAY[1,4,3] >= ARRAY[1,4,3]	t
@>	包含	ARRAY[1,4,3] @> ARRAY[3,1]	t
<@	被包含	ARRAY[2,7] <@ ARRAY[1,7,4,2,6]	t
&&	重叠(存在共同的元素)	ARRAY[1,4,3] && ARRAY[2,1]	t
||	数组和数组连接	ARRAY[1,2,3] || ARRAY[4,5,6]	{1,2,3,4,5,6}
||	数组和数组连接	ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]]	{{1,2,3},{4,5,6},{7,8,9}}
||	元素和数组连接	3 || ARRAY[4,5,6]	{3,4,5,6}
||	数组和元素连接	ARRAY[4,5,6] || 7	{4,5,6,7}

比较两个数组时，比较的规则是一个一个元素地比。比较两个多维数组时，规则是行序优先。
参见第6.13节得到数组运算符的详细信息。
表7-36列出了数组函数。表7-37列出了数组函数的实例。参考6.13节得到这些函数的详细用法和例子。
表7-36. 数组函数

函数名	返回值类型	描述
array_append(anyarray, anyelement)	anyarray	将一个元素附加到数组的末尾
array_cat(anyarray, anyarray)	anyarray	连接两个数组
array_dims(anyarray)	text	返回数组的维数和每个维的大小
array_lower(anyarray, int)	int	返回数组指定的维的下标的下界
array_prepend(anyelement, anyarray)	anyarray	将一个元素附加到数组的开始
array_to_string(anyarray, text)	text	使用指定的分隔符将数组的元素连接起来，形成一个字符串
array_upper(anyarray, int)	int	返回数组指定的维的下标的上界
string_to_array(text, text)	text[]	用指定的分隔符将字符串分成多个元素，构成一个一维数组

表7-37. 数组函数实例

例子	结果
array_append(ARRAY[1,2], 3)	{1,2,3}
array_cat(ARRAY[1,2,3], ARRAY[4,5])	{1,2,3,4,5}
array_dims(ARRAY[[1,2,3], [4,5,6]])	[1:2][1:3]
array_lower('[0:2]={1,2,3}'::int[], 1)	0
array_prepend(1, ARRAY[2,3])	{1,2,3}
array_to_string(ARRAY[1, 2, 3], '~^~')	1~^~2~^~3
array_upper(ARRAY[1,2,3,4], 1)	4
string_to_array('xx~^~yy~^~zz', '~^~')	{xx,yy,zz}

窗体顶端

7.17 聚集函数


聚集函数以一个集合的所有元素为参数，计算出一个单一的结果。表7-38和表7-39列出了数据库的内置的聚集函数。
表7-38. 通用聚集函数

函数名	参数类型	返回值类型	描述
avg(expression)	smallint, int, bigint, real, double precision, numeric, or interval	如果参数是整数类型，返回numeric。如果参数是浮点类型，返回double precision。其它的情况下，与参数的类型相同	所有输入值的算数平均值
bit_and(expression)	smallint, int, bigint, or bit	与参数的类型相同	所有的不是空值的输入值按位与的结果。如果所有的输入值都是空值，则返回空值
bit_or(expression)	smallint, int, bigint, or bit	与参数的类型相同	所有的不是空值的输入值按位或的结果。如果所有的输入值都是空值，则返回空值
bool_and(expression)	bool	bool	如果所有的输入值为true，则返回true。否则，返回false
bool_or(expression)	bool	bool	如果有一个输入值为true，则返回true。否则，返回false
count(*)		bigint	输入的数据行的个数
count(expression)	any	bigint	在expression上求值的结果不是空值的数据行的个数
every(expression)	bool	bool	等同于bool_and
max(expression)	any array, numeric, string, or date/time type	与参数的类型相同	所有的输入值中的最大值
min(expression)	any array, numeric, string, or date/time type	与参数的类型相同	所有的输入值中的最小值
sum(expression)	smallint, int, bigint, real, double precision, numeric, or interval	如果参数是int或smallint，返回bigint。如果参数是bigint，返回numeric。如果参数是浮点类型，返回double precision。其它的情况下，与参数的类型相同	所有的输入值的和

如果输入的集合是空集，函数count将返回0，其它的所有的函数都将返回一个空值。
注意：在PostgreSQL中，下面的查询将使用一个全表扫描操作，所以执行效率很低，如果表中的数据量很大，将花费很长的时间才能得到结果：
SELECT count(*) FROM sometable;
表7-39列出了典型的统计分析用的聚集函数。函数描述中的N表示在所有的输入的表达式上的值不是空值的数据行的个数。如果计算是没有意义的，所有的函数都会返回一个空值，例如，N为0的时候，所有的函数都会返回空值。
表 7-39. 统计类型的聚集函数

函数名	参数类型	返回值类型	描述
corr(Y, X)	double precision	double precision	相关系数
covar_pop(Y, X)	double precision	double precision	总体协方差
covar_samp(Y, X)	double precision	double precision	样本协方差
regr_avgx(Y, X)	double precision	double precision	独立变量的均值(sum(X)/N)
regr_avgy(Y, X)	double precision	double precision	独立变量的均值(sum(Y)/N)
regr_count(Y, X)	double precision	bigint	在两个表达式上的值都不是空值的数据行的个数
regr_intercept(Y, X)	double precision	double precision	基于(X, Y)用最方适配法确定的线性方程的y截距
regr_r2(Y, X)	double precision	double precision	相关系数的平方
regr_slope(Y, X)	double precision	double precision	基于(X, Y)用最方适配法确定的线性方程的斜率
regr_sxx(Y, X)	double precision	double precision	sum(X^2) - sum(X)^2/N
regr_sxy(Y, X)	double precision	double precision	sum(X*Y) - sum(X) * sum(Y)/N
regr_syy(Y, X)	double precision	double precision	sum(Y^2) - sum(Y)^2/N
stddev(expression)	smallint, int, bigint, real, double precision, or numeric	如果参数是浮点类型，返回double precision。其它的情况下，返回numeric	等同于函数stddev_samp
stddev_pop(expression)	smallint, int, bigint, real, double precision, or numeric	如果参数是浮点类型，返回double precision。其它的情况下，返回numeric	总体标准差
stddev_samp(expression)	smallint, int, bigint, real, double precision, or numeric	如果参数是浮点类型，返回double precision。其它的情况下，返回numeric	样本标准差
variance(expression)	smallint, int, bigint, real, double precision, or numeric	如果参数是浮点类型，返回double precision。其它的情况下，返回numeric	等同于函数var_samp
var_pop(expression)	smallint, int, bigint, real, double precision, or numeric	如果参数是浮点类型，返回double precision。其它的情况下，返回numeric	总体方差
var_samp(expression)	smallint, int, bigint, real, double precision, or numeric	如果参数是浮点类型，返回double precision。其它的情况下，返回numeric	样本方差

7.18 子查询表达式


本节介绍SQL子查询表达式。所有的表达式都返回一个布尔值作为结果。


7.18.1 EXISTS


EXISTS (subquery)
EXISTS的参数是一个任意的SELECT语句，也叫子查询。如果子查询至少返回一个数据行，则EXISTS的结果是真。如果子查询没有返回任何数据行，则EXISTS的结果是假。
子查询也可以引用包含它的父查询中的变量，这些变量在每次对子查询进行求值时，作为常量来看待。
一旦子查询在执行过程中返回了一个数据行，它就会停止执行，不会一直执行到返回所有的数据行，因为这样做是没有必要的。最好子查询不要有任何副作用（如调用一个序列函数），因为无法预测副作用是否会发生。
因为EXISTS的结果取决与子查询是否会返回任何数据行，而不是数据行内容，子查询的输出列表通常是没有任何作用的。可以采用下面的简单的语法来实现EXISTS的功能（如果子查询使用了INTERSECT,则不能使用这种语法）：
EXISTS(SELECT 1 WHERE ...)
下面是一个例子：
SELECT col1 FROM tab1
WHERE EXISTS(SELECT 1 FROM tab2 WHERE col2 = tab1.col2);


7.18.2 IN


expression IN (subquery)
IN的右边是一个用括号括起来的子查询，这个子查询必须也只能返回一个列。IN左边的表达式的值与子查询的的结果中的每个数据行相比较，如果子查询的的结果中至少存在一个数据行与表达式的值相等，则IN的结果是真，否则，IN的结果是假。另外，子查询没有返回任何数据行的情况下，IN的结果也是假。
如果IN的左边的表达式的值是空值，IN的结果是空值。如果子查询的结果中没有一个数据行与表达式的值相等，同时查询的结果中有一个数据行是空值，IN的结果是空值，而不是假。
同EXISTS一样，IN右边的子查询也许执行到中间就能确定IN的结果，这时子查询就会停止执行。
IN还有另外一种用法：

row_constructor IN (subquery)
在这个用法中，IN的左边是一个数据行构造器（参见第2.12节）。右边是一个用括号括起来的子查询，这个子查询返回的列的个数必须与数据行构造器中的列的个数完全相同。IN左边的表达式的值与子查询的的结果中的每个数据行相比较，如果子查询的的结果中至少存在一个数据行与表达式的值相等，则IN的结果是真，否则，IN的结果是假。另外，子查询没有返回任何数据行的情况下，IN的结果也是假。
两个数据行只有在所有的列上的值都不是空值而且相对应的列上的值相等的情况下，才是相等的。两个数据行如果在任何一个相对应的列上的值都不是空值而且在这个列上的值不相等的情况下，它们才是不相等的。其它情况下，两个数据行相比较的结果是一个空值。如果表达式的值与子查询结果中的每个数据行都不相等，而且至少与其中的一个数据行比较的结果是空值，IN的结果是空值。


7.18.3 NOT IN


expression NOT IN (subquery)
NOT IN的右边是一个用括号括起来的子查询，这个子查询必须也只能返回一个列。NOT IN左边的表达式的值与子查询的的结果中的每个数据行相比较，如果子查询的的结果中的每个数据行都与表达式的值不相等，则NOT IN的结果是真。如果子查询的的结果中至少存在一个数据行与表达式的值相等，则NOT IN的结果是假。另外，子查询没有返回任何数据行的情况下，NOT IN的结果也是真。
如果IN的左边的表达式的值是空值，NOT IN的结果是空值。如果子查询的结果中没有一个数据行与表达式的值相等，同时查询的结果中有一个数据行是空值，NOT IN的结果是空值，而不是真。
同EXISTS一样，NOT IN右边的子查询也许执行到中间就能确定IN的结果，这时子查询就会停止执行。
NOT IN还有另外一种用法：

row_constructor NOT IN (subquery)
在这个用法中，NOT IN的左边是一个数据行构造器（参见第2.12节）。右边是一个用括号括起来的子查询，这个子查询返回的列的个数必须与数据行构造器中的列的个数完全相同。NOT IN左边的表达式的值与子查询的的结果中的每个数据行相比较，如果子查询的的结果中的每个数据行都与表达式的值不相等，则NOT IN的结果是真。如果子查询的的结果中至少存在一个数据行与表达式的值相等，则NOT IN的结果是假。另外，子查询没有返回任何数据行的情况下，NOT IN的结果也是真。
两个数据行只有在所有的列上的值都不是空值而且相对应的列上的值相等的情况下，才是相等的。两个数据行如果在任何一个相对应的列上的值都不是空值而且在这个列上的值不相等的情况下，它们才是不相等的。其它情况下，两个数据行相比较的结果是一个空值。如果表达式的值与子查询结果中的每个数据行都不相等，而且至少与其中的一个数据行比较的结果是空值，NOT IN的结果是空值。


7.18.4 ANY/SOME


expression operator ANY (subquery)
expression operator SOME (subquery)
ANY和SOME的右边是一个用括号括起来的子查询，这个子查询必须也只能返回一个列。用给定的运算符operator将左边的表达式的值与子查询的的结果中的每个数据行相比较，如果有一个比较操作的结果为真，ANY的结果为真。如果所有的比较操作的结果为假，ANY的结果为假。另外，子查询没有返回任何数据行的情况下，ANY的结果也是假。
SOME是ANY的同义词，两者是等价的。IN等价于= ANY。
如果没有一个比较操作的结果为真，同时至少有一个比较操作的结果是空值，ANY的结果是空值，而不是假。
同EXISTS一样，ANY和SOME右边的子查询也许执行到中间就能确定ANY的结果，这时子查询就会停止执行。
ANY和SOME还有另外一种用法：

row_constructor operator ANY (subquery)
row_constructor operator SOME (subquery)
在这个用法中，ANY的左边是一个数据行构造器（参见第2.12节）。右边是一个用括号括起来的子查询，这个子查询返回的列的个数必须与数据行构造器中的列的个数完全相同。用给定的运算符operator将左边的表达式的值与子查询的的结果中的每个数据行相比较，如果有一个比较操作的结果为真，ANY的结果为真。如果所有的比较操作的结果为假，ANY的结果为假。另外，子查询没有返回任何数据行的情况下，ANY的结果也是假。
如果没有一个比较操作的结果为真，同时至少有一个比较操作的结果是空值，ANY的结果是空值，而不是假。两个数据行比较的规则同前面介绍的规则是一样的。


7.18.5 ALL


expression operator ALL (subquery)
ALL右边是一个用括号括起来的子查询，这个子查询必须也只能返回一个列。用给定的运算符operator将左边的表达式的值与子查询的的结果中的每个数据行相比较，如果所有的比较操作的结果为真，ALL的结果为真。如果有一个比较操作的结果为假，ALL的结果为假。另外，子查询没有返回任何数据行的情况下，ALL的结果也是真。
如果没有一个比较操作的结果为假，同时至少有一个比较操作的结果是空值，ALL的结果是空值。
NOT IN 等价于 <> ALL。
同EXISTS一样，ALL右边的子查询也许执行到中间就能确定ALL的结果，这时子查询就会停止执行。
ALL还有另外一种用法：

row_constructor operator ALL (subquery)
在这个用法中，ALL的左边是一个数据行构造器（参见第2.12节）。右边是一个用括号括起来的子查询，这个子查询返回的列的个数必须与数据行构造器中的列的个数完全相同。用给定的运算符operator将左边的表达式的值与子查询的的结果中的每个数据行相比较，如果所有的比较操作的结果为真，ALL的结果为真。如果有一个比较操作的结果为假，ALL的结果为假。另外，子查询没有返回任何数据行的情况下，ALL的结果也是真。
如果没有一个比较操作的结果为假，同时至少有一个比较操作的结果是空值，ALL的结果是空值。两个数据行比较的规则同前面介绍的规则是一样的。


7.18.6 按数据行比较


row_constructor operator (subquery)
左边是一个数据行构造器（参见第2.12节）。右边是一个用括号括起来的子查询，这个子查询返回的列的个数必须与数据行构造器中的列个个数完全相同。此外，子查询最多只能返回一个数据行，如果子查询返回的数据行的个数是0，则比较的结果是空值。左边的数据行与右边子查询的结果中的数据行用运算符operator进行比较。数据行比较的规则参见第7.19.5节，本节内容前面也有介绍。




7.19 与数据行和数组相关的比较


本节介绍与数组和数据行相关的比较表达式，它们在语法上与上一节介绍的子查询表达式很相似，但是不使用子查询。与数组相关的比较表达式是PostgreSQL的扩展特性。本节中所有的比较表达式的结果都是一个布尔类型的值。


7.19.1 IN


expression IN (value [, ...])
IN的右边是一个用括号括起来的标量值的列表。如果左边的表达式的值与任何一个右边的列表中的值相等，IN的结果为真。如果左边的表达式的值与任何一个右边的列表中的值都不相等，IN的结果为假。相当于：
expression = value1
OR
expression = value2
OR
...
如果左边的表达式的值为空值，IN的值为空值。如果左边的表达式的值与任何一个右边的列表中的值都不相等，同时右边的值列表中有至少有一个值是空值，IN的结果为空值。


7.19.2 NOT IN


expression NOT IN (value [, ...])
NOT IN的右边是一个用括号括起来的标量值的列表。如果左边的表达式的值与右边的列表中的所有的值都不相等，NOT IN的结果为真。相当于：
expression <> value1
AND
expression <> value2
AND
...
如果左边的表达式的值为空值，NOT IN的值为空值。如果左边的表达式的值与任何一个右边的列表中的值都不相等，同时右边的值列表中有至少有一个值是空值，NOT IN的结果为空值。


7.19.3 ANY/SOME (array)


expression operator ANY (array expression)
expression operator SOME (array expression)
右边是一个用括号括起来的表达式，它的值是一个数组。用给定的运算符operator将左边的表达式的值与右边的数组的每一个元素进行比较，如果有一个比较操作的结果为真，ANY的结果为真。如果所有的比较操作的结果都为假，ANY的结果为假。如果右边的数组中的元素的个数为0，ANY的结果也为假。
如果右边的数组是一个空值，ANY的结果为空值。如果左边的表达式的值为空值，ANY的结果为空值。如果左边的表达式与右边的数组中的每个元素比较的结果都不是真，同时数组中至少有一个元素是空值，ANY的结果也为空值。
SOME 是ANY的同义词。


7.19.4 ALL (array)


expression operator ALL (array expression)
右边是一个用括号括起来的表达式，它的值是一个数组。用给定的运算符operator将左边的表达式的值与右边的数组的每一个元素进行比较，如果所有的比较操作的结果为真，ALL的结果为真。如果有一个比较操作的结果为假，ALL的结果为假。如果右边的数组中的元素的个数为0，ALL的结果也为真。
如果右边的数组是一个空值，ALL的结果为空值。如果左边的表达式的值为空值，ALL的结果为空值。如果左边的表达式与右边的数组中的每个元素比较的结果都不是假，同时数组中至少有一个元素是空值，ANY的结果也为空值。


7.19.5 按数据行比较


row_constructor operator row_constructor
左边和右边都是一个数据行构造器。参见第2.12节得到数据行构造器的详细信息。左右两边的数据行的域的个数必须完全相同。operator可以是=, <>, <, <=, > or >=。
=和<>与其它的运算符有一点不同。两个数据行只有在所有对应的成员都不是空值并且相等的情况下，才是相等的。两个数据行如果有一个对应的成员都不是空值并且不相等的情况下，才是不相等的。其它的情况下，两个数据行比较的结果是一个空值。
对于运算符<、 <=、 > 和>= ，数据行的元素从左向右，一个一个地比较。如果发现两个不相等的元素或有一个元素是空值，比较操作立即停止，这对不相等的元素（或者至少有一个是空值的元素）的比较结果就是数据行比较操作的结果。这对元素如果有一个元素是空值，数据行比较操作的结果是空值。例如，ROW(1,2,NULL) < ROW(1,3,0)的结果为真，而不是空值，因为第二对元素(2,3)的比较结果决定了这两个数据行的比较结果。
还有另外两种形式的比较表达式：
（1）row_constructor IS DISTINCT FROM row_constructor
（2）row_constructor IS NOT DISTINCT FROM row_constructor
IS DISTINCT FROM的功能类似于<>。但它对于空值的比较规则有一些不同。两个空值是相等的，空值和任何非空值都是不相等的。IS DISTINCT FROM的结果不是真就是假，不会是空值。
IS DISTINCT FROM的功能类似于=。但它对于空值的比较规则有一些不同。两个空值是相等的，空值和任何非空值都是不相等的。IS DISTINCT FROM的结果不是真就是假，不会是空值。



7.20返回集合的函数


本节描述返回值是一个集合而非单个值的函数。当前只有产生序列的函数属于这类函数。表7-40列出了这些函数。
表 7-40. 产生序列的函数

函数名	参数类型	返回值类型	描述
generate_series(start, stop)	int or bigint	setof int 或setof bigint (与参数的类型相同)	产生一个值的序列，从start开始，以stop结束，每个值比前面的一个值大1
generate_series(start, stop, step)	int or bigint	setof int 或setof bigint (与参数的类型相同)	产生一个值的序列，从start开始，以stop结束，每个值比前面的一个值大step

如果step是正数，同时start比stop大，则返回一个空集。如果step是负数，同时start比stop小，也返回一个空集。如果输入的值是空值，也返回一个空集。step的值不能等于0，否则系统会报错。

下面是一些例子:
（1）select * from generate_series(2,4);
generate_series
-----------------
2
3
4
(3 rows)

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

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

（4）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)




7.21 系统信息函数


表7-41列出了获取会话和系统信息的函数。
表7-41. 会话信息函数

函数名	返回值类型	描述
current_database()	name	当前数据库的名称
current_schema()	name	当前模式的名称
current_schemas(boolean)	name[]	模式搜索路径中的所有模式（包括隐含的模式）
current_user	name	当前用户的名称
inet_client_addr()	inet	客户端的IP地址
inet_client_port()	int	客户端的端口
inet_server_addr()	inet	服务器的IP地址
inet_server_port()	int	服务器的端口
pg_my_temp_schema()	oid	会话临时模式的OID，如果是0表示会话没有临时模式
pg_is_other_temp_schema(oid)	boolean	指定的模式是不是其它会话的临时模式？
pg_postmaster_start_time()	timestamp with time zone	服务器的启动时间
session_user	name	会话用户名
user	name	等价于current_user
version()	text	数据库版本信息

session_user（会话用户）是建立当前的数据库连接的用户。会话的当前用户是指对话的命令进行权限检查时使用的用户。会话刚刚建立的时候，当前用户与会话用户是一样的。但超级用户可以通过命令SET SESSION AUTHORIZATION改变会话的当前用户。此外，会话在执行具有SECURITY DEFINER属性的函数的过程中，当前用户也会发生该变（变成创建被调用函数的用户）。

current_schema返回位于模式搜索路径最前面的模式（如果模式搜索路径为空，则返回一个空值），这个模式是默认的存放新创建的数据库对象的地方。current_schemas(boolean)返回模式搜索路径中的所有模式，如果参数为真，则也会返回pg_catalog这样的系统模式，如果参数为假，则不返回pg_catalog这样的系统模式。

注意: 可以在数据库运行时，随时修改会话的模式搜索路径，命令是：
SET search_path TO schema [, schema, ...]
net_client_addr返回当前会话的客户端的IP地址，inet_client_port返回当前会话的客户端的端口。inet_server_addr返回服务器接受当前的客户端连接时使用的IP地址（数据库服务器可能同时在多个IP地址上监听客户端连接请求）。inet_server_port返回服务器使用的端口。如果当前的连接是通过Unix-domain socket建立的，所有的这些函数都返回空值。
pg_my_temp_schema返回当前会话的临时模式的OID，如果是0则表示当前会话没有临时模式（因为还没有创建任何临时表）。pg_is_other_temp_schema 判断给定的模式是否是其它会话的临时模式。
pg_postmaster_start_time返回服务器的启动时间，类型是timestamp with time zone。
version 返回数据库的版本信息。

表7-42中的函数允许用户查看指定的用户对某个数据库对象是否拥有指定的权限。参见《数据库管理员手册》第2章得到数据库权限的详细信息。
表 7-42. 访问权限查询函数

函数名	返回值类型	描述
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_table_privilege(user, table, privilege)	boolean	指定的用户是否对表有指定的权限
has_table_privilege(table, privilege)	boolean	当前用户是否对表有指定的权限
has_tablespace_privilege(user, tablespace, privilege)	boolean	指定的用户是否对表空间有指定的权限
has_tablespace_privilege(tablespace, privilege)	boolean	当前用户是否对表空间有指定的权限

has_database_privilege检查一个用户对数据库是否拥有指定的权限。权限类型可以是CREATE、CONNECT、TEMPORARY或TEMP（与TEMPORARY等价）。
has_database_privilege检查一个用户对函数是否拥有指定的权限。权限类型可以是CREATE、CONNECT、TEMPORARY或TEMP（与TEMPORARY等价）。函数的权限类型必须是EXECUTE，例如：
SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');

has_language_privilege检查一个用户对语言是否拥有指定的权限。语言的权限类型必须是USAGE。
has_schema_privilege检查一个用户对模式是否拥有指定的权限。模式的权限类型必须是USAGE或CREATE。
has_table_privilege检查一个用户对表是否拥有指定的权限。权限类型可以是SELECT、INSERT、 UPDATE、 DELETE、REFERENCES或 TRIGGER（权限不区分大小写）。例如：
SELECT has_table_privilege('myschema.mytable', 'select');
has_tablespace_privilege检查一个用户对表空间是否拥有指定的权限。表空间的权限类型必须是CREATE。
如果想测试用户是否对指定的权限有GRANT OPTION，在权限的名称后面加上WITH GRANT OPTION即可，例如：'UPDATE WITH GRANT OPTION'。

表7-43列出了查看某个数据库对象在当前模式搜索路径中是否可见的函数。可以使用下面的命令查看所有当前可见的表：
SELECT relname FROM pg_class WHERE pg_table_is_visible(oid);
表7-43. 查看数据库对象是否可见的函数

函数名	返回值类型	描述
pg_conversion_is_visible(conversion_oid)	boolean	类型转换在模式搜索路径中是否可见
pg_function_is_visible(function_oid)	boolean	函数在模式搜索路径中是否可见
pg_operator_is_visible(operator_oid)	boolean	运算符在模式搜索路径中是否可见
pg_opclass_is_visible(opclass_oid)	boolean	运算符类在模式搜索路径中是否可见
pg_table_is_visible(table_oid)	boolean	表（视图、索引或序列）在模式搜索路径中是否可见
pg_ts_config_is_visible(config_oid)	boolean	全文检索配置在模式搜索路径中是否可见
pg_ts_dict_is_visible(dict_oid)	boolean	全文检索字典在模式搜索路径中是否可见
pg_ts_parser_is_visible(parser_oid)	boolean	全文检索解析器在模式搜索路径中是否可见
pg_ts_template_is_visible(template_oid)	boolean	全文检索模板在模式搜索路径中是否可见
pg_type_is_visible(type_oid)	boolean	类型（或域）在模式搜索路径中是否可见

注意，可以用pg_table_is_visible查看是视图、索引和序列在模式搜索路径中是否是可见的。可以用pg_type_is_visible查看域在模式搜索路径中是否是可见的。

所有的这些函数的参数类型都是OID，如果使用对象名作为参数，可以使用 OID的别名类型（regclass、regtype、 regprocedure、regoperator、 regconfig或regdictionary）。例如：
SELECT pg_type_is_visible('myschema.widget'::regtype);

表7-44 列出了查看系统数据字典信息的函数。
表7-44. 数据字典信息函数

函数名	返回值类型	描述
format_type(type_oid, typemod)	text	得到数据类型的名称
pg_get_constraintdef(constraint_oid)	text	得到约束的定义
pg_get_constraintdef(constraint_oid, pretty_bool)	text	得到约束的定义
pg_get_expr(expr_text, relation_oid)	text	将表达式从内部的表示形式中解码
pg_get_expr(expr_text, relation_oid, pretty_bool)	text	将表达式从内部的表示形式中解码
pg_get_indexdef(index_oid)	text	得到创建索引的命令CREATE INDEX
pg_get_ruledef(rule_oid)	text	得到创建规则的命令CREATE RULE
pg_get_ruledef(rule_oid, pretty_bool)	text	得到创建规则的命令CREATE RULE
pg_get_serial_sequence(table_name, column_name)	text	得到表中类型为serial和bigserial的列使用的序列名称。
pg_get_triggerdef(trigger_oid)	text	得到创建触发器的命令 CREATE [ CONSTRAINT ] TRIGGER
pg_get_userbyid(roleid)	name	根据角色的 OID得到角色的名称
pg_get_viewdef(view_oid)	text	得到创建视图的SELECT命令
pg_get_viewdef(view_oid, pretty_bool)	text	得到创建视图的SELECT命令
pg_tablespace_databases(tablespace_oid)	setof oid	得到在指定的表空间中存放数据库对象的所有的数据库的OID

format_type根据类型的OID和修饰符返回类型的名称，如果不知道类型的修饰符，可以用NULL表示。
pg_get_constraintdef、pg_get_indexdef、 pg_get_ruledef和pg_get_triggerdef分别用来重构创建约束、索引、规则和触发器的SQL命令。pg_get_expr将表达式从内部的表示形式中解码，例如一个列的默认值表达式。pg_get_viewdef 用来重构定义视图的SELECT命令。这些函数的参数pretty_bool（取值是true或false）用来控制函数输出结果的显示格式。如果是true，输出的结果的可读性强一些，如果是false，输出的结果的可读性差一些。函数如果没有参数pretty_bool，结果与参数pretty_bool的值是false时的结果是一样的。
pg_get_serial_sequence得到表中类型为serial和bigserial的列使用的序列名称。如果没有序列与指定的列相关，则返回NULL。第一个参数是表的名称，可以用模式来修饰，第二个参数是列的名称。这个函数返回的序列名称的格式符合序列函数的要求，可以直接作为序列函数的参数使用。
pg_get_userbyid 通过角色（或用户）的OID得到角色名（或用户名）。
pg_tablespace_databases得到在指定的表空间中存放数据库对象的所有的数据库的OID，如果一个表空间中存有任何数据库对象，它就不能被删除。

表7-45中的函数用来查看数据库对象的注释信息。如果指定的数据库对象不存在注释信息，这些函数将返回空值。可以使用命令COMMENT为数据库对象添加注释信息。
表7-45. 查看注释信息函数

函数名	返回值类型	描述
col_description(table_oid, column_number)	text	查看表的列的注释信息
obj_description(object_oid, catalog_name)	text	查看数据库对象的注释信息
shobj_description(object_oid, catalog_name)	text	查看共享的数据库对象的注释信息

col_description 返回一个表的某个列的注释信息。第一个参数是表的OID，第二个参数是列的编号。
obj_description返回一个数据库对象的注释信息，第一个参数是对象的OID，第二个参数是对象所在的系统表的名称。例如obj_description(123456,'pg_class')返回OID为123456的数据库表的注释信息。
shobj_description用来得到共享数据库对象的注释信息。有些系统表是数据库集群中所有的数据库都使用的。


7.22 系统管理函数


表7-46列出了查询和设置数据库参数的函数。
表7-46. 配置参数函数

函数名	返回值类型	描述
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为真，新的值只在当前事务内有效。如果is_local为假，新的值在整个会话的生存时间内都有效。它与SQL命令SET的作用是一样的。例如：
SELECT set_config('log_statement_stats', 'off', false);

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

表7-47中的函数的功能是向其它数据库进程发送信号。只有超级用户才能使用这些函数。
表7-47. 向数据库进程发送信号的函数

函数名	返回值类型	描述
pg_cancel_backend(pid int)	boolean	中止一个backend进程正在执行的查询
pg_reload_conf()	boolean	让所有的数据库进程重新读取参数配置文件
pg_rotate_logfile()	boolean	切换数据库运行日志文件

所有的这些函数执行成功返回真，否则返回假。
pg_cancel_backend发送一个中止查询的信号(SIGINT)给一个数据库backend进程，该进程由进程ID确定（参数pid）。可以使用操作系统命令“ps -efw”得到数据库进程的PID，也可以查询视图pg_stat_activity的列procpid得到数据库进程的PID。
pg_reload_conf让所有的数据库进程重新读取参数配置文postgresql.conf。
pg_rotate_logfile告诉数据库重新建立一个新的数据库运行日志文件来存放数据库的运行日志。

表7-48中的函数在对数据库进行联机热备份时使用。只有超级用户才能使用前面的三个函数。
表7-48. 备份控制函数

函数名	返回值类型	描述
pg_start_backup(label text)	text	开始进行联机热备份
pg_stop_backup()	text	联机热备份结束
pg_switch_xlog()	text	强行切换到下一个事务日志
pg_current_xlog_location()	text	得到当前事务日志写的位置
pg_current_xlog_insert_location()	text	得到当前事务日志插入的位置
pg_xlogfile_name_offset(location text)	text, integer	将事务日志的位置字符串转换成事务日志文件名和文件内的偏移
pg_xlogfile_name(location text)	text	将事务日志的位置字符串转换成事务日志文件名

如何使用这些函数，参见《数据库管理员指南》第9.3.1节。函数pg_start_backup、pg_stop_backup、pg_switch_xlog、pg_current_xlog_location和pg_current_xlog_insert_location的输出可以直接用作函数pg_xlogfile_name_offset和pg_xlogfile_name的输入。
下面是一些实例：

（1）postgres=# select pg_start_backup('label_goes_here');
pg_start_backup
-----------------
0/D4445B8
(1 row)

（2）postgres=# select * from pg_xlogfile_name_offset(pg_stop_backup());
file_name | file_offset
--------------------------+-------------
00000001000000000000000D | 4039624
(1 row)

（3）postgres=# select * from pg_xlogfile_name(pg_stop_backup());
file_name
-------------------------
00000001000000000000000D
(1 row)

表7-49列出了计算数据库对象所占的存储空间的函数。
表7-49. 数据库对象大小函数

函数名	返回值类型	描述
pg_column_size(any)	int	一个特定的值所占的存储空间（可能被压缩以后再存储）,单位是字节
pg_database_size(oid)	bigint	指定的OID确定的数据库所占的存储空间
pg_database_size(name)	bigint	指定的名字的数据库所占的存储空间
pg_relation_size(oid)	bigint	指定的OID确定的表、索引或toast表占的存储空间
pg_relation_size(text)	bigint	指定的名字确定的表或索引占的存储空间。表明前面可以用模式名来修饰。
pg_size_pretty(bigint)	text	将以字节为单位的数值转换成以kB、 MB、GB或 TB为单位的数值
pg_tablespace_size(oid)	bigint	指定的 OID确定的表空间占的存储空间
pg_tablespace_size(name)	bigint	指定的名字确定的表空间占的存储空间
pg_total_relation_size(oid)	bigint	指定的OID确定的表占的全部存储空间，包括表上定义的索引和所有相关的toast表占的存储空间
pg_total_relation_size(text)	bigint	指定的名字确定的表占的全部存储空间，包括表上定义的索引和所有相关的toast表占的存储空间

表7-50中的函数用来管理建议锁。关于建议锁的详细信息，参见第9.3.4节。
表7-50. 管理建议锁的函数

函数名	返回值类型	描述
pg_advisory_lock(key bigint)	void	得到互斥的建议锁
pg_advisory_lock(key1 int, key2 int)	void	得到互斥的建议锁
pg_advisory_lock_shared(key bigint)	void	得到共享的建议锁
pg_advisory_lock_shared(key1 int, key2 int)	void	得到共享的建议锁
pg_try_advisory_lock(key bigint)	boolean	尝试得到互斥的建议锁
pg_try_advisory_lock(key1 int, key2 int)	boolean	尝试得到互斥的建议锁
pg_try_advisory_lock_shared(key bigint)	boolean	尝试得到共享的建议锁
pg_try_advisory_lock_shared(key1 int, key2 int)	boolean	尝试得到共享的建议锁
pg_advisory_unlock(key bigint)	boolean	释放互斥的建议锁
pg_advisory_unlock(key1 int, key2 int)	boolean	释放互斥的建议锁
pg_advisory_unlock_shared(key bigint)	boolean	释放共享的建议锁
pg_advisory_unlock_shared(key1 int, key2 int)	boolean	释放共享的建议锁
pg_advisory_unlock_all()	void	释放当前会话持有的所有建议锁

pg_advisory_lock用互斥模式锁住一个应用程序定义的资源。这个资源可以用一个单一的64位的值来表示，也可以用两个32位的值来表示。如果其它会话已经锁住了这个资源，该函数将进入等待状态，直到其它会话释放这个资源。多个加锁请求会被系统自动记载下来，所有如果同一个资源被锁住三次，它必须被释放三次，才能被其它的会话使用。

pg_advisory_lock_shared用共享模式锁住一个应用程序定义的资源，多个会话可以同时以共享模式锁住同一个资源。如果这个资源已经被其它会话用互斥模式锁住，它会进入等待状态，直到没有任何会话用互斥模式锁住这个资源。

pg_try_advisory_lock类似于pg_advisory_lock，它试图用互斥模式锁住一个应用程序定义的资源。如果这个资源已经被其它会话锁住，它不会进入等待状态，而是立即返回。如果得到锁，返回true，如果没得到锁，返回false。

pg_try_advisory_lock_shared类似于pg_try_advisory_lock，它试图用共享模式锁住一个应用程序定义的资源，如果这个资源已经被其它会话用互斥模式锁住，它不会进入等待状态，而是立即返回。如果得到锁，返回true，如果没得到锁，返回false。

pg_advisory_unlock释放一个以前用互斥模式锁住的资源。如果返回true，表示释放成功。如果返回false，表示当前会话以前并没有用互斥模式锁住这个资源，同时系统会发出警告信息。

pg_advisory_unlock释放一个以前用共享模式锁住的资源。如果返回true，表示释放成功。如果返回false，表示当前会话以前并没有用共享模式锁住这个资源，同时系统会发出警告信息。

pg_advisory_unlock_all释放当前会话持有的所有建议锁。

窗体顶端