PostgreSQL函数和运算符(二)
7.7模式匹配
PostgreSQL 提供了三种模式匹配的方法：SQL LIKE 运算符、SIMILAR TO 运算符（SQL:1999定义的）和POSIX风格的正则表达式。
提示: 如果上面的三种模式匹配方法还不能满足应用程序的要求，可以用 Perl 或 Tcl 写一个用户自定义函数。
7.7.1 LIKE
string LIKE pattern [ ESCAPE escape-character ]
string NOT LIKE pattern [ ESCAPE escape-character ]
每个 pattern都定义了一个字符串的集合。如果该 string 包含在 pattern 代表的字符串集合里，那么 LIKE表达式返回真。如果string不在 pattern 代表的字符串集合里，NOT LIKE表达式返回真。与NOT LIKE等价的表达式是 NOT （string LIKE pattern）。
如果 pattern 不包含百分号和下划线，那么它定义的字符串的集合中只有一个元素，就是pattern自己，此时LIKE 就相当于一个等号运算符。pattern 里面的下划线 （_）代表任何单个字符，百分号（%）表示由任意字符组成的长度为零或n（表示一个任意大的正整数）的字符串。
下面是一些实例∶
'abc' LIKE 'abc' true
'abc' LIKE 'a%' true
'abc' LIKE '_b_' true
'abc' LIKE 'c' false
如果想让pattern里面出现的下划线和百分号表示它们自己，而不是匹配其它字符，那就要在下划线和百分号前面加上转义字符，默认的转义字符是反斜杠，也可以使用ESCAPE子句指定其它字符作为转义字符，如果pattern中也出现了转义字符，那么用两个转义字符表示。例如：
（1）select 'abc_' like 'abc$_' escape '$';
?column?
----------
t
指定$作为转义字符，'abc$_' 表示字符串'abc_'。
（2）select 'abc_' like 'abc//_' ;
?column?
----------
t
'abc//_' 表示字符串'abc_'。
（3）select 'abc_$' like 'abc$_$$' escape '$';
?column?
----------
t
如果使用了ESCAPE子句，但又没有指定任何字符作为转义字符，那么转义机制将被关闭，pattern中的百分号和下划线不能被用来表示它们自身，只能被用来匹配其它字符或字符串。例如：
select 'abc_' like 'abc//_' escape '';
?column?
----------
f
(1 row)
运算符 ~~ 等价于 LIKE，运算符!~~ 等价于NOT ILIKE。这两个运算符是 PostgreSQL 特有的，SQL标准中没有这两个运算符。
7.7.2 SIMILAR TO 正则表达式
string SIMILAR TO pattern [ESCAPE escape-character]
string NOT SIMILAR TO pattern [ESCAPE escape-character]
其中的pattern是一个正则表达式，如果string匹配pattern，SIMILAR TO返回真，否则返回假。
和LIKE类似，SIMILAR TO 使用 _ 和 % 分别代表任意字符和任意字符串字符（_和%相当于POSIX 正则表达式里的 . 和 .* ）。
SIMILAR TO 支持下面这些从 POSIX 正则表达式借用的模式匹配元字符：
a| b ，表示a或b。
a* , 表示零个或多个a。
a+， 表示一个或多个a。
()，用来将表达式分组 。
[...]， 表示一个字符类。
请注意没有提供元字符? 和 {...}，它们在POSIX正则表达式中用来表示有限次数的重复。同时，点（.）不是元字符，是普通的字符。
和LIKE 一样，反斜杠关闭所有这些元字符的特殊含义，也可以用 ESCAPE 指定其它的字符作为转意字符。
下面是一些例子：
（1）'abc' SIMILAR TO 'abc' 结果为true
（2）'abc' SIMILAR TO 'a' 结果为false
（3）'abc' SIMILAR TO '%(b|d)%' 结果为true
（4）'abc' SIMILAR TO '(b|c)%' 结果为false
可以使用函数substring从一个字符串中找出匹配指定的正则表达式的子串，它的形式是substring(string from pattern for escape-character)，注意string必须完全符合pattern指定的正则表达式，否则该函数将会返回NULL。参数escape-character指定正则表达式使用的转义字符。函数返回的字符串是string中的一个匹配pattern中的一个子表达式的子串，pattern中的这个子表达式必须用escape-character指定的转义字符括起来，而且每个转义字符后面必须跟一个双引号，例如：
（1）substring('foobar' from '%#"o_b#"%' for '#')
结果为oob，foobar匹配正则表达式%o_b%，o_b是匹配返回的子串的子正则表达式
（2）substring('foobar' from '#"o_b#"%' for '#')
结果为 NULL，foobar不匹配正则表达式o_b%
7.7.3 POSIX 正则表达式
POSIX 正则表达式提供了比 LIKE和SIMILAR TO运算符更强大的模式匹配的方法。许多 Unix 系统工具都是用正则表达式作为它们的模式匹配语言，例如egrep、sed和awk。
表 7-16列出了使用POSIX 正则表达式进行模式匹配的的运算符。
表 7-16. 正则表达式匹配运算符
|
运算符 |
描述 |
例子 |
|
~ |
二元运算符，左边的操作数是普通字符串，右边的操作数是正则表达式，意识是左边的字符串是否匹配右边的正则表达式，左边的字符串大小写相关。 |
'thomas' ~ '.*thomas.*' |
|
~* |
二元运算符，左边的操作数是普通字符串，右边的操作数是正则表达式，意识是左边的字符串是否匹配右边的正则表达式，左边的字符串大小写无关。 |
'thomas' ~* '.*Thomas.*' |
|
!~ |
二元运算符，左边的操作数是普通字符串，右边的操作数是正则表达式，意识是左边的字符串是否不匹配右边的正则表达式，左边的字符串大小写相关。 |
'thomas' !~ '.*Thomas.*' |
|
!~* |
二元运算符，左边的操作数是普通字符串，右边的操作数是正则表达式，意识是左边的字符串是否不匹配右边的正则表达式，左边的字符串大小写无关。 |
'thomas' !~* '.*vadim.*' |
下面是一些关于这些运算符的实例：
（1）'abc' ~ 'abc' 结果为true
（2）'abc' ~ '^a' 结果为true
（3）'abc' ~ '(b|d)' 结果为true
（4）'abc' ~ '^(b|c)' 结果为false
PostgresSQL支持两种POSIX正则表达式：基本的正则表达式和扩展的正则表达式（POSIX 1003.2）。数据库参数regex_flavor用来控制系统使用基本的正则表达式还是扩展的正则表达式，如果它的值是basic，系统使用基本的正则表达式，如果它的值是extended，系统使用扩展的正则表达式。关于POSIX正则表达式的详细信息，请查阅相关资料，此处不再赘述。数据库对正则表达式的长度没有限制。
下面的四个函数支持使用POSIX正则表达式：
（1）substring(string from pattern),
（2）regexp_replace(source, pattern, replacement [, flags ])
（3）regexp_matches(string, pattern [, flags ]).
（4）regexp_split_to_table(string, pattern [, flags ])
substring用来从一个字符串string中提取一个符合正则表达式pattern的字串，如果pattern中含有用括号括起来的子表达式，则返回符合匹配括号中的子表达式的子串。如果string中不存在匹配pattern的子串，则返回空，例如：
substring('foobar' from 'o.b') 结果为oob
substring('foobar' from 'o(.)b') 结果为o
regexp_replace的作用是用字符串replacement替换字符串source中符合模式pattern的子串。如果source中没有符合pattern的字串，则返回source。参数flags是可选的，它由一个或多个字符组成，字符i表示进行模式匹配时，不考虑大小写，字符g表示source中每个匹配指定的模式的子串都将被替换。例如：
regexp_replace('fooBarbaz', 'b..', 'X','i') 结果为fooXbaz
regexp_replace('fooBarbaz', 'b..', 'X') 结果为fooBarX
regexp_replace('foobarbaz', 'b..', 'X') 结果为fooXbaz
regexp_replace('foobarbaz', 'b..', 'X', 'g') 结果为fooXX
regexp_replace('fooBarbaz', 'b..', 'X','ig') 结果为fooXX
regexp_matches返回字符串 string中所有匹配模式pattern的子串，参数flags是可选的，例如：
（1）SELECT regexp_matches('foobarbequebaz', '(bar)(beque)');
regexp_matches
----------------
{bar,beque}
(1 row)
（2）SELECT regexp_matches('foobarbequebaz', 'barbeque');
regexp_matches
----------------
{barbeque}
(1 row)
regexp_split_to_table使用字符串string中匹配pattern的字串作为分隔符将string分成多个子串，然后返回这些子串。如果string中没有匹配pattern的子串，则返回string。参数flags是可选的。例如:
（1）SELECT foo FROM regexp_split_to_table('the quick brown fox jumped over the lazy dog', E'///s+') AS foo;
foo
--------
the
quick
brown
fox
jumped
over
the
lazy
dog
(9 rows)
（2）SELECT regexp_split_to_array('the quick brown fox jumped over the lazy dog', E'//s+');
regexp_split_to_array
------------------------------------------------
{the,quick,brown,fox,jumped,over,the,lazy,dog}
(1 row)
（3）SELECT foo FROM regexp_split_to_table('the quick brown fox', E'//s*') AS foo;
foo
-----
t
h
e
q
u
i
c
k
b
r
o
w
n
f
o
x
(16 rows)
7.8 数据类型格式化函数
PostgreSQL提供了丰富的函数将不同的数据类型转换成字符串类型，同时进行输出格式变换，也可以将字符串转换成其它的数据类型。表7-17列出了所有的这些函数，这些函数的第一个参数是要被格式化的值，第二个参数是定义输出或输入字符串格式的模板。
函数to_timestamp可以将用双精度类型表示的Unix epoch时间转换成timestamp with time zone类型。对于用整数类型表示的Unix epoch，先将它转换成双精度类型，然后再转换成timestamp with time zone类型。
表7-17. 格式化函数
|
函数名 |
返回值类型 |
描述 |
例子 |
|
to_char(timestamp, text) |
text |
将time stamp 转换成字符串 |
to_char(current_timestamp, 'HH12:MI:SS') |
|
to_char(interval, text) |
text |
将interval转换成字符串 |
to_char(interval '15h 2m 12s', 'HH24:MI:SS') |
|
to_char(int, text) |
text |
将integer转换成字符串 |
to_char(125, '999') |
|
to_char(double precision, text) |
text |
将real/double precision转换成字符串 |
to_char(125.8::real, '999D9') |
|
to_char(numeric, text) |
text |
将numeric转换成字符串 |
to_char(-125.8, '999D99S') |
|
to_date(text, text) |
date |
将字符串转换成date |
to_date('05 Dec 2000', 'DD Mon YYYY') |
|
to_number(text, text) |
numeric |
将字符串转换成numeric |
to_number('12,454.8-', '99G999D9S') |
|
to_timestamp(text, text) |
timestamp with time zone |
将字符串转换成time stamp |
to_timestamp('05 Dec 2000', 'DD Mon YYYY') |
|
to_timestamp(double precision) |
timestamp with time zone |
将UNIX epoch转换成time stamp |
to_timestamp(200120400) |
将非字符串类型的数据转换成字符串类型的数据时，输出模板字符串中的字符如果不是模板模式，将被原封不动地复制到输出的结果中。相应地，将字符串类型的数据转换成非字符串类型的数据时，不属于输入模板字符串确定的字符串的字符也将被忽略。
表7-18列出了日期/时间类型格式化模板模式。
表7-18. 日期/时间类型格式化模板模式
|
模式 |
描述 |
|
HH |
小时 (01-12) |
|
HH12 |
小时(01-12) |
|
HH24 |
小时(00-23) |
|
MI |
分钟 (00-59) |
|
SS |
秒(00-59) |
|
MS |
微妙 (000-999) |
|
US |
毫秒(000000-999999) |
|
SSSS |
从午夜到现在过去的时间，用秒表示(0-86399) |
|
AM 或A.M.， PM 或P.M. |
上午下午指示符 (大写) |
|
am 或 a.m.， pm 或p.m. |
上午下午指示符(小写) |
|
Y,YYY |
年 (4个或更多个数字位) ，中间有逗号 |
|
YYYY |
年(4个或更多个数字位) |
|
YYY |
年的最后三个数字位 |
|
YY |
年的最后两个数字位 |
|
Y |
年的最后一个数字位 |
|
IYYY |
ISO 年 (4个或更多个数字位) |
|
IYY |
ISO年的最后三个数字位 |
|
IY |
ISO年的最后三个数字位 |
|
I |
ISO年的最后三个数字位 |
|
BC 或B.C.， AD 或A.D. |
世纪指示符(大写) |
|
bc 或 b.c. ，ad 或a.d. |
世纪指示符(小写) |
|
MONTH |
月名，全部大写(9个字符，不够用空格表示) |
|
Month |
月名，第一个字母大写，其它的字母都小写 (9个字符，不够在末尾填充空格) |
|
month |
月名，全部小写(9个字符，不够在末尾填充空格) |
|
MON |
缩写的月名，全部大写 (如果是英语，则是三个字符。其它语言，长度不固定) |
|
Mon |
缩写的月名，第一个字母大写，其它的字母都小写(如果是英语，则是三个字符。其它语言，长度不固定) |
|
mon |
缩写的月名，全部小写(如果是英语，则是三个字符。其它语言，长度不固定) |
|
MM |
月编号(01-12) |
|
DAY |
天名称，全部大写 (9个字符，不够用空格表示) |
|
Day |
天名称，第一个字母大写，其它的字母都小写(9个字符，不够在末尾填充空格) |
|
day |
天名称，全部小写 (9个字符，不够在末尾填充空格) |
|
DY |
缩写的天名称，全部大写(如果是英语，则是三个字符。其它语言，长度不固定) |
|
Dy |
缩写的天名称，第一个字母大写，其它的字母都小写(如果是英语，则是三个字符。其它语言，长度不固定) |
|
dy |
缩写的天名称，全部小写(如果是英语，则是三个字符。其它语言，长度不固定) |
|
DDD |
一年中天的编号(001-366) |
|
IDDD |
ISO格式表示的一年中天的编号(001-371) |
|
DD |
每月中天的编号(01-31) |
|
D |
一个星期中天的编号, 星期天是1，星期六是7 |
|
ID |
ISO格式表示的一个星期中天的编号, 星期一是1，星期天是7 |
|
W |
一个月中每个星期的编号 (1-5) (一个月的第一个星期从这个月的第一天开始.) |
|
WW |
一年中星期的编号(1-53) (一个月的第一个星期从这个月的第一天开始.) |
|
IW |
ISO 格式表示的一年中每个星期的编号(1–53) |
|
CC |
世纪编号(2个数字位) (二十世纪从2001-01-01开始) |
|
J |
儒略日 |
|
Q |
季度 |
|
RM |
罗马数字表示的月编号(I-XII; I=一月) (大写) |
|
rm |
罗马数字表示的月编号(i-xii; i=一月) (小写) |
|
TZ |
时区名 (大写) |
|
tz |
时区名(小写) |
有些修饰符可以改变模板模式的行为，表7-19列出了适用于日期/时间类型模板模式的修饰符。
表7-19. 适用于日期/时间类型模板模式的修饰符
|
修饰符 |
描述 |
例子 |
|
FM 前缀 |
去掉填充的零和空格 |
FMMonth |
|
TH 后缀 |
序号大写 |
DDTH |
|
th后缀 |
序号小写 |
DDth |
|
FX前缀 |
参见下面的注意事项 |
FX Month DD Day |
|
SP 后缀 |
尚未实现 |
DDSP |
日期/时间类型数据格式化注意事项：
（1）FM去掉字符串头部的零和字符串尾部的空格。
（2）TM不会修改字符串末尾的空格。
（3）如果没有使用FX修饰符， to_timestamp 和to_date会自动跳过输入字符串中的多余的空格。如果使用了FX修饰符，FX必须出现在模板字符串的最前面，而且输入的字符串中不能有多余的空格，年月日只能用一个空格分开。例如，to_timestamp('2000 JUN', 'YYYY MON')是正确的，但to_timestamp('2000 JUN', 'FXYYYY MON')是错误的，系统会报错。
（4）to_char中的模板字符串会被直接输出，如果想直接输出某个模式字符串，可以用双引号将模式字符串括起来。例如，对于'"Hello Year "YYYY'， “Hello Year”将会被直接输出，而YYYY将被替换成具体的年数据。
（5）如果想输出一个双引号，前面必须加上一个反斜杠。例如，E'//"YYYY Month//"（注意要用两个反斜杠，原因参考字符串语法）。
（6）将一个字符串转换成timestamp或date类型时，在默认的情况下，系统认为年的数据位是4个。如果表示年的数据位超过5个，应该在输入的字符串中用一个非数字的字符将表示年的子串和剩下的子串隔开。例如, to_date('200021131', 'YYYYMMDD')的结果中年将是2000，但to_date('20000-1131', 'YYYY-MMDD')和to_date('20000Nov31', 'YYYYMonDD')的结果中年将是20002。
（7）将一个字符串转换成timestamp或date类型时，如果模板字符串中有YYY、 YYYY 或Y,YYY，CC将被忽略。如果CC和YY或Y一起使用，那么年将用下面的公式(CC-1)*100+YY来计算。
（8）使用to_timestamp和to_char将字符串转换成ISO类型的日期数据时，有两种使用格式：
a)年、星期和一个星期中天编号。例如to_date('2006-42-4', 'IYYY-IW-ID')返回日期2006-10-19，如果忽略天，则默认是1。
b）年和一年中的天编号。例如to_date('2006-291', 'IYYY-IDDD')返回2006-10-19。
（9）将字符串转换成timestamp类型时，微妙和毫秒的计算方法将用一些实例来说明。例如，to_timestamp('12:3', 'SS:MS')表示12秒和300微妙，因为微妙的计算方法是0.3*1000。同样，to_timestamp('12:3', 'SS:MS')、to_timestamp('12:30', 'SS:MS')和to_timestamp('12:300', 'SS:MS')也表示12秒和300微妙,而to_timestamp('12:003', 'SS:MS')则表示12秒3微妙。再看一个更复杂的例子，to_timestamp('15:12:02.020.001230', 'HH:MI:SS.MS.US')表示15小时12分钟2秒中200微妙和1230毫秒，其中001230表示0.001230秒，所以一共有0.001230*1000000=1230毫秒。
（10）to_char(..., 'ID')结果中表示天的部分与 extract('isodow', ...)的结果中表示天的部分是一样的，但to_char(..., 'D')的结果中表示天的部分与extract('dow', ...)的结果中表示天的部分不相同。
表7-20列出了数值类型的格式化模板模式。
表7-20. 数值类型格式化模板模式
|
模式 |
描述 |
|
9 |
表示一个十进制位 |
|
0 |
数值前面加上一个前导0 |
|
. |
小数点 |
|
, |
组分隔符(以千为单位) |
|
PR |
如果一个数是负数，用两个尖括号（<和>）括起来。 |
|
S |
数值的符号（正号或者符号）(受LC_NUMERIC影响) |
|
L |
货币符号 (受LC_MONEY影响) |
|
D |
小数点 (受LC_NUMERIC影响) |
|
G |
组分隔符(受LC_NUMERIC影响) |
|
MI |
在指定的位置添加负号(如果是一个负数) |
|
PL |
在指定的位置添加正号(如果是一个整数) |
|
SG |
在指定的位置添加正号或者负号 |
|
RN |
将输入的数字转换成罗马数字(输入的数字应该在1和3999之间，否则函数会输出多个“#”作为结果) |
|
TH or th |
序数后缀 |
|
V |
将数值进行移位操作 (参见下面的注意事项) |
|
EEEE |
科学计数法 (还没有实现) |
数值类型数据格式化注意事项:
（1）如果使用模式s，则正负号紧挨着数值被输出，如果使用SG、PL或 MI正负号不一定紧挨着数值被输出。例如，to_char(-12, 'S9999')的结果是' -12'，但to_char(-12, 'MI9999')的结果是'- 12'。
（2）9代表一个十进制位，如果模板中的某个9对应的数字位不存在，系统将会输出一个空格， 例如，to_char(-12, 'S9999') 的结果是' -12'。
（3）TH 对于负数和分数没有任何作用。
（4）PL、SG和TH是PostgreSQL扩展特性。
（5）V 的作用是把输入的数值乘以 10的n次方， n是跟在v后面的数值位的个数。小数点和v不能同时出现在to_char的格式化模板字符串中, 例如，99.9V99是不合法的。
表7-21是一些to_char类型函数的实例。
表7-21. to_char 例子
|
表达式 |
结果 |
|
to_char(current_timestamp, 'Day, DD HH12:MI:SS') |
'Tuesday , 06 05:39:18' |
|
to_char(current_timestamp, 'FMDay, FMDD HH12:MI:SS') |
'Tuesday, 6 05:39:18' |
|
to_char(-0.1, '99.99') |
' -.10' |
|
to_char(-0.1, 'FM9.99') |
'-.1' |
|
to_char(0.1, '0.9') |
' 0.1' |
|
to_char(12, '9990999.9') |
' 0012.0' |
|
to_char(12, 'FM9990999.9') |
'0012.' |
|
to_char(485, '999') |
' 485' |
|
to_char(-485, '999') |
'-485' |
|
to_char(485, '9 9 9') |
' 4 8 5' |
|
to_char(1485, '9,999') |
' 1,485' |
|
to_char(1485, '9G999') |
' 1 485' |
|
to_char(148.5, '999.999') |
' 148.500' |
|
to_char(148.5, 'FM999.999') |
'148.5' |
|
to_char(148.5, 'FM999.990') |
'148.500' |
|
to_char(148.5, '999D999') |
' 148,500' |
|
to_char(3148.5, '9G999D999') |
' 3 148,500' |
|
to_char(-485, '999S') |
'485-' |
|
to_char(-485, '999MI') |
'485-' |
|
to_char(485, '999MI') |
'485 ' |
|
to_char(485, 'FM999MI') |
'485' |
|
to_char(485, 'PL999') |
'+485' |
|
to_char(485, 'SG999') |
'+485' |
|
to_char(-485, 'SG999') |
'-485' |
|
to_char(-485, '9SG99') |
'4-85' |
|
to_char(-485, '999PR') |
'<485>' |
|
to_char(485, 'L999') |
'DM 485 |
|
to_char(485, 'RN') |
' CDLXXXV' |
|
to_char(485, 'FMRN') |
'CDLXXXV' |
|
to_char(5.2, 'FMRN') |
'V' |
|
to_char(482, '999th') |
' 482nd' |
|
to_char(485, '"Good number:"999') |
'Good number: 485' |
|
to_char(485.8, '"Pre:"999" Post:" .999') |
'Pre: 485 Post: .800' |
|
to_char(12, '99V999') |
' 12000' |
|
to_char(12.4, '99V999') |
' 12400' |
|
to_char(12.45, '99V9') |
' 125' |
7.9 日期/时间函数和运算符
表7-22列出了处理理日期/时间类型数据的运算符。表7-23列出了用于处理日期/时间类型数据的函数，这些函数细节将在后面详细介绍。所有的函数和运算符的参数的类型可以是time或timestamp。表7-24列出了表7-23中的函数的实例。
表7-22. 日期/时间运算符
|
运算符 |
例子 |
结果 |
|
+ |
date '2001-09-28' + integer '7' |
date '2001-10-05' |
|
+ |
date '2001-09-28' + interval '1 hour' |
timestamp '2001-09-28 01:00:00' |
|
+ |
date '2001-09-28' + time '03:00' |
timestamp '2001-09-28 03:00:00' |
|
+ |
interval '1 day' + interval '1 hour' |
interval '1 day 01:00:00' |
|
+ |
timestamp '2001-09-28 01:00' + interval '23 hours' |
timestamp '2001-09-29 00:00:00' |
|
+ |
time '01:00' + interval '3 hours' |
time '04:00:00' |
|
- |
- interval '23 hours' |
interval '-23:00:00' |
|
- |
date '2001-10-01' - date '2001-09-28' |
integer '3' |
|
- |
date '2001-10-01' - integer '7' |
date '2001-09-24' |
|
- |
date '2001-09-28' - interval '1 hour' |
timestamp '2001-09-27 23:00:00' |
|
- |
time '05:00' - time '03:00' |
interval '02:00:00' |
|
- |
time '05:00' - interval '2 hours' |
time '03:00:00' |
|
- |
timestamp '2001-09-28 23:00' - interval '23 hours' |
timestamp '2001-09-28 00:00:00' |
|
- |
interval '1 day' - interval '1 hour' |
interval '1 day -01:00:00' |
|
- |
timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' |
interval '1 day 15:00:00' |
|
* |
900 * interval '1 second' |
interval '00:15:00' |
|
* |
21 * interval '1 day' |
interval '21 days' |
|
* |
double precision '3.5' * interval '1 hour' |
interval '03:30:00' |
|
/ |
interval '1 hour' / double precision '1.5' |
interval '00:40:00' |
表7-23. 日期/时间函数
|
函数名 |
返回值类型 |
描述 |
|
age(timestamp, timestamp) |
Interval |
用第一个参数减去第二个参数。 |
|
age(timestamp) |
interval |
用当前的日期（current_date）减去参数。 |
|
clock_timestamp() |
timestamp with time zone |
当前的日期和时间，参见第7.9.4节。 |
|
current_date |
date |
当前的日期，参见第7.9.4节。 |
|
current_time |
time with time zone |
当前的时间，参见第7.9.4节 |
|
current_timestamp |
timestamp with time zone |
当前事物开始的时间，参见第7.9.4节。
|
|
date_part(text, timestamp) |
double precision |
得到日期/时间数据的某个部分的值，(等同于extract)，参见第7.9.1节。 |
|
date_part(text, interval) |
double precision |
得到interval数据的某个部分的值(等同于extract)， 参见第7.9.1节。 |
|
date_trunc(text, timestamp) |
timestamp |
用指定的精度截断日期/时间数据，参见第7.9.2节。 |
|
extract(field from timestamp) |
double precision |
得到日期/时间数据的某个域的值，参见第7.9.1节。 |
|
extract(field from interval) |
double precision |
得到interval数据的某个域的值，参见第7.9.1节。 |
|
isfinite(timestamp) |
boolean |
测试日期/时间数据是否等于infinity。 |
|
isfinite(interval) |
boolean |
测试日期/时间数据是否等于interval。 |
|
justify_days(interval) |
interval |
将30天换算成一个月。 |
|
justify_hours(interval) |
interval |
将24小时换算成一天。 |
|
justify_interval(interval) |
interval |
使用justify_days 和justify_hours来换算interval数据。 |
|
localtime |
time |
当前事务开始的时间，参见第7.9.4节。 |
|
localtimestamp |
timestamp |
当前事务开始的日期和时间，参见第7.9.4节。 |
|
now() |
timestamp with time zone |
当前事务开始的日期和时间，参见第7.9.4节。 |
|
statement_timestamp() |
timestamp with time zone |
当前语句开始的日期和时间，参见第7.9.4节。 |
|
timeofday() |
text |
当前日期与时间 (与clock_timestamp类似， 但返回值的类型是字符串)，参见第7.9.4节。 |
|
transaction_timestamp() |
timestamp with time zone |
当前事务开始的日期和时间，参见第7.9.4节。 |
表7-24. 日期/时间函数实例
|
例子 |
结果 |
|
age(timestamp '2001-04-10', timestamp '1957-06-13') |
43 years 9 mons 27 days |
|
age(timestamp '1957-06-13') |
43 years 8 mons 3 days |
|
date_part('hour', timestamp '2001-02-16 20:38:40') |
20 |
|
date_part('month', interval '2 years 3 months') |
3 |
|
date_trunc('hour', timestamp '2001-02-16 20:38:40') |
2001-02-16 20:00:00 |
|
extract(hour from timestamp '2001-02-16 20:38:40') |
20 |
|
extract(month from interval '2 years 3 months') |
3 |
|
isfinite(timestamp '2001-02-16 21:28:30') |
true |
|
isfinite(interval '4 hours') |
true |
|
justify_days(interval '30 days') |
1 month |
|
justify_hours(interval '24 hours') |
1 day |
|
justify_interval(interval '1 mon -1 hour') |
29 days 23:00:00 |
PostgreSQL还支持SQL标准中的OVERLAPS运算符，它有两种使用方式：
（1）(start1, end1) OVERLAPS (start2, end2)
（2）(start1, length1) OVERLAPS (start2, length2)
如果两个时间段表示的时间范围有重叠的部分，上面的两个表达式返回真，否则返回假。例如：
（1）SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
(DATE '2001-10-30', DATE '2002-10-30');
结果为真
（2）SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
(DATE '2001-10-30', DATE '2002-10-30');
结果为假
将一个timesatamp类型的值与一个interval类型的值相加或相减的时候，两个数值的表示天的部分会直接相加或相减，不考虑夏时制的影响，但表示小时的部分相加或相减时可能会考虑夏时制的影响（要求会话的时区能够识别夏时制），所以有时候，interval '1 day'和 interval '24 hours'可能不等价。 假设会话的时区是CST7CDT，timestamp with time zone '2005-04-02 12:00-07' + interval '1 day'的结果是timestamp with time zone '2005-04-03 12:00-06'。但timestamp with time zone '2005-04-02 12:00-07'+ interval '24 hours'的结果却是timestamp with time zone '2005-04-03 13:00-06'，因为在时区CST7CDT中，在2005-04-03 02:00有夏时制调整。
7.9.1 函数EXTRACT和 date_part
EXTRACT(field FROM source)
extract函数从日期/时间数据中提取某个域的值，例如年、小时等。source的数据类型必须是timestamp、time或interval（date类型的表达式会被转化成timestamp类型，所以也可以使用）。field指定要选择的域的名称。函数返回值的类型是double precision。下面是合法的 field的名称：
（1）century
表示世纪。例如：
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
结果: 20
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
结果: 21
（2）day
表示天，取值是1到31。例如：
SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
结果: 16
（3）decade
表示10年，用年除以10。例如：
SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
结果: 200
（4）dow
一个星期中天的编号，取值从0到6，0表示星期天，6表示星期六。例如：
SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
结果: 5
（5）doy
一年中天的编号，取值从0到365/366。例如：
SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
结果: 47
（6）epoch
对于date和timestamp类型的数据，返回从 1970-01-01 00:00:00-00到现在经过的秒数（可以是负数）。对于interval类型的数据，返回这个interval包含的所有秒数。例如：
SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-08');
结果: 982384720
SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
结果: 442800
也可以将epoch值转换为timestamp类型的值。例如：
SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';
（7）hour
表示小时，取值范围从0到23。例如：
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
结果: 20
（8）isodow
表示一个星期中天的编号，取值范围从1到7，1表示星期一，7表示星期天。例如：
SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
结果: 7
（9）isoyear
表示年，以ISO 8601为标准。例如：
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01');
结果: 2005
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
结果: 2006
（10）microseconds
表示毫妙。例如：
SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
结果: 28500000
（11）millennium
表示千年。例如：
SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
结果: 3
第三个千年从2001年1月1日开始。
（12）milliseconds
表示微妙。例如：
SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
结果: 28500
（13）minute
表示分钟。取值范围0到59。例如：
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
结果: 38
（14）month
表示月。对于timestamp类型的值，返回一年中的月编号（1到12）。对于interval类型的值，用该值中包含的所有的月的个数对12取模，结果在0到11之间。例如：
SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
结果: 2
SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
结果: 3
SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
结果: 1
（15）quarter
表示季度。取值范围是1到4。例如：
SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
结果: 1
（16）second
表示秒，包括小数部分。取值范围一般是0到59, 如果操作系统支持闰秒（leap second），取值范围是0到60。例如：
SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
结果: 40
SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
结果: 28.5
（17）timezone
表示同UTC时区偏移值，以秒为单位。正数表示UTC东边的时区。负数表示UTC西边的时区。
（18）timezone_hour
时区偏移的小时部分。
（19）timezone_minute
时区偏移的分钟部分。
（20）week
一年中星期的编号（以ISO 8601为标准）。例如：
SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
结果: 7
（21）year
表示年。例如：
SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
结果: 2001
函数date_part的功能与extract类似，它的参数field的取值与extract一样，例如：
SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
结果: 16
SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
结果: 4
7.9.2 date_trunc函数
date_trunc 的作用类似于作用于数值类型数据的trunc函数。
date_trunc('field', source)
source是timestamp或interval类型的值表达式（date类型的值会被自动转换为timestamp类型的值，time类型的值会被自动转换为interval类型的值）。Field表示截断操作的精度。返回值的类型是timestamp或interval。进行截断操作时，被截断的域的类型如果是day或month，它的值被设为1，其它的被截断的域的值被设为0。filed的合法取值是：
|
microseconds |
|
milliseconds |
|
second |
|
minute |
|
hour |
|
day |
|
week |
|
month |
|
quarter |
|
year |
|
decade |
|
century |
|
millennium |
例如：
（1）SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
结果: 2001-02-16 20:00:00
（2）SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
结果: 2001-01-01 00:00:00
7.9.3 AT TIME ZONE
可以使用AT TIME ZONE 将timestamp 类型的值转换到不同的时区。表7-25列出了它的具体用法
表7-25. AT TIME ZONE 用法
|
表达式 |
返回值类型 |
描述 |
|
timestamp without time zone AT TIME ZONE zone |
timestamp with time zone |
将timestamp without time zone 类型的值作为指定的时区中的时间看待。
|
|
timestamp with time zone AT TIME ZONE zone |
timestamp without time zone |
将timestamp with time zone 类型的值转换到新的时区。 |
|
time with time zone AT TIME ZONE zone |
time with time zone |
将time with time zone 类型的值转换到新的时区。 |
可以用字符串来指定时区的类型，也可以用interval来指定时区的类型。例如，'PST' 和INTERVAL '-08:00'。
下面是一些实例 (假定本地的时区是PST8PDT)：
（1）SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
结果: 2001-02-16 19:38:40-08
（2）SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
结果: 2001-02-16 18:38:40
第一个例子将timestamp without time zone类型的值的时区看成时区为MST的值，然后在将这个值转换成时区为PST的值作为作为结果显示给用户。第二个例子将时区为UTC-5的值抓换成成时区为MST的值，然后再转换成时区为PST的值作为结果显示给用户。
函数timezone(zone, timestamp)的作用与timestamp AT TIME ZONE zone是一样的。
7.9.4 当前日期/时间
下列函数用来返回当前事务开始的日期和时间:
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME(precision)
CURRENT_TIMESTAMP(precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME(precision)
LOCALTIMESTAMP(precision)
now()
transaction_timestamp()
在同一个事务内多次调用同一个上面的函数，得到的结果都是一样的。
CURRENT_TIME 和CURRENT_TIMESTAMP 返回带时区的值， LOCALTIME 和LOCALTIMESTAMP返回不带时区的值。
CURRENT_TIME、 CURRENT_TIMESTAMP、 LOCALTIME和LOCALTIMESTAMP 可以带一个表示精度的小数，精度确定了秒的小数位的个数，如果不带精度，秒的精度就是系统的最大精度。
now()和transaction_timestamp()与CURRENT_TIME是等价的。
下面是一些例子：
（1）SELECT CURRENT_TIME;
结果: 14:39:53.662522-05
（2）SELECT CURRENT_DATE;
结果: 2001-12-23
（3）SELECT CURRENT_TIMESTAMP;
结果: 2001-12-23 14:39:53.662522-05
（4）SELECT CURRENT_TIMESTAMP(2);
结果: 2001-12-23 14:39:53.66-05
（5）SELECT LOCALTIMESTAMP;
结果: 2001-12-23 14:39:53.662522
statement_timestamp()返回当前语句开始的时间。
clock_timestamp()和timeofday()都返回当前的时间，但它们的返回值的类型不同，一个是字符串，一个是timestamp with time zone。
所有的日期/时间数据类型都接受常量now，它表示当前事务开始的时间。下面的三条命令返回的结果是一样的：
SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now';
7.9.5 pg_sleep 函数
使用函数pg_sleep让数据库服务进程睡眠指定的时间：
pg_sleep(seconds)
参数seconds的单位是秒，类型是double precision。例如：
SELECT pg_sleep(1.5);
7.10 枚举类型函数
表7-26列出了枚举类型的函数。表7-27列出了枚举类型的函数的实例。表中的例子都以下面的枚举类型为基础：
CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple');
表7-26. 枚举类型函数
|
函数名 |
描述 |
|
enum_first(anyenum) |
返回枚举类型的第一个值。 |
|
enum_last(anyenum) |
返回枚举类型的最后一个值。 |
|
enum_range(anyenum) |
返回枚举类型的所有值，这些值将按在枚举类型中出现的先后顺序在结果中被派序。 |
|
enum_range(anyenum, anyenum) |
返回两个给定的枚举类型值之间的所有枚举类型的值，这些值将按在枚举类型中出现的先后顺序在结果中被排序。如果第一个参数是NULL，则从枚举类型的第一个值开始计算。如果第二个参数是NULL，则结果一直渠道枚举类型的最后一个值。 |
表7-27. 枚举类型函数实例
|
例子 |
结果 |
|
enum_first(null::rainbow) |
red |
|
enum_last(null::rainbow) |
purple |
|
enum_range(null::rainbow) |
{red,orange,yellow,green,blue,purple} |
|
enum_range('orange'::rainbow, 'green'::rainbow) |
{orange,yellow,green} |
|
enum_range(NULL, 'green'::rainbow) |
{red,orange,yellow,green} |
|
enum_range('orange'::rainbow, NULL) |
{orange,yellow,green,blue,purple} |
注意，除了函数enum_range，其它函数不关心参数的值，只关心参数的类型，输入null或者某个枚举类型的值，得到的结果都是一样的。
7.11 几何函数和运算符
几何数据类型包括point、 box, 、lseg、 line、 path、 polygon和 circle 。表7-28、表7-29和表7-30列出了和这些类型相关的函数和运算符。
表7-28. 几何运算符
|
运算符 |
描述 |
例子 |
|
+ |
平移 |
box '((0,0),(1,1))' + point '(2.0,0)' |
|
- |
平移 |
box '((0,0),(1,1))' - point '(2.0,0)' |
|
* |
比例/旋转 |
box '((0,0),(1,1))' * point '(2.0,0)' |
|
/ |
比例/旋转 |
box '((0,0),(2,2))' / point '(2.0,0)' |
|
# |
相交的point或box |
'((1,-1),(-1,1))' # '((1,1),(-1,-1))' |
|
# |
路径或多边形中的点的数目 |
# '((1,0),(0,1),(-1,0))' |
|
@-@ |
周长 |
@-@ path '((0,0),(1,0))' |
|
@@ |
圆心 |
@@ circle '((0,0),10)' |
|
## |
第二个操作数上离第一个操作数最近的点 |
point '(0,0)' ## lseg '((2,0),(0,2))' |
|
<-> |
距离 |
circle '((0,0),1)' <-> circle '((5,0),1)' |
|
&& |
是否重叠? |
box '((0,0),(1,1))' && box '((0,0),(2,2))' |
|
<< |
是否严格在左边? |
circle '((0,0),1)' << circle '((5,0),1)' |
|
>> |
是否严格在右边? |
circle '((5,0),1)' >> circle '((0,0),1)' |
|
&< |
没有扩展到右边? |
box '((0,0),(1,1))' &< box '((0,0),(2,2))' |
|
&> |
没有扩展到左边? |
box '((0,0),(3,3))' &> box '((0,0),(2,2))' |
|
<<| |
是否严格在下面? |
box '((0,0),(3,3))' <<| box '((3,4),(5,5))' |
|
|>> |
是否严格在上面? |
box '((3,4),(5,5))' |>> box '((0,0),(3,3))' |
|
&<| |
没有扩展到上面? |
box '((0,0),(1,1))' &<| box '((0,0),(2,2))' |
|
|&> |
没有扩展到下面? |
box '((0,0),(3,3))' |&> box '((0,0),(2,2))' |
|
<^ |
在下面 (允许接触)? |
circle '((0,0),1)' <^ circle '((0,5),1)' |
|
>^ |
在上弄 (允许接触)? |
circle '((0,5),1)' >^ circle '((0,0),1)' |
|
?# |
相交? |
lseg '((-1,0),(1,0))' ?# box '((-2,-2),(2,2))' |
|
?- |
是否水平? |
?- lseg '((-1,0),(1,0))' |
|
?- |
是否水平对齐? |
point '(1,0)' ?- point '(0,0)' |
|
?| |
是否垂直? |
?| lseg '((-1,0),(1,0))' |
|
?| |
是否垂直对齐? |
point '(0,1)' ?| point '(0,0)' |
|
?-| |
两条线是否互相垂直? |
lseg '((0,0),(0,1))' ?-| lseg '((0,0),(1,0))' |
|
?|| |
是否平行? |
lseg '((-1,0),(1,0))' ?|| lseg '((-1,2),(1,2))' |
|
@> |
包含? |
circle '((0,0),2)' @> point '(1,1)' |
|
<@ |
在里面还是在外面? |
point '(1,1)' <@ circle '((0,0),2)' |
|
~= |
是否相同? |
polygon '((0,0),(1,1))' ~= polygon '((1,1),(0,0))' |
表7-29. 几何函数
|
函数名 |
返回值类型 |
描述 |
例子 |
|
area(object) |
double precision |
面积 |
area(box '((0,0),(1,1))') |
|
center(object) |
point |
中心 |
center(box '((0,0),(1,2))') |
|
diameter(circle) |
double precision |
圆的直径 |
diameter(circle '((0,0),2.0)') |
|
height(box) |
double precision |
box的垂直长度 |
height(box '((0,0),(1,1))') |
|
isclosed(path) |
boolean |
是否闭路? |
isclosed(path '((0,0),(1,1),(2,0))') |
|
isopen(path) |
boolean |
是否开路? |
isopen(path '[(0,0),(1,1),(2,0)]') |
|
length(object) |
double precision |
长度 |
length(path '((-1,0),(1,0))') |
|
npoints(path) |
int |
点的个数 |
npoints(path '[(0,0),(1,1),(2,0)]') |
|
npoints(polygon) |
int |
点的个数 |
npoints(polygon '((1,1),(0,0))') |
|
pclose(path) |
path |
将路径转换成闭路 |
pclose(path '[(0,0),(1,1),(2,0)]') |
|
popen(path) |
path |
将路径转换成开路 |
popen(path '((0,0),(1,1),(2,0))') |
|
radius(circle) |
double precision |
半径 |
radius(circle '((0,0),2.0)') |
|
width(box) |
double precision |
Box的水平长度 |
width(box '((0,0),(1,1))') |
表7-30. 几何类型转换函数
|
函数名 |
返回值类型 |
描述 |
例子 |
|
box(circle) |
box |
circle 到 box |
box(circle '((0,0),2.0)') |
|
box(point, point) |
box |
points 到 box |
box(point '(0,0)', point '(1,1)') |
|
box(polygon) |
box |
polygon 到 box |
box(polygon '((0,0),(1,1),(2,0))') |
|
circle(box) |
circle |
box 到circle |
circle(box '((0,0),(1,1))') |
|
circle(point, double precision) |
circle |
center 和 radius到 circle |
circle(point '(0,0)', 2.0) |
|
circle(polygon) |
circle |
polygon 到circle |
circle(polygon '((0,0),(1,1),(2,0))') |
|
lseg(box) |
lseg |
box diagonal 到line segment |
lseg(box '((-1,0),(1,0))') |
|
lseg(point, point) |
lseg |
points 到 line segment |
lseg(point '(-1,0)', point '(1,0)') |
|
path(polygon) |
point |
polygon 到 path |
path(polygon '((0,0),(1,1),(2,0))') |
|
point(double precision, double precision) |
point |
construct point |
point(23.4, -44.5) |
|
point(box) |
point |
Box的中心 |
point(box '((-1,0),(1,0))') |
|
point(circle) |
point |
圆心 |
point(circle '((0,0),2.0)') |
|
point(lseg) |
point |
line segment的中心 |
point(lseg '((-1,0),(1,0))') |
|
point(polygon) |
point |
Polygon的中心 |
point(polygon '((0,0),(1,1),(2,0))') |
|
polygon(box) |
polygon |
box 到 4-point polygon |
polygon(box '((0,0),(1,1))') |
|
polygon(circle) |
polygon |
circle 到 12-point polygon |
polygon(circle '((0,0),2.0)') |
|
polygon(npts, circle) |
polygon |
circle 到 npts-point polygon |
polygon(12, circle '((0,0),2.0)') |
|
polygon(path) |
polygon |
path 到polygon |
polygon(path '((0,0),(1,1),(2,0))') |
7.12 网络地址函数和运算符
表7-31列出了cidr和 inet运算符。运算符<<、 <<=、 >>和>>= 的作用是测试两个子网是否互相包含，它们只考虑两个地址的网络部分，忽略地址的主机部分。
表7-31. cidr 和inet运算符
|
运算符 |
描述 |
例子 |
|
< |
小于 |
inet '192.168.1.5' < inet '192.168.1.6' |
|
<= |
小于或等于 |
inet '192.168.1.5' <= inet '192.168.1.5' |
|
= |
相等 |
inet '192.168.1.5' = inet '192.168.1.5' |
|
>= |
大于或等于 |
inet '192.168.1.5' >= inet '192.168.1.5' |
|
> |
大于 |
inet '192.168.1.5' > inet '192.168.1.4' |
|
<> |
I不相等 |
inet '192.168.1.5' <> inet '192.168.1.4' |
|
<< |
被包含 |
inet '192.168.1.5' << inet '192.168.1/24' |
|
<<= |
被包含或相等 |
inet '192.168.1/24' <<= inet '192.168.1/24' |
|
>> |
包含 |
inet '192.168.1/24' >> inet '192.168.1.5' |
|
>>= |
包含或相等 |
inet '192.168.1/24' >>= inet '192.168.1/24' |
|
~ |
按位NOT |
~ inet '192.168.1.6' |
|
& |
按位AND |
inet '192.168.1.6' & inet '0.0.0.255' |
|
| |
按位OR |
inet '192.168.1.6' | inet '0.0.0.255' |
|
+ |
加 |
inet '192.168.1.6' + 25 |
|
- |
减 |
inet '192.168.1.43' - 36 |
|
- |
减 |
inet '192.168.1.43' - inet '192.168.1.19' |
表7-32列出了cidr和inet函数。函数host、text和abbrev的作用是提供不同的显示格式。
表7-32. cidr 和inet函数
|
函数名 |
返回值类型 |
描述 |
例子 |
结果 |
|
abbrev(inet) |
text |
缩写的显示格式 |
abbrev(inet '10.1.0.0/16') |
10.1.0.0/16 |
|
abbrev(cidr) |
text |
缩写的显示格式 |
abbrev(cidr '10.1.0.0/16') |
10.1/16 |
|
broadcast(inet) |
inet |
网络的广播地址 |
broadcast('192.168.1.5/24') |
192.168.1.255/24 |
|
family(inet) |
int |
获得IP地址的类型，4表示IPv4，6表示IPv6 |
family('::1') |
6 |
|
host(inet) |
text |
获得IP地址 |
host('192.168.1.5/24') |
192.168.1.5 |
|
hostmask(inet) |
inet |
构建网络的主机掩码 |
hostmask('192.168.23.20/30') |
0.0.0.3 |
|
masklen(inet) |
int |
获得掩码长度 |
masklen('192.168.1.5/24') |
24 |
|
netmask(inet) |
inet |
构建网络掩码 |
netmask('192.168.1.5/24') |
255.255.255.0 |
|
network(inet) |
cidr |
获得地址的网络部分 |
network('192.168.1.5/24') |
192.168.1.0/24 |
|
set_masklen(inet, int) |
inet |
为inet值设置掩码长度 |
set_masklen('192.168.1.5/24', 16) |
192.168.1.5/16 |
|
set_masklen(cidr, int) |
cidr |
为 cidr值设置掩码长度 |
set_masklen('192.168.1.0/24'::cidr, 16) |
192.168.0.0/16 |
|
text(inet) |
text |
获得IP地址和掩码长度 |
text(inet '192.168.1.5') |
192.168.1.5/32 |
任何cidr值都可以显示或隐式地被转换成inet值，所以上面的参数为inet类型的函数也可以接受cidr类型的参数。也可以将inet类型的值转换成cidr类型的值，进行转换时，inet地址中任何网络掩码右边的二进制位都会被设为0。也可以使用类型转换语法将字符串转换成inet或cidr类型。例如，inet(expression) or colname::cidr。
表7-33列出了类型macaddr的函数。函数trunc(macaddr) 将MAC地址的最后三个字节置为0，可以使用它来得到网卡生产厂商的编号。
表7-33. macaddr函数
|
函数名 |
返回值类型 |
描述 |
例子 |
结果 |
|
trunc(macaddr) |
macaddr |
将MAC地址的最后三个字节置为0 |
trunc(macaddr '12:34:56:78:90:ab') |
12:34:56:00:00:00 |
macaddr 类型也支持标准的比较运算符（>，<=等），排序的规则是按字典顺序。
7.13 XML 函数
7.13.1. 产生 XML 文档
可以使用下面的函数将sql查询的结果转换成xml文档。
7.13.1.1 xmlcomment
xmlcomment(text)
函数xmlcomment将字符串text转换成xml注释。Text中不能包含“--”，也不能以 “-” 结尾。如果参数为null，结果也为null。例如：
SELECT xmlcomment('hello');
xmlcomment
--------------
<!--hello-->
7.13.1.2 xmlconcat
xmlconcat(xml[, ...])
函数xmlconcat将一个或多个xml值连接起来形成一个单一的xml值，空值将被忽略。如果所有的参数都是空值，它将返回空值。例如：
SELECT xmlconcat('<abc/>', '<bar>foo</bar>');
xmlconcat
----------------------
<abc/><bar>foo</bar>
如果某个值中存在xml声明，将按如下规则来处理：
（1）如果所有的参数都含有同样xml版本声明，那么结果中将包含版本声明，否则结果中将不包含版本声明。
（2）如果所有参数都含有standalone声明，而且它的值是“yes”，结果中也会包含值为“yes”的standalone声明。其它情况下，结果中不会含有standalone声明。
（3）如果结果中需要一个standalone声明，但是参数中没有提供版本声明，结果中的版本声明的值将是1.0。
（4）参数中编码声明在任何情况下都会被忽略，结果中不会含有编码声明。
例如:
SELECT xmlconcat('<?xml version="1.1"?><foo/>', '<?xml version="1.1" standalone="no"?><bar/>');
xmlconcat
-----------------------------------
<?xml version="1.1"?><foo/><bar/>
7.13.1.3 xmlelement
xmlelement(name name [, xmlattributes(value [AS attname] [, ... ])] [, content, ...])
函数根据给定的名字、属性和内容产生一个xml元素。例如:
（1）SELECT xmlelement(name foo);
xmlelement
------------
<foo/>
（2）SELECT xmlelement(name foo, xmlattributes('xyz' as bar));
xmlelement
------------------
<foo bar="xyz"/>
（3）SELECT xmlelement(name foo, xmlattributes(current_date as bar), 'cont', 'ent');
xmlelement
-------------------------------------
<foo bar="2007-01-26">content</foo>
不是合法的xml名字和属性的字符将会用该字符对应的Unicode编码表示（十六进制），格式是_xHHHH_。例如:
SELECT xmlelement(name "foo$bar", xmlattributes('xyz' as "a&b"));
xmlelement
----------------------------------
<foo_x0024_bar a_x0026_b="xyz"/>
如果属性的值是一个列引用，则不需要指定属性的名字，系统默认使用列的名字作为属性的名字。其它的情况下需要指定属性的名字。例如，下面的例子是合法的：
CREATE TABLE test (a xml, b xml);
SELECT xmlelement(name test, xmlattributes(a, b)) FROM test;
下面的例子是不合法的：
SELECT xmlelement(name test, xmlattributes('constant'), a, b) FROM test;
SELECT xmlelement(name test, xmlattributes(func(a, b))) FROM test;
如果指定了文档的内容，将会把它转换成xml类型。如果文档内容的类型是xml，则可以构造复杂的xml文档。例如：
SELECT xmlelement(name foo, xmlattributes('xyz' as bar),
xmlelement(name abc),
xmlcomment('test'),
xmlelement(name xyz));
xmlelement
----------------------------------------------
<foo bar="xyz"><abc/><!--test--><xyz/></foo>
其它类型文档在被转换成xml类型时，字符<、>和 &将被转换成xml实体。bytea类型的数据将以base64或hex格式表示，选择哪种格式取决于参数xmlbinary的值。
7.13.1.4 xmlforest
xmlforest(content [AS name] [, ...])
函数xmlforest 根据给定的名字和内容产生一个xml森林。例如：
（1）SELECT xmlforest('abc' AS foo, 123 AS bar);
xmlforest
------------------------------
<foo>abc</foo><bar>123</bar>
（2）SELECT xmlforest(table_name, column_name) FROM information_schema.columns WHERE table_schema = 'pg_catalog';
xmlforest
-------------------------------------------------------------------------------------------
<table_name>pg_authid</table_name><column_name>rolname</column_name>
<table_name>pg_authid</table_name><column_name>rolsuper</column_name>
...
其它规则与xmlelement相同。
7.13.1.5 xmlpi
xmlpi(name target [, content])
函数xmlpi产生一个XML处理指令。如果指定了参数content，其中不能含有“?>”。例如：
SELECT xmlpi(name php, 'echo "hello world";');
xmlpi
-----------------------------
<?php echo "hello world";?>
7.13.1.6 xmlroot
xmlroot(xml, version text|no value [, standalone yes|no|no value])
函数xmlroot修改一个xml值的根节点。如果指定了版本，则用它替换参数xml的版本声明。如果指定了standalone值，则用它替换换参数xml的standalone声明。例如：
SELECT xmlroot(xmlparse(document '<?xml version="1.1"?><content>abc</content>'), version '1.0', standalone yes);
xmlroot
----------------------------------------
<?xml version="1.0" standalone="yes"?>
<content>abc</content>
7.13.1.7 xmlagg
xmlagg(xml)
函数xmlagg的作用类似xmlconcat,将多个xml值连接在一起，它是一个聚集函数。例如:
CREATE TABLE test (y int, x xml);
INSERT INTO test VALUES (1, '<foo>abc</foo>');
INSERT INTO test VALUES (2, '<bar/>');
SELECT xmlagg(x) FROM test;
xmlagg
----------------------
<foo>abc</foo><bar/>
注意在当前的实现中，各个xml值出现的顺序是不确定。如果想确定xml值的顺序，可以使用下面的命令：
SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab;
xmlagg
----------------------
<bar/><foo>abc</foo>
7.13.1.8 XML 谓词
xml IS DOCUMENT
如果参数的值是 XML文档，则表达式IS DOCUMENT返回真。如果参数是xml内容片段，则返回假。如果参数是NULL，则返回NULL。
7.13.2 处理 XML值
函数xpath用来处理xml值, 它处理Xpath表达式 ：
xpath(xpath, xml[, nsarray])
函数xpath对XML值xml使用XPath表达式xpath进行求值。它返回符合xpath的XML值。第三个参数是一个名字空间映射。它应该是一个二维数组，数组的第二个维的大小必须是2。例如:
SELECT xpath('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>', ARRAY[ARRAY['my', 'http://example.com']]);
xpath
--------
{test}
(1 row)
7.13.3 将表中的数据转换成XML格式
下面的函数用于将表中的数据转换成XML的格式，返回值的类型都是xml：
（1）table_to_xml(tbl regclass, nulls boolean, tableforest boolean, targetns text)
（2）query_to_xml(query text, nulls boolean, tableforest boolean, targetns text)
（3）cursor_to_xml(cursor refcursor, count int, nulls boolean, tableforest boolean, targetns text)
table_to_xml用于将数据库中的表中的数据转换成XML格式，参数tb1表示表的名称。query_to_xml执行一个查询然后将查询结果过转换成XML格式，参数query表示要执行的查询。cursor_to_xml从参数cursor指定的游标中取出count个数据行，然后将这些数据行转换成XML格式。
对于函数table_to_xml，如果参数tableforest的值是fasle，则结果的格式如下：
<tablename>
<row>
<columnname1>data</columnname1>
<columnname2>data</columnname2>
</row>
<row>
...
</row>
...
</tablename>
对于函数table_to_xml，如果参数tableforest的值是true，则结果的格式如下：
<tablename>
<columnname1>data</columnname1>
<columnname2>data</columnname2>
</tablename>
<tablename>
...
</tablename>
...
对于函数query_to_xml和cursor_to_xml，如果参数tableforest的值是fasle，则结果的格式如下：
<table>
< table >
<columnname1>data</columnname1>
<columnname2>data</columnname2>
</table >
<table >
...
</table>
...
</table>
对于函数query_to_xml和cursor_to_xml，如果参数tableforest的值是true，则结果的格式如下：
<row >
<columnname1>data</columnname1>
<columnname2>data</columnname2>
</row>
<row>
...
</row>
...
将表中的数据转换成XML类型时，规则同函数xmlelement一样。
参数nulls决定空值是否被包含在输出的结果中。如果它的值为假，空值将忽略，不会出现在输出的结果中。被如果它的值是真，列的空值用下面的形式表示：
<columnname xsi:nil="true"/>
其中的xsi是XML模式实例的名字空间前缀。
参数targetns决定结果的XML名字空间。如果不想指定任何名字空间，应该把它置为空串。
下面的函数返回XML模式文档，该模式文档描述上面的对应的数据映射函数产生的XML数据：
（1）table_to_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns text)
（2）query_to_xmlschema(query text, nulls boolean, tableforest boolean, targetns text)
（3）cursor_to_xmlschema(cursor refcursor, nulls boolean, tableforest boolean, targetns text)
下面的函数将表中的数据转换成xml格式，同时产生一个XML模式文档来描述这些 xml数据：
（1）table_to_xml_and_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns text)
（2）query_to_xml_and_xmlschema(query text, nulls boolean, tableforest boolean, targetns text)
下面的函数用于将一个模式中的所有的表中的数据转换成xml格式：
（1）schema_to_xml(schema name, nulls boolean, tableforest boolean, targetns text)
（2）schema_to_xmlschema(schema name, nulls boolean, tableforest boolean, targetns text)
（3）schema_to_xml_and_xmlschema(schema name, nulls boolean, tableforest boolean, targetns text)
下面的函数用于将一个数据库中的所有的表中的数据转换成xml格式：
（1）database_to_xml(nulls boolean, tableforest boolean, targetns text)
（2）database_to_xmlschema(nulls boolean, tableforest boolean, targetns text)
（3）database_to_xml_and_xmlschema(nulls boolean, tableforest boolean, targetns text)
上面的6个函数会产生大量的数据，会消耗大量的内存。一般情况下，不要使用它们，应该一个表一个表地单独转换，如果单个表中的数据量也比较大，应该使用游标来进行转换。
模式映射的结果如下:
<schemaname>
table1-mapping
table2-mapping
...
</schemaname>
数据库映射的结果如下:
<dbname>
<schema1name>
...
</schema1name>
<schema2name>
...
</schema2name>
...
</dbname>
下面列出了一个XSLT样式表，可以用它将table_to_xml_and_xmlschema输出的数据转换成HTML文档：
<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://www.w3.org/1999/xhtml"
>
<xsl:output method="xml"
doctype-system="http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"
doctype-public="-//W3C/DTD XHTML 1.0 Strict//EN"
indent="yes"/>
<xsl:template match="/*">
<xsl:variable name="schema" select="//xsd:schema"/>
<xsl:variable name="tabletypename"
select="$schema/xsd:element[@name=name(current())]/@type"/>
<xsl:variable name="rowtypename"
select="$schema/xsd:complexType[@name=$tabletypename]/xsd:sequence/xsd:element[@name='row']/@type"/>
<html>
<head>
<title><xsl:value-of select="name(current())"/></title>
</head>
<body>
<table>
<tr>
<xsl:for-each select="$schema/xsd:complexType[@name=$rowtypename]/xsd:sequence/xsd:element/@name">
<th><xsl:value-of select="."/></th>
</xsl:for-each>
</tr>
<xsl:for-each select="row">
<tr>
<xsl:for-each select="*">
<td><xsl:value-of select="."/></td>
</xsl:for-each>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>
