一、逻辑操作符
常用的逻辑操作符有：
AND
OR
NOT
SQL使用三值的逻辑体系。真、假和null，这是null代表“未知”。观察下面真值表：
| a | b | a AND b | a OR b |
|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE |
| TRUE | FALSE | FALSE | TRUE |
| TRUE | NULL | NULL | TRUE |
| FALSE | FALSE | FALSE | FALSE |
| FALSE | NULL | FALSE | NULL |
| NULL | NULL | NULL | NULL |
| a | NOT a |
|---|---|
| TRUE | FALSE |
| FALSE | TRUE |
| NULL | NULL |
操作符AND和OR都是可以交换的，也就是说，你可以交换左右操作数而不影响结果。
二、比较操作符
可用的比较操作符见下表。
比较操作符
| 操作符 | 描述 |
|---|---|
| < | 小于 |
| > | 大于 |
| <= | 小于或等于 |
| >= | 大于或等于 |
| = | 等于 |
| <>或!= | 不等于 |
注意: !=操作符在分析器阶段被转换成<>。 !=和<>操作符是完全等价的。
比较操作符可以用于所有相关的数据类型。所有比较操作符都是双目操作符， 返回boolean类型数值；像1 < 2 < 3 这样的表达式是非法的(因为布尔值和3之间不能做比较)。
除了比较操作符以外，我们还可以使用BETWEEN构造。
a BETWEEN x AND y
等价于
a >= x AND a <= y
注意BETWEEN认为端点值是包含在范围内的。NOT BETWEEN 做相反的比较：
a NOT BETWEEN x AND y
等价于
a < x OR a > y
BETWEEN SYMMETRIC和BETWEEN一样，只是没有要求 AND左边的参数小于或等于右边的参数。如果左面的参数不小于或等于右面的参数， 那么两个参数是自动交换的，所以非空范围总是适用。
要检查一个值是否为 NULL ，使用下面的构造：
expression IS NULL
expression IS NOT NULL或者等效，但并不标准的构造：
expression ISNULL
expression NOTNULL不要写expression = NULL 因为NULL是不"等于"NULL的。 NULL 代表一个未知的数值，因此我们无法知道两个未知的数值是否相等。这个行为遵循 SQL 标准。
TIPS:
有些应用可能要求表达式expression = NULL, 在expression为 NULL 时候返回真。 我们 强烈建议这样的应用修改成遵循 SQL 标准。但是，如果这样修改是不可能的， 那么我们可 以打开transform_null_equals配置参数， 让PostgreSQL将x = NULL 自动转换成x IS NULL。
NOTES:
如果expression是行值， 那么当行表达式本身为 NULL 或该行的所有字段都为 NULL 时，IS NULL将为真； 当行表达式本身不为 NULL 并且该行的所有字段都不为 NULL 时， IS NOT NULL 也将为真。因为这个行为，IS NULL和IS NOT NULL 并不总是为行值表达式 返回相反的值，也就是， 一个同时包含NULL和non-null值的行值表达式将在两种情况下都 返回false。 这个规定符合 SQL 标准，但是与PostgreSQL之前的版本不兼容。
如果有任何一个输入是 NULL ，那么普通的比较操作符生成 NULL(表示"未知")， 而不是true或false。例如，7 = NULL生成null，7 <> NULL 也生成null。当这种行为不适用时，使用IS [ NOT ] DISTINCT FROM构造：
expression IS DISTINCT FROM expression
expression IS NOT DISTINCT FROM expression对于非 NULL 的输入IS DISTINCT FROM与<> 操作符相同。但是，如果两个输入都是 NULL ，那么它将返回假；如果只有一个输入是 NULL ， 那么它将返回真。类似的，对于非 NULL 的输入IS NOT DISTINCT FROM 与=操作符相同。但是，如果两个输入都是 NULL ，那么它将返回真； 如果只有一个输入是 NULL ，那么它将返回假。这样就很有效地把 NULL 当作一个普通数据值看待， 而不是"未知"。
布尔数值可以用下面的构造进行测试
expression IS TRUE
expression IS NOT TRUE
expression IS FALSE
expression IS NOT FALSE
expression IS UNKNOWN
expression IS NOT UNKNOWN这些构造将总是返回真或假，从来不返回 NULL ，即使操作数是 NULL 也如此。 NULL 输入被当做逻辑数值"未知"。请注意实际上IS UNKNOWN 和IS NOT UNKNOWN分别与IS NULL和IS NOT NULL 相同，只是输入表达式必须是布尔类型。
三、数学函数和操作符
PostgreSQL为许多类型提供了数学操作符。 对于那些没有标准的数学传统的类型(比如日期/时间类型)， 我们在随后的章节里描述实际的行为。
下表显示了可用的数据操作符。
数学操作符
| 操作符 | 描述 | 例子 | 结果 |
|---|---|---|---|
| + | 加 | 2 + 3 | 5 |
| - | 减 | 2 - 3 | -1 |
| * | 乘 | 2 * 3 | 6 |
| / | 除(整数除法将截断结果) | 4 / 2 | 2 |
| % | 模(求余) | 5 % 4 | 1 |
| ^ | 幂(指数运算) | 2.0 ^ 3.0 | 8 |
| |/ | 平方根 | |/ 25.0 | 5 |
| ||/ | 立方根 | ||/ 27.0 | 3 |
| ! | 阶乘 | 5 ! | 120 |
| !! | 阶乘(前缀操作符) | !! 5 | 120 |
| @ | 绝对值 | @ -5.0 | 5 |
| & | 二进制 AND | 91 & 15 | 11 |
| | | 二进制 OR | 32 | 3 | 35 |
| # | 二进制 XOR | 17 # 5 | 20 |
| ~ | 二进制 NOT | ~1 | -2 |
| << | 二进制左移 | 1 << 4 | 16 |
| >> | 二进制右移 | 8 >> 2 | 2 |
位操作符只能用于整数类型，而其它的操作符可以用于全部数值类型。 位操作符还可以用于位串类型bit和bit varying。
下表显示了可用的数学函数。在该表中， dp表示double precision。 这些函数中有许多都有多种不同的形式，区别是参数不同。除非特别指明， 任何特定形式的函数都返回和它的参数相同的数据类型。处理double precision 数据的函数大多数是在宿主系统的C库的基础上实现的；因此， 精度和数值范围方面的行为是根据宿主系统而变化的。
数学函数
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
abs(x) | (与输入相同) | 绝对值 | abs(-17.4) | 17.4 |
cbrt(dp) | dp | 立方根 | cbrt(27.0) | 3 |
ceil(dp 或 numeric) | (与输入相同) | 不小于参数的最小的整数 | ceil(-42.8) | -42 |
ceiling(dp 或 numeric) | (与输入相同) | 不小于参数的最小整数(ceil的别名) | ceiling(-95.3) | -95 |
degrees(dp) | dp | 把弧度转为角度 | degrees(0.5) | 28.6478897565412 |
div(y numeric, x numeric) | numeric | integer quotient of y/x | div(9,4) | 2 |
exp(dp 或 numeric) | (与输入相同) | 自然指数 | exp(1.0) | 2.71828182845905 |
floor(dp 或 numeric) | (与输入相同) | 不大于参数的最大整数 | floor(-42.8) | -43 |
ln(dp 或 numeric) | (与输入相同) | 自然对数 | ln(2.0) | 0.693147180559945 |
log(dp 或 numeric) | (与输入相同) | 以 10 为底的对数 | log(100.0) | 2 |
log(b numeric, x numeric) | numeric | 以b为底数的对数 | log(2.0, 64.0) | 6.0000000000 |
mod(y, x) | (与参数类型相同) | y/x的余数(模) | mod(9,4) | 1 |
pi() | dp | "π" 常量 | pi() | 3.14159265358979 |
power(a dp, b dp) | dp | a的b次幂 | power(9.0, 3.0) | 729 |
power(a numeric, b numeric) | numeric | a的b次幂 | power(9.0, 3.0) | 729 |
radians(dp) | dp | 把角度转为弧度 | radians(45.0) | 0.785398163397448 |
random() | dp | 0.0 到 1.0 之间的随机数 | random() | |
round(dp 或 numeric) | (与输入相同) | 圆整为最接近的整数 | round(42.4) | 42 |
round(v numeric, s int) | numeric | 圆整为s 位小数 | round(42.4382, 2) | 42.44 |
setseed(dp) | void | 为随后的random()调用设置种子( -1.0 到 1.0 之间，包含) | setseed(0.54823) | |
sign(dp 或 numeric) | (与输入相同) | 参数的符号(-1, 0, +1) | sign(-8.4) | -1 |
sqrt(dp 或 numeric) | (与输入相同) | 平方根 | sqrt(2.0) | 1.4142135623731 |
trunc(dp 或 numeric) | (与输入相同) | 截断(向零靠近) | trunc(42.8) | 42 |
trunc(v numeric, s int) | numeric | 截断为s位小数 | trunc(42.4382, 2) | 42.43 |
width_bucket(op numeric, b1 numeric, b2 numeric, count int) | int | 返回一个桶，这个桶是在一个有count个桶， 上界为b1下界为b2的等深柱图中 operand将被赋予的那个桶。 | width_bucket(5.35, 0.024, 10.06, 5) | 3 |
width_bucket(op dp, b1 dp, b2 dp, count int) | int | 返回一个桶，这个桶是在一个有count个桶， 上界为b1下界为b2的等深柱图中 operand将被赋予的那个桶。 | width_bucket(5.35, 0.024, 10.06, 5) | 3 |
最后，下表显示了可用的三角函数。 所有三角函数都使用类型为double precision 的参数和返回类型。 三角函数参数用弧度来表达。反函数的返回值也是用弧度来表达的。 参阅上面的单元转换函数radians() 和degrees()。
三角函数
| 函数 | 描述 |
|---|---|
acos(x) | 反余弦 |
asin(x) | 反正弦 |
atan(x) | 反正切 |
atan2(y, x) | y/x的反正切 |
cos(x) | 余弦 |
cot(x) | 余切 |
sin(x) | 正弦 |
tan(x) | 正切 |
四、字符串函数和操作符
本节描述了用于检查和操作字符串数值的函数和操作符。 在这个环境中的字符串包括character,character varying, text类型的值。除非另外说明，所有下面列出的函数都可以处理这些类型， 不过要小心的是，在使用character类型的时候，需要注意自动填充的潜在影响。 有些函数还可以处理位串类型。
SQL定义了一些字符串函数，用特定的关键字而不是逗号来分隔参数。 详情请见表4-1。PostgreSQL 也提供了使用正常的函数调用语法实现的这些函数的版本(参阅 表4-2)。
NOTES:
在PostgreSQL 8.3之前， 这些函数将默默接受一些非字符串数据类型的值，由于存在从 这些数据类型到text 的隐式强制转换，转换后的它们经常发生意外的行为，因此删除了隐式强 制转换。 然而，字符串连接操作符(||)仍接受非字符串输入， 只要至少有一个输入是字符串类 型，如表4-2所示。 对于其它情况下，如果你需要重复以前的行为，插入一个明确的强制转 换到text。
表 4-1. SQL 字符串函数和操作符
| 函数 | 返回类型 | 描述 | 示例 | 结果 |
|---|---|---|---|---|
| string || string | text | 字符串连接 | 'Post' || 'greSQL' | PostgreSQL |
| string || non-string 或 non-string || string | text | 带有一个非字符串输入的字符串连接 | 'Value: ' || 42 | Value: 42 |
bit_length(string) | int | 字符串的位 | bit_length('jose') | 32 |
char_length(string) 或 character_length(string) | int | 字符串中的字符个数 | char_length('jose') | 4 |
lower(string) | text | 把字符串转化为小写 | lower('TOM') | tom |
octet_length(string) | int | 字符串中的字节数 | octet_length('jose') | 4 |
overlay(string placing string from int [for int]) | text | 替换子字符串 | overlay('Txxxxas' placing 'hom' from 2 for 4) | Thomas |
position(substring in string) | int | 指定子字符串的位置 | position('om' in 'Thomas') | 3 |
substring(string [from int] [for int]) | text | 截取子字符串 | substring('Thomas' from 2 for 3) | hom |
substring(string from pattern) | text | 截取匹配POSIX正则表达式的子字符串。参阅第七节获取更多关于模式匹配的信息。 | substring('Thomas' from '...$') | mas |
substring(string from pattern for escape) | text | 截取匹配SQL正则表达式的子字符串。 参阅第七节获取更多关于模式匹配的信息。 | substring('Thomas' from '%#"o_a#"_' for '#') | oma |
trim([leading | trailing | both] [characters] from string) | text | 从字符串string的开头/结尾/两边删除只包含 characters中字符 (缺省是空白)的最长的字符串 | trim(both 'x' from 'xTomxx') | Tom |
upper(string) | text | 把字符串转化为大写 | upper('tom') | TOM |
还有额外的字符串操作函数可以用，它们在表4-2列出。 它们有些在内部用于实现表4-1列出的SQL标准字符串函数。
表 4-2. 其它字符串函数
| 函数 | 返回类型 | 描述 | 示例 | 结果 |
|---|---|---|---|---|
ascii(string) | int | 参数中第一个字符的ASCII编码值。 对于UTF8返回字符的宽字节编码值。 对于其它的多字节编码，参数必须是一个ASCII字符。 | ascii('x') | 120 |
btrim(string text [, characters text]) | text | 从string开头和结尾删除只包含 characters中字符(缺省是空白)的最长字符串。 | btrim('xyxtrimyyx', 'xy') | trim |
chr(int) | text | 给定编码的字符。对于UTF8这个参数作为宽字节代码处理。 对于其它的多字节编码，这个参数必须指定一个ASCII字符， 因为text数据类型无法存储NULL数据字节，不能将NULL(0)作为字符参数。 | chr(65) | A |
concat(str "any" [, str "any" [, ...] ]) | text | 连接所有参数的文本表示。NULL 参数被忽略。 | concat('abcde', 2, NULL, 22) | abcde222 |
concat_ws(sep text, str "any" [, str "any" [, ...] ]) | text | 连接所有参数，但是第一个参数是分隔符，用于将所有参数分隔。NULL 参数被忽略。 | concat_ws(',', 'abcde', 2, NULL, 22) | abcde,2,22 |
convert(string bytea, src_encoding name, dest_encoding name) | bytea | 把原来编码为src_encoding的字符串转换为 dest_encoding编码。 在这种编码格式中string必须是有效的。 用CREATE CONVERSION定义转换。 这也有些预定义的转换。 参阅表4-3显示可用的转换。 | convert('text_in_utf8', 'UTF8', 'LATIN1') | text_in_utf8 用 Latin-1 编码表示 (ISO 8859-1) |
convert_from(string bytea, src_encoding name) | text | 把原来编码为src_encoding的字符串转换为数据库编码格式。 这种编码格式中，string必须是有效的。 | convert_from('text_in_utf8', 'UTF8') | text_in_utf8 用当前数据库编码表示 |
convert_to(string text, dest_encoding name) | bytea | 将字符串转化为dest_encoding编码格式。 | convert_to('some text', 'UTF8') | some text 用 UTF8 编码表示 |
decode(string text, format text) | bytea | 把用string表示的文本里面的二进制数据解码。 format选项和encode相同。 | decode('MTIzAAE=', 'base64') | \x3132330001 |
encode(data bytea, format text) | text | 把二进制数据编码为文本表示。支持的格式有：base64, hex, escape。escape 转换零字节和高位设置字节为八进制序列(\nnn) 和双反斜杠。 | encode(E'123\\000\\001', 'base64') | MTIzAAE= |
format(formatstr text [, formatarg "any" [, ...] ]) | text | 根据格式字符串格式参数。这个函数类似C函数sprintf。 参阅下面 4.1 格式化。 | format('Hello %s, %1$s', 'World') | Hello World, World |
initcap(string) | text | 把每个单词的第一个字母转为大写，其它的保留小写。 单词是一系列字母数字组成的字符，用非字母数字分隔。 | initcap('hi THOMAS') | Hi Thomas |
left(str text, n int) | text | 返回字符串的前n个字符。当n是负数时， 返回除最后|n|个字符以外的所有字符。 | left('abcde', 2) | ab |
length(string) | int | string中字符的数目 | length('jose') | 4 |
length(string bytea, encoding name ) | int | 指定encoding编码格式的string的字符数。 在这个编码格式中，string必须是有效的。 | length('jose', 'UTF8') | 4 |
lpad(string text, length int [, fill text]) | text | 通过填充字符fill(缺省时为空白)， 把string填充为length长度。 如果string已经比length长则将其尾部截断。 | lpad('hi', 5, 'xy') | xyxhi |
ltrim(string text [, characters text]) | text | 从字符串string的开头删除只包含characters 中字符(缺省是一个空白)的最长的字符串。 | ltrim('zzzytrim', 'xyz') | trim |
md5(string) | text | 计算string的MD5散列，以十六进制返回结果。 | md5('abc') | 900150983cd24fb0 d6963f7d28e17f72 |
pg_client_encoding() | name | 当前客户端编码名称 | pg_client_encoding() | SQL_ASCII |
quote_ident(string text) | text | 返回适用于SQL语句的标识符形式(使用适当的引号进行界定)。 只有在必要的时候才会添加引号(字符串包含非标识符字符或者会转换大小写的字符)。 嵌入的引号被恰当地写了双份。 | quote_ident('Foo bar') | "Foo bar" |
quote_literal(string text) | text | 返回适用于在SQL语句里当作文本使用的形式(使用适当的引号进行界定)。 嵌入的引号和反斜杠被恰当地写了双份。请注意，当输入是null时， quote_literal返回null；如果参数可能为null， 通常quote_nullable更适用。 | quote_literal(E'O\'Reilly') | 'O''Reilly' |
quote_literal(value anyelement) | text | 将给定的值强制转换为text，加上引号作为文本。嵌入的引号和反斜杠被恰当地写了双份。 | quote_literal(42.5) | '42.5' |
quote_nullable(string text) | text | 返回适用于在SQL语句里当作字符串使用的形式(使用适当的引号进行界定)。 或者，如果参数为空，返回NULL。嵌入的引号和反斜杠被恰当地写了双份。 | quote_nullable(NULL) | NULL |
quote_nullable(value anyelement) | text | 将给定的参数值转化为text，加上引号作为文本；或者，如果参数为空， 返回NULL。嵌入的引号和反斜杠被恰当地写了双份。 | quote_nullable(42.5) | '42.5' |
regexp_matches(string text, pattern text [, flags text]) | setof text[] | 返回string中所有匹配POSIX正则表达式的子字符串。 | regexp_matches('foobarbequebaz', '(bar)(beque)') | {bar,beque} |
regexp_replace(string text, pattern text, replacement text [, flags text]) | text | 替换匹配 POSIX 正则表达式的子字符串。 | regexp_replace('Thomas', '.[mN]a.', 'M') | ThM |
regexp_split_to_array(string text, pattern text [, flags text ]) | text[] | 用POSIX正则表达式作为分隔符，分隔string。 | regexp_split_to_array('hello world', E'\\s+') | {hello,world} |
regexp_split_to_table(string text, pattern text [, flags text]) | setof text | 用POSIX正则表达式作为分隔符，分隔string。 | regexp_split_to_table('hello world', E'\\s+') | hello world (2 rows) |
repeat(string text, number int) | text | 将string重复number次 | repeat('Pg', 4) | PgPgPgPg |
replace(string text, from text, to text) | text | 把字符串string里出现地所有子字符串from 替换成子字符串to | replace('abcdefabcdef', 'cd', 'XX') | abXXefabXXef |
reverse(str) | text | 返回颠倒的字符串 | reverse('abcde') | edcba |
right(str text, n int) | text | 返回字符串中的后n个字符。当n是负值时， 返回除前|n|个字符以外的所有字符。 | right('abcde', 2) | de |
rpad(string text, length int [, fill text]) | text | 使用填充字符fill(缺省时为空白)， 把string填充到length长度。 如果string已经比length长则将其从尾部截断。 | rpad('hi', 5, 'xy') | hixyx |
rtrim(string text [, characters text]) | text | 从字符串string的结尾删除只包含 characters中字符(缺省是个空白)的最长的字符串。 | rtrim('trimxxxx', 'x') | trim |
split_part(string text, delimiter text, field int) | text | 根据delimiter分隔string 返回生成的第 field 个子字符串(1为基)。 | split_part('abc~@~def~@~ghi', '~@~', 2) | def |
strpos(string, substring) | int | 指定的子字符串的位置。和position(substring in string)一样，不过参数顺序相反。 | strpos('high', 'ig') | 2 |
substr(string, from [, count]) | text | 抽取子字符串。和substring(string from from for count))一样 | substr('alphabet', 3, 2) | ph |
to_ascii(string text [, encoding text]) | text | 把string从其它编码转换为ASCII (仅支持LATIN1, LATIN2, LATIN9, WIN1250编码)。 | to_ascii('Karel') | Karel |
to_hex(number int or bigint) | text | 把number转换成十六进制表现形式 | to_hex(2147483647) | 7fffffff |
translate(string text, from text, to text) | text | 把在string中包含的任何匹配from 中字符的字符转化为对应的在to中的字符。 如果from比to长， 删掉在from中出现的额外的字符。 | translate('12345', '143', 'ax') | a2x5 |
concat, concat_ws和format 函数是可变的，所以用VARIADIC 关键字标记传递的数值以连接或者格式化为一个数组是可能的。数组的元素对函数来说是单独的普通参数。 如果可变数组的元素是NULL，那么concat和concat_ws 返回NULL，但是format把NULL作为零元素数组对待。
表 4-3. 内置的转换
| 转换名 [a] | 源编码 | 目的编码 |
|---|---|---|
| ascii_to_mic | SQL_ASCII | MULE_INTERNAL |
| ascii_to_utf8 | SQL_ASCII | UTF8 |
| big5_to_euc_tw | BIG5 | EUC_TW |
| big5_to_mic | BIG5 | MULE_INTERNAL |
| big5_to_utf8 | BIG5 | UTF8 |
| euc_cn_to_mic | EUC_CN | MULE_INTERNAL |
| euc_cn_to_utf8 | EUC_CN | UTF8 |
| euc_jp_to_mic | EUC_JP | MULE_INTERNAL |
| euc_jp_to_sjis | EUC_JP | SJIS |
| euc_jp_to_utf8 | EUC_JP | UTF8 |
| euc_kr_to_mic | EUC_KR | MULE_INTERNAL |
| euc_kr_to_utf8 | EUC_KR | UTF8 |
| euc_tw_to_big5 | EUC_TW | BIG5 |
| euc_tw_to_mic | EUC_TW | MULE_INTERNAL |
| euc_tw_to_utf8 | EUC_TW | UTF8 |
| gb18030_to_utf8 | GB18030 | UTF8 |
| gbk_to_utf8 | GBK | UTF8 |
| iso_8859_10_to_utf8 | LATIN6 | UTF8 |
| iso_8859_13_to_utf8 | LATIN7 | UTF8 |
| iso_8859_14_to_utf8 | LATIN8 | UTF8 |
| iso_8859_15_to_utf8 | LATIN9 | UTF8 |
| iso_8859_16_to_utf8 | LATIN10 | UTF8 |
| iso_8859_1_to_mic | LATIN1 | MULE_INTERNAL |
| iso_8859_1_to_utf8 | LATIN1 | UTF8 |
| iso_8859_2_to_mic | LATIN2 | MULE_INTERNAL |
| iso_8859_2_to_utf8 | LATIN2 | UTF8 |
| iso_8859_2_to_windows_1250 | LATIN2 | WIN1250 |
| iso_8859_3_to_mic | LATIN3 | MULE_INTERNAL |
| iso_8859_3_to_utf8 | LATIN3 | UTF8 |
| iso_8859_4_to_mic | LATIN4 | MULE_INTERNAL |
| iso_8859_4_to_utf8 | LATIN4 | UTF8 |
| iso_8859_5_to_koi8_r | ISO_8859_5 | KOI8R |
| iso_8859_5_to_mic | ISO_8859_5 | MULE_INTERNAL |
| iso_8859_5_to_utf8 | ISO_8859_5 | UTF8 |
| iso_8859_5_to_windows_1251 | ISO_8859_5 | WIN1251 |
| iso_8859_5_to_windows_866 | ISO_8859_5 | WIN866 |
| iso_8859_6_to_utf8 | ISO_8859_6 | UTF8 |
| iso_8859_7_to_utf8 | ISO_8859_7 | UTF8 |
| iso_8859_8_to_utf8 | ISO_8859_8 | UTF8 |
| iso_8859_9_to_utf8 | LATIN5 | UTF8 |
| johab_to_utf8 | JOHAB | UTF8 |
| koi8_r_to_iso_8859_5 | KOI8R | ISO_8859_5 |
| koi8_r_to_mic | KOI8R | MULE_INTERNAL |
| koi8_r_to_utf8 | KOI8R | UTF8 |
| koi8_r_to_windows_1251 | KOI8R | WIN1251 |
| koi8_r_to_windows_866 | KOI8R | WIN866 |
| koi8_u_to_utf8 | KOI8U | UTF8 |
| mic_to_ascii | MULE_INTERNAL | SQL_ASCII |
| mic_to_big5 | MULE_INTERNAL | BIG5 |
| mic_to_euc_cn | MULE_INTERNAL | EUC_CN |
| mic_to_euc_jp | MULE_INTERNAL | EUC_JP |
| mic_to_euc_kr | MULE_INTERNAL | EUC_KR |
| mic_to_euc_tw | MULE_INTERNAL | EUC_TW |
| mic_to_iso_8859_1 | MULE_INTERNAL | LATIN1 |
| mic_to_iso_8859_2 | MULE_INTERNAL | LATIN2 |
| mic_to_iso_8859_3 | MULE_INTERNAL | LATIN3 |
| mic_to_iso_8859_4 | MULE_INTERNAL | LATIN4 |
| mic_to_iso_8859_5 | MULE_INTERNAL | ISO_8859_5 |
| mic_to_koi8_r | MULE_INTERNAL | KOI8R |
| mic_to_sjis | MULE_INTERNAL | SJIS |
| mic_to_windows_1250 | MULE_INTERNAL | WIN1250 |
| mic_to_windows_1251 | MULE_INTERNAL | WIN1251 |
| mic_to_windows_866 | MULE_INTERNAL | WIN866 |
| sjis_to_euc_jp | SJIS | EUC_JP |
| sjis_to_mic | SJIS | MULE_INTERNAL |
| sjis_to_utf8 | SJIS | UTF8 |
| tcvn_to_utf8 | WIN1258 | UTF8 |
| uhc_to_utf8 | UHC | UTF8 |
| utf8_to_ascii | UTF8 | SQL_ASCII |
| utf8_to_big5 | UTF8 | BIG5 |
| utf8_to_euc_cn | UTF8 | EUC_CN |
| utf8_to_euc_jp | UTF8 | EUC_JP |
| utf8_to_euc_kr | UTF8 | EUC_KR |
| utf8_to_euc_tw | UTF8 | EUC_TW |
| utf8_to_gb18030 | UTF8 | GB18030 |
| utf8_to_gbk | UTF8 | GBK |
| utf8_to_iso_8859_1 | UTF8 | LATIN1 |
| utf8_to_iso_8859_10 | UTF8 | LATIN6 |
| utf8_to_iso_8859_13 | UTF8 | LATIN7 |
| utf8_to_iso_8859_14 | UTF8 | LATIN8 |
| utf8_to_iso_8859_15 | UTF8 | LATIN9 |
| utf8_to_iso_8859_16 | UTF8 | LATIN10 |
| utf8_to_iso_8859_2 | UTF8 | LATIN2 |
| utf8_to_iso_8859_3 | UTF8 | LATIN3 |
| utf8_to_iso_8859_4 | UTF8 | LATIN4 |
| utf8_to_iso_8859_5 | UTF8 | ISO_8859_5 |
| utf8_to_iso_8859_6 | UTF8 | ISO_8859_6 |
| utf8_to_iso_8859_7 | UTF8 | ISO_8859_7 |
| utf8_to_iso_8859_8 | UTF8 | ISO_8859_8 |
| utf8_to_iso_8859_9 | UTF8 | LATIN5 |
| utf8_to_johab | UTF8 | JOHAB |
| utf8_to_koi8_r | UTF8 | KOI8R |
| utf8_to_koi8_u | UTF8 | KOI8U |
| utf8_to_sjis | UTF8 | SJIS |
| utf8_to_tcvn | UTF8 | WIN1258 |
| utf8_to_uhc | UTF8 | UHC |
| utf8_to_windows_1250 | UTF8 | WIN1250 |
| utf8_to_windows_1251 | UTF8 | WIN1251 |
| utf8_to_windows_1252 | UTF8 | WIN1252 |
| utf8_to_windows_1253 | UTF8 | WIN1253 |
| utf8_to_windows_1254 | UTF8 | WIN1254 |
| utf8_to_windows_1255 | UTF8 | WIN1255 |
| utf8_to_windows_1256 | UTF8 | WIN1256 |
| utf8_to_windows_1257 | UTF8 | WIN1257 |
| utf8_to_windows_866 | UTF8 | WIN866 |
| utf8_to_windows_874 | UTF8 | WIN874 |
| windows_1250_to_iso_8859_2 | WIN1250 | LATIN2 |
| windows_1250_to_mic | WIN1250 | MULE_INTERNAL |
| windows_1250_to_utf8 | WIN1250 | UTF8 |
| windows_1251_to_iso_8859_5 | WIN1251 | ISO_8859_5 |
| windows_1251_to_koi8_r | WIN1251 | KOI8R |
| windows_1251_to_mic | WIN1251 | MULE_INTERNAL |
| windows_1251_to_utf8 | WIN1251 | UTF8 |
| windows_1251_to_windows_866 | WIN1251 | WIN866 |
| windows_1252_to_utf8 | WIN1252 | UTF8 |
| windows_1256_to_utf8 | WIN1256 | UTF8 |
| windows_866_to_iso_8859_5 | WIN866 | ISO_8859_5 |
| windows_866_to_koi8_r | WIN866 | KOI8R |
| windows_866_to_mic | WIN866 | MULE_INTERNAL |
| windows_866_to_utf8 | WIN866 | UTF8 |
| windows_866_to_windows_1251 | WIN866 | WIN |
| windows_874_to_utf8 | WIN874 | UTF8 |
| euc_jis_2004_to_utf8 | EUC_JIS_2004 | UTF8 |
| utf8_to_euc_jis_2004 | UTF8 | EUC_JIS_2004 |
| shift_jis_2004_to_utf8 | SHIFT_JIS_2004 | UTF8 |
| utf8_to_shift_jis_2004 | UTF8 | SHIFT_JIS_2004 |
| euc_jis_2004_to_shift_jis_2004 | EUC_JIS_2004 | SHIFT_JIS_2004 |
| shift_jis_2004_to_euc_jis_2004 | SHIFT_JIS_2004 | EUC_JIS_2004 |
| 表注: a. 转换名遵循一个标准的命名模式：将源编码中的所有非字母数字字符用下划线替换， 后面跟着_to_，然后后面再跟着经过相似处理的目标编码的名字。 因此这些名字可能和客户的编码名字不同。 | ||
4.1 格式化
函数format生成根据格式字符串格式化了的输出，风格类似于C函数sprintf。
format(formatstr text [, formatarg "any" [, ...] ])
formatstr是指定结果如何格式化的格式字符串。格式字符串中的文本直接拷贝到结果中， 除非已经使用了格式说明符。格式说明符在字符串中作为占位符使用， 定义后续函数参数应该格式化并且插入到结果中。每个formatarg 参数根据这种数据类型的通常输出规则转化为文本，然后根据格式说明符格式化并且插入到结果中。
格式说明符由%字符引进，格式为
%[position][flags][width]type
组件的字段有：
①：position (optional)
n$格式的字符串，这里的n 是要打印的参数的索引。索引为1表示在formatstr 之后的第一个参数。如果省略了formatstr， 默认使用序列中的下一个参数。
②：flags (optional)
附加选项，控制如何格式化格式说明符的输出。当前只支持负号(-)， 负号导致格式说明符的输出是左对齐的。这是没有影响的，除非指定了 width字段。
③：width (optional)
声明字符数的minimum值用来显示格式说明符的输出。需要补充宽度时， 空白添加到输出的左侧或右侧（取决于-标志）。一个比较小的宽度不会导致输出的截断， 只是简单的忽略了。宽度可以用下列方法指定：一个正整数；一个星号(*) 表示使用下一个函数参数作为宽度；或一个格式为*n$ 的字符串表示使用第n个函数参数作为宽度。
如果宽度来自函数参数，那么这个参数在作为格式说明符的数值之前消耗掉。 如果宽度参数是负的，那么结果是左对齐的（就像声明了-标志一样）， 并且字段长度为abs(width)。
④：type (required)
格式转换的类型用来产生格式说明符的输出。支持下列的类型：
s格式参数值为简单的字符串。空值作为空字符串对待。
I将参数值作为SQL标识符对待，如果需要，双写它。值为空是错误的。
L引用参数值作为SQL文字。空值用字符串NULL显示，没有引用。
除了上述的格式说明符，特殊的序列%%可以用作输出%字符。
这里有一些基本的格式转换的例子：
SELECT format('Hello %s', 'World');
Result: Hello World
SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');
Result: Testing one, two, three, %
SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');
Result: INSERT INTO "Foo bar" VALUES('O''Reilly')
SELECT format('INSERT INTO %I VALUES(%L)', 'locations', E'C:\\Program Files');
Result: INSERT INTO locations VALUES(E'C:\\Program Files')这里是使用width字段和-标志的例子：
SELECT format('|%10s|', 'foo');
Result: | foo|
SELECT format('|%-10s|', 'foo');
Result: |foo |
SELECT format('|%*s|', 10, 'foo');
Result: | foo|
SELECT format('|%*s|', -10, 'foo');
Result: |foo |
SELECT format('|%-*s|', 10, 'foo');
Result: |foo |
SELECT format('|%-*s|', -10, 'foo');
Result: |foo |下面是使用position字段的例子：
SELECT format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three');
Result: Testing three, two, one
SELECT format('|%*2$s|', 'foo', 10, 'bar');
Result: | bar|
SELECT format('|%1$*2$s|', 'foo', 10, 'bar');
Result: | foo|和C函数sprintf不同，PostgreSQL的format 函数允许带有或不带有position字段的格式说明符在相同的格式字符串中混合使用。 没有position字段的格式说明符总是使用最后消耗参数的下一个参数。 另外，format函数不要求在格式字符串中使用所有函数参数。例如：
SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
Result: Testing three, two, three%I和%L格式说明符对于安全构造动态SQL语句尤其有用。
五、二进制字符串函数和操作符
本节描述那些检查和操作类型为bytea数值的函数和操作符。
SQL定义了一些字符串函数， 在这些函数里使用关键字而不是逗号来分隔参数。 详情请见 表5-1。 PostgreSQL也提供了使用常用语法进行函数调用的函数的版本 (参阅 表5-2)。
NOTES:
本页面例子的结果在假设服务器的参数 bytea_output 设置为escape的基础上的（传统的PostgreSQL格式）。
表 5-1. SQL 二进制字符串函数和操作符
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
| string || string | bytea | 字符串连接 | E'\\\\Post'::bytea || E'\\047gres\\000'::bytea | \\Post'gres\000 |
octet_length(string) | int | 二进制字符串中的字节数 | octet_length(E'jo\\000se'::bytea) | 5 |
overlay(string placing string from int [for int]) | bytea | 替换子串 | overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 2 for 3) | T\\002\\003mas |
position(substring in string) | int | 特定子字符串的位置 | position(E'\\000om'::bytea in E'Th\\000omas'::bytea) | 3 |
substring(string [from int] [for int]) | bytea | 截取子串 | substring(E'Th\\000omas'::bytea from 2 for 3) | h\000o |
trim([both] bytes from string) | bytea | 从string的开头和结尾删除只包含 bytes中字节的最长字符串 | trim(E'\\000'::bytea from E'\\000Tom\\000'::bytea) | Tom |
还有一些二进制字符串处理函数可以使用，在 表5-2 列出。其中有一些是在内部使用，用于实现表5-1 列出的SQL标准的字符串函数。
表 5-2. 其它二进制字符串函数
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
btrim(string bytea, bytes bytea) | bytea | 从string的开头和结尾删除只包含bytes 中字节的最长的字符串 | btrim(E'\\000trim\\000'::bytea, E'\\000'::bytea) | trim |
decode(string text, format text) | bytea | 把string中的文本表示解码为二进制数据。format 的选项和encode相同。 | decode(E'123\\000456', 'escape') | 123\000456 |
encode(data bytea, format text) | text | 把二进制数据编码为文本表现形式。支持的格式：base64, hex, escape。escape 转换零字节和高位设置字节为八进制序列(\nnn) 和双写反斜杠。 | encode(E'123\\000456'::bytea, 'escape') | 123\000456 |
get_bit(string, offset) | int | 从字符串中抽取位 | get_bit(E'Th\\000omas'::bytea, 45) | 1 |
get_byte(string, offset) | int | 从字符串中抽取字节 | get_byte(E'Th\\000omas'::bytea, 4) | 109 |
length(string) | int | 二进制字符串的长度 | length(E'jo\\000se'::bytea) | 5 |
md5(string) | text | 计算string的MD5散列值，以十六进制方式返回结果。 | md5(E'Th\\000omas'::bytea) | 8ab2d3c9689aaf18 b4958c334c82d8b1 |
set_bit(string, offset, newvalue) | bytea | 设置字符串中的位 | set_bit(E'Th\\000omas'::bytea, 45, 0) | Th\000omAs |
set_byte(string, offset, newvalue) | bytea | 设置字符串中的字节 | set_byte(E'Th\\000omas'::bytea, 4, 64) | Th\000o@as |
get_byte和set_byte数以二进制字符串的第一个字节为0字节。 get_bit和set_bit从每个字节的右边取位； 例如位0是第一个字节的最低位，位15是第二个字节的最高位。
六、位串函数和操作符
本节描述用于检查和操作位串的函数和操作符，也就是操作类型为bit 和bit varying值的函数和操作符。除了常用的比较操作符之外， 还可以使用 表6-1 里显示的操作符。 &, |,# 的位串操作数必须等长。在移位的时候，保留原始的位串长度(并以 0 填充)，如例子所示。
表6-1. 位串操作符
| 操作符 | 描述 | 例子 | 结果 |
|---|---|---|---|
| || | 连接 | B'10001' || B'011' | 10001011 |
| & | 位与 | B'10001' & B'01101' | 00001 |
| | | 位或 | B'10001' | B'01101' | 11101 |
| # | 位异或 | B'10001' # B'01101' | 11100 |
| ~ | 位非 | ~ B'10001' | 01110 |
| << | 位左移 | B'10001' << 3 | 01000 |
| >> | 位右移 | B'10001' >> 2 | 00100 |
下面的SQL标准函数除了可以用于字符串之外，也可以用于位串： length, bit_length, octet_length, position, substring, overlay。
下面的函数用于位串和二进制字符串：get_bit， set_bit。当用于位串时， 这些函数位数从字符串的第一位（最左边）作为0位 。
另外，我们可以在整数和bit之间来回转换。例子：
44::bit(10) 0000101100
44::bit(3) 100
cast(-44 as bit(12)) 111111010100
'1110'::bit(4)::integer 14请注意，只是转换为"bit"的意思是转换成bit(1)，因此只会转换成整数的最低位。
注意: 在PostgreSQL 8.0以前，把一个整数转换成bit(n) 将拷贝整数的最左边的n位，而现在是拷贝最右边的n位。 还有，把一个整数转换成比整数本身长的位串，就会扩展最左边的位(非负数为 0 ，负数为 1)。
七、模式匹配
PostgreSQL提供了三种实现模式匹配的方法： 传统SQL的LIKE操作符、SQL99 新增的 SIMILAR TO操作符、POSIX风格的正则表达式。 除了基本的"这个字符串匹配这个模式"操作符之外， 也可以使用函数抽取或替换匹配的子字符串并且在匹配的位置分隔字符串。
提示: 如果你的模式匹配要求比这些还多，请考虑用 Perl 或 Tcl 写一个用户定义函数。
7.1 LIKE
string LIKE pattern [ESCAPE escape-character]
string NOT LIKE pattern [ESCAPE escape-character]如果该string匹配提供的pattern， 那么LIKE表达式返回真。和我们想像的一样，如果LIKE 返回真，那么NOT LIKE表达式将返回假，反之亦然。 一个等效的表达式是NOT (string LIKE pattern)。
如果pattern不包含百分号或者下划线，那么该模式只代表它本身； 这时候LIKE的行为就像等号操作符。在pattern 里的下划线(_)匹配任何单个字符；而一个百分号(%) 匹配零或多个任何序列。
一些例子：
'abc' LIKE 'abc' true
'abc' LIKE 'a%' true
'abc' LIKE '_b_' true
'abc' LIKE 'c' falseLIKE模式匹配总是覆盖整个字符串。因此， 如果想要匹配在字符串内部任何位置的序列，该模式必须以百分号开头和结尾。
要匹配下划线或者百分号本身，在pattern 里相应的字符必须前导转义字符。缺省的转义字符是反斜杠， 但是你可以用ESCAPE子句指定一个。要匹配转义字符本身， 写两个转义字符。
注意: 如果你关闭了standard_conforming_strings选项， 那么在文本字符串常量里的任意反斜杠都需要双写。
我们也可以通过写成ESCAPE ''的方式关闭转义机制， 这时，我们就不能关闭下划线和百分号的特殊含义。
关键字ILIKE可以用于替换LIKE， 令该匹配就当前的区域设置是大小写无关的。这个特性不是SQL标准， 是PostgreSQL扩展。
操作符~~等效于LIKE，而~~* 等效于ILIKE。还有!~~和!~~* 操作符分别代表NOT LIKE和NOT ILIKE。 所有这些操作符都是PostgreSQL特有的。
7.2 SIMILAR TO 正则表达式
string SIMILAR TO pattern [ESCAPE escape-character]
string NOT SIMILAR TO pattern [ESCAPE escape-character]SIMILAR TO根据自己的模式是否匹配给定字符串而返回真或者假。 它和LIKE非常类似，只不过它使用 SQL 标准定义的正则表达式理解模式。 SQL 标准的正则表达式是在LIKE 表示法和普通的正则表达式表示法之间古怪的交叉。
类似LIKE，SIMILAR TO 操作符只有在它的模式匹配整个字符串的时候才能成功； 这一点和普通的正则表达式的行为不同，在普通的正则表达式里， 模式匹配字符串的任意部分。和LIKE类似的地方还有 SIMILAR TO使用_和% 分别匹配单个字符和任意字符串(这些和 POSIX 正则表达式里的. 和.*兼容)。
除了这些从LIKE借用的功能之外，SIMILAR TO 支持下面这些从 POSIX 正则表达式借用的模式匹配元字符：
-
|表示选择(两个候选之一)
-
*表示重复前面的项零次或更多次
-
+表示重复前面的项一次或更多次
-
?表示重复前面的项零次或一次
-
{m}表示重复前面的项正好m次
-
{m,}表示重复前面的项m或更多次
-
{m,n} 表示重复前面的项至少m次，最多不超过n次
-
Parentheses ()把项组合成一个逻辑项
-
[...] 声明一个字符类，只在POSIX正则表达式中
请注意点(.)对于SIMILAR TO来说不是元字符。
和LIKE一样，反斜杠关闭所有这些元字符的特殊含义； 当然我们也可以用ESCAPE声明另外一个转义字符。
一些例子：
'abc' SIMILAR TO 'abc' true
'abc' SIMILAR TO 'a' false
'abc' SIMILAR TO '%(b|d)%' true
'abc' SIMILAR TO '(b|c)%' false带三个参数的substring(string from pattern for escape-character) 函数提供了一个从字符串中抽取一个匹配 SQL 正则表达式模式的子字符串功能。 和SIMILAR TO一样，声明的模式必须匹配整个字符串， 否则函数失效并返回 NULL 。为了标识在成功的时候应该返回的模式部分， 模式必须出现后跟双引号(")的两个转义字符。 匹配这两个标记之间的模式的字符串将被返回。
一些例子，以#"分隔返回的字符串：
substring('foobar' from '%#"o_b#"%' for '#') oob
substring('foobar' from '#"o_b#"%' for '#') NULL7.3 POSIX正则表达式
表7-3-1列出了所有用于 POSIX 正则表达式的操作符。
表7-3-1 正则表达式匹配操作符
| 操作符 | 描述 | 例子 |
|---|---|---|
| ~ | 匹配正则表达式，大小写相关 | 'thomas' ~ '.*thomas.*' |
| ~* | 匹配正则表达式，大小写无关 | 'thomas' ~* '.*Thomas.*' |
| !~ | 不匹配正则表达式，大小写相关 | 'thomas' !~ '.*Thomas.*' |
| !~* | 不匹配正则表达式，大小写无关 | 'thomas' !~* '.*vadim.*' |
POSIX正则表达式提供了比LIKE和SIMILAR TO 操作符更强大的模式匹配的方法。许多 Unix 工具，比如egrep, sed,awk使用类似的模式匹配语言。
正则表达式是一个字符序列，它是定义一个字符串集合(一个正则集合 )的缩写。如果一个字符串是正则表达式描述的正则集合中的一员时， 我们就说这个字符串匹配该正则表达式。和LIKE一样， 模式字符准确地匹配字符串字符，除非在正则表达式语言里有特殊字符 (不过正则表达式用的特殊字符和LIKE用的不同)。 和LIKE不一样的是，正则表达式可以匹配字符串里的任何位置， 除非该正则表达式明确地锚定在字符串的开头或者结尾。
一些例子：
'abc' ~ 'abc' true
'abc' ~ '^a' true
'abc' ~ '(b|d)' true
'abc' ~ '^(b|c)' falsePOSIX模式语言将在下面详细描述。
带两个参数的substring(string from pattern) 函数提供了从字符串中抽取一个匹配 POSIX 正则表达式模式的子字符串的方法。 如果没有匹配它返回 NULL ，否则就是文本中匹配模式的那部分。 但是如果该模式包含任何圆括弧，那么将返回匹配第一对子表达式(对应第一个左圆括弧的)的文本。 如果你想在表达式里使用圆括弧而又不想导致这个例外， 那么你可以在整个表达式外边放上一对圆括弧。如果你需要在想抽取的子表达式前有圆括弧， 参阅描述的非捕获性圆括弧。
一些例子：
substring('foobar' from 'o.b') oob
substring('foobar' from 'o(.)b') oregexp_replace(source, pattern, replacement [, flags ])函数提供了将匹配 POSIX 正则表达式模式的子字符串替换为新文本的功能。如果没有匹配 pattern 的子字符串， 那么返回不加修改的source字符串。如果有匹配， 则返回的 source字符串里面的对应子字符串将被replacement 字符串替换掉。replacement字符串可以包含\n， 这里的n是 1 到 9 ，表明源字符串中匹配第n 个圆括弧子表达式的部分将插入在该位置，并且它可以包含\& 表示应该插入匹配整个模式的字符串。如果你需要放一个文本反斜杠在替换文本里， 那么写\\。可选的flags 参数包含零个或多个改变函数行为的单字母标记。i表示进行大小写无关的匹配， g表示替换每一个匹配的子字符串而不仅仅是第一个。其他支持的标记在 表7-3-9 中描述。
一些例子：
regexp_replace('foobarbaz', 'b..', 'X')
fooXbaz
regexp_replace('foobarbaz', 'b..', 'X', 'g')
fooXX
regexp_replace('foobarbaz', 'b(..)', E'X\\1Y', 'g')
fooXarYXazYregexp_matches(string, pattern[, flags ])函数返回一个从匹配POSIX正则表达式模式中获取的所有子串结果的text数组。 这个函数可以返回零行，一行，或者多行（参阅下面的g标记）。如果pattern 没有匹配，则函数返回零行。如果模式包含没有括号的子表达式，则每行返回的是单元素的文本数组， 其中包含的子串相匹配整个模式。如果模式包含有括号的子表达式，函数返回一个文本数组，它的第 n个元素是子串匹配模式括号子表达式内的第n个元素。 （不计"非捕获"的括号；详细信息参阅下面）。参数flags是一个可选的text字符串， 含有0或者更多单字母标记来改变函数行为。标记g导致查找字符串中的每个匹配，而不仅是第一个， 每个匹配返回一行，其它支持的标记在 表7-3-9 里描述。
一些例子：
SELECT regexp_matches('foobarbequebaz', '(bar)(beque)');
regexp_matches
----------------
{bar,beque}
(1 row)
SELECT regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g');
regexp_matches
----------------
{bar,beque}
{bazil,barf}
(2 rows)
SELECT regexp_matches('foobarbequebaz', 'barbeque');
regexp_matches
----------------
{barbeque}
(1 row)使用select子句，可能强制regexp_matches()总是返回一行； 当你想要返回SELECT目标列表中的所有行，甚至没有匹配的情况下，是有特别有用的。
SELECT col1, (SELECT regexp_matches(col2, '(bar)(beque)')) FROM tab;
regexp_split_to_table(string, pattern[, flags ])函数使用POSIX正则表达式模式作为分隔符，分隔字符串。 如果没有匹配pattern，函数将返回string。如果有至少一个匹配， 每个匹配返回从最后一个匹配结束（或者字符串的开头）到匹配开始的文本。当没有更多的匹配， 返回最后一个匹配的结束到字符串的结束的文本。flags参数是一个可选text字符串， 含有0或者更多单字母标记来改变函数行为。regexp_split_to_table 支持的标记在 表7-3-9 里描述。
除了regexp_split_to_array返回结果为text数组，regexp_split_to_array 函数行为与regexp_split_to_table相同，使用语法regexp_split_to_array (string, pattern[, flags ])。 参数与regexp_split_to_table相同。
一些例子：
SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', E'\\s+') AS foo;
foo
-------
the
quick
brown
fox
jumps
over
the
lazy
dog
(9 rows)
SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', E'\\s+');
regexp_split_to_array
-----------------------------------------------
{the,quick,brown,fox,jumps,over,the,lazy,dog}
(1 row)
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)作为最后一个例子表明，发生在字符串的开始或结束或紧接前一个的匹配，regexp分隔函数忽略零长度匹配， 这样实现regexp_matches严格上来说是违背了的正则表达式匹配的定义，但在实际使用中， 通常是最便利的的行为。如Perl等软件系统，使用了类似的定义。
7.3.1 正则表达式细节
PostgreSQL的正则表达式使用 Henry Spencer 写的一个包来实现。 下面的大部分描述都是从他的手册页里逐字拷贝过来的。
正则表达式(REs)，在POSIX 1003.2中定义， 它有两种形式：扩展 RE或ERE (基本上就是在egrep里的那些)，基本RE 或BRE(基本上就是在ed里的那些)。PostgreSQL 两种形式都实现了，并且还做了一些 POSIX 里面没有的， 但是因为在类似 Perl 或者 Tcl 这样的语言中得到广泛应用的一些扩展。 使用了那些非 POSIX 扩展的正则表达式叫高级RE 或ARE。ARE 几乎完全是 ERE 的超集，但是 BRE 有几个符号上的不兼容(以及更多的限制)。 我们首先描述 ARE 和 ERE 形式，描述那些只适用于 ARE 的特性，然后描述与 BRE 的区别是什么。
注意: PostgreSQL总是初始化一个遵循ARE规则的正则表达式。然而， 更多限制的ERE或BRE规则可以通过在RE模式前放置一个embedded option来选择， 这对于期望完全兼容POSIX 1003.2规则的应用程序是有用的。
表 7-3-2. 正则表达式原子
| 原子 | 描述 |
|---|---|
| (re) | (re 是任意正则表达式)匹配一个对re 的匹配，有可报告的匹配信息。 |
| (?:re) | 同上，但是匹配不会被报告(一个"非捕获"圆括弧)，只在 ARE 中有。 |
| . | 匹配任意单个字符 |
| [chars] | 一个方括弧表达式，匹配任意的字符 |
| \k | (k是非字母数字字符)匹配一个当作普通字符看待的特定字符， 比如\\匹配一个反斜杠。 |
| \c | c是一个字母数字(可能跟着其它字符)，它是一个转义。仅存在于 ARE 中；在 ERE 和 BRE 中， 它匹配c。 |
| { | 如果后面跟着一个非数字字符，那么就匹配左花括弧{； 如果跟着一个数字，那么它是范围的开始(见下面) |
| x | 这里的x是一个没有其它特征的单个字符，则匹配该字符 |
RE不能以(\)结尾。
注意: 如果关闭了standard_conforming_stringsstandard_conforming_stringsstandard_conforming_strings， 任何文本字符串常量中的反斜杠都需要双写。
表 7-3-3. 正则表达式量词
| 量词 | 匹配 |
|---|---|
| * | 一个匹配 0 或者更多个原子的序列 |
| + | 一个匹配 1 或者更多个原子的序列 |
| ? | 一个匹配 0 或者 1个原子的序列 |
| {m} | 一个正好匹配m个原子的序列 |
| {m,} | 一个匹配m个或者更多原子的序列 |
| {m,n} | 一个匹配m到n个(包含两端)原子的序列； m不能比n大 |
| *? | *的非贪婪模式 |
| +? | +的非贪婪模式 |
| ?? | ?的非贪婪模式 |
| {m}? | {m}的非贪婪模式 |
| {m,}? | {m,}的非贪婪模式 |
| {m,n}? | {m,n}的非贪婪模式 |
{...}的形式被称作范围。 一个范围内的数字m和n都是无符号十进制整数， 允许的数值从 0 到 255 (闭区间)。
非贪婪的量词(只在 ARE 中可用)匹配对应的正常(贪婪)模式， 区别是它寻找最少的匹配，而不是最多的匹配。
注意: 一个量词不能紧跟在另外一个量词后面，例如，**是非法的。 量词不能是表达式或者子表达式的开头，也不能跟在^ 或|后面。
表 7-3-4. 正则表达式约束
| 约束 | 描述 |
|---|---|
| ^ | 匹配字符串的开头 |
| $ | 匹配字符串的结尾 |
| (?=re) | 正前瞻匹配任何匹配re 的子字符串起始点(只在 ARE 中有) |
| (?!re) | 负前瞻匹配任何不匹配re 的子字符串起始点(只在 ARE 中有) |
前瞻约束不能包含后引用， 并且在其中的所有圆括弧都被认为是不捕获的。
7.3.2 方括弧表达式
方括弧表达式是一个包围在[]里的字符列表。 它通常匹配任意单个列表中的字符(又见下文)。如果列表以^开头， 它匹配任意单个(又见下文)不在该列表中的字符。如果该列表中两个字符用 -隔开，那它就是那两个字符(包括在内)之间的所有字符范围的缩写， 比如，在ASCII里[0-9]包含任何十进制数字。 两个范围共享一个终点是非法的，比如a-c-e。这个范围与字符集关系密切， 可移植的程序不应该依靠它们。
想在列表中包含文本]，可以让它做列表的首字符(如果用到了， 跟在^ 后面)。想在列表中包含文本-， 可以让它做列表的首字符或者末字符，或者一个范围的第二个终点。 想在列表中把文本-当做范围的起点，把它用[. 和.]包围起来，这样它就成为一个集合元素(见下文)。 除了这些字符本身，和一些用[的组合(见下段)， 以及转义(只在 ARE 中有效)以外，所有其它特殊字符在方括弧表达式里都失去它们的特殊含义。 特别是，在 ERE 和 BRE 规则下\不是特殊的，但在 ARE 里， 它是特殊的(还是引入一个转义)。
在一个方括弧表达式里，一个集合元素(一个字符、一个当做一个字符的多字符序列、 或者一个表示上面两种情况的集合序列)包含在[.和.] 里面的时候表示该集合元素的字符序列。该序列是该方括弧列表的一个元素。 这允许一个包含多字符集合元素的方括弧表达式就可以匹配多于一个字符，比如， 如果集合序列包含一个ch集合元素，那么[[.ch.]]*c 匹配chchcc的头五个字符。译注：其实把 [. 和 .] 括起来的整体当一个字符看就行了。
注意: PostgreSQL目前不支持多字符集合元素。这些信息描述了将来可能有的行为。
在方括弧表达式里，在[=和=] 里包围的集合元素是一个等效表，代表等于这里所有集合元素的字符序列， 包括它本身(如果没有其它等效集合元素，那么就好像封装元素是[. 和.])。比如，如果o和^ 是一个等效表的成员，那么[[=o=]],[[=^=]], [o^]都是同义的。一个等效表不能是一个范围的端点。
在方括弧表达式里，在[:和:] 里面封装的字符表名字代表属于该表的所有字符的列表。标准的字符表名字是： alnum, alpha, blank, cntrl, digit, graph, lower, print, punct, space, upper, xdigit。 它们代表在ctype 里定义的字符表。本地化设置可能会提供其它的表。字符表不能用做一个范围的端点。
在方括弧表达式里有两个特例：方括弧表达式[[:<:]]和 [[:>:]]是约束，分别匹配一个单词开头和结束的空串。 单词定义为一个单词字符序列，前面和后面都没有其它单词字符。 单词字符是一个alnum字符(和ctype 里定义的一样)或者一个下划线。这是一个扩展，兼容POSIX 1003.2 ， 但那里面并没有说明，而且在准备移植到其它系统里去的软件里一定要小心使用。 通常下面描述的约束转义更好些；他们并非更标准，但是更容易输入。
7.3.3 正则表达式转义
转义是以\开头，后面跟着一个字母数字字符的特殊序列。 转义有好几种变体：字符项、表缩写、约束转义、后引用。在 ARE 里，如果一个\ 后面跟着一个字母数字，但是并未组成一个合法的转义，那么它是非法的。在 ERE 里则没有转义： 在方括弧表达式之外，一个跟着字母数字字符的\只是表示该字符是一个普通的字符， 而在一个方括弧表达式里，\是一个普通的字符(后者实际上是 ERE 和 ARE 之间的不兼容)。
字符项转义用于方便我们声明正则表达式里那些不可打印的字符。 它们在 表7-3-5 里列出。
类缩写转义用来提供一些常用的字符类缩写。 他们在表 9-16表7-3-6表 9-16里列出。
约束转义是一个约束，如果满足特定的条件，它匹配该空字符串，以转义形式写出。 它们在表 9-17表7-3-7表 9-17里列出。
后引用(\n)匹配数字n 指定的前面的圆括弧子表达式匹配的同一个字符串(参阅表 9-18表7-3-8表 9-18)。 比如，([bc])\1匹配bb或cc但是不匹配bc或cb。 正则表达式里的子表达式必须完全在后引用前面。子表达式以它的括号的顺序排序。 非捕获圆括弧并不定义子表达式。
注意: 请注意，如果把模式当作一个 SQL 字符串常量输入，那么转义前导的\需要双倍地写：
'123' ~ E'^\\d{3}' true
表 7-3-5. 正则表达式字符项转义
| 转义 | 描述 |
|---|---|
| \a | 警笛(铃声)字符，和 C 里一样 |
| \b | 退格，和 C 里一样 |
| \B | \的同义词，用于减少反斜杠加倍的需要 |
| \cX | (这里X是任意字符)字符的低 5 位和X 里的相同，其它位都是 0 |
| \e | 集合序列名字是ESC的字符，如果不是， 则是八进制值为 033 的字符 |
| \f | 进纸，和 C 里一样 |
| \n | 新行，和 C 里一样 |
| \r | 回车，和 C 里一样 |
| \t | 水平制表符，和 C 里一样 |
| \uwxyz | (这里的wxyz是恰好四位十六进制位)本机字节序的 UTF-16 字符 U+wxyz |
| \Ustuvwxyz | (这里的stuvwxyz是恰好八位十六进制位) 为假想中的 Unicode 32 位扩展保留的 |
| \v | 垂直制表符，和 C 里一样 |
| \xhhh | (这里的hhh是一个十六进制序列)十六进制值为 0xhhh的字符(不管用了几个十六进制位， 都是一个字符) |
| \0 | 值为0的字符 (null 字节) |
| \xy | (这里的xy是恰好两个八进制位， 并且不是一个后引用)八进制值为0xy 的字符 |
| \xyz | (这里的xyz是恰好三位八进制位， 并且不是一个后引用)八进制值为0xyz 的字符 |
十六进制位是0-9, a-f, A-F。八进制位是0-7。
字符项转义总是被当作普通字符。比如，\135是 ASCII 中的]， 但\135并不终止一个方括弧表达式。
表 7-3-6. 正则表达式类缩写转义
| 转义 | 描述 |
|---|---|
| \d | [[:digit:]] |
| \s | [[:space:]] |
| \w | [[:alnum:]_] (注意，这里是包含下划线的) |
| \D | [^[:digit:]] |
| \S | [^[:space:]] |
| \W | [^[:alnum:]_] (注意，这里是包含下划线的) |
在方括弧表达式里，\d, \s, \w 会失去他们的外层方括弧，而 \D, \S, \W 是非法的。比如[a-c\d]等效于[a-c[:digit:]]。 同样[a-c\D]原来等效于[a-c^[:digit:]]的，也是非法的。
表 7-3-7. 正则表达式约束转义
| 转义 | 描述 |
|---|---|
| \A | 只匹配字符串开头 |
| \m | 只匹配一个词的开头 |
| \M | 只匹配一个词的结尾 |
| \y | 只匹配一个词的开头或者结尾 |
| \Y | 只匹配那些既不是词的开头也不是词的结尾的点 |
| \Z | 只匹配一个字符串的结尾 |
一个词的定义是上面[[:<:]]和[[:>:]]的声明。 在方括弧表达式里，约束转义是非法的。
表 7-3-8. 正则表达式后引用
| 转义 | 描述 |
|---|---|
| \m | (这里的m是一个非零十进制位) 一个指向第m个子表达式的后引用 |
| \mnn | (这里的m是一个非零十进制位，nn 是更多的十进制位，并且十进制数值mnn 不能大于到这个位置为止的闭合捕获圆括弧的个数)一个指向第mnn 个子表达式的后引用 |
注意: 在八进制字符项转义和后引用之间有一个继承的歧义存在，这个歧义是通过跟着的启发分析解决的， 像上面描述的那样。前导零总是表示这是一个八进制转义。而单个非零数字， 如果没有跟着任何其它数字，那么总是认为是后引用。 一个多数据位的非零开头的序列也认为是后引用(只要它在合适的子表达式后面， 也就是说，数值在后引用的合法范围内)，否则就认为是一个八进制。
7.3.4 正则表达式元语法
除了上面描述的主要语法之外，还有几种特殊形式和杂项语法。
正则表达式可以以两个特殊的指示器前缀之一开始： 如果一个正则表达式以***:开头，那么剩下的正则表达式都被当作 ARE 。 （这在PostgreSQL中通常没有影响，因为正则表达式被假设为ARE； 但是如果ERE或BRE模式被flags参数指定为正则表达式函数时是有影响的。） 如果一个的正则表达式以***=开头，那么剩下的正则表达式被当作一个文本串， 所有的字符都被认为是一个普通字符。
一个 ARE 可以以嵌入选项开头：一个(?xyz) 序列(这里的xyz是一个或多个字母字符)声明影响剩余正则表达式的选项。 这些选项覆盖任何前面判断的选项—它们可以重写正则表达式操作符隐含的大小写敏感性， 或者正则表达式函数的flags参数。可用的选项字母在 表7-3-9 显示。请注意， 正则表达式函数的flags参数使用相同的选项字母。
表7-3-9. ARE 嵌入选项字母
| 选项 | 描述 |
|---|---|
| b | 剩余的正则表达式是 BRE |
| c | 大小写敏感匹配(覆盖操作符类型) |
| e | 剩余的正则表达式是 ERE |
| i | 大小写不敏感匹配(覆盖操作符类型) |
| m | n的历史同义词 |
| n | 新行敏感匹配 |
| p | 部分新行敏感匹配 |
| q | 重置正则表达式为一个文本("引起")字符串，所有都是普通字符。 |
| s | 非新行敏感匹配(缺省) |
| t | 紧语法(缺省，见下文) |
| w | 反转部分新行敏感("怪异")匹配 |
| x | 扩展的语法(见下文) |
嵌入的选项在终止其序列的)发生作用。他们只在 ARE 的开始处起作用(如果有， 则在任何***:指示器后面)。
除了通常的(紧)正则表达式语法(这种情况下所有字符都重要)， 还有一种扩展语法，可以通过声明嵌入的x选项获得。 在扩展语法里，正则表达式中的空白字符被忽略，就像那些在# 和新行之间的字符一样（或正则表达式的结尾）。 这样就允许我们给一个复杂的正则表达式分段和注释。不过这个基本规则上有三种例外：
-
前置了\的空白字符或者 #保留
-
方括弧里的空白或者#保留
-
在多字符符号里面不能出现空白和注释，比如(?:
在这里，空白是空格、水平制表符、新行、和任何属于space(空白)字符表的字符。
最后，在 ARE 里，方括弧表达式外面，序列(?#ttt) (这里的ttt是任意不包含)的文本)是一个注释，完全被忽略。 同样，这样的东西是不允许出现在多字符符号的字符中间的，比如(?:。 这样的注释是比有用的机制的更久远的历史造成的，他们的用法已经废弃了； 我们应该使用扩展语法代替他。
如果声明了一个初始化的***=指示器，那么所有这些元语法扩展都 不能使用，因为这样表示把用户输入当作一个文本字符串而不是正则表达式对待。
7.3.5 正则表达式匹配规则
在正则表达式可以匹配给出的字符串中多于一个子字符串的情况下， 正则表达式匹配字符串中最靠前的那个子字符串。 如果正则表达式可以匹配在那个位置开始的多个子字符串，要么是取最长的子字符串， 要么是最短的，具体哪种，取决于正则表达式是贪婪的还是 非贪婪的。
一个正则表达式是否贪婪取决于下面规则：
-
大多数原子，以及所有约束，都没有贪婪属性(因为它们毕竟无法匹配个数变化的文本)。
-
在一个正则表达式周围加上圆括弧并不会改变其贪婪性。
-
一个带一个固定重复次数的量词({m} 或{m}?) 量化的原子和原子自身有着同样的贪婪性(可能是没有)。
-
一个带其它普通的量词(包括{m,n} 中m等于n的情况)量化的原子是贪婪的(首选最长匹配)。
-
一个带非贪婪量词(包括{m,n}? 中m等于n的情况)量化原子是非贪婪的(首选最短匹配)。
-
一个分支(也就是一个没有顶级|操作的正则表达式) 和它里面的第一个有贪婪属性的量化原子有着同样的贪婪性。
-
一个由|操作符连接起来的两个或者更多分支组成的正则表达式总是贪婪的。
上面的规则所描述的贪婪属性不仅仅适用于独立的量化原子， 而且也适用于包含量化原子的分支和整个正则表达式。这里的意思是， 匹配是按照分支或者整个正则表达式作为一个整体 匹配最长或者最短的子字符串的可能。一旦整个匹配的长度确定， 那么匹配任意子表达式的部分就基于该子表达式的贪婪属性进行判断， 在正则表达式里面靠前的子表达式的优先级高于靠后的子表达式。
一个表达这些的例子：
SELECT SUBSTRING('XY1234Z', 'Y*([0-9]{1,3})');
Result: 123
SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
Result: 1在第一个例子里，正则表达式作为整体是贪婪的，因为Y*是贪婪的。 它可以匹配从Y开始的东西，并且它匹配从这个位置开始的最长的字符串， 也就是Y123。输出是这里的圆括弧包围的部分，或者说是123。 在第二个例子里，正则表达式总体上是一个非贪婪的正则表达式 ，因为Y*? 是非贪婪的。它可以匹配从Y开始的最短的子字符串，也就是说Y1。 子表达式[0-9]{1,3}是贪婪的，但是它不能修改总体匹配长度的决定； 因此它被迫只匹配1。
简单说，如果一个正则表达式同时包含贪婪和非贪婪的子表达式， 那么总匹配长度要么是最长可能，要么是最短可能，取决于给整个正则表达式赋予的贪婪属性。 给子表达式赋予的贪婪属性只影响在这个匹配里，各个子表达式之间相互允许 "吃进"的多少。
量词{1,1}和{1,1}? 可以分别用于在一个子表达式或者整个正则表达式上强制贪婪或者非贪婪。
匹配长度是以字符衡量的，而不是集合的元素。一个空字符串会被认为比什么都不匹配长。 比如：bb*匹配abbbc的中间三个字符； (week|wee)(night|knights)匹配weeknights的所有十个字符； 而(.*).*匹配abc的时候，圆括弧包围的子表达式匹配所有三个字符； 而如果用(a*)*匹配bc，那么正则表达式和圆括弧子表达式都匹配空字符串。
如果声明了大小写无关的匹配，那么效果就好像把所有字母上的大小写区别取消了一样。 如果一个存在大小写差别的字母以一个普通字符的形式出现在方括弧表达式外面， 那么它实际上被转换成一个包含大小写的方括弧表达式，也就是说，x 变成[xX]。如果它出现在一个方括弧表达式里面， 那么它的所有大小写的同族都被加入方括弧表达式中，也就是说，[x] 变成[xX]而[^x]变成 [^xX]。
如果声明了新行敏感匹配，.和使用^ 的方括弧表达式将永远不会匹配新行字符(这样，匹配就绝对不会跨新行， 除非正则表达式明确地安排了这样的情况)并且^和$ 除了分别匹配字符串开头和结尾之外，还将分别匹配新行后面和前面的空字符串。 但是 ARE 转义\A和\Z 仍然只匹配字符串的开头和结尾。
如果声明了部分新行敏感匹配，那么它影响.和方括弧表达式， 这个时候和新行敏感匹配一样，但是不影响^和$。
如果声明了反转部分新行敏感匹配，那么它影响^和$， 作用和新行敏感匹配里一样，但是不影响.和方括弧表达式。 这个没什么太多用途，只是为了对称提供的。
7.3.6 限制和兼容性
在这个实现里，对正则表达式的长度没有特别的限制，但是， 那些希望能够有很好移植行的程序应该避免写超过 256 字节的正则表达式 ， 因为 POSIX 兼容的实现可以拒绝接受这样的正则表达式。
ARE 实际上和 POSIX ERE 不兼容的唯一的特性是在方括弧表达式里\ 并不失去它特殊的含义。所有其它 ARE 特性都使用在 POSIX ERE 里面是非法或者是未定义、 未声明效果的语法；指示器的***就是在 POSIX 的 BRE 和 ERE 之外的语法。
许多 ARE 扩展都是从 Perl 那里借来的，但是有些我做了修改，清理了一下， 以及一些 Perl 里没有出现的扩展。要注意的不兼容包括\b, \B， 对结尾的新行缺乏特别的处理，对那些新行敏感匹配的附加的补齐方括弧表达式， 在前瞻约束里对圆括弧和方括弧引用的限制，以及最长/最短匹配(而不是第一匹配)语义。
PostgreSQL 7.4 之前的版本里的 ARE 和 ERE 存在两个非常显著的不兼容：
-
在 ARE 里，后面跟着一个字母数字的\要么是一个转义，要么是错误， 但是在以前的版本里，它只是写那个字母数字的另外一种方法。这个应该不是什么问题， 因为在以前的版本里没有什么原因让我们写这样的序列。
-
在 ARE 里，\在[]里还是一个特殊字符， 因此在方括弧表达式里的一个文本\必须写成\\。
7.3.7 基本正则表达式
BRE 在几个方面和 ERE 不太一样。在BRE里，|, +,? 都是普通字符，它们没有等效的功能替换。范围的分隔符是\{和\}， 因为{和}本身是普通字符。嵌套的子表达式的圆括弧是\( 和\)，因为(和)自身是普通字符。 除非在正则表达式开头或者是圆括弧封装的子表达式开头，^都是普通字符， 除非在正则表达式结尾或者是圆括弧封装的子表达式的结尾，$是一个普通字符， 而如果*出现在正则表达式开头或者是圆括弧封装的子表达式开头 (前面可能有^)，那么它是个普通字符。最后，可以用单数字的后引用， 以及\<和\>分别是[[:<:]]和[[:>:]] 的同义词；在BRE里没有其它的转义。
八、数据类型和格式化函数
PostgreSQL格式化函数提供一套强大的工具用于把各种数据类型 （日期/时间、整数、浮点、数字） 转换成格式化的字符串以及反过来从格式化的字符串转换成 指定的数据类型。表 8-1 列出了这些函数。这些函数都遵循一个公共的调用规范： 第一个参数是待格式化的值，而第二个是一个定义输出或输入格式的模板。
表 8-1 格式化函数
注意：
还有一个单一参数的
to_timestamp函数
提示：
to_timestamp和to_date存在的目的是为了处理无法用简单造型转换的输入格式。对于大部分标准的日期/时间格式，简单地把源字符串造型成所需的数据类型是可以的，并且简单很多。类似地，对于标准的数字表示形式，to_number也是没有必要的。
在一个to_char输出模板串中，一些特定的模式可以被识别并且被替换成基于给定值的被恰当地格式化的数据。任何不属于模板模式的文本都简单地照字面拷贝。同样，在一个输入 模板串里（对其他函数），模板模式标识由输入数据串提供的值。如果在模板字符串中有不是模板模式的字符，输入数据字符串中的对应字符会被简单地跳过（不管它们是否等于模板字符串字符）。
表 8-2 展示了可以用于格式化日期和时间值的模版。
| 模式 | 描述 |
|---|---|
HH | 一天中的小时 （01-12） |
HH12 | 一天中的小时 （01-12） |
HH24 | 一天中的小时 （00-23） |
MI | 分钟 （00-59）minute (00-59) |
SS | 秒（00-59） |
MS | 毫秒（000-999） |
US | 微秒（000000-999999） |
SSSS | 午夜后的秒（0-86399） |
AM, am, PM or pm | 正午指示器（不带句号） |
A.M., a.m., P.M. or p.m. | 正午指示器（带句号） |
Y,YYY | 带逗号的年（4 位或者更多位） |
YYYY | 年（4 位或者更多位） |
YYY | 年的后三位 |
YY | 年的后两位 |
Y | 年的最后一位 |
IYYY | ISO 8601 周编号方式的年（4 位或更多位） |
IYY | ISO 8601 周编号方式的年的最后 3 位 |
IY | ISO 8601 周编号方式的年的最后 2 位 |
I | ISO 8601 周编号方式的年的最后一位 |
BC, bc, AD或者ad | 纪元指示器（不带句号） |
B.C., b.c., A.D.或者a.d. | 纪元指示器（带句号） |
MONTH | 全大写形式的月名（空格补齐到 9 字符） |
Month | 全首字母大写形式的月名（空格补齐到 9 字符） |
month | 全小写形式的月名（空格补齐到 9 字符） |
MON | 简写的大写形式的月名（英文 3 字符，本地化长度可变） |
Mon | 简写的首字母大写形式的月名（英文 3 字符，本地化长度可变） |
mon | 简写的小写形式的月名（英文 3 字符，本地化长度可变） |
MM | 月编号（01-12） |
DAY | 全大写形式的日名（空格补齐到 9 字符） |
Day | 全首字母大写形式的日名（空格补齐到 9 字符） |
day | 全小写形式的日名（空格补齐到 9 字符） |
DY | 简写的大写形式的日名（英语 3 字符，本地化长度可变） |
Dy | 简写的首字母大写形式的日名（英语 3 字符，本地化长度可变） |
dy | 简写的小写形式的日名（英语 3 字符，本地化长度可变） |
DDD | 一年中的日（001-366） |
IDDD | ISO 8601 周编号方式的年中的日（001-371，年的第 1 日时第一个 ISO 周的周一） |
DD | 月中的日（01-31） |
D | 周中的日，周日（1）到周六（7） |
ID | 周中的 ISO 8601 日，周一（1）到周日（7） |
W | 月中的周（1-5）（第一周从该月的第一天开始） |
WW | 年中的周数（1-53）（第一周从该年的第一天开始） |
IW | ISO 8601 周编号方式的年中的周数（01 - 53；新的一年的第一个周四在第一周） |
CC | 世纪（2 位数）（21 世纪开始于 2001-01-01） |
J | 儒略日（从午夜 UTC 的公元前 4714 年 11 月 24 日开始的整数日数） |
Q | 季度（to_date和to_timestamp会忽略） |
RM | 大写形式的罗马计数法的月（I-XII；I 是 一月） |
rm | 小写形式的罗马计数法的月（i-xii；i 是 一月） |
TZ | 大写形式的时区缩写（仅在to_char中支持） |
tz | 小写形式的时区缩写（仅在to_char中支持） |
TZH | 时区的小时 |
TZM | 时区的分钟 |
OF | 从UTC开始的时区偏移（仅在to_char中支持） |
修饰语可以被应用于模板模式来修改它们的行为。例如，FMMonth就是带着FM修饰语的Month模式。表 8-3 展示了可用于日期/时间格式化的修饰语模式。
表 8-3. 用于日期/时间格式化的模板模式修饰语
| 修饰语 | 描述 | 例子 |
|---|---|---|
FM prefix | 填充模式（抑制前导零和填充的空格） | FMMonth |
TH suffix | 大写形式的序数后缀 | DDTH, e.g., 12TH |
th suffix | 小写形式的序数后缀 | DDth, e.g., 12th |
FX prefix | 固定的格式化全局选项（见使用须知） | FX Month DD Day |
TM prefix | 翻译模式（基于lc_time打印本地化的日和月名） | TMMonth |
SP suffix | 拼写模式（未实现） | DDSP |
日期/时间格式化的使用须知：
-
FM抑制前导的零或尾随的空白， 否则会把它们增加到输入从而把一个模式的输出变成固定宽度。在PostgreSQL中，FM只修改下一个声明，而在 Oracle 中，FM影响所有随后的声明，并且重复的FM修饰语将触发填充模式开和关。 -
TM不包括结尾空白。to_timestamp和to_date会忽略TM修饰语。 -
如果没有使用
FX选项，to_timestamp和to_date会跳过输入字符串中的多个空白。例如，to_timestamp('2000 JUN', 'YYYY MON')是正确的，但to_timestamp('2000 JUN', 'FXYYYY MON')会返回一个错误，因为to_timestamp只期望一个空白。FX必须被指定为模板中的第一个项。 -
在
to_char模板里可以有普通文本，并且它们会被照字面输出。你可以把一个子串放到双引号里强迫它被解释成一个文本，即使它里面包含模板模式也如此。例如，在'"Hello Year "YYYY'中，YYYY将被年份数据代替，但是Year中单独的Y不会。在to_date、to_number以及to_timestamp中，文本和双引号字符串会导致跳过该字符串中所包含的字符数量，例如"XX"会跳过两个输入字符（不管它们是不是XX）。 -
如果你想在输出里有双引号，那么你必须在它们前面放反斜线，例如
'\"YYYY Month\"'。不然，在双引号字符串外面的反斜线就不是特殊的。在双引号字符串内，反斜线会导致下一个字符被取其字面形式，不管它是什么字符（但是这没有特殊效果，除非下一个字符是一个双引号或者另一个反斜线）。 -
在
to_timestamp和to_date中，如果年份格式声明少于四位（如YYY）并且提供的年份少于四位，年份将被调整为最接近于 2020 年，例如95会变成 1995。 -
在
to_timestamp和to_date中，在处理超过4位数的年份时，YYYY转换具有限制。你必须在YYYY后面使用一些非数字字符或者模板， 否则年份总是被解释为 4 位数字。例如（对于 20000 年）：to_date('200001131', 'YYYYMMDD')将会被解释成一个 4 位数字的年份，而不是在年份后使用一个非数字分隔符，像to_date('20000-1131', 'YYYY-MMDD')或to_date('20000Nov31', 'YYYYMonDD')。 -
在
to_timestamp和to_date中，CC（世纪）字段会被接受，但是如果有YYY、YYYY或者Y,YYY字段则会忽略它。如果CC与YY或Y一起使用，则结果被计算为指定世纪中的那一年。如果指定了世纪但是没有指定年，则会假定为该世纪的第一年。 -
在
to_timestamp和to_date中，工作日名称或编号（DAY、D以及相关的字段类型）会被接受，但会为了计算结果的目的而忽略。季度（Q）字段也是一样。 -
在
to_timestamp和to_date中，一个 ISO 8601 周编号的日期（与一个格里高利日期相区别）可以用两种方法之一被指定为to_timestamp和to_date：-
年、周编号和工作日：例如
to_date('2006-42-4', 'IYYY-IW-ID')返回日期2006-10-19。如果你忽略工作日，它被假定为 1（周一）。 -
年和一年中的日：例如
to_date('2006-291', 'IYYY-IDDD')也返回2006-10-19。
尝试使用一个混合了 ISO 8601 周编号和格里高利日期的域来输入一个日期是无意义的，并且将导致一个错误。在一个 ISO 周编号的年的环境下，一个“月”或“月中的日”的概念没有意义。在一个格里高利年的环境下，ISO 周没有意义。用户应当避免混合格里高利和 ISO 日期声明。
-
WARNING:
虽然
to_date将会拒绝混合使用格里高利和 ISO 周编号日期的域，to_char却不会，因为YYYY-MM-DD (IYYY-IDDD)这种输出格式也会有用。但是避免写类似IYYY-MM-DD的东西，那会得到在 起始年附近令人惊讶的结果。
-
在
to_timestamp中，毫秒（MS）和微秒（US）域都被用作小数点后的秒位。例如to_timestamp('12.3', 'SS.MS')不是 3 毫秒, 而是 300，因为该转换把它看做 12 + 0.3 秒。这意味着对于格式SS.MS而言，输入值12.3、12.30和12.300指定了相同数目的毫秒。要得到三毫秒，你必须使用12.003，转换会把它看做 12 + 0.003 = 12.003 秒。下面是一个更复杂的例子∶
to_timestamp('15:12:02.020.001230', 'HH24:MI:SS.MS.US')是 15 小时、12 分钟和 2 秒 + 20 毫秒 + 1230微秒 = 2.021230 秒。 -
to_char(..., 'ID')的一周中日的编号匹配extract(isodow from ...)函数，但是to_char(..., 'D')不匹配extract(dow from ...)的日编号。 -
to_char(interval)格式化HH和HH12为显示在一个 12 小时的时钟上，即零小时和 36 小时输出为12，而HH24会输出完整的小时值，对于间隔它可以超过 23.
表 8-4 展示了可以用于格式化数字值的模版模式。
表 8-4. 用于数字格式化的模板模式
| 模式 | 描述 |
|---|---|
9 | 数位（如果无意义可以被删除） |
0 | 数位（即便没有意义也不会被删除） |
. (period) | 小数点 |
, (comma) | 分组（千）分隔符 |
PR | 尖括号内的负值 |
S | 带符号的数字（使用区域） |
L | 货币符号（使用区域） |
D | 小数点（使用区域） |
G | 分组分隔符（使用区域） |
MI | 在指定位置的负号（如果数字 < 0） |
PL | 在指定位置的正号（如果数字 > 0） |
SG | 在指定位置的正/负号 |
RN | 罗马数字（输入在 1 和 3999 之间） |
TH or th | 序数后缀 |
V | 移动指定位数（参阅注解） |
EEEE | 科学记数的指数 |
数字格式化的用法须知：
-
0指定一个总是被打印的数位，即便它包含前导/拖尾的零。9也指定一个数位，但是如果它是前导零则会被空格替换，而如果是拖尾零并且指定了填充模式则它会被删除（对于to_number()来说，这两种模式字符等效）。 -
模式字符
S、L、D以及G表示当前locale定义的负号、货币符号、小数点以及数字分隔符字符（见lc_monetary和lc_numeric）。不管locale是什么，模式字符句号和逗号就表示小数点和数字分隔符。 -
对于
to_char()的模式中的一个负号，如果没有明确的规定，将为该负号保留一列，并且它将被锚接到（出现在左边）那个数字。如果S正好出现在某个9的左边，它也将被锚接到那个数字。 -
使用
SG、PL或MI格式化的符号并不挂在数字上面； 例如，to_char(-12, 'MI9999')生成'- 12'，而to_char(-12, 'S9999')生成' -12'。（Oracle 里的实现不允许在9前面使用MI，而是要求9在MI前面。） -
TH不会转换小于零的数值，也不会转换小数。 -
PL、SG和TH是PostgreSQL扩展。 -
在
to_number中，如果没有使用L或TH之类的非数据模板模式，相应数量的输入字符会被跳过，不管它们是否匹配模板模式，除非它们是数据字符（也就是数位、负号、小数点或者逗号）。例如，TH会跳过两个非数据字符。 -
带有
to_char的V会把输入值乘上10^，其中nn是跟在V后面的位数。带有to_number的V以类似的方式做除法。to_char和to_number不支持使用结合小数点的V（例如，不允许99.9V99）。 -
EEEE（科学记数法）不能和任何其他格式化模式或修饰语（数字和小数点模式除外）组合在一起使用，并且必须位于格式化字符串的最后（例如9.99EEEE是一个合法的模式）。
某些修饰语可以被应用到任何模板来改变其行为。例如，FM99.99是带有FM修饰语的99.99模式。表 8-5 中展示了用于数字格式化模式修饰语。
表 8-5. 用于数字格式化的模板模式修饰语
| 修饰语 | 描述 | 例子 |
|---|---|---|
FM prefix | 填充模式（抑制拖尾零和填充的空白） | FM99.99 |
TH suffix | 大写序数后缀 | 999TH |
th suffix | 小写序数后缀 | 999th |
表 8-6 展示了一些使用to_char函数的例子。
表 8-6. 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, 'FM90.99') | '-0.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' |
to_char(0.0004859, '9.99EEEE') | ' 4.86e-04' |
九、时间、日期函数和操作符
表 9-2 展示了可用于处理日期/时间值的函数，其细节在随后的小节中描述。表 9-1 演示了基本算术操作符 （+、*等）的行为。 而与格式化相关的函数，可以参考上面第八节。你应该很熟悉上面第八节中的日期/时间数据类型的背景知识。
所有下文描述的接受time或timestamp输入的函数和操作符实际上都有两种变体： 一种接收time with time zone或timestamp with time zone， 另外一种接受time without time zone或者 timestamp without time zone。为了简化，这些变种没有被独立地展示。此外，+和*操作符都是可交换的操作符对（例如，date + integer 和 integer + date）；我们只显示其中一个。
表 9-1. 日期/时间操作符
| 操作符 | 例子 | 结果 |
|---|---|---|
+ | 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' (days) |
- | 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' |
表 9-2. 日期/时间函数
除了这些函数以外，还支持 SQL 操作符OVERLAPS：
(start1, end1) OVERLAPS (start2, end2)
(start1, length1) OVERLAPS (start2, length2)这个表达式在两个时间域（用它们的端点定义）重叠的时候得到真，当它们不重叠时得到假。端点可以用一对日期、时间或者时间戳来指定；或者是用一个后面跟着一个间隔的日期、时间或时间戳来指定。当一对值被提供时，起点或终点都可以被写在前面，OVERLAPS会自动地把较早的值作为起点。每一个时间段被认为是表示半开的间隔start <= time < end，除非start和end相等，这种情况下它表示单个时间实例。例如这表示两个只有一个共同端点的时间段不重叠。
SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
(DATE '2001-10-30', DATE '2002-10-30');
结果：true
SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
(DATE '2001-10-30', DATE '2002-10-30');
结果：false
SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS
(DATE '2001-10-30', DATE '2001-10-31');
结果：false
SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS
(DATE '2001-10-30', DATE '2001-10-31');
结果：true当把一个interval值添加到timestamp with time zone上（或从中减去）时， days 部分会按照指定的天数增加或减少timestamp with time zone的日期。 对于横跨夏令时的变化（当会话的时区被设置为可识别DST的时区时），这意味着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'，而将interval '24 hours'增加到相同的初始timestamp with time zone的结果 则是timestamp with time zone '2005-04-03 13:00-06'， 因为CST7CDT时区在2005-04-03 02:00有一个夏令时变更。
注意age返回的月数域可能有歧义，因为不同的月份有不同的天数。 PostgreSQL的方法是当计算部分月数时，采用两个日期中较早的月。例如：age('2004-06-01', '2004-04-30')使用4月份得到1 mon 1 day，而用5月分时会得到1 mon 2 days，因为5月有31天，而4月只有30天。
日期和时间戳的减法也可能会很复杂。执行减法的一种概念上很简单的方法是，使用 EXTRACT(EPOCH FROM ...)把每个值都转换成秒数，然后执行减法， 这样会得到两个值之间的秒数。这种方法将会适应每个月中天数、 时区改变和夏令时调整。使用“-”操作符的日期或时间 戳减法会返回值之间的天数（24小时）以及时/分/秒，也会做同样的调整。 age函数会返回年、月、日以及时/分/秒，执行按域的减法，然后对 负值域进行调整。下面的查询展示了这些方法的不同。例子中的结果由 timezone = 'US/Eastern'产生，这使得两个使用的日期之间存在着夏令 时的变化：
SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00');
Result: 10537200
SELECT (EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00'))
/ 60 / 60 / 24;
Result: 121.958333333333
SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00';
Result: 121 days 23:00:00
SELECT age(timestamptz '2013-07-01 12:00:00', timestamptz '2013-03-01 12:00:00');
Result: 4 mons9.1 EXTRACT, date_part
EXTRACT(field FROM source)
extract函数从日期/时间值中抽取子域，例如年或者小时等。source必须是一个类型 timestamp、time或interval的值表达式（类型为date的表达式将被造型为 timestamp，并且因此也可以被同样使用）。field是一个标识符或者字符串，它指定从源值中抽取的域。extract函数返回类型为double precision的值。 下列值是有效的域名字∶
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第一个世纪从 0001-01-01 00:00:00 AD 开始， 尽管那时候人们还不知道这是第一个世纪。这个定义适用于所有使用格里高利历法的国家。其中没有 0 世纪，我们直接从公元前 1 世纪到公元 1 世纪。 如果你认为这个不合理，那么请把抱怨发给：罗马圣彼得教堂，梵蒂冈，教皇收。
day
对于timestamp值，是（月份）里的日域（1-31）；对于interval值，是日数
SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
结果：16
SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
结果：40decade
年份域除以10
SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
结果：200dow
一周中的日，从周日（0）到周六（6）
SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
结果：5请注意，extract的一周中的日和to_char(..., 'D')函数不同。
doy
一年的第几天（1 -365/366）
SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
结果：47epoch
对于timestamp with time zone值， 是自 1970-01-01 00:00:00 UTC 以来的秒数（结果可能是负数）； 对于date and timestamp值，是自本地时间 1970-01-01 00:00:00 以来的描述；对于interval值，它是时间间隔的总秒数。
SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');
结果：982384720.12
SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
结果：442800不能用to_timestamp把一个 epoch 值转换回成时间戳：
SELECT to_timestamp(982384720.12);
Result: 2001-02-17 04:38:40.12+00hour
小时域（0 - 23）
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
结果：20isodow
一周中的日，从周一（1）到周日（7）
SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
结果：7除了周日，这和dow相同。这符合ISO 8601 中一周中的日的编号。
isoyear
日期所落在的ISO 8601 周编号的年（不适用于间隔）
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01');
结果：2005
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
结果：2006每一个ISO 8601 周编号的年都开始于包含1月4日的那一周的周一，在早的1月或迟的12月中ISO年可能和格里高利年不同。更多信息见week域。
这个域不能用于 PostgreSQL 8.3之前的版本。
microseconds
秒域，包括小数部分，乘以 1,000,000。请注意它包括全部的秒
SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
结果：28500000millennium
千年
SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
结果：319xx的年份在第二个千年里。第三个千年从 2001 年 1 月 1 日开始。
milliseconds
秒域，包括小数部分，乘以 1000。请注意它包括完整的秒。
SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
结果：28500minute
分钟域（0 - 59）
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
结果：38month
对于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');
结果：1quarter
该天所在的该年的季度（1 - 4）
SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
结果：1second
秒域，包括小数部分（0 - 59）
SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
结果：40
SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
结果：28.5
timezone
与 UTC 的时区偏移，以秒记。正数对应 UTC 东边的时区，负数对应 UTC 西边的时区（从技术上来看，PostgreSQL不使用 UTC，因为其中不处理闰秒）。
timezone_hour
时区偏移的小时部分。
timezone_minute
时区偏移的分钟部分。
week
该天在所在的ISO 8601 周编号的年份里是第几周。根据定义， 一年的第一周包含该年的 1月 4 日并且 ISO 周从星期一开始。换句话说，一年的第一个星期四在第一周。
在 ISO 周编号系统中，早的 1 月的日期可能位于前一年的第五十二或者第五十三周，而迟的 12 月的日期可能位于下一年的第一周。例如， 2005-01-01位于 2004 年的第五十三周，并且2006-01-01位于 2005 年的第五十二周，而2012-12-31位于 2013 年的第一周。我们推荐把isoyear域和week一起使用来得到一致的结果。
SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
结果：7year
年份域。要记住这里没有0 AD，所以从AD年里抽取BC年应该小心处理。
SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
结果：2001注意：
当输入值为 +/-Infinity 时，
extract对于单调增的域（epoch、julian、year、isoyear、decade、century以及millennium）返回 +/-Infinity。对于其他域返回 NULL。PostgreSQL 9.6 之前的版本对所有输入无穷的情况都返回零。
extract函数主要的用途是做计算性处理。对于用于显示的日期/时间值格式化，参阅 上一节（第八节）。
在传统的Ingres上建模的date_part函数等价于SQL标准函数extract：
date_part('field', source)
请注意这里的field参数必须是一个串值，而不是一个名字。有效的date_part域名 和extract相同。
SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
结果：16
SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
结果：49.2 date_trunc
date_trunc函数在概念上和用于数字的trunc函数类似。
date_trunc('field', source)
source是类型timestamp或interval的值表达式（类型date和 time的值都分别被自动转换成timestamp或者interval）。field选择对输入值选用什么样的精度进行截断。返回的值是timestamp类型或者所有小于选定的 精度的域都设置为零（或者一，对于日期和月份）的interval。
field的有效值是∶
microseconds |
milliseconds |
second |
minute |
hour |
day |
week |
month |
quarter |
year |
decade |
century |
millennium |
例子：
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
结果：2001-02-16 20:00:00
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
结果：2001-01-01 00:00:009.3 AT TIME ZONE
AT TIME ZONE把时间戳without time zone转换成时间戳with time zone或者反过来，并且把time值转换成不同的时区。表 9.1 展示了它的变体。
表 9.1. AT TIME ZONE变体
| 表达式 | 返回类型 | 描述 |
|---|---|---|
| timestamp with time zone | 把给定的不带时区的时间戳当作位于指定时区的时间对待 |
| timestamp without time zone | 把给定的带时区的时间戳转换到新的时区，不带时区指定 |
| time with time zone | 把给定的带时区的时间转换到新时区 |
在这些表达式里，我们需要的时区zone可以指定为文本串（例如，'America/Los_Angeles'）或者一个间隔 （例如，INTERVAL '-08:00'）。 在文本情况下，可用的时区名字可以用第八节中描述的任何方式指定。
例子（假设本地时区是America/Los_Angeles）：
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';
Result: 2001-02-16 19:38:40-08
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver';
Result: 2001-02-16 18:38:40
SELECT TIMESTAMP '2001-02-16 20:38:40-05' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago';
Result: 2001-02-16 05:38:40第一个例子给缺少时区的值加上了时区，并且显示了使用当前TimeZone设置的值。第二个例子把带有时区值的时间戳移动到指定的时区，并且返回不带时区的值。这允许存储和显示不同于当前TimeZone设置的值。第三个例子把东京时间转换成芝加哥时间。把time值转换成其他时区会使用当前活跃的时区规则，因为没有提供日期。
函数timezone(zone, timestamp)timestamp AT TIME ZONE zone
9.4 当前日期/时间
PostgreSQL提供了许多返回当前日期和时间的函数。这些 SQL 标准的函数全部都按照当前事务的开始时刻返回值：
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME(precision)
CURRENT_TIMESTAMP(precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME(precision)
LOCALTIMESTAMP(precision)CURRENT_TIME和CURRENT_TIMESTAMP传递带有时区的值；LOCALTIME和LOCALTIMESTAMP传递的值不带时区。
CURRENT_TIME、CURRENT_TIMESTAMP、LOCALTIME和 LOCALTIMESTAMP可以有选择地接受一个精度参数， 该精度导致结果的秒域被园整为指定小数位。如果没有精度参数，结果将被给予所能得到的全部精度。
一些例子：
SELECT CURRENT_TIME;
结果：14:39:53.662522-05
SELECT CURRENT_DATE;
结果：2001-12-23
SELECT CURRENT_TIMESTAMP;
结果：2001-12-23 14:39:53.662522-05
SELECT CURRENT_TIMESTAMP(2);
结果：2001-12-23 14:39:53.66-05
SELECT LOCALTIMESTAMP;
结果：2001-12-23 14:39:53.662522因为这些函数全部都按照当前事务的开始时刻返回结果，所以它们的值在事务运行的整个期间内都不改变。 我们认为这是一个特性：目的是为了允许一个事务在“当前”时间上有一致的概念， 这样在同一个事务里的多个修改可以保持同样的时间戳。
注意：
许多其它数据库系统可能会更频繁地推进这些值。
PostgreSQL还提供了返回当前语句的开始时间以及 调用该函数时的实际当前时间的函数。这些非 SQL 标准的函数列表如下：
transaction_timestamp()
statement_timestamp()
clock_timestamp()
timeofday()
now()transaction_timestamp()等价于CURRENT_TIMESTAMP，但是其命名清楚地反映了它的返回值。statement_timestamp()返回当前语句的开始时刻（更准确的说是收到 客户端最后一条命令的时间）。statement_timestamp()和transaction_timestamp()在一个事务的第一条命令期间返回值相同，但是在随后的命令中却不一定相同。 clock_timestamp()返回真正的当前时间，因此它的值甚至在同一条 SQL 命令中都会变化。timeofday()是一个有历史原因的PostgreSQL函数。和clock_timestamp()相似，timeofday()也返回真实的当前时间，但是它的结果是一个格式化的text串，而不是timestamp with time zone值。now()是PostgreSQL的一个传统，等效于transaction_timestamp()。
所有日期/时间类型还接受特殊的文字值now，用于指定当前的日期和时间（重申，被解释为当前事务的开始时刻）。 因此，下面三个都返回相同的结果：
SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now'; -- 对于和 DEFAULT 一起使用是不正确的提示：
在创建表期间指定一个
DEFAULT子句时，你不会希望使用第三种形式。系统将在分析这个常量的时候把now转换为一个timestamp， 这样需要默认值时就会得到创建表的时间！而前两种形式要到实际使用缺省值的时候才被计算， 因为它们是函数调用。因此它们可以给出每次插入行的时刻。
9.5 延时执行
下面的这些函数可以用于让服务器进程延时执行：
pg_sleep(seconds)
pg_sleep_for(interval)
pg_sleep_until(timestamp with time zone)
pg_sleep让当前的会话进程休眠seconds 秒以后再执行。seconds是一个double precision 类型的值，所以可以指定带小数的秒数。pg_sleep_for是针对用 interval指定的较长休眠时间的函数。pg_sleep_until 则可以用来休眠到一个指定的时刻唤醒。例如：
SELECT pg_sleep(1.5);
SELECT pg_sleep_for('5 minutes');
SELECT pg_sleep_until('tomorrow 03:00');注意：
有效的休眠时间间隔精度是平台相关的，通常 0.01 秒是通用值。休眠延迟将至少持续指 定的时长， 也有可能由于服务器负荷而比指定的时间长。特别地，
pg_sleep_until并不保证能刚好在指定的时刻被唤醒，但它不会 在比指定时刻早的时候醒来。
警告：
请确保在调用
pg_sleep或者其变体时，你的会话没有持有不必要 的锁。否则其它会话可能必须等待你的休眠会话，因而减慢整个系统速度。
十、枚举支持函数
对于枚举类型(在上一章（教程十一）中描述）， 有一些函数允许更清洁的编码，而不需要为一个枚举类型硬写特定的值。它们被列在 表 10.1 中。本例假定一个枚举类型被创建为：
CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple');
表 10.1. 枚举支持函数
请注意，除了双参数形式的enum_range外， 这些函数忽略传递给它们的具体值，它们只关心声明的数据类型。 空值或类型的一个特定值可以通过，并得到相同的结果。这些函数更多地被用于一个表列或函数参数，而不是一个硬写的类型名，如例子中所建议。
十一、几何函数和操作符
几何类型point、box、 lseg、line、path、 polygon和circle有一大堆本地支持函数和操作符，如 表11.1、表 11.2 和表 11.3 中所示。
小心：
请注意“same as”操作符（
~=），表示point、box、polygon和circle类型的一般相等概念。这些类型中的某些还有一个=操作符，但是=只比较相同的面积。其它的标量比较操作符 （<=等等）也是为这些类型比较面积。
表 11.1. 几何操作符
| 操作符 | 描述 | 例子 |
|---|---|---|
+ | 平移 | 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)' |
# | 相交的点或方框 | box '((1,-1),(-1,1))' # box '((1,1),(-2,-2))' |
# | 路径或多边形中的点数 | # path '((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))' |
注意：
在PostgreSQL之前，包含操作符
@>和<@被分别称为~和@。 这些名字仍然可以使用，但是已被废除并且最终将被移除。
表 11.2. 几何函数
| 函数 | 返回类型 | 描述 | 例子 |
|---|---|---|---|
| double precision | 面积 | area(box '((0,0),(1,1))') |
| point | 中心 | center(box '((0,0),(1,2))') |
| double precision | 圆的直径 | diameter(circle '((0,0),2.0)') |
| double precision | 方框的垂直尺寸 | height(box '((0,0),(1,1))') |
| boolean | 一个封闭路径？ | isclosed(path '((0,0),(1,1),(2,0))') |
| boolean | 一个开放路径？ | isopen(path '[(0,0),(1,1),(2,0)]') |
| double precision | 长度 | length(path '((-1,0),(1,0))') |
| int | 点数 | npoints(path '[(0,0),(1,1),(2,0)]') |
| int | 点数 | npoints(polygon '((1,1),(0,0))') |
| path | 将路径转换成封闭的 | pclose(path '[(0,0),(1,1),(2,0)]') |
| path | 将路径转换成开放 | popen(path '((0,0),(1,1),(2,0))') |
| double precision | 圆的半径 | radius(circle '((0,0),2.0)') |
| double precision | 方框的水平尺寸 | width(box '((0,0),(1,1))') |
表 11.3. 几何类型转换函数
我们可以把一个point的两个组成数字当作具有索引 0 和 1 的数组访问。例如，如果t.p是一个point列，那么SELECT p[0] FROM t检索 X 座标而 UPDATE t SET p[1] = ...改变 Y 座标。同样，box或者lseg类型的值可以当作两个point值的数组值看待。
函数area可以用于类型box、circle和path。area函数操作path数据类型的时候， 只有在path的点没有交叉的情况下才可用。例如，path '((0,0),(0,1),(2,1),(2,2),(1,2),(1,0),(0,0))'::PATH是不行的， 而下面的视觉上相同的 path '((0,0),(0,1),(1,1),(1,2),(2,2),(2,1),(1,1),(1,0),(0,0))'::PATH就可以。 如果交叉和不交叉的path概念让你疑惑，那么把上面两个path都画在一张图纸上，你就明白了。
十二、网络地址函数和操作符
表 12.1 展示了可以用于cidr和 inet类型的操作符。 操作符<<、<<=、 >>、>>=和 &&测试用于子网包含。它们只考虑两个地址的网 络部分（忽略任何主机部分），然后判断其中一个网络部分是等于另外一个或者是 另外一个的子网。
表 12.1. 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' |
<> | 不等于 | 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' |
&& | 包含或者被包含contains or is contained by | inet '192.168.1/24' && inet '192.168.1.80/28' |
~ | 按位 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' |
表 12.2 展示了所有可以用于cidr和inet类型的函数。函数abbrev、host和text主要是为了提供可选的显示格式用的。
表 12.2. cidr和inet函数
任何cidr值都能够被隐式或显式地转换为inet值， 因此上述能够操作inet值的函数也同样能够操作cidr值（也有独立的用于inet和cidr的函数，因为它的行为应当和这两种情况不同）。inet值也可以转换为cidr值。完成时，该网络掩码右侧的任何位都将无声地转换为零以获得一个有效的cidr值。另外，你还可以使用常规的造型语法将一个文本字符串转换为inet或cidr值：例如，inet(或expression)colname::cidr
表 12.3 展示了可以用于macaddr类型的函数。 函数trunc(macaddr)
表 12.3. macaddr函数
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
| macaddr | 设置最后3个字节为零 | trunc(macaddr '12:34:56:78:90:ab') | 12:34:56:00:00:00 |
macaddr类型还支持标准关系操作符 （>、<=等） 用于编辑次序，并且按位算术操作符（~、&和|）用于 NOT、AND 和 OR。
表 12.4 中展示了可以用于macaddr8类型的函数。函数trunc(macaddr8)
表 12.4. macaddr8函数
macaddr8类型也支持用于排序的标准关系操作符（>、<=等）以及用于NOT、AND和OR的位运算操作符（~、&和|）。
十三、文本搜索函数和操作符
表 13.1、表 13.2 和 表 13.3 总结了为全文搜索提供的函数和操作符。PostgreSQL的文本搜索功能的详细解释可参考后面章节。
表 13.1. 文本搜索操作符
| 操作符 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
@@ | boolean | tsvector匹配tsquery吗？ | to_tsvector('fat cats ate rats') @@ to_tsquery('cat & rat') | t |
@@@ | boolean | @@的已废弃同义词 | to_tsvector('fat cats ate rats') @@@ to_tsquery('cat & rat') | t |
|| | tsvector | 连接tsvector | 'a:1 b:2'::tsvector || 'c:1 d:2 b:3'::tsvector | 'a':1 'b':2,5 'c':3 'd':4 |
&& | tsquery | 将tsquery用 AND 连接起来 | 'fat | rat'::tsquery && 'cat'::tsquery | ( 'fat' | 'rat' ) & 'cat' |
|| | tsquery | 将tsquery用 OR 连接起来 | 'fat | rat'::tsquery || 'cat'::tsquery | ( 'fat' | 'rat' ) | 'cat' |
!! | tsquery | 对一个tsquery取反 | !! 'cat'::tsquery | !'cat' |
<-> | tsquery | tsquery后面跟着tsquery | to_tsquery('fat') <-> to_tsquery('rat') | 'fat' <-> 'rat' |
@> | boolean | tsquery包含另一个？ | 'cat'::tsquery @> 'cat & rat'::tsquery | f |
<@ | boolean | tsquery被包含？ | 'cat'::tsquery <@ 'cat & rat'::tsquery | t |
注意：
tsquery的包含操作符只考虑两个查询中的词位，而忽略组合操作符。除了显示在表中的操作符，还定义了
tsvector和tsquery类型的普通B-tree比较操作符（=、<等）。它们对于文本搜索不是很有用，但是允许使用。例如，建在这些类型列上的唯一索引。
表 13.2. 文本搜索函数
注意：
所有接受一个可选的
regconfig参数的文本搜索函数在该参数被忽略时，使用由 default_text_search_config指定的配置。表 13.3 中的函数被单独列出，因为它们通常不被用于日常的文本搜索操作。 它们有助于开发和调试新的文本搜索配置。
表 13.3. 文本搜索调试函数
十四、XML函数
本节中描述的函数以及类函数的表达式都在类型xml的值上操作。用于在值和类型xml之间转换的类函数的表达式xmlparse和xmlserialize就不在这里重复介绍。使用大部分这些函数要求安装时使用了configure --with-libxml进行编译。
14.1 产生XML内容
有一组函数和类函数的表达式可以用来从 SQL 数据产生 XML 内容。它们特别适合于将查询结果格式化成 XML 文档以便于在客户端应用中处理。
14.1.1 xmlcomment
xmlcomment(text)
函数xmlcomment创建了一个 XML 值，它包含一个使用指定文本作为内容的 XML 注释。该文本不包含“--”或者也不会以一个“-”结尾，这样结果的结构是一个合法的 XML 注释。如果参数为空，结果也为空。
例子：
SELECT xmlcomment('hello');
xmlcomment
--------------
<!--hello-->14.1.2 xmlconcat
xmlconcat(xml[, ...])
函数xmlconcat将由单个 XML 值组成的列表串接成一个单独的值，这个值包含一个 XML 内容片断。空值会被忽略，只有当没有参数为非空时结果才为空。
例子：
SELECT xmlconcat('<abc/>', '<bar>foo</bar>');
xmlconcat
----------------------
<abc/><bar>foo</bar>如果 XML 声明存在，它们会按照下面的方式被组合。如果所有的参数值都有相同的 XML 版本声明，该版本将被用在结果中，否则将不使用版本。如果所有参数值有独立声明值“yes”，那么该值将被用在结果中。如果所有参数值都有一个独立声明值并且至少有一个为“no”，则“no”被用在结果中。否则结果中将没有独立声明。如果结果被决定要要求一个独立声明但是没有版本声明，将会使用一个版本 1.0 的版本声明，因为 XML 要求一个 XML 声明要包含一个版本声明。编码声明会被忽略并且在所有情况中都会被移除。
例子：
SELECT xmlconcat('<?xml version="1.1"?><foo/>', '<?xml version="1.1" standalone="no"?><bar/>');
xmlconcat
-----------------------------------
<?xml version="1.1"?><foo/><bar/>
14.1.3 xmlelement
xmlelement(name name [, xmlattributes(value [AS attname] [, ... ])] [, content, ...])
表达式xmlelement使用给定名称、属性和内容产生一个 XML 元素。
例子：
SELECT xmlelement(name foo);
xmlelement
------------
<foo/>
SELECT xmlelement(name foo, xmlattributes('xyz' as bar));
xmlelement
------------------
<foo bar="xyz"/>
SELECT xmlelement(name foo, xmlattributes(current_date as bar), 'cont', 'ent');
xmlelement
-------------------------------------
<foo bar="2007-01-26">content</foo>不是合法 XML 名字的元素名和属性名将被逃逸，逃逸的方法是将违反的字符用序列_x替换，其中HHHH_HHHH是被替换字符的 Unicode 代码点的十六进制表示。例如：
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 文档。例如：
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 字符数据。这意味着字符 <, >, 和 & 将被转换为实体。二进制数据（数据类型bytea）将被表示成 base64 或十六进制编码，具体取决于配置参数 xmlbinary的设置。为了将 SQL 和 PostgreSQL 数据类型和 XML 模式声明对齐，我们期待单独数据类型的特定行为能够改进，到那时将会出现一个更为精确的描述。
14.1.4 xmlforest
xmlforest(content [AS name] [, ...])
表达式xmlforest使用给定名称和内容产生一个元素的 XML 森林（序列）。
例子：
SELECT xmlforest('abc' AS foo, 123 AS bar);
xmlforest
------------------------------
<foo>abc</foo><bar>123</bar>
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所示，非法 XML 名字的元素名会被逃逸。相似地，内容数据也会被逃逸来产生合法的 XML 内容，除非它已经是一个xml类型。
注意如果 XML 森林由多于一个元素组成，那么它不是合法的 XML 文档，因此在xmlelement中包装xmlforest表达式会有用处。
14.1.5 xmlpi
xmlpi(name target [, content])
表达式xmlpi创建一个 XML 处理指令。如果存在内容，内容不能包含字符序列?>。
例子：
SELECT xmlpi(name php, 'echo "hello world";');
xmlpi
-----------------------------
<?php echo "hello world";?>14.1.6 xmlroot
xmlroot(xml, version text | no value [, standalone yes|no|no value])
表达式xmlroot修改一个 XML 值的根结点的属性。如果指定了一个版本，它会替换根节点的版本声明中的值；如果指定了一个独立设置，它会替换根节点的独立声明中的值。
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>14.1.7 xmlagg
xmlagg(xml)
和这里描述的其他函数不同，函数xmlagg是一个聚集函数。它将聚集函数调用的输入值串接起来，非常像xmlconcat所做的事情，除了串接是跨行发生的而不是在单一行的多个表达式上发生。
例子：
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/>为了决定串接的顺序，可以为聚集调用增加一个ORDER BY子句，例如：
SELECT xmlagg(x ORDER BY y DESC) FROM test;
xmlagg
----------------------
<bar/><foo>abc</foo>我们推荐在以前的版本中使用下列非标准方法，并且它们在特定情况下仍然有用：
SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab;
xmlagg
----------------------
<bar/><foo>abc</foo>14.2 XML 谓词
这一节描述的表达式检查xml值的属性。
14.2.1 IS DOCUMENT
xml IS DOCUMENT
如果参数 XML 值是一个正确的 XML 文档，则IS DOCUMENT返回真，如果不是则返回假（即它是一个内容片断），或者是参数为空时返回空。
14.2.2 IS NOT DOCUMENT
xml IS NOT DOCUMENT
如果参数中的XML值是一个正确的XML文档，那么表达式IS NOT DOCUMENT返回假，否则返回真（也就是说它是一个内容片段），如果参数为空则返回空。
14.2.3 XMLEXISTS
XMLEXISTS(text PASSING [BY REF] xml [BY REF])
如果第一个参数中的 XPath 表达式返回任何结点，则函数xmlexists返回真，否则返回假（如果哪一个参数为空，则结果就为空）。
例子：
SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF '<towns><town>Toronto</town><town>Ottawa</town></towns>');
xmlexists
------------
t
(1 row)BY REF子句在 PostgreSQL 中没有作用，但是为了和其他实现保持 SQL 一致性和兼容性还是允许它出现。每一种 SQL 标准，第一个BY REF是被要求的，而第二个则是可选的。也要注意 SQL 标准指定xmlexists结构来将一个 XQuery 表达式作为第一个参数，但 PostgreSQL 目前只支持 XPath，它是 XQuery的一个子集。
14.2.4 xml_is_well_formed
xml_is_well_formed(text)
xml_is_well_formed_document(text)
xml_is_well_formed_content(text)
这些函数检查一个text串是不是一个良构的 XML，返回一个布尔结果。xml_is_well_formed_document检查一个良构的文档，而xml_is_well_formed_content检查良构的内容。如果xmloption配置参数被设置为DOCUMENT，xml_is_well_formed会做第一个函数的工作；如果配置参数被设置为CONTENT，xml_is_well_formed会做第二个函数的工作。这意味着xml_is_well_formed对于检查一个到类型xml的简单造型是否会成功非常有用，而其他两个函数对于检查XMLPARSE的对应变体是否会成功有用。
例子：
SET xmloption TO DOCUMENT;
SELECT xml_is_well_formed('<>');
xml_is_well_formed
--------------------
f
(1 row)
SELECT xml_is_well_formed('<abc/>');
xml_is_well_formed
--------------------
t
(1 row)
SET xmloption TO CONTENT;
SELECT xml_is_well_formed('abc');
xml_is_well_formed
--------------------
t
(1 row)
SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</pg:foo>');
xml_is_well_formed_document
-----------------------------
t
(1 row)
SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</my:foo>');
xml_is_well_formed_document
-----------------------------
f
(1 row)最后一个例子显示了这些检查也包括名字空间是否正确地匹配。
14.3 处理 XML
要处理数据类型xml的值， PostgreSQL 提供了函数xpath和xpath_exists，它们计算 XPath 1.0 表达式以及XMLTABLE表函数。
14.3.1 xpath
xpath(xpath, xml [, nsarray])
函数xpath在 XML 值 xml上计算 XPath 表达式xpath (a text value)。它返回一个 XML 值的数组，该数组对应于该 XPath 表达式产生的结点集合。如果该 XPath 表达式返回一个标量值而不是一个结点集合，将会返回一个单一元素的数组。
第二个参数必须是一个良构的 XML 文档。特殊地，它必须有一个单一根结点元素。
该函数可选的第三个参数是一个名字空间映射的数组。这个数组应该是一个二维text数组，其第二轴长度等于2（即它应该是一个数组的数组，其中每一个都由刚好 2 个元素组成）。每个数组项的第一个元素是名字空间的名称（别名），第二个元素是名字空间的 URI。并不要求在这个数组中提供的别名和在 XML 文档本身中使用的那些名字空间相同（换句话说，在 XML 文档中和在xpath函数环境中，别名都是本地的）。
例子：
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)要处理默认（匿名）命名空间，做这样的事情：
SELECT xpath('//mydefns:b/text()', '<a xmlns="http://example.com"><b>test</b></a>',
ARRAY[ARRAY['mydefns', 'http://example.com']]);
xpath
--------
{test}
(1 row)14.3.2 xpath_exists
xpath_exists(xpath, xml [, nsarray])
函数xpath_exists是xpath函数的一种特殊形式。这个函数不是返回满足 XPath 的单一 XML 值，它返回一个布尔值表示查询是否被满足。这个函数等价于标准的XMLEXISTS谓词，不过它还提供了对一个名字空间映射参数的支持。
例子：
SELECT xpath_exists('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>',
ARRAY[ARRAY['my', 'http://example.com']]);
xpath_exists
--------------
t
(1 row)14.3.3 xmltable
xmltable( [XMLNAMESPACES(namespace uri AS namespace name[, ...]), ]
row_expression PASSING [BY REF] document_expression [BY REF]
COLUMNS name { type [PATH column_expression] [DEFAULT default_expression] [NOT NULL | NULL]
| FOR ORDINALITY }
[, ...]
)xmltable函数基于给定的XML值产生一个表、一个抽取行的XPath过滤器以及一个可选的列定义集合。
可选的XMLNAMESPACES子句是一个逗号分隔的名字空间列表。它指定文档中使用的XML名字空间极其别名。当前不支持默认的名字空间说明。
必需的row_expression参数是一个XPath表达式，该表达式会根据所提供的XML文档进行计算来得到一个有序的XML节点序列。xmltable会把这个序列转变成输出行。
document_expression提供要操作的XML文档。BY REF子句在PostgreSQL中没有效果，允许它的目的是为了遵守SQL以及与其他实现相兼容。该参数必须是一个结构良好的XML文档，不接受XML片段或者森林。
强制需要的COLUMNS子句指定输出表中的列列表。如果COLUMNS子句被省略，结果集合中的行包含类型为xml的单一列，列中包含匹配row_expression的数据。如果指定了COLUMNS，则每一项描述一个列。格式请见上面的语法综述。列名和类型是必需的，路径、默认值以及为空性子句是可选的。
被标记为FOR ORDINALITY的列将被行号填充，它们匹配输出列出现在原始的输入XML文档中的顺序。最多只能有一个列被标记为FOR ORDINALITY。
一个列的column_expression是一个要针对每行（与row_expression的结果有关）计算的XPath表达式，它用来得到该列的值。如果没有给出column_expression，则把列名用作一种隐式路径。
如果一个列的XPath表达式返回多个元素，则会发生错误。如果该表达式匹配一个空标记，则结果是一个空字符串（不是NULL）。任何xsi:nil属性都会被忽略。
被column_expression匹配上的XML的文本主体被用作该列的值。一个元素中的多个text()节点会按照顺序串接起来。任何子元素、处理指令以及注释都会被忽略，但是子元素的文本内容会被串接到结果中。注意，两个非文本元素之间的仅有空格的text()节点会被保留，并且text()节点上的前导空格不会被平面化。
如果路径表达式不匹配一个给定行但制定有default_expression，则会使用从该表达式计算出的结果值。如果没有对该列给出DEFAULT子句，则该字段将被设置为NULL。default_expression可以引用在列列表中出现在它前面的输出列值，因此一列的默认值可能会基于另一列的值。
列可能会被标记为NOT NULL。如果一个NOT NULL列的column_expression不匹配任何东西并且没有DEFAULT或者default_expression也计算为空，则会报告一个错误。
和常规的PostgreSQL函数不同，column_expression和default_expression在调用前不会被计算为简单值。column_expression通常为每一个输入行计算正好一次，default_expression则在每当一个字段需要默认值时都会被计算。如果表达式是稳定的或者不变的，则重复计算可以被跳过。实际上xmltable的行为更像一个子查询而不是函数调用。这意味着你可以在default_expression中使用易变函数，并且column_expression可以基于XML文档的其他部分。
例子：
CREATE TABLE xmldata AS SELECT
xml $$
<ROWS>
<ROW id="1">
<COUNTRY_ID>AU</COUNTRY_ID>
<COUNTRY_NAME>Australia</COUNTRY_NAME>
</ROW>
<ROW id="5">
<COUNTRY_ID>JP</COUNTRY_ID>
<COUNTRY_NAME>Japan</COUNTRY_NAME>
<PREMIER_NAME>Shinzo Abe</PREMIER_NAME>
<SIZE unit="sq_mi">145935</SIZE>
</ROW>
<ROW id="6">
<COUNTRY_ID>SG</COUNTRY_ID>
<COUNTRY_NAME>Singapore</COUNTRY_NAME>
<SIZE unit="sq_km">697</SIZE>
</ROW>
</ROWS>
$$ AS data;
SELECT xmltable.*
FROM xmldata,
XMLTABLE('//ROWS/ROW'
PASSING data
COLUMNS id int PATH '@id',
ordinality FOR ORDINALITY,
"COUNTRY_NAME" text,
country_id text PATH 'COUNTRY_ID',
size_sq_km float PATH 'SIZE[@unit = "sq_km"]',
size_other text PATH
'concat(SIZE[@unit!="sq_km"], " ", SIZE[@unit!="sq_km"]/@unit)',
premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified') ;
id | ordinality | COUNTRY_NAME | country_id | size_sq_km | size_other | premier_name
----+------------+--------------+------------+------------+--------------+---------------
1 | 1 | Australia | AU | | | not specified
5 | 2 | Japan | JP | | 145935 sq_mi | Shinzo Abe
6 | 3 | Singapore | SG | 697 | | not specified
接下来的例子展示了多个text()节点的串接、列名用作XPath过滤器的用法以及对空格、XML注释和处理指令的处理：
CREATE TABLE xmlelements AS SELECT
xml $$
<root>
<element> Hello<!-- xyxxz -->2a2<?aaaaa?> <!--x--> bbb<x>xxx</x>CC </element>
</root>
$$ AS data;
SELECT xmltable.*
FROM xmlelements, XMLTABLE('/root' PASSING data COLUMNS element text);
element
----------------------
Hello2a2 bbbCC 下面的例子展示了如何使用XMLNAMESPACES子句指定用在XML文档以及XPath表达式中的名字空间列表：
WITH xmldata(data) AS (VALUES ('
<example xmlns="http://example.com/myns" xmlns:B="http://example.com/b">
<item foo="1" B:bar="2"/>
<item foo="3" B:bar="4"/>
<item foo="4" B:bar="5"/>
</example>'::xml)
)
SELECT xmltable.*
FROM XMLTABLE(XMLNAMESPACES('http://example.com/myns' AS x,
'http://example.com/b' AS "B"),
'/x:example/x:item'
PASSING (SELECT data FROM xmldata)
COLUMNS foo int PATH '@foo',
bar int PATH '@B:bar');
foo | bar
-----+-----
1 | 2
3 | 4
4 | 5
(3 rows)14.4 将表映射到 XML
下面的函数将会把关系表的内容映射成 XML 值。它们可以被看成是 XML 导出功能：
table_to_xml(tbl regclass, nulls boolean, tableforest boolean, targetns text)
query_to_xml(query text, nulls boolean, tableforest boolean, targetns text)
cursor_to_xml(cursor refcursor, count int, nulls boolean,
tableforest boolean, targetns text)
每一个函数的返回值都是xml。
table_to_xml映射由参数tbl传递的命名表的内容。regclass类型接受使用常见标记标识表的字符串，包括可选的模式限定和双引号。query_to_xml执行由参数query传递的查询并且映射结果集。cursor_to_xml从cursor指定的游标中取出指定数量的行。如果需要映射一个大型的表，我们推荐这种变体，因为每一个函数都是在内存中构建结果值的。
如果tableforest为假，则结果的 XML 文档看起来像这样：
<tablename>
<row>
<columnname1>data</columnname1>
<columnname2>data</columnname2>
</row>
<row>
...
</row>
...
</tablename>如果tableforest为真，结果是一个看起来像这样的 XML 内容片断：
<tablename>
<columnname1>data</columnname1>
<columnname2>data</columnname2>
</tablename>
<tablename>
...
</tablename>
...
如果没有表名可用，在映射一个查询或一个游标时，在第一种格式中使用串table，在第二种格式中使用row。
这几种格式的选择由用户决定。第一种格式是一个正确的 XML 文档，它在很多应用中都很重要。如果结果值要被重组为一个文档，第二种格式在cursor_to_xml函数中更有用。前文讨论的产生 XML 内容的函数（特别是xmlelement）可以被用来把结果修改成符合用户的要求。
数据值会被以前文的函数xmlelement中描述的相同方法映射。
参数nulls决定空值是否会被包含在输出中。如果为真，列中的空值被表示为：
<columnname xsi:nil="true"/>
其中xsi是 XML 模式实例的 XML 名字空间前缀。一个合适的名字空间声明将被加入到结果值中。如果为假，包含空值的列将被从输出中忽略掉。
参数targetns指定想要的结果的 XML 名字空间。如果没有想要的特定名字空间，将会传递一个空串。
下面的函数返回 XML 模式文档，这些文档描述上述对应函数所执行的映射：
table_to_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns text)
query_to_xmlschema(query text, nulls boolean, tableforest boolean, targetns text)
cursor_to_xmlschema(cursor refcursor, nulls boolean, tableforest boolean, targetns text)最重要的是相同的参数被传递来获得匹配的 XML 数据映射和 XML 模式文档。
下面的函数产生 XML 数据映射和对应的 XML 模式，并把产生的结果链接在一起放在一个文档（或森林）中。在要求自包含和自描述的结果是它们非常有用：
table_to_xml_and_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns text)
query_to_xml_and_xmlschema(query text, nulls boolean, tableforest boolean, targetns text)另外，下面的函数可用于产生相似的整个模式或整个当前数据库的映射：
schema_to_xml(schema name, nulls boolean, tableforest boolean, targetns text)
schema_to_xmlschema(schema name, nulls boolean, tableforest boolean, targetns text)
schema_to_xml_and_xmlschema(schema name, nulls boolean, tableforest boolean, targetns text)
database_to_xml(nulls boolean, tableforest boolean, targetns text)
database_to_xmlschema(nulls boolean, tableforest boolean, targetns text)
database_to_xml_and_xmlschema(nulls boolean, tableforest boolean, targetns text)注意这些函数可能产生很多数据，它们都需要在内存中被构建。在请求大型模式或数据库的内容映射时，可以考虑分别映射每一个表，甚至通过一个游标来映射。
一个模式内容映射的结果看起来像这样：
<schemaname>
table1-mapping
table2-mapping
...
</schemaname>其中一个表映射的格式取决于上文解释的tableforest参数。
一个数据库内容映射的结果看起来像这样：
<dbname>
<schema1name>
...
</schema1name>
<schema2name>
...
</schema2name>
...
</dbname>其中的模式映射如上所述。
作为一个使用这些函数产生的输出的例子，下例中展示了一个 XSLT 样式表，它将table_to_xml_and_xmlschema的输出转换为一个包含表数据的扁平转印的 HTML 文档。以一种相似的方式，这些函数的结果可以被转换成其他基于 XML 的格式。
例子：转换 SQL/XML 输出到 HTML 的 XSLT 样式表：
<?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>
十五、JSON函数和操作符
表 15.1 展示了可以用于两种 JSON 数据类型的操作符。
表 15.1. json和jsonb 操作符
| 操作符 | 右操作数类型 | 描述 | 例子 | 例子结果 |
|---|---|---|---|---|
-> | int | 获得 JSON 数组元素（索引从 0 开始，负整数从末尾开始计） | '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 | {"c":"baz"} |
-> | text | 通过键获得 JSON 对象域 | '{"a": {"b":"foo"}}'::json->'a' | {"b":"foo"} |
->> | int | 以text形式获得 JSON 数组元素 | '[1,2,3]'::json->>2 | 3 |
->> | text | 以text形式获得 JSON 对象域 | '{"a":1,"b":2}'::json->>'b' | 2 |
#> | text[] | 获取在指定路径的 JSON 对象 | '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' | {"c": "foo"} |
#>> | text[] | 以text形式获取在指定路径的 JSON 对象 | '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' | 3 |
注意：
对
json和jsonb类型，这些操作符都有其并行变体。 域/元素/路径抽取操作符返回与其左手输入（json或jsonb） 相同的类型，不过那些被指定为返回text的除外，它们的返回值会被强制 为文本。如果该 JSON 输入没有匹配请求的正确结构（例如那样的元素不存在），这些 域/元素/路径抽取操作符会返回 NULL 而不是失败。 接受整数 JSON 数组下标的 域/元素/路径抽取操作符都支持表示从数组末尾开始的负值下标形式。
如 表 15.2 中所示，还存在一些只适合 jsonb的操作符。这些操作符中的很多可以用jsonb 操作符类索引。
表 15.2. 额外的jsonb操作符
| 操作符 | 右操作数类型 | 描述 | 例子 |
|---|---|---|---|
@> | jsonb | 左边的 JSON 值是否在顶层包含右边的 JSON 路径/值项？ | '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb |
<@ | jsonb | 左边的 JSON 路径/值项是否被包含在右边的 JSON 值的顶层？ | '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb |
? | text | 键/元素字符串是否存在于 JSON 值的顶层？ | '{"a":1, "b":2}'::jsonb ? 'b' |
?| | text[] | 这些数组字符串中的任何一个是否做为顶层键存在？ | '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c'] |
?& | text[] | 是否所有这些数组字符串都作为顶层键存在？ | '["a", "b"]'::jsonb ?& array['a', 'b'] |
|| | jsonb | 把两个jsonb值串接成一个新的jsonb值 | '["a", "b"]'::jsonb || '["c", "d"]'::jsonb |
- | text | 从左操作数删除键/值对或者string 元素。键/值对基于它们的键值来匹配。 | '{"a": "b"}'::jsonb - 'a' |
- | text[] | 从左操作数中删除多个键/值对或者string元素。键/值对基于它们的键值来匹配。 | '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] |
- | integer | 删除具有指定索引（负值表示倒数）的数组元素。如果 顶层容器不是数组则抛出一个错误。 | '["a", "b"]'::jsonb - 1 |
#- | text[] | 删除具有指定路径的域或者元素（对于 JSON 数组，负值 表示倒数） | '["a", {"b":1}]'::jsonb #- '{1,b}' |
注意：
||操作符将其每一个操作数的顶层的元素串接起来。它不会递归 操作。例如，如果两个操作数都是具有公共域名称的对象，结果中的域值将 只是来自右手操作数的值。
表 15.3 展示了可用于创建 json 和 jsonb值的函数（没有用于 jsonb的与row_to_json和 array_to_json等价的函数。不过，to_jsonb函数 提供了这些函数的很大一部分相同的功能）。
表 15.3. JSON 创建函数
| 函数 | 描述 | 例子 | 例子结果 |
|---|---|---|---|
|
| 把该值返回为json或者jsonb。数组和组合 会被（递归）转换成数组和对象；对于不是数组和组合的值，如果有 从该类型到json的造型，造型函数将被用来执行该 转换；否则将产生一个标量值。对于任何不是数字、布尔、空值的标 量类型，将使用文本表达，在这种风格下它是一个合法的 json或者jsonb值。 | to_json('Fred said "Hi."'::text) | "Fred said \"Hi.\"" |
array_to_json(anyarray [, pretty_bool]) | 把数组作为一个 JSON 数组返回。一个 PostgreSQL 多维数组会成为一个数组 的 JSON 数组。如果pretty_bool为真，将在 第 1 维度的元素之间增加换行。 | array_to_json('{{1,5},{99,100}}'::int[]) | [[1,5],[99,100]] |
row_to_json(record [, pretty_bool]) | 把行作为一个 JSON 对象返回。如果pretty_bool为真，将在第1层元素之间增加换行。 | row_to_json(row(1,'foo')) | {"f1":1,"f2":"foo"} |
|
| 从一个可变参数列表构造一个可能包含异质类型的 JSON 数组。 | json_build_array(1,2,'3',4,5) | [1, 2, "3", 4, 5] |
|
| 从一个可变参数列表构造一个 JSON 对象。通过转换，该参数列表由交替 出现的键和值构成。 | json_build_object('foo',1,'bar',2) | {"foo": 1, "bar": 2} |
|
| 从一个文本数组构造一个 JSON 对象。该数组必须可以是具有偶数个成员的 一维数组（成员被当做交替出现的键/值对），或者是一个二维数组（每一个 内部数组刚好有 2 个元素，可以被看做是键/值对）。 |
| {"a": "1", "b": "def", "c": "3.5"} |
|
| json_object的这种形式从两个独立的数组得到键/值对。在其 他方面和一个参数的形式相同。 | json_object('{a, b}', '{1,2}') | {"a": "1", "b": "2"} |
注意：
array_to_json和row_to_json与to_json具有相同的行为，不过它们提供了更好的打印选项。针对to_json所描述 的行为同样也适用于由其他 JSON 创建函数转换的每个值。
hstore扩展有一个从hstore到json的造型，因此通过 JSON 创建函数转换的hstore值将被表示为 JSON 对象，而不是原始字符串值。
表 15.4 展示了可用来处理json 和jsonb值的函数。
表 15.4. JSON 处理
| 函数 | 返回值 | 描述 | 例子 | 例子结果 |
|---|---|---|---|---|
|
| int | 返回最外层 JSON 数组中的元素数量。 | json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]') | 5 |
|
|
| 扩展最外层的 JSON 对象成为一组键/值对。 | select * from json_each('{"a":"foo", "b":"bar"}') | key | value -----+------- a | "foo" b | "bar" |
|
| setof key text, value text | 扩展最外层的 JSON 对象成为一组键/值对。返回值将是text类型。 | select * from json_each_text('{"a":"foo", "b":"bar"}') | key | value -----+------- a | foo b | bar |
|
|
| 返回由path_elems指向的 JSON 值（等效于#>操作符）。 | json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4') | {"f5":99,"f6":"foo"} |
|
| text | 以text返回由path_elems指向的 JSON 值（等效于#>>操作符）。 | json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6') | foo |
|
| setof text | 返回最外层 JSON 对象中的键集合。 | json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}') | json_object_keys ------------------ f1 f2 |
|
| anyelement | 扩展from_json中的对象成一个行，它的列匹配由base定义的记录类型（见下文的注释）。 | select * from json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}}') | a | b | c
---+-----------+-------------
1 | {2,"a b"} | (4,"a b c")
|
|
| setof anyelement | 扩展from_json中最外的对象数组为一个集合，该集合的列匹配由base定义的记录类型。 | select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]') | a | b ---+--- 1 | 2 3 | 4 |
|
|
| 把一个 JSON 数组扩展成一个 JSON 值的集合。 | select * from json_array_elements('[1,true, [2,false]]') | value ----------- 1 true [2,false] |
|
| setof text | 把一个 JSON 数组扩展成一个text值集合。 | select * from json_array_elements_text('["foo", "bar"]') | value ----------- foo bar |
|
| text | 把最外层的 JSON 值的类型作为一个文本字符串返回。可能的类型是： object、array、string、number、 boolean以及null。 | json_typeof('-123.4') | number |
|
| record | 从一个 JSON 对象（见下文的注解）构建一个任意的记录。正如所有返回record 的函数一样，调用者必须用一个AS子句显式地定义记录的结构。 | select * from json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r myrowtype) | a | b | c | d | r
---+---------+---------+---+---------------
1 | [1,2,3] | {1,2,3} | | (123,"a b c")
|
|
| setof record | 从一个 JSON 对象数组（见下文的注解）构建一个任意的记录集合。正如所有返回record 的函数一样，调用者必须用一个AS子句显式地定义记录的结构。 | select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text); | a | b ---+----- 1 | foo 2 | |
|
|
| 返回from_json，其中所有具有空值的 对象域都被省略。其他空值不动。 | json_strip_nulls('[{"f1":1,"f2":null},2,null,3]') | [{"f1":1},2,null,3] |
|
|
| 返回target，其中由 path指定的节用 new_value替换，如果 path指定的项不存在并且 create_missing为真（默认为 true）则加上 new_value。正如面向路径的 操作符一样，出现在path中的 负整数表示从 JSON 数组的末尾开始数。 |
|
|
|
|
| 返回被插入了new_value的target。如果path指定的target节在一个 JSONB 数组中，new_value将被插入到目标之前（insert_after为false，默认情况）或者之后（insert_after为真）。如果path指定的target节在一个 JSONB 对象内，则只有当target不存在时才插入new_value。对于面向路径的操作符来说，出现在path中的负整数表示从 JSON 数组的末尾开始计数。 |
|
|
|
|
| 把from_json返回成一段 缩进后的 JSON 文本。 | jsonb_pretty('[{"f1":1,"f2":null},2,null,3]') | [
{
"f1": 1,
"f2": null
},
2,
null,
3
] |
注意：
很多这些函数和操作符将把 JSON 字符串中的 Unicode 转义转换成合适的单一字符。如果 输入类型是
jsonb，这就没有问题，因为该转换已经完成了。但是对于json输入，这可能会导致抛出一个错误。虽然函数
json_populate_record、json_populate_recordset、json_to_record以及json_to_recordset的例子使用了常量，但常见的用法是引用FROM子句中的表并且使用其json或jsonb列之一作为函数的参数。然后抽取出的键值可以被查询的其他部分引用，例如WHERE子句和目标列表。以这种方式抽取多个值的性能比用以键为单位的操作符单个抽取它们的性能更好。JSON键被匹配到目标行类型中的相同列名。这些函数的JSON类型强制是一种“尽力而为”的方式并且对于某些类型可能得不到想要的值。不出现在目标行类型中的JSON字段将从输出中忽略，而且不匹配任何JSON字段的目标列将为NULL。
jsonb_set和jsonb_insert的path参数中除最后一项之外的所有项都必须存在于target中。如果create_missing为假，jsonb_set的path参数的所有项都必须存在。如果这些条件不满足，则返回的target不会被改变。如果最后的路径项是一个对象键，在它不存在且给定了新值的情况下会创建它。如果最后的路径项是一个数组索引，为正值则表示从左边开始计数，为负值表示从右边开始计数 -
-1表示最右边的元素，以此类推。如果该项超过范围 -array_length .. array_length -1 并且 create_missing 为真，则该项为负时把新值加载数组的开始处，而该项为正时把新值加在数组的末尾处。
不要把json_typeof函数的null返回值与 SQL 的 NULL 弄混。 虽然调用json_typeof('null'::json)将会返回null，但调用json_typeof(NULL::json)将会返回一个 SQL 的 NULL。
如果
json_strip_nulls的参数在任何对象中包含重复的域名称， 结果在语义上可能有所不同，具体取决于它们发生的顺序。这不是jsonb_strip_nulls的一个问题，因为jsonb值 不能具有重复的对象域名称。
十六、序列操作函数
本节描述用于操作序列对象的函数，序列对象也被称为序列生成器或者就是序列。序列对象都是用CREATE SEQUENCE创建的特殊的单行表。序列对象通常用于为表的行生成唯一的标识符。表 16.1 中列出的这些序列函数，可以为我们从序列对象中获取连续的序列值提供了简单的、多用户安全的 方法。
表 16.1. 序列函数
| 函数 | 返回类型 | 描述 |
|---|---|---|
| bigint | 返回最近一次用nextval获取的指定序列的值 |
| bigint | 返回最近一次用nextval获取的任何序列的值 |
| bigint | 递增序列并返回新值 |
| bigint | 设置序列的当前值 |
| bigint | 设置序列的当前值以及is_called标志 |
将要由序列函数调用操作的序列是用一个regclass参数声明的， 它只是序列在pg_class系统表里面的 OID。不过，你不需要手工查找 OID， 因为regclass数据类型的输入转换器会帮你做这件事情。 只要写出用单引号包围的序列名字即可，因此它看上去像文本常量。为了和普通SQL名字处理兼容，这个字串将转换成小写形式， 除非在序列名字周围包含双引号。因此：
nextval('foo') 操作序列foo
nextval('FOO') 操作序列foo
nextval('"Foo"') 操作序列Foo必要时序列名可以用模式限定∶
nextval('myschema.foo') 操作myschema.foo
nextval('"myschema".foo') 同上
nextval('foo') 在搜索路径中查找foo
注意：
在PostgreSQL 8.1 之前，序列函数的参数类型是text， 而不是regclass，并且前文所述的从文本串到 OID 值的转换将在每次调用的时候发生。 为了向后兼容，这个处理仍然存在，但是在内部实际上是通过在函数调用前隐式地将text转换成regclass实现的。当你把一个序列函数的参数写成一个无修饰的文字串，那么它将变成类型为
regclass的常量。因为这只是一个 OID，它将跟踪最初标识的序列，而不管后面是否改名、模式变化等等。 这种“早期绑定”的行为通常是列默认值和视图中引用的序列所需要的。 但是有时候你可能想要“延迟绑定”，其中序列的引用是在运行时解析的。要得到延迟绑定的行为，我们可以强制常量被存储为text常量，而不是regclass：nextval('foo'::text)foo在运行时查找请注意，延迟绑定是PostgreSQL版本 8.1 之前唯一被支持的行为， 因此你可能需要做这些来保留旧应用的语义。
当然，序列函数的参数也可以是表达式。如果它是一个文本表达式，那么隐式的转换将导致运行时的查找。
可用的序列函数有∶
nextval
递增序列对象到它的下一个值并且返回该值。这个动作是自动完成的： 即使多个会话并发执行nextval，每个进程也会安全地收到一个唯一的序列值。
如果一个序列对象是用默认参数创建的，连续的nextval调用将会返回从 1 开始的连续的值。其他的行为可以通过在CREATE SEQUENCE命令中使用特殊参数来获得；详见该命令的参考页。
重要
为了避免阻塞从同一个序列获取序号的并发事务，nextval操作从来不会被回滚。也就是说，一旦一个值被取出就视同被用掉并且不会被再次返回给调用者，即便调用该操作的外层事务后来中止或者调用查询后来没有使用取得的值也是这样。例如一个带有ON CONFLICT子句的INSERT会计算要被插入的元组，其中可能就包括调用nextval，然后才会检测到导致它转向ON CONFLICT规则的冲突。这种情况就会在已分配值的序列中留下未被使用的“空洞”。因此，PostgreSQL的序列对象不能被用来得到“无间隙”的序列。
这个函数要求序列上的USAGE或者UPDATE特权。
currval
在当前会话中返回最近一次nextval取到的该序列的值（如果在本会话中从未在该序列上调用过nextval，那么会报告一个错误）。请注意因为此函数返回一个会话本地的值，不论其它会话是否在当前会话之后执行过nextval，它都能给出一个可预测的回答。
这个函数要求序列上的USAGE或者SELECT特权。
lastval
返回当前会话里最近一次nextval返回的值。 这个函数等效于currval，只是它不用序列名作为参数， 它会引用当前会话里面最近一次被应用的序列的nextval。如果当前会话还没有调用过nextval，那么调用lastval会报错。
这个函数要求上一次使用的序列上的USAGE或者SELECT特权。
setval
重置序列对象的计数器值。双参数的形式设置序列的last_value域为指定值并且将其is_called域设置为 true，表示下一次nextval将在返回值之前递增该序列。currval报告的值也被设置为指定的值。在三参数形式里，is_called可以设置为true或false。true具有和双参数形式相同的效果。如果你把它设置为false，那么下一次nextval将返回指定的值，而从随后的nextval才开始递增该序列。此外，在这种情况中currval报告的值不会被改变。 例如：
SELECT setval('foo', 42); 下一次nextval会返回 43
SELECT setval('foo', 42, true); 同上
SELECT setval('foo', 42, false); 下一次nextval将返回 42 setval返回的结果就是它的第二个参数的值。
重要
因为序列是非事务的，setval造成的改变不会由于事务的回滚而撤销。
这个函数要求序列上的UPDATE特权。
十七、条件表达式
本节描述在PostgreSQL中可用的SQL兼容的条件表达式。
提示
如果你的需求超过这些条件表达式的能力，你可能会希望用一种更富表现力的编程语言写一个服务器端函数。
17.1 CASE
SQL CASE表达式是一种通用的条件表达式，类似于其它编程语言中的 if/else 语句：
CASE WHEN condition THEN result
[WHEN ...]
[ELSE result]
ENDCASE子句可以用于任何表达式可以出现的地方。每一个condition是一个返回boolean结果的表达式。如果结果为真，那么CASE表达式的结果就是符合条件的result，并且剩下的CASE表达式不会被处理。如果条件的结果不为真，那么以相同方式搜寻任何随后的WHEN子句。如果没有WHEN condition为真，那么CASE表达式的值就是在ELSE子句里的result。如果省略了ELSE子句而且没有条件为真，结果为空。
例子：
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表达式是上述通用形式的一个变种：
CASE expression
WHEN value THEN result
[WHEN ...]
[ELSE result]
END第一个expression会被计算，然后与所有在WHEN子句中的每一个value对比，直到找到一个相等的。如果没有找到匹配的，则返回在ELSE子句中的result（或者控制）。 这类似于 C 里的switch语句。
上面的例子可以用简单CASE语法来写：
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;
注意：
在有几种情况中一个表达式的子表达式 会被计算多次，因此“CASE只计算必要的表达式”这 一原则并非不可打破。例如一个常量子表达式1/0通常将会在规划时导致一次 除零错误，即便它位于一个执行时永远也不会进入的CASE分支时也是 如此。
17.2 COALESCE
COALESCE(value [, ...])
COALESCE函数返回它的第一个非空参数的值。当且仅当所有参数都为空时才会返回空。它常用于在为显示目的检索数据时用缺省值替换空值。例如：
SELECT COALESCE(description, short_description, '(none)') ...
如果description不为空，这将会返回它的值，否则如果short_description非空则返回short_description的值，如果前两个都为空则返回(none)。
和CASE表达式一样，COALESCE将不会 计算无助于判断结果的参数；也就是说，在第一个非空参数右边的参数不会被计算。这个 SQL 标准函数提供了类似于NVL和IFNULL的能力，它们被用在某些其他数据库系统中。
17.3 NULLIF
NULLIF(value1, value2)
当value1和value2相等时，NULLIF返回一个空值。 否则它返回value1。 这些可以用于执行前文给出的COALESCE例子的逆操作：
SELECT NULLIF(value, '(none)') ...
在这个例子中，如果value是(none)，将返回空值，否则返回value的值。
17.4 GREATEST和LEAST
GREATEST(value [, ...])
LEAST(value [, ...])
GREATEST和LEAST函数从一个任意的数字表达式列表里选取最大或者最小的数值。 这些表达式必须都可以转换成一个普通的数据类型，它将会是结果类型 。列表中的 NULL 数值将被忽略。只有所有表达式的结果都是 NULL 的时候，结果才会是 NULL。
请注意GREATEST和LEAST都不是 SQL 标准，但却是很常见的扩展。某些其他数据库让它们在任何参数为 NULL 时返回 NULL，而不是在所有参数都为 NULL 时才返回 NULL。
十八、数组函数和操作符
表18.1 显示了可以用于数组类型的操作符。
表 18.1. 数组操作符
| 操作符 | 描述 | 例子 | 结果 |
|---|---|---|---|
= | 等于 | 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} |
数组比较使用默认的 B-Tree 在元素数据类型上的比较函数对数组内容按元素逐一进行。 多维数组的元素按照行序进行访问（最后的下标变化最快）。如果两个数组的内容相同但维数不等，那么维度信息中的第一个不同将决定排序顺序（这是对PostgreSQL 8.2 之前版本的修改： 老版本认为内容相同的两个数组相等，即使它们的维数或下标范围并不相同）。
表 18.2 展示了可以用于数组类型的函数。
表 18.2. 数组函数
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
| anyarray | 向一个数组的末端追加一个元素 | array_append(ARRAY[1,2], 3) | {1,2,3} |
| anyarray | 连接两个数组 | array_cat(ARRAY[1,2,3], ARRAY[4,5]) | {1,2,3,4,5} |
| int | 返回数组的维度数 | array_ndims(ARRAY[[1,2,3], [4,5,6]]) | 2 |
| text | 返回数组的维度的文本表示 | array_dims(ARRAY[[1,2,3], [4,5,6]]) | [1:2][1:3] |
| anyarray | 返回一个用提供的值和维度初始化好的数组，可以选择下界不为 1 | array_fill(7, ARRAY[3], ARRAY[2]) | [2:4]={7,7,7} |
| int | 返回被请求的数组维度的长度 | array_length(array[1,2,3], 1) | 3 |
| int | 返回被请求的数组维度的下界 | array_lower('[0:2]={1,2,3}'::int[], 1) | 0 |
| int | 返回在该数组中从第三个参数指定的元素开始或者 第一个元素开始（数组必须是一维的）、第二个参数的 第一次出现的下标 | array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon') | 2 |
| int[] | 返回在第一个参数给定的数组（数组必须是一维的）中， 第二个参数所有出现位置的下标组成的数组 | array_positions(ARRAY['A','A','B','A'], 'A') | {1,2,4} |
| anyarray | 向一个数组的首部追加一个元素 | array_prepend(1, ARRAY[2,3]) | {1,2,3} |
| anyarray | 从数组中移除所有等于给定值的所有元素（数组必须是一维的） | array_remove(ARRAY[1,2,3,2], 2) | {1,3} |
| anyarray | 将每一个等于给定值的数组元素替换成一个新值 | array_replace(ARRAY[1,2,5,4], 5, 3) | {1,2,3,4} |
| text | 使用提供的定界符和可选的空串连接数组元素 | array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*') | 1,2,3,*,5 |
| int | 返回被请求的数组维度的上界 | array_upper(ARRAY[1,8,3,7], 1) | 4 |
| int | 返回数组中元素的总数，如果数组为空则返回 0 | cardinality(ARRAY[[1,2],[3,4]]) | 4 |
| text[] | 使用提供的定界符和可选的空串将字符串划分成数组元素 | string_to_array('xx~^~yy~^~zz', '~^~', 'yy') | {xx,NULL,zz} |
| setof anyelement | 将一个数组扩展成一组行 | unnest(ARRAY[1,2]) | 1 2(2 rows) |
| setof anyelement, anyelement [, ...] | 把多维数组（可能是不同类型）扩展成一个行的集合。 这只允许用在 FROM 子句中 | unnest(ARRAY[1,2],ARRAY['foo','bar','baz']) | 1 foo 2 bar NULL baz(3 rows) |
在array_position和array_positions中， 每一个数组元素都使用IS NOT DISTINCT FROM 语义与要搜索的值比较。
在array_position中，如果值没有找到则返回 NULL。
在array_positions中，只有当数组为 NULL时才返回NULL，如果该值 没有在该数组中找到则返回一个空数组。
在string_to_array中，如果定界符参数为 NULL，输入字符串中的每一个字符将变成结果数组中的一个独立元素。如果定界符是一个空串，则整个输入字符串被作为一个单一元素的数组返回。否则输入字符串会被在每一个出现定界符字符串的位置分裂。
在string_to_array中，如果空值串参数被忽略或者为 NULL，输入中的子串不会被替换成 NULL。在array_to_string中，如果空值串参数被忽略或者为 NULL，该数组中的任何空值元素会被简单地跳过并且不会在输出串中被表示。
注意：
string_to_array的行为中有两点与PostgreSQL 9.1之前的版本不同。首先，当输入串的长度为零时，它将返回一个空（无元素）数组而不是 NULL。其次，如果定界符串为 NULL，该函数会将输入划分成独立字符，而不是像以前那样返回 NULL。
十九、范围函数和操作符
表19.1 展示了范围类型可用的操作符。
表 19.1. 范围操作符
| 操作符 | 描述 | 例子 | 结果 |
|---|---|---|---|
= | 等于 | int4range(1,5) = '[1,4]'::int4range | t |
<> | 不等于 | numrange(1.1,2.2) <> numrange(1.1,2.3) | t |
< | 小于 | int4range(1,10) < int4range(2,3) | t |
> | 大于 | int4range(1,10) > int4range(1,5) | t |
<= | 小于等于 | numrange(1.1,2.2) <= numrange(1.1,2.2) | t |
>= | 大于等于 | numrange(1.1,2.2) >= numrange(1.1,2.0) | t |
@> | 包含范围 | int4range(2,4) @> int4range(2,3) | t |
@> | 包含元素 | '[2011-01-01,2011-03-01)'::tsrange @> '2011-01-10'::timestamp | t |
<@ | 范围被包含 | int4range(2,4) <@ int4range(1,7) | t |
<@ | 元素被包含 | 42 <@ int4range(1,7) | f |
&& | 重叠（有公共点） | int8range(3,7) && int8range(4,12) | t |
<< | 严格左部 | int8range(1,10) << int8range(100,110) | t |
>> | 严格右部 | int8range(50,60) >> int8range(20,30) | t |
&< | 不超过右部 | int8range(1,20) &< int8range(18,20) | t |
&> | 不超过左部 | int8range(7,20) &> int8range(5,10) | t |
-|- | 相邻 | numrange(1.1,2.2) -|- numrange(2.2,3.3) | t |
+ | 并 | numrange(5,15) + numrange(10,20) | [5,20) |
* | 交 | int8range(5,15) * int8range(10,20) | [10,15) |
- | 差 | int8range(5,15) - int8range(10,20) | [5,10) |
简单比较操作符<、 >、<=和 >=首先比较下界，并且只有在下界相等时才比较上界。这些比较通常对范围不怎么有用，但是还是提供它们以便能够在范围上构建 B树索引。
当涉及一个空范围时，左部/右部/相邻操作符总是返回假；即一个空范围被认为不在任何其他范围前面或者后面。
如果结果范围可能需要包含两个分离的子范围，并和差操作符将会失败，因为这样的范围无法被表示。
表 19.2 显示可用于范围类型的函数。
表 19.2. 范围函数
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
| 范围的元素类型 | 范围的下界 | lower(numrange(1.1,2.2)) | 1.1 |
| 范围的元素类型 | 范围的上界 | upper(numrange(1.1,2.2)) | 2.2 |
| boolean | 范围为空？ | isempty(numrange(1.1,2.2)) | false |
| boolean | 下界包含在内？ | lower_inc(numrange(1.1,2.2)) | true |
| boolean | 上界包含在内？ | upper_inc(numrange(1.1,2.2)) | false |
| boolean | 下界无限？ | lower_inf('(,)'::daterange) | true |
| boolean | 上界无限？ | upper_inf('(,)'::daterange) | true |
| anyrange | 包含两个给定范围的最小范围 | range_merge('[1,2)'::int4range, '[3,4)'::int4range) | [1,4) |
如果范围为空或者被请求的界是无限的，lower和upper函数返回空值。函数lower_inc、upper_inc、lower_inf和upper_inf对一个空范围全部返回假。
二十、聚集函数
聚集函数从一个输入值的集合计算出一个单一值。内建的通用聚集函数在 表 20.1中列出，而统计性聚集在 表 20.2 中列出。内建的组内有序集聚集函数在 表 20.3 中列出，而内建的组内假想集聚集在 表 20.4 中列出。与聚集函数紧密相关的分组操作在 表 20.5 中列出。
表 20.1 通用聚集函数
请注意，除了count以外，这些函数在没有行被选中时返回空值。尤其是sum函数在没有输入行时返回空值，而不是零，并且array_agg在这种情况返回空值而不是一个空数组。必要时可以用coalesce把空值替换成零或一个空数组。
支持部分模式的聚集函数有资格参与到各种优化中，例如并行聚集。
注意：
布尔聚集
bool_and和bool_or对应于标准的 SQL 聚集every和any或some。而对于any和some，似乎在标准语法中有一个歧义：SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;如果子查询返回一行有一个布尔值的结果，这里的
ANY可以被认为是引入一个子查询，或者是作为一个聚集函数。因而标准的名称不能指定给这些聚集。
在把
count聚集应用到整个表上时，习惯于使用其他 SQL 数据管理系统的用户可能会对它的性能感到失望。一个如下的查询：SELECT count(*) FROM sometable;将会要求与整个表大小成比例的工作：PostgreSQL将需要扫描整个表或者整个包含表中所有行的索引。
与相似的用户定义的聚集函数一样，聚集函数array_agg、 json_agg、jsonb_agg、json_object_agg、jsonb_object_agg、string_agg和xmlagg会依赖输入值的顺序产生有意义的不同结果值。这个顺序默认是不用指定的，但是可以在聚集调用时使用ORDER BY子句进行控制。作为一种选择，从一个排序号的子查询来提供输入值通常会有帮助。例如：
SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
注意如果外面的查询层次包含额外的处理（例如连接），这种方法可能会失败，因为这可能导致子查询的输出在计算聚集之前被重新排序。
表 20.2 展示了通常被用在统计分析中的聚集函数（这些被隔离出来是为了不和常用聚集混淆）。其中描述提到了N，它表示对应于所有非空输入表达式的输入行的数目。在所有情况中，如果计算是无意义的，将会返回空值，例如当N为零。
表 20.2. 用于统计的聚集函数
表 20.3 展示了一些使用 有序集聚集语法的聚集函数。这些函数有时也被称为 “逆分布”函数。
表 20.3. 有序集聚集函数
所有列在表 20.3中的聚集会忽略它们的已 排序输入中的空值。对那些有一个fraction参数的聚 集来说，该分数值必须位于 0 和 1 之间，否则会抛出错误。不过，一个空分数值会 产生一个空结果。
每个列在 表 20.4 中的聚集都与一个定义在 下一节（二十一节）中的同名窗口函数相关联。在每种情况中，聚集 结果的计算方法是：假设根据args构建的“假想”行已 经被增加到从sorted_args计算得到的已排序行分组中， 然后用相关联的窗口函数针对该行返回的值就是聚集的结果。
表 20.4. 假想集聚集函数
对于这些假想集聚集的每一个，args中给定的直接参数 列表必须匹配sorted_args中给定的聚集参数的 数量和类型。与大部分的内建聚集不同，这些聚集并不严格，即它们不会丢弃包含空值 的输入行。空值的排序根据ORDER BY子句中指定的规则进行。
表 20.5. 分组操作
| 函数 | 返回类型 | 描述 |
|---|---|---|
GROUPING( | integer | 整数位掩码指示哪些参数不被包括在当前分组集合中 |
分组操作用来与分组集合共同来 区分结果行。GROUPING操作的参数并不会被实际计算，但是它们必 须准确地匹配在相关查询层次的GROUP BY子句中给定的表达式。 最右边参数指派的位是最低有效位，如果对应的表达式被包括在产生结果行的分组 集合的分组条件中则每一位是 0，否则是 1。例如：
=>
SELECT * FROM items_sold;
make | model | sales
-------+-------+-------
Foo | GT | 10
Foo | Tour | 20
Bar | City | 15
Bar | Sport | 5
(4 rows)
=>
SELECT make, model, GROUPING(make,model), sum(sales) FROM items_sold GROUP BY ROLLUP(make,model);
make | model | grouping | sum
-------+-------+----------+-----
Foo | GT | 0 | 10
Foo | Tour | 0 | 20
Bar | City | 0 | 15
Bar | Sport | 0 | 5
Foo | | 1 | 30
Bar | | 1 | 20
| | 3 | 50
(7 rows)二十一、窗口函数
窗口函数提供在与当前查询行相关的行集合上执行计算的能力。
表 21.1 列出了内建的窗口函数。注意必须使用窗口函数的语法调用这些函数； 一个OVER子句是必需的。
在这些函数之外，任何内建的或者用户定义的通用或者统计性聚集（即非有序集和假想集聚集）都可以被用作一个窗口函数，内建聚集的列表请见 上一节（第二十节）。仅当聚集函数调用后面跟着一个OVER子句时，聚集函数才会像窗口函数那样工作，否则它们会按非窗口聚集的方式运行并且为整个集合返回一个单一行。
表 21.1. 通用窗口函数
在 表21.1中列出的所有函数都依赖于相关窗口定义的ORDER BY子句指定的排序顺序。仅考虑ORDER BY列时不能区分的行被称为是同等行。定义的这四个排名函数（包括 cume_dist），对于任何两个同等行的答案相同。
注意first_value、last_value和nth_value只考虑“窗口帧”内的行，它默认情况下包含从分区的开始行直到当前行的最后一个同等行。这对last_value可能不会给出有用的结果，有时对nth_value也一样。你可以通过向OVER子句增加一个合适的帧声明（RANGE或GROUPS）来重定义帧。
当一个聚集函数被用作窗口函数时，它将在当前行的窗口帧内的行上聚集。 一个使用ORDER BY和默认窗口帧定义的聚集产生一种“运行时求和”类型的行为，这可能是或者不是想要的结果。为了获取在整个分区上的聚集，忽略ORDER BY或者使用ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。 其它窗口帧声明可以用来获得其它的效果。
注意：
SQL 标准为
lead、lag、first_value、last_value和nth_value定义了一个RESPECT NULLS或IGNORE NULLS选项。 这在PostgreSQL中没有实现：行为总是与标准的默认相同，即RESPECT NULLS。 同样，标准中用于nth_value的FROM FIRST或FROM LAST选项没有实现： 只有支持默认的FROM FIRST行为（你可以通过反转ORDER BY的排序达到FROM LAST的结果）。
cume_dist计算小于等于当前行及其平级行的分区行所占的分数，而percent_rank计算小于当前行的分区行所占的分数，假定当前行不存在于该分区中。
二十二、子查询表达式
本节描述PostgreSQL中可用的SQL兼容的子查询表达式。所有本节中成文的表达式都返回布尔值（真/假）结果。
22.1 EXISTS
EXISTS (subquery)
EXISTS的参数是一个任意的SELECT语句， 或者说子查询。系统对子查询进行运算以判断它是否返回行。如果它至少返回一行，那么EXISTS的结果就为“真”； 如果子查询没有返回行，那么EXISTS的结果是“假”。
子查询可以引用来自周围的查询的变量，这些变量在该子查询的任何一次计算中都起常量的作用。
这个子查询通常只是运行到能判断它是否可以返回至少一行为止， 而不是等到全部结束。在这里写任何有副作用的子查询都是不明智的（例如调用序列函数）；这些副作用是否发生是很难判断的。
因为结果只取决于是否会返回行，而不取决于这些行的内容， 所以这个子查询的输出列表通常是无关紧要的。一个常用的编码习惯是用EXISTS(SELECT 1 WHERE ...)的形式写所有的EXISTS测试。不过这条规则有例外，例如那些使用INTERSECT的子查询。
下面这个简单的例子类似在col2上的一次内联接，但是它为每个 tab1的行生成最多一个输出，即使存在多个匹配tab2的行也如此∶
SELECT col1
FROM tab1
WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);22.2 IN
expression IN (subquery)
右手边是一个圆括弧括起来的子查询， 它必须正好只返回一个列。左手边表达式将被计算并与子查询结果逐行进行比较。 如果找到任何等于子查询行的情况，那么IN的结果就是“真”。 如果没有找到相等行，那么结果是“假”（包括子查询没有返回任何行的情况）。
请注意如果左手边表达式得到空值，或者没有相等的右手边值， 并且至少有一个右手边行得到空值，那么IN结构的结果将是空值，而不是假。这个行为是遵照 SQL 处理空值的一般规则的。
和EXISTS一样，假设子查询将被完成运行完全是不明智的。
row_constructor IN (subquery)
这种形式的IN的左手边是一个行构造器。 右手边是一个圆括弧子查询，它必须返回和左手边返回的行中表达式所构成的完全一样多的列。 左手边表达式将被计算并与子查询结果逐行进行比较。如果找到任意相等的子查询行，则IN的结果为“真”。如果没有找到相等行， 那么结果为“假”（包括子查询不返回行的情况）。
通常，表达式或者子查询行里的空值是按照 SQL 布尔表达式的一般规则进行组合的。 如果两个行对应的成员都非空并且相等，那么认为这两行相等；如果任意对应成员为非空且不等，那么这两行不等； 否则这样的行比较的结果是未知（空值）。如果所有行的结果要么是不等， 要么是空值，并且至少有一个空值，那么IN的结果是空值。
22.3 NOT IN
expression NOT IN (subquery)
右手边是一个用圆括弧包围的子查询，它必须返回正好一个列。左手边表达式将被计算并与子查询结果逐行进行比较。 如果只找到不相等的子查询行（包括子查询不返回行的情况），那么NOT IN的结果是“真”。 如果找到任何相等行，则结果为“假”。
请注意如果左手边表达式得到空值，或者没有相等的右手边值， 并且至少有一个右手边行得到空值，那么NOT IN结构的结果将是空值，而不是真。这个行为是遵照 SQL 处理空值的一般规则的。
和EXISTS一样，假设子查询会完全结束是不明智的。
row_constructor NOT IN (subquery)
这种形式的NOT IN的左手边是一个行构造器。 右手边是一个圆括弧子查询，它必须返回和左手边返回的行中表达式所构成的完全一样多的列。 左手边表达式将被计算并与子查询结果逐行进行比较。如果找到不等于子查询行的行，则NOT IN的结果为“真”。如果找到相等行， 那么结果为“假”（包括子查询不返回行的情况）。
通常，表达式或者子查询行里的空值是按照 SQL 布尔表达式的一般规则进行组合的。 如果两个行对应的成员都非空并且相等，那么认为这两行相等；如果任意对应成员为非空且不等，那么这两行不等； 否则这样的行比较的结果是未知（空值）。如果所有行的结果要么是不等， 要么是空值，并且至少有一个空值，那么NOT IN的结果是空值。
22.4 ANY/SOME
expression operator ANY (subquery)
expression operator SOME (subquery)这种形式的右手边是一个圆括弧括起来的子查询， 它必须返回正好一个列。左手边表达式将被计算并使用给出的 操作符对子查询结果逐行进行比较。如果获得任何真值结果，那么ANY的结果就是“真”。 如果没有找到真值结果，那么结果是“假”（包括子查询没有返回任何行的情况）。
SOME是ANY的同义词。IN等价于= ANY。
请注意如果没有任何成功并且至少有一个右手边行为该操作符结果生成空值， 那么ANY结构的结果将是空值，而不是假。 这个行为是遵照 SQL 处理空值布尔组合的一般规则制定的。
和EXISTS一样，假设子查询将被完全运行是不明智的。
row_constructor operator ANY (subquery)
row_constructor operator SOME (subquery)这种形式的左手边是一个行构造器。右手边是一个圆括弧括起来的子查询， 它必须返回和左手边列表给出的表达式一样多的列。左手边表达式将被计算并使用给出的操作符对子查询结果逐行进行比较。如果比较为任何子查询行返回真，则ANY的结果为“真”。如果比较对每一个子查询行都返回假，则结果为“假”（包括子查询不返回行的情况）。如果比较不对任何行返回真并且至少对一行返回 NULL，则结果为 NULL。
22.5 ALL
expression operator ALL (subquery)
ALL 的这种形式的右手边是一个圆括弧括起来的子查询， 它必须只返回一列。左手边表达式将被计算并使用给出的 操作符对子查询结果逐行进行比较。该操作符必须生成布尔结果。 如果所有行得到真（包括子查询没有返回任何行的情况），ALL的结果就是“真”。如果没有存在任何假值结果，那么结果是“假”。如果比较为任何行都不返回假并且对至少一行返回 NULL，则结果为 NULL。
NOT IN等价于<> ALL。
和EXISTS一样，假设子查询将被完全运行是不明智的。
row_constructor operator ALL (subquery)
ALL的这种形式的左手边是一个行构造器。 右手边是一个圆括弧括起来的子查询，它必须返回和左手边行中表达式一样多的列。 左手边表达式将被计算并使用给出的 操作符对子查询结果逐行进行比较。如果对所有子查询行该比较都返回真，那么ALL的结果就是“真”（包括子查询没有返回任何行的情况）。如果对任何子查询行比较返回假，则结果为“假”。如果比较对任何子查询行都不返回假并且对至少一行返回 NULL，则结果为 NULL。
22.6 单一行比较
row_constructor operator (subquery)
左手边是一个行构造器。 右手边是一个圆括弧括起来的子查询，该查询必须返回和左手边行中表达式数目完全一样的列。 另外，该子查询不能返回超过一行的数量（如果它返回零行，那么结果就是空值）。 左手边被计算并逐行与右手边的子查询结果行比较。
二十三、行和数组比较
本节描述几个特殊的结构，用于在值的组之间进行多重比较。这些形式语法上和前面一节的子查询形式相关，但是不涉及子查询。 这种形式涉及的数组子表达式是PostgreSQL的扩展； 其它的是SQL兼容的。所有本节记录的表达式形式都返回布尔（Boolean）结果（真/假）。
23.1 IN
expression IN (value [, ...])
右手边是一个圆括弧包围的标量列表。如果左手边表达式的结果等于任何右手边表达式中的一个，结果为“真”。它是下面形式的缩写
expression = value1
OR
expression = value2
OR
...请注意如果左手边表达式得到空值，或者没有相等的右手边值并且至少有一个右手边的表达式得到空值，那么IN结构的结果将为空值，而不是假。这符合 SQL 处理空值的布尔组合的一般规则。
23.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更可能迷惑新手。最好尽可能用正逻辑来表达你的条件。
23.3 ANY/SOME（array）
expression operator ANY (array expression)
expression operator SOME (array expression)右手边是一个圆括弧包围的表达式，它必须得到一个数组值。左手边表达式被计算并且使用给出的操作符对数组的每个元素进行比较，这个操作符必须得到布尔结果。如果得到了任何真值结果，那么ANY的结果是“真”。 如果没有找到真值结果（包括数组只有零个元素的情况），那么结果是“假”。
如果数组表达式得到一个空数组，ANY的结果将为空值。如果左手边的表达式得到空值，ANY通常是空值（尽管一个非严格比较操作符可能得到一个不同的结果）。另外，如果右手边的数组包含任何空值元素或者没有得到真值比较结果，ANY的结果将是空值而不是假（再次，假设是一个严格的比较操作符）。这符合 SQL 对空值的布尔组合的一般规则。
SOME是ANY的同义词。
23.4 ALL（array）
expression operator ALL (array expression)
右手边是一个圆括弧包围的表达式，它必须得到一个数组值。左手边表达式将被计算并使用给出的操作符与数组的每个元素进行比较，这个操作符必须得到一个布尔结果。如果所有比较都得到真值结果，那么ALL的结果是 “真”（包括数组只有零个元素的情况）。如果有任何假值结果，那么结果是“假”。
如果数组表达式得到一个空数组，ALL的结果将为空值。如果左手边的表达式得到空值，ALL通常是空值（尽管一个非严格比较操作符可能得到一个不同的结果）。另外，如果右手边的数组包含任何空值元素或者没有得到假值比较结果，ALL的结果将是空值而不是真（再次，假设是一个严格的比较操作符）。这符合 SQL 对空值的布尔组合的一般规则。
23.5 行构造器比较
row_constructor operator row_constructor
每一边都是一个行构造器。两个行值必须具有相同数量的域。每一边被计算并且被逐行比较。当操作符是 =、 <>、 < 、<=、 >、 >=时，允许进行行构造器比较。每一个行元素必须是具有一个默认 B 树操作符类的类型，否则尝试比较会产生一个错误。
注意：
Errors related to the number or types of elements might not occur if the comparison is resolved using earlier columns.
=和<>情况略有不同。如果两行的所有对应成员都是非空且相等则这两行被认为相等；如果任何对应成员是非空但是不相等则这两行不相等；否则行比较的结果为未知（空值）。
对于<、<=、>和>=情况，行元素被从左至右比较，在找到一处不等的或为空的元素对就立刻停下来。如果这一对元素都为空值，则行比较的结果为未知（空值）；否则这一对元素的比较结果决定行比较的结果。例如，ROW(1,2,NULL) < ROW(1,3,0)得到真，而不是空值，因为第三对元素并没有被考虑。
注意：
在PostgreSQL 8.2之前，
<、<=、>和>=情况不是按照每个 SQL 声明来处理的。一个像ROW(a,b) < ROW(c,d)的比较会被实现为a < c AND b < d，而结果行为等价于a < c OR (a = c AND b < d)。
row_constructor IS DISTINCT FROM row_constructor
这个结构与<>行比较相似，但是它对于空值输入不会得到空值。任何空值被认为和任何非空值不相等（有区别），并且任意两个空值被认为相等（无区别）。因此结果将总是为真或为假，永远不会是空值。
row_constructor IS NOT DISTINCT FROM row_constructor
这个结构与=行比较相似，但是它对于空值输入不会得到空值。任何空值被认为和任何非空值不相等（有区别），并且任意两个空值被认为相等（无区别）。因此结果将总是为真或为假，永远不会是空值。
23.6 组合类型比较
record operator record
SQL 规范要求在结果依赖于比较两个 NULL 值或者一个 NULL 与一个非 NULL 时逐行比较返回 NULL。PostgreSQL只有在比较两个行构造器（如23.5节中所述）的结果或者比较一个行构造器与一个子查询的输出时才这样做（如二十二节中所述）。在其他比较两个组合类型值的环境中，两个 NULL 域值被认为相等，并且一个 NULL 被认为大于一个非 NULL。为了得到组合类型的一致的排序和索引行为，这样做是必要的。
每一边都会被计算并且它们会被逐行比较。当操作符是 =、 <>、 <、 <=、 >或者 >=时或者具有与这些类似的语义时，允许组合类型的比较（更准确地说，如果一个操作符是一个 B 树操作符类的成员，或者是一个 B 树操作符类的=成员的否定词，它就可以是一个行比较操作符）。 上述操作符的行为与用于行构造器（见第23.5节）的IS [ NOT ] DISTINCT FROM相同。
为了支持包含无默认 B 树操作符类的元素的行匹配，为组合类型比较定义了下列操作符： *=、 *<>、 *<、 *<=、 *>以及 *>=。 这些操作符比较两行的内部二进制表达。即使两行用相等操作符的比较为真，两行也可能 具有不同的二进制表达。行在这些比较操作符之下的排序是决定性的，其他倒没什么意义。 这些操作符在内部被用于物化视图并且可能对其他如复制之类的特殊功能有用，但是它们 并不打算用在书写查询这类普通用途中。
二十四、集合返回函数
本节描述那些可能返回多于一行的函数。目前这个类中被使用最广泛的是级数生成函数， 如 表 24.1 和 表 24.2 所述。其他更特殊的集合返回函数在本手册的其他地方描述。组合多集合返回函数的方法可见第十章查询（表表达式）
表 24.1. 级数生成函数
| 函数 | 参数类型 | 返回类型 | 描述 |
|---|---|---|---|
| int、bigint或者numeric | setof int、setof bigint或者setof numeric（与参数类型相同） | 产生一系列值，从start到stop，步长为 1 |
| int、bigint或者numeric | setof int、setof bigint或者setof numeric（与参数类型相同） | 产生一系列值，从start到stop，步长为step |
| timestamp或timestamp with time zone | setof timestamp或setof timestamp with time zone（和参数类型相同） | 产生一系列值，从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 generate_series(1.1, 4, 1.3);
generate_series
-----------------
1.1
2.4
3.7
(3 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)
SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,
'2008-03-04 12:00', '10 hours');
generate_series
---------------------
2008-03-01 00:00:00
2008-03-01 10:00:00
2008-03-01 20:00:00
2008-03-02 06:00:00
2008-03-02 16:00:00
2008-03-03 02:00:00
2008-03-03 12:00:00
2008-03-03 22:00:00
2008-03-04 08:00:00
(9 rows)表 24.2. 下标生成函数
| 函数 | 返回类型 | 描述 |
|---|---|---|
| setof int | 生成一个级数组成给定数组的下标。 |
| setof int | 生成一个级数组成给定数组的下标。当reverse为真，级数以逆序返回。 |
generate_subscripts是一个快捷函数，它为给定数组的指定维度生成一组合法的下标。对于不具有请求维度的数组返回零行，对于 NULL 数组也返回零行（但是会对 NULL 数组元素返回合法的下标）。下面是一些例子：
-- 基本使用
SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1) AS s;
s
---
1
2
3
4
(4 rows)当FROM子句中的一个函数后面有WITH ORDINALITY时，输出中会追加一个bigint列，它的值从1开始并且该函数输出的每一行加1。这在unnest()之类的集合返回函数中最有用。
SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
ls | n
----------------------+----
pg_twophase | 1
global | 2
pg_stat | 3
postmaster.opts | 4
pg_stat_tmp | 5
pg_tblspc | 6
pg_serial | 7
pg_logical | 8
pg_commit_ts | 9
pg_dynshmem | 10
pg_snapshots | 11
pg_hba.conf | 12
PG_VERSION | 13
pg_xact | 14
pg_subtrans | 15
postmaster.pid | 16
base | 17
pg_ident.conf | 18
pg_multixact | 19
postgresql.conf | 20
pg_notify | 21
pg_replslot | 22
pg_wal | 23
postgresql.auto.conf | 24
(24 rows)
二十五、系统信息函数
表25.1展示了多个可以抽取会话和系统信息的函数。
表 25.1. 会话信息函数
| 名称 | 返回类型 | 描述 |
|---|---|---|
| name | 当前数据库名（SQL 标准中称作“目录”） |
| name | 当前数据库名 |
| text | 当前正在执行的查询的文本，和客户端提交的一样（可能包含多于一个语句） |
| name | 等效于current_user |
| name | 当前模式名 |
| name[] | 搜索路径中的模式名，可以选择是否包含隐式模式 |
| name | 当前执行上下文的用户名 |
| inet | 远程连接的地址 |
| int | 远程连接的端口 |
| inet | 本地连接的地址 |
| int | 本地连接的端口 |
| int | 与当前会话关联的服务器进程的进程 ID |
| int[] | 阻塞指定服务器进程ID获得锁的进程 ID |
| timestamp with time zone | 配置载入时间 |
| text | 当前日志收集器在使用的主日志文件名或者所要求格式的日志的文件名 |
| oid | 会话的临时模式的 OID，如果没有则为 0 |
| boolean | 模式是另一个会话的临时模式吗？ |
| boolean | 这个会话中JIT编译是否可用（见第 32 章）？如果jit被设置为假，则返回false。 |
| setof text | 会话当前正在监听的频道名称 |
| double | 异步通知队列当前被占用的分数（0-1） |
| timestamp with time zone | 服务器启动时间 |
| int[] | 阻止指定服务器进程ID获取安全快照的进程ID |
| int | PostgreSQL触发器的当前嵌套层次（如果没有调用则为 0，直接或间接，从一个触发器内部开始） |
| name | 会话用户名 |
| name | 等价于current_user |
| text | PostgreSQL版本信息。机器可读的版本还可见server_version_num。 |
注意：
current_catalog、current_role、current_schema、current_user、session_user和user在SQL里有特殊的语意状态： 它们被调用时结尾不要跟着园括号（在 PostgreSQL 中，圆括号可以有选择性地被用于current_schema，但是不能和其他的一起用）。
session_user通常是发起当前数据库连接的用户，不过超级用户可以用SET SESSION AUTHORIZATION修改这个设置。current_user是用于权限检查的用户标识。通常， 它总是等于会话用户，但是可以被SET改变。它也会在函数执行的过程中随着属性SECURITY DEFINER的改变而改变。在 Unix 的说法里，那么会话用户是“真实用户”，而当前用户是“有效用户”。current_role以及user是current_user的同义词（SQL标准在current_role和current_user之间做了区分，但PostgreSQL不区分，因为它把用户和角色统一成了一种实体）。
current_schema返回在搜索路径中的第一个模式名（如果搜索路径是空则返回空值）。 如果创建表或者其它命名对象时没有声明目标模式，那么它将是被用于这些对象的模式。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。
pg_blocking_pids返回一个进程 ID 的数组，数组中的进程中的会话阻塞了指定进程 ID 所代表的服务器进程，如果指定的服务器进程不存在或者没有被阻塞则返回空数组。如果一个进程持有与另一个进程加锁请求冲突的锁（硬锁），或者前者正在等待一个与后者加锁请求冲突的锁并且前者在该锁的等待队列中位于后者的前面（软锁），则前者会阻塞后者。在使用并行查询时，这个函数的结果总是会列出客户端可见的进程 ID（即pg_backend_pid的结果），即便实际的锁是由工作者进程所持有或者等待也是如此。这样造成的后果是，结果中可能会有很多重复的 PID。还要注意当一个预备事务持有一个冲突锁时，这个函数的结果中它将被表示为一个为零的进程 ID。对这个函数的频繁调用可能对数据库性能有一些影响，因为它需要短时间地独占访问锁管理器的共享状态。
pg_conf_load_time返回服务器配置文件最近被载入的timestamp with time zone（如果当前会话在那时就已经存在，这个值将是该会话自己重新读取配置文件的时间，因此在不同的会话中这个读数会有一点变化。如果不是这样，这个值就是 postmaster 进程重读配置文件的时间）。
pg_current_logfile以text类型返回当前被日志收集器使用的日志文件的路径。该路径包括log_directory目录和日志文件名。日志收集必须被启用，否则返回值为NULL。当多个日志文件存在并且每一个都有不同的格式时，不带参数调用pg_current_logfile会返回这样的文件的路径：在所有的文件中，没有任何文件的格式在列表stderr、csvlog中排在这个文件的格式前面。如果没有任何日志文件有上述格式，则返回NULL。要请求一种特定的文件格式，可以以text将csvlog或者stderr作为可选参数的值。当所请求的日志格式不是已配置的log_destination时，会返回NULL。pg_current_logfile反映了current_logfiles文件的内容。
pg_my_temp_schema返回当前会话临时模式的 OID，如果没有使用临时模式（因为它没有创建任何临时表）则返回零。如果给定的 OID 是另一个会话的临时模式的 OID，则pg_is_other_temp_schema返回真（这是有用的，例如，要将其他会话的临时表从一个目录显示中排除）。
pg_listening_channels返回当前会话正在监听的异步通知频道的名称的集合。pg_notification_queue_usage返回等待处理的通知占可用的通知空间的比例，它是一个 0-1 范围内的double值。
pg_postmaster_start_time返回服务器启动的timestamp with time zone。
pg_safe_snapshot_blocking_pids一个进程ID的数组，它们代表阻止指定进程ID对应的服务器进程获取安全快照的会话，如果没有这类服务器进程或者它没有被阻塞，则会返回一个空数组。一个运行着SERIALIZABLE事务的会话会阻止SERIALIZABLE READ ONLY DEFERRABLE事务获取快照，直到后者确定避免拿到任何谓词锁是安全的。频繁调用这个函数可能会对数据库性能产生一些影响，因为它需要短时间访问谓词锁管理器的共享状态。
version返回一个描述PostgreSQL服务器版本的字符串。你也可以从server_version或者一个机器可读的版本server_version_num到这个信息。软件开发者应该使用server_version_num（从 8.2 开始可用）或者PQServerVersion，而不必解析文本形式的版本。
表25.2列出那些允许用户编程查询对象访问权限的函数。
表 25.2. 访问权限查询函数
| 名称 | 返回类型 | 描述 |
|---|---|---|
| boolean | 用户有没有表中任意列上的权限 |
| boolean | 当前用户有没有表中任意列上的权限 |
| boolean | 用户有没有列的权限 |
| boolean | 当前用户有没有列的权限 |
| boolean | 用户有没有数据库的权限 |
| boolean | 当前用户有没有数据库的权限 |
| boolean | 用户有没有外部数据包装器上的权限 |
| boolean | 当前用户有没有外部数据包装器上的权限 |
| boolean | 用户有没有函数上的权限 |
| boolean | 当前用户有没有函数上的权限 |
| boolean | 用户有没有语言上的权限 |
| boolean | 当前用户有没有语言上的权限 |
| boolean | 用户有没有模式上的权限 |
| boolean | 当前用户有没有模式上的权限 |
| boolean | 用户有没有序列上的权限 |
| boolean | 当前用户有没有序列上的权限 |
| boolean | 用户有没有外部服务器上的权限 |
| boolean | 当前用户有没有外部服务器上的权限 |
| boolean | 用户有没有表上的权限 |
| boolean | 当前用户有没有表上的权限 |
| boolean | 用户有没有表空间上的权限 |
| boolean | 当前用户有没有表空间上的权限 |
| boolean | 用户有没有类型的特权 |
| boolean | 当前用户有没有类型的特权 |
| boolean | 用户有没有角色上的权限 |
| boolean | 当前用户有没有角色上的权限 |
| boolean | 当前用户是否在表上开启了行级安全性 |
has_table_privilege判断一个用户是否可以用某种特定的方式访问一个表。 该用户可以通过名字或者 OID （pg_authid.oid） 来指定，也可以用public表示 PUBLIC 伪角色。如果省略该参数，则使用current_user。 该表可以通过名字或者 OID 指定（因此，实际上有六种 has_table_privilege的变体，我们可以通过它们的参数数目和类型来区分它们） 。如果用名字指定，那么在必要时该名字可以是模式限定的。 所希望的权限类型是用一个文本串来指定的，它必须是下面的几个值之一： SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES或TRIGGER。WITH GRANT OPTION可以被选择增加到一个权限类型来测试是否该权限是使用转授选项得到。另外，可以使用逗号分隔来列出多个权限类型，在这种情况下只要具有其中之一的权限则结果为真（权限字符串的大小写并不重要，可以在权限名称之间出现额外的空白，但是在权限名内部不能有空白）。一些例子：
SELECT has_table_privilege('myschema.mytable', 'select');
SELECT has_table_privilege('joe', 'mytable', 'INSERT, SELECT WITH GRANT OPTION');has_sequence_privilege检查一个用户是否能以某种特定方式访问一个序列。它的参数可能性和has_table_privilege相似。所希望测试的访问权限类型必须是下列之一：USAGE、SELECT或UPDATE。
has_any_column_privilege检查一个用户是否能以特定方式访问一个表的任意列。其参数可能性和has_table_privilege类似，除了所希望的访问权限类型必须是下面值的某种组合：SELECT、INSERT、UPDATE或REFERENCES。注意在表层面上具有这些权限的任意一个都会隐式地把它授权给表中的每一列，因此如果has_table_privilege对同样的参数返回真则has_any_column_privilege将总是返回真。但是如果在至少一列上有一个该权限的列级授权，has_any_column_privilege也会成功。
has_column_privilege检查一个用户是否能以特定方式访问一个列。它的参数可能性与has_table_privilege类似，并且列还可以使用名字或者属性号来指定。希望的访问权限类型必须是下列值的某种组合：SELECT、INSERT、UPDATE或REFERENCES。注意在表级别上具有这些权限中的任意一种将会隐式地把它授予给表上的每一列。
has_database_privilege检查一个用户是否能以特定方式访问一个数据库。它的参数可能性类似 has_table_privilege。希望的访问权限类型必须是以下值的某种组合：CREATE、CONNECT、TEMPORARY或TEMP（等价于TEMPORARY）。
has_function_privilege检查一个用户是否能以特定方式访问一个函数。其参数可能性类似has_table_privilege。在用一个文本串而不是 OID 指定一个函数时，允许的输入和regprocedure数据类型一样。希望的访问权限类型必须是EXECUTE。一个例子：
SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');
has_foreign_data_wrapper_privilege检查一个用户是否能以特定方式访问一个外部数据包装器。它的参数可能性类似于has_table_privilege。希望的访问权限类型必须是USAGE。
has_language_privilege检查一个用户是否可以以某种特定的方式访问一个过程语言。 其参数可能性类似 has_table_privilege。希望的访问权限类型必须是USAGE。
has_schema_privilege检查一个用户是否可以以某种特定的方式访问一个模式。 其参数可能性类似 has_table_privilege。希望的访问权限类型必须是CREATE或USAGE。
has_server_privilege检查一个用户是否可以以某种特定的方式访问一个外部服务器。 其参数可能性类似 has_table_privilege。希望的访问权限类型必须是USAGE。
has_tablespace_privilege检查一个用户是否可以以某种特定的方式访问一个表空间。其参数可能性类似 has_table_privilege。希望的访问权限类型必须是CREATE。
has_type_privilege检查一个用户是否能以特定的方式访问一种类型。其参数的可能性类同于has_table_privilege。在用字符串而不是 OID 指定类型时，允许的输入和regtype数据类型相同。期望的访问特权类型必须等于USAGE。
pg_has_role检查一个用户是否可以以某种特定的方式访问一个角色。其参数可能性类似 has_table_privilege，除了public不能被允许作为一个用户名。希望的访问权限类型必须是下列值的某种组合：MEMBER或USAGE。MEMBER表示该角色中的直接或间接成员关系（即使用SET ROLE的权力），而USAGE表示不做SET ROLE的情况下该角色的权限是否立即可用。
row_security_active检查在 current_user的上下文和环境中是否为指定的 表激活了行级安全性。表可以用名称或者 OID 指定。
表25.3展示了决定是否一个特定对象在当前模式搜索路径中可见的函数。例如，如果一个表所在的模式在当前搜索路径中并且在它之前没有出现过相同的名字，这个表就被说是可见的。这等价于在语句中表可以被用名称引用但不加显式的模式限定。要列出所有可见表的名字：
SELECT relname FROM pg_class WHERE pg_table_is_visible(oid);
表 25.3. 模式可见性查询函数
| 名称 | 返回类型 | 描述 |
|---|---|---|
| boolean | 排序规则在搜索路径中可见吗？ |
| boolean | 转换在搜索路径中可见吗？ |
| boolean | 函数在搜索路径中可见吗？ |
| boolean | 操作符类在搜索路径中可见吗？ |
| boolean | 操作符在搜索路径中可见吗？ |
| boolean | 操作符族在搜索路径中可见吗？ |
| boolean | 是搜索路径中的统计信息对象 |
| boolean | 表在搜索路径中可见吗？ |
| boolean | 文本搜索配置在搜索路径中可见吗？ |
| boolean | 文本搜索字典在搜索路径中可见吗？ |
| boolean | 文本搜索解析器在搜索路径中可见吗？ |
| boolean | 文本搜索模板在搜索路径中可见吗？ |
| boolean | 类型（或域）在搜索路径中可见吗？ |
每一个函数对一种数据库对象执行可见性检查。注意pg_table_is_visible也可被用于视图、物化视图、索引、序列和外部表，pg_function_is_visible也能被用于过程和聚集，pg_type_is_visible也可以被用于域。对于函数和操作符，如果在路径中更早的地方没有出现具有相同名称和参数数据类型的对象，该对象在搜索路径中是可见的。对于操作符类，名称和相关的索引访问方法都要考虑。
所有这些函数都要求用对象 OID 来标识将被检查的对象。如果你想用名称来测试一个对象，使用 OID 别名类型（regclass、regtype、regprocedure、regoperator、regconfig或regdictionary）将会很方便。例如：
SELECT pg_type_is_visible('myschema.widget'::regtype);
注意以这种方式测试一个非模式限定的类型名没什么意义 — 如果该名称完全能被识别，它必须是可见的。
表25.4列出了从系统目录抽取信息的函数。
表 25.4. 系统目录信息函数
| 名称 | 返回类型 | 描述 |
|---|---|---|
| text | 获得一个数据类型的 SQL 名字 |
| text | 获得一个约束的定义 |
| text | 获得一个约束的定义 |
| text | 反编译一个表达式的内部形式，假定其中的任何 Var 指向由第二个参数指示的关系 |
| text | 反编译一个表达式的内部形式，假定其中的任何 Var 指向由第二个参数指示的关系 |
| text | 获得一个函数或过程的定义 |
| text | 获得一个函数或过程定义的参数列表（带有默认值） |
| text | 获得标识一个函数或过程的参数列表（不带默认值） |
| text | 获得函数的RETURNS子句（对过程返回空） |
| text | 获得索引的CREATE INDEX命令 |
| text | 获得索引的CREATE INDEX命令，或者当column_no为非零时只得到一个索引列的定义 |
| setof record | 获得 SQL 关键字的列表及其分类 |
| text | 获得规则的CREATE RULE命令 |
| text | 获得规则的CREATE RULE命令 |
| text | 获得一个序列列或标识列使用的序列的名称 |
| text | 为扩展的统计信息对象得到CREATE STATISTICS命令 |
pg_get_triggerdef(trigger_oid) | text | 获得触发器的CREATE [ CONSTRAINT ] TRIGGER命令 |
pg_get_triggerdef(trigger_oid, pretty_bool) | text | 获得触发器的CREATE [ CONSTRAINT ] TRIGGER命令 |
| name | 获得给定 OID 指定的角色名 |
| text | 获得视图或物化视图的底层SELECT命令（已废弃） |
| text | 获得视图或物化视图的底层SELECT命令（已废弃） |
| text | 获得视图或物化视图的底层SELECT命令 |
| text | 获得视图或物化视图的底层SELECT命令 |
| text | 获得视图或物化视图的底层SELECT命令；带域的行被包装成指定的列数，并隐含了优质打印 |
| boolean | 测试一个索引列是否有指定的性质 |
| boolean | 测试一个索引是否有指定的性质 |
| boolean | 测试一个索引访问方法是否有指定的性质 |
| setof record | 获得存储选项的名称/值对的集合 |
| setof oid | 获得在该表空间中有对象的数据库的 OID 的集合 |
| text | 获得这个表空间所在的文件系统的路径 |
| regtype | 获得任意值的数据类型 |
| text | 获得该参数的排序规则 |
| regclass | 得到指定关系的 OID |
| regproc | 得到指定函数的 OID |
| regprocedure | 得到指定函数的 OID |
| regoper | 得到指定操作符的 OID |
| regoperator | 得到指定操作符的 OID |
| regtype | 得到指定类型的 OID |
| regnamespace | 得到指定模式的 OID |
| regrole | 得到指定角色的 OID |
format_type返回一个数据类型的 SQL 名称，它由它的类型 OID 标识并且可能是一个类型修饰符。如果不知道相关的修饰符，则为类型修饰符传递 NULL。
pg_get_keywords返回一组记录描述服务器识别的 SQL 关键字。word列包含关键字。catcode列包含一个分类码：U为未被预定，C 为列名，T类型或函数名，R为预留。catdesc列包含一个可能本地化的描述分类的字符串。
pg_get_constraintdef、pg_get_indexdef、pg_get_ruledef、pg_get_statisticsobjdef和pg_get_triggerdef分别重建一个约束、索引、规则、扩展统计对象或触发器的创建命令（注意这是一个反编译的重构，而不是命令的原始文本）。pg_get_expr反编译一个表达式的内部形式，例如一个列的默认值。在检查系统目录内容时有用。如果表达式可能包含 Var，在第二个参数中指定它们引用的关系的 OID；如果不会出现 Var，第二个参数设置为 0 即可。pg_get_viewdef重构定义一个视图的SELECT查询。这些函数的大部分都有两种变体，一种可以可选地“优质打印”结果。优质打印的格式可读性更强，但是默认格式更可能被未来版本的PostgreSQL以相同的方式解释。在转出目的中避免使用优质打印输出。为优质打印参数传递假将得到和不带该参数的变体相同的结果。
pg_get_functiondef为一个函数返回一个完整的CREATE OR REPLACE FUNCTION语句。pg_get_function_arguments返回一个函数的参数列表，形式按照它们出现在CREATE FUNCTION中的那样。pg_get_function_result类似地返回函数的合适的RETURNS子句。pg_get_function_identity_arguments返回标识一个函数必要的参数列表，形式和它们出现在ALTER FUNCTION中的一样。这种形式忽略默认值。
pg_get_serial_sequence返回与一个列相关联的序列的名称，如果与列相关联的序列则返回 NULL。如果该列是一个标识列，相关联的序列是为该标识列内部创建的序列。对于使用序列类型之一（serial、smallserial、bigserial）创建的列，它是为那个序列列定义创建的序列。在后一种情况中，这种关联可以用ALTER SEQUENCE OWNED BY修改或者移除（该函数可能应该已经被pg_get_owned_sequence调用，它当前的名称反映了它通常被serial或bigserial列使用）。第一个输入参数是一个带可选模式的表名，第二个参数是一个列名。因为第一个参数可能是一个模式和表，它不能按照一个双引号包围的标识符来对待，意味着它默认情况下是小写的。而第二个参数只是一个列名，将被当作一个双引号包围的来处理并且会保留其大小写。函数返回的值会被适当地格式化以便传递给序列函数。一种典型的用法是为标识列或者序列列读取当前值，例如：
SELECT currval(pg_get_serial_sequence('sometable', 'id'));
pg_get_userbyid抽取给定 OID 的角色的名称。
pg_index_column_has_property、pg_index_has_property和pg_indexam_has_property返回指定的索引列、索引或者索引访问方法是否具有指定性质。如果性质的名称找不到或者不适用于特定的对象，亦或者 OID 或者列号不表示合法的对象，则返回NULL。列的性质可参见表25.5，索引的性质可参见表25.6，访问方法的性质可参见表25.7（注意扩展访问方法可以为其索引定义额外的性质）。
表 25.5. 索引列属性
| 名称 | 描述 |
|---|---|
asc | 在向前扫描时列是按照升序排列吗？ |
desc | 在向前扫描时列是按照降序排列吗？ |
nulls_first | 在向前扫描时列排序会把空值排在前面吗？ |
nulls_last | 在向前扫描时列排序会把空值排在最后吗？ |
orderable | 列具有已定义的排序顺序吗？ |
distance_orderable | 列能否通过一个“distance”操作符（例如ORDER BY col <-> constant）有序地扫描？ |
returnable | 列值是否可以通过一次只用索引扫描返回？ |
search_array | 列是否天然支持col = ANY(array)搜索？ |
search_nulls | 列是否支持IS NULL和IS NOT NULL搜索？ |
表 25.6. 索引性质
| 名称 | 描述 |
|---|---|
clusterable | 索引是否可以用于CLUSTER命令？ |
index_scan | 索引是否支持普通扫描（非位图）？ |
bitmap_scan | 索引是否支持位图扫描？ |
backward_scan | 在扫描中扫描方向能否被更改（为了支持游标上无需物化的FETCH BACKWARD）？ |
表 25.7. 索引访问方法性质
| 名称 | 描述 |
|---|---|
can_order | 访问方法是否支持ASC、DESC以及CREATE INDEX中的有关关键词？ |
can_unique | 访问方法是否支持唯一索引？ |
can_multi_col | 访问方法是否支持多列索引？ |
can_exclude | 访问方法是否支持排除约束？ |
can_include | 访问方法是否支持CREATE INDEX的INCLUDE子句？ |
当传入pg_class.reloptions或pg_attribute.attoptions时，pg_options_to_table返回存储选项名称/值对（option_name/option_value）的集合。
pg_tablespace_databases允许一个表空间被检查。它返回一组数据库的 OID，这些数据库都有对象存储在该表空间中。如果这个函数返回任何行，则该表空间为非空并且不能被删除。为了显示该表空间中的指定对象，你将需要连接到pg_tablespace_databases标识的数据库并且查询它们的pg_class目录。
pg_typeof返回传递给它的值的数据类型的 OID。这在检修或者动态构建 SQL 查询时有用。函数被声明为返回regtype，它是一个 OID 别名类型；这表明它和一个用于比较目的的 OID 相同，但是作为一个类型名称显示。例如：
SELECT pg_typeof(33);
pg_typeof
-----------
integer
(1 row)
SELECT typlen FROM pg_type WHERE oid = pg_typeof(33);
typlen
--------
4
(1 row)表达式collation for返回传递给它的值的排序规则。例子：
SELECT collation for (description) FROM pg_description LIMIT 1;
pg_collation_for
------------------
"default"
(1 row)
SELECT collation for ('foo' COLLATE "de_DE");
pg_collation_for
------------------
"de_DE"
(1 row)值可能被加上引号并且变成模式限定的。如果从参数表达式得不到排序规则，则返回一个空值。如果参数不是一个可排序的数据类型，则抛出一个错误。
to_regclass、to_regproc、to_regprocedure, to_regoper、to_regoperator、to_regtype、to_regnamespace和to_regrole函数把关系、函数、操作符、类型、模式和角色的名称（以text给出）分别转换成、regclass、regproc、regprocedure、regoper、regoperator、regtype、regnamespace和regrole对象。这些函数与 text 转换的不同在于它们不接受数字 OID，并且在名称无法找到时不会抛出错误而是返回空。对于to_regproc和to_regoper，如果给定名称匹配多个对象时返回空。
表25.8列出了与数据库对象 标识和定位有关的函数。
表 25.8. 对象信息和定位函数
| 名称 | 返回类型 | 描述 |
|---|---|---|
| text | 得到一个数据库对象的描述 |
| type text, schema text, name text, identity text | 得到一个数据库对象的标识 |
| type text, name text[], args text[] | 得到一个数据库对象的地址的外部表示 |
| class_id oid, object_id oid, object_sub_id int32 | 从一个数据库对象的内部表示得到它的地址 |
pg_describe_object返回由目录OID、对象OID以及子对象ID（例如表中的一个列号，当子对象引用了一整个对象时其ID为零）指定的数据库对象的文本描述。这种描述是为 了人类可读的，并且可能是被翻译过的，具体取决于服务器配置。这有助于确定一 个存储在pg_depend目录中的对象的标识。
pg_identify_object返回一行，其中包含有足以唯一标识 由目录 OID、对象 OID 和一个（可能为零的）子对象 ID 指定的数据库对象的信息。 该信息是共机器读取的，并且不会被翻译。type标识数据库对象 的类型；schema是该对象所属的模式名，如果对象类型不属于 模式则为NULL；如果名称（加上方案名，如果相关）足以唯一标识对象，则name就是对象的名称（必要时会被加上引号），否则为NULL；identity是完整的对象标识， 它会表现为与对象类型相关的精确格式，并且如有必要，该格式中的每个部分都会 被模式限定。
pg_identify_object_as_address返回一行，其中包含有 足以唯一标识由目录 OID、对象 OID 和一个（可能为零的）子对象 ID 指定的数据 库对象的信息。返回的信息独立于当前服务器，也就是说，它可以被用来在另一个 服务器中标识一个具有相同命名的对象。type标识数据库对象 的类型；object_names和object_args是文本数组，它们一起 构成了对对象的引用。这三个值可以被传递给 pg_get_object_address以获得该对象的内部地址。这个函数是 pg_get_object_address的逆函数。
pg_get_object_address返回一行，其中包含有足以唯一 标识由类型、对象名和参数数组指定的数据库对象的信息。返回值可以被用在诸如 pg_depend等系统目录中并且可以被传递给 pg_identify_object或pg_describe_object等其他 系统函数。class_id是包含该对象的系统目录 OID； objid是对象本身的 OID，而 objsubid是子对象 ID，如果没有则为零。这个函数是 pg_identify_object_as_address的逆函数。
表25.9中展示的函数抽取注释，注释是由COMMENT命令在以前存储的。如果对指定参数找不到注释，则返回空值。
表 25.9. 注释信息函数
| 名称 | 返回类型 | 描述 |
|---|---|---|
| text | 为一个表列获得注释 |
| text | 为一个数据库对象获得注释 |
| text | 为一个数据库对象获得注释（已被废弃） |
| text | 为一个共享数据库对象获得注释 |
col_description为一个表列返回注释，该表列由所在表的 OID 和它的列号指定（obj_description不能被用在表列，因为表列没有自己的 OID）。
obj_description的双参数形式返回一个由其 OID 和所在系统目录名称指定的数据库对象的注释。例如，obj_description(123456,'pg_class')将会检索出 OID 为123456的表的注释。obj_description的单参数形式只要求对象 OID。它已经被废弃，因为无法保证 OID 在不同系统目录之间是唯一的；这样可能会返回错误的注释。
shobj_description用起来就像obj_description，但是前者是用于检索共享对象上的注释。某些系统目录对于一个集簇中的所有数据库是全局的，并且其中的对象的描述也是全局存储的。
表25.10中展示的函数以一种可导出的形式提供了服务器事务信息。这些函数的主要用途是判断在两个快照之间哪些事务被提交。
表 25.10. 事务 ID 和快照
| 名称 | 返回类型 | 描述 |
|---|---|---|
| bigint | 获得当前事务 ID，如果当前事务没有 ID 则分配一个新的 ID |
| bigint | 与txid_current()相同，但是在事务没有分配ID时是返回空值而不是分配一个新的事务ID |
| txid_snapshot | 获得当前快照 |
| setof bigint | 获得快照中正在进行的事务 ID |
| bigint | 获得快照的xmax |
| bigint | 获得快照的xmin |
| boolean | 事务 ID 在快照中可见吗？（不能用于子事务 ID） |
| text | 报告给定事务的状态：committed、aborted、in progress，如果事务ID太老则为空值 |
内部事务 ID 类型（xid）是 32 位宽并且每 40 亿个事务就会回卷。但是，这些函数导出一种 64 位格式，它被使用一个“世代”计数器，这样在一个安装的生命期内不会回卷。这些函数使用的数据类型txid_snapshot存储了在一个特定时刻有关事务 ID 可见性的信息。它的成分在表25.11中描述。
表 25.11. 快照成分
| 名称 | 描述 |
|---|---|
xmin | 仍然活动的最早的事务 ID （txid）。所有更早的事务要么已经被提交并且可见，要么已经被回滚并且死亡。 |
xmax | 第一个还未分配的 txid。所有大于等于它的 txid 在快照的时刻还没有开始，并且因此是不可见的。 |
xip_list | 在快照时刻活动的 txid。这个列表只包括那些位于xmin和xmax之间的活动 txid；可能有活动的超过xmax的 txid。一个满足xmin <= txid < xmax并且不在这个列表中的 txid 在快照时刻已经结束，并且因此根据其提交状态要么可见要么死亡。该列表不包括子事务的 txid。 |
txid_snapshot的文本表示是xmin:xmax:xip_list10:20:10,14,15表示xmin=10, xmax=20, xip_list=10, 14, 15。
txid_status(bigint)报告一个近期事务的提交状态。当一个应用和数据库服务器的连接在COMMIT正在进行时断开，应用可以用它来判断事务是提交了还是中止了。一个事务的状态将被报告为in progress、committed或者aborted，前提是该事务的发生时间足够近，这样系统才会保留它的提交状态。如果事务太老，则系统中不会留下对该事务的引用并且提交状态信息也已经被抛弃，那么这个函数将会返回NULL。注意，预备事务会被报告为in progress，如果应用需要判断该txid是否是一个预备事务，应用必须检查pg_prepared_xacts。
表25.12中展示的函数提供了有关于 已经提交事务的信息。这些函数主要提供有关事务何时被提交的信息。只有当 track_commit_timestamp配置选项被启用时它们才能 提供有用的数据，并且只对已提交事务提供数据。
表 25.12. 已提交事务信息
| 名称 | 返回类型 | 描述 |
|---|---|---|
| timestamp with time zone | 得到一个事务的提交时间戳 |
| xid xid, timestamp timestamp with time zone | 得到最后一个已提交事务的事务 ID 和提交时间戳 |
表25.13中所展示的函数能打印initdb期间初始化的信息，例如系统目录版本。它们也能显示有关预写式日志和检查点处理的信息。这些信息是集簇范围内的，不与任何特定的一个数据库相关。对于同一种来源，它们返回和pg_controldata大致相同的信息，不过其形式更适合于SQL函数。
表 25.13. 控制数据函数
| 名称 | 返回类型 | 描述 |
|---|---|---|
| record | 返回有关当前检查点状态的信息。 |
| record | 返回有关当前控制文件状态的信息。 |
| record | 返回有关集簇初始化状态的信息。 |
| record | 返回有关恢复状态的信息。 |
pg_control_checkpoint返回一个表25.14中所示的记录
表 25.14. pg_control_checkpoint列
| 列名 | 数据类型 |
|---|---|
checkpoint_location | pg_lsn |
redo_lsn | pg_lsn |
redo_wal_file | text |
timeline_id | integer |
prev_timeline_id | integer |
full_page_writes | boolean |
next_xid | text |
next_oid | oid |
next_multixact_id | xid |
next_multi_offset | xid |
oldest_xid | xid |
oldest_xid_dbid | oid |
oldest_active_xid | xid |
oldest_multi_xid | xid |
oldest_multi_dbid | oid |
oldest_commit_ts_xid | xid |
newest_commit_ts_xid | xid |
checkpoint_time | timestamp with time zone |
pg_control_system返回一个表25.15中所示的记录
表 25.15. pg_control_system列
| 列名 | 数据类型 |
|---|---|
pg_control_version | integer |
catalog_version_no | integer |
system_identifier | bigint |
pg_control_last_modified | timestamp with time zone |
pg_control_init返回一个表25.16中所示的记录
表 25.16. pg_control_init列
| 列名 | 数据类型 |
|---|---|
max_data_alignment | integer |
database_block_size | integer |
blocks_per_segment | integer |
wal_block_size | integer |
bytes_per_wal_segment | integer |
max_identifier_length | integer |
max_index_columns | integer |
max_toast_chunk_size | integer |
large_object_chunk_size | integer |
float4_pass_by_value | boolean |
float8_pass_by_value | boolean |
data_page_checksum_version | integer |
pg_control_recovery返回一个表25.17中所示的记录
表 25.17. pg_control_recovery列
| 列名 | 数据类型 |
|---|---|
min_recovery_end_lsn | pg_lsn |
min_recovery_end_timeline | integer |
backup_start_lsn | pg_lsn |
backup_end_lsn | pg_lsn |
end_of_backup_record_required | boolean |
二十六、系统管理函数
这一节描述的函数被用来控制和监视一个PostgreSQL安装。
26.1 配置设定函数
表26.1展示了那些可以用于查询以及修改运行时配置参数的函数。
表 26.1. 配置设定函数
| 名称 | 返回类型 | 描述 |
|---|---|---|
| text | 获得设置的当前值 |
| text | 设置一个参数并返回新值 |
current_setting得到setting_name设置的当前值。它对应于SQL命令SHOW。一个例子：
SELECT current_setting('datestyle');
current_setting
-----------------
ISO, MDY
(1 row)
如果没有名为setting_name的设置，除非提供missing_ok并且其值为true，current_setting会抛出错误。
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)26.2 服务器信号函数
在表26.2中展示的函数向其它服务器进程发送控制信号。默认情况下这些函数只能被超级用户使用，但是如果需要，可以利用GRANT把访问特权授予给其他用户。
表 26.2. 服务器信号函数
| 名称 | 返回类型 | 描述 |
|---|---|---|
| boolean | 取消一个后端的当前查询。如果调用角色是被取消后端的拥有者角色的成员或者调用角色已经被授予pg_signal_backend，这也是允许的，不过只有超级用户才能取消超级用户的后端。 |
| boolean | 导致服务器进程重载它们的配置文件 |
| boolean | 切换服务器的日志文件 |
| boolean | 中止一个后端。如果调用角色是被取消后端的拥有者角色的成员或者调用角色已经被授予pg_signal_backend，这也是允许的，不过只有超级用户才能取消超级用户的后端。 |
这些函数中的每一个都在成功时返回true，并且在失败时返回false。
pg_cancel_backend和pg_terminate_backend向由进程 ID 标识的后端进程发送信号（分别是SIGINT或SIGTERM）。一个活动后端的进程 ID可以从pg_stat_activity视图的pid列中找到，或者通过在服务器上列出postgres进程（在 Unix 上使用ps或者在Windows上使用任务管理器）得到。一个活动后端的角色可以在pg_stat_activity视图的usename列中找到。
pg_reload_conf给服务器发送一个SIGHUP信号， 导致所有服务器进程重载配置文件。
pg_rotate_logfile给日志文件管理器发送信号，告诉它立即切换到一个新的输出文件。这个函数只有在内建日志收集器运行时才能工作，因为否则就不存在日志文件管理器子进程。 subprocess.
26.3 备份控制函数
表26.3中展示的函数可以辅助制作在线备份。这些函数不能在恢复期间执行（pg_is_in_backup、pg_backup_start_time和pg_wal_lsn_diff除外）。
表 26.3. 备份控制函数
| 名称 | 返回类型 | 描述 |
|---|---|---|
| pg_lsn | 为执行恢复创建一个命名点（默认只限于超级用户，但是可以授予其他用户 EXECUTE 特权来执行该函数） |
| pg_lsn | 得到当前的预写式日志刷写位置 |
| pg_lsn | 获得当前预写式日志插入位置 |
| pg_lsn | 获得当前预写式日志写入位置 |
| pg_lsn | 准备执行在线备份（默认只限于超级用户或者复制角色，但是可以授予其他用户 EXECUTE 特权来执行该函数） |
| pg_lsn | 完成执行排他的在线备份（默认只限于超级用户或者复制角色，但是可以授予其他用户 EXECUTE 特权来执行该函数） |
| setof record | 结束执行排他或者非排他的在线备份 （默认只限于超级用户，但是可以授予其他用户 EXECUTE 特权来执行该函数） |
| bool | 如果一个在线排他备份仍在进行中则为真。 |
| timestamp with time zone | 获得一个进行中的在线排他备份的开始时间。 |
| pg_lsn | 强制切换到一个新的预写式日志文件（默认只限于超级用户，但是可以授予其他用户 EXECUTE 特权来执行该函数） |
| pg_lsn | 转换预写式日志位置字符串为文件名 |
| pg_lsn, integer | 转换预写式日志位置字符串为文件名以及文件内的十进制字节偏移 |
| numeric | 计算两个预写式日志位置间的差别 |
pg_start_backup接受一个参数，这个参数可以是备份的任意用户定义的标签（通常这是备份转储文件将被存储的名字）。当被用在排他模式中时，该函数向数据库集簇的数据目录写入一个备份标签文件（backup_label）和一个表空间映射文件（tablespace_map，如果在pg_tblspc/目录中有任何链接），执行一个检查点，然后以文本方式返回备份的起始预写式日志位置。用户可以忽略这个结果值，但是为了可能需要的场合我们还是提供该值。 当在非排他模式中使用时，这些文件的内容会转而由pg_stop_backup函数返回，并且应该由调用者写入到备份中去。
postgres=# select pg_start_backup('label_goes_here');
pg_start_backup
-----------------
0/D4445B8
(1 row)第二个参数是可选的，其类型为boolean。如果为true，它指定尽快执行pg_start_backup。这会强制一个立即执行的检查点，它会导致 I/O 操作的峰值，拖慢任何并发执行的查询。
在一次排他备份中，pg_stop_backup会移除标签文件以及pg_start_backup创建的tablespace_map文件（如果存在）。在一次非排他备份中，backup_label和tablespace_map的内容会包含在该函数返回的结果中，并且应该被写入到该备份的文件中（这些内容不在数据目录中）。有一个可选的boolean类型的第二参数。如果为假，pg_stop_backup将在备份完成后立即返回而不等待WAL被归档。这种行为仅对独立监控WAL归档的备份软件有用。否则，让备份一致所要求的WAL可能会丢失，进而让备份变得毫无用处。当这个参数被设置为真时，在启用归档的前提下pg_stop_backup将等待WAL被归档，在后备服务器上，这意味只有archive_mode = always时才会等待。如果主服务器上的写活动很低，在主服务器上运行pg_switch_wal以触发一次即刻的段切换会很有用。
当在主服务器上执行时，该函数还在预写式日志归档区里创建一个备份历史文件。这个历史文件包含给予pg_start_backup的标签、备份的起始与终止预写式日志位置以及备份的起始和终止时间。返回值是备份的终止预写式日志位置（同样也可以被忽略）。在记录结束位置之后，当前预写式日志插入点被自动地推进到下一个预写式日志文件，这样结束的预写式日志文件可以立即被归档来结束备份。
pg_switch_wal移动到下一个预写式日志文件，允许当前文件被归档（假定你正在使用连续归档）。返回值是在甘冈完成的预写式日志文件中结束预写式日志位置 + 1。如果从上一次预写式日志切换依赖没有预写式日志活动，pg_switch_wal不会做任何事情并且返回当前正在使用的预写式日志文件的开始位置。
pg_create_restore_point创建一个命名预写式日志记录，它可以被用作恢复目标，并且返回相应的预写式日志位置。这个给定的名字可以用于revovery_target_name来指定恢复要进行到的点。避免使用同一个名称创建多个恢复点，因为恢复会停止在第一个匹配名称的恢复目标。
pg_current_wal_lsn以上述函数所使用的相同格式显示当前预写式日志的写位置。类似地，pg_current_wal_insert_lsn显示当前预写式日志插入点，而pg_current_wal_flush_lsn显示当前预写式日志的刷写点。在任何情况下，插入点是预写式日志的“逻辑”终止点，而写入位置是已经实际从服务器内部缓冲区写出的日志的终止点，刷写位置则是被确保写入到持久存储中的日志的终止点。写入位置是可以从服务器外部检查的终止点，对那些关注归档部分完成预写式日志文件的人来说，这就是他们需要的位置。插入和刷写点主要是为了服务器调试目的而存在的。这些都是只读操作并且不需要超级用户权限。
你可以使用pg_walfile_name_offset从任何上述函数的结果中抽取相应的预写式日志文件名称以及字节偏移。例如：
postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
file_name | file_offset
--------------------------+-------------
00000001000000000000000D | 4039624
(1 row)相似地，pg_walfile_name只抽取预写式日志文件名。当给定的预写式日志位置正好在一个预写式日志文件的边界，这些函数都返回之前的预写式日志文件的名称。这对管理预写式日志归档行为通常是所希望的行为，因为前一个文件是当前需要被归档的最后一个文件。
pg_wal_lsn_diff以字节数计算两个预写式日志位置之间的差别。它可以和pg_stat_replication或表26.3中其他的函数一起使用来获得复制延迟。
26.4 恢复控制函数
表26.4中展示的函数提供有关后备机当前状态的信息。这些函数可以在恢复或普通运行过程中被执行。
表 26.4. 恢复信息函数
| 名称 | 返回类型 | 描述 |
|---|---|---|
| bool | 如果恢复仍在进行中，为真。 |
| pg_lsn | 获得最后一个收到并由流复制同步到磁盘的预写式日志位置。当流复制在进行中时，这将单调增加。如果恢复已经完成，这将保持静止在恢复过程中收到并同步到磁盘的最后一个 WAL 记录。如果流复制被禁用，或者还没有被启动，该函数返回 NULL。 |
| pg_lsn | 获得恢复过程中被重放的最后一个预写式日志位置。当流复制在进行中时，这将单调增加。如果恢复已经完成，这将保持静止在恢复过程中被应用的最后一个 WAL 记录。如果服务器被正常启动而没有恢复，该函数返回 NULL。 |
| timestamp with time zone | 获得恢复过程中被重放的最后一个事务的时间戳。这是在主机上产生的事务的提交或中止 WAL 记录的时间。如果在恢复过程中没有事务被重放，这个函数返回 NULL。否则，如果恢复仍在进行这将单调增加。如果恢复已经完成，则这个值会保持静止在恢复过程中最后一个被应用的事务。如果服务器被正常启动而没有恢复，该函数返回 NULL。 |
表26.5中展示的函数空值恢复的进程。这些函数只能在恢复过程中被执行。
表 26.5. 恢复控制函数
| 名称 | 返回类型 | 描述 |
|---|---|---|
| bool | 如果恢复被暂停，为真。 |
| void | 立即暂停恢复（默认仅限于超级用户, 但是可以授予其他用户 EXECUTE 特权来执行该函数）。 |
| void | 如果恢复被暂停，重启之（默认仅限于超级用户，但是可以授予其他用户 EXECUTE 特权来执行该函数）。 |
在恢复被暂停时，不会有进一步的数据库改变被应用。如果在热备模式，所有新的查询将看到数据库的同一个一致快照，并且在恢复被继续之前不会有更多查询冲突会产生。
如果流复制被禁用，暂停状态可以无限制地继续而不出问题。在流复制进行时，WAL 记录将继续被接收，最后将会填满可用的磁盘空间，取决于暂停的持续时间、WAL 的产生率和可用的磁盘空间。
26.5 快照同步函数
PostgreSQL允许数据库会话同步它们的快照。一个快照决定对于正在使用该快照的事务哪些数据是可见的。当两个或者更多个会话需要看到数据库中的相同内容时，就需要同步快照。如果两个会话独立开始其事务，就总是有可能有某个第三事务在两个START TRANSACTION命令的执行之间提交，这样其中一个会话就可以看到该事务的效果而另一个则看不到。
为了解决这个问题，PostgreSQL允许一个事务导出它正在使用的快照。只要导出的事务仍然保持打开，其他事务可以导入它的快照，并且因此可以保证它们可以看到和第一个事务看到的完全一样的数据库视图。但是注意这些事务中的任何一个对数据库所作的更改对其他事务仍然保持不可见，和未提交事务所作的修改一样。因此这些事务是针对以前存在的数据同步，而对由它们自己所作的更改则采取正常的动作。
如表26.6中所示，快照通过pg_export_snapshot函数导出，并且通过SET_TRANSACTION命令导入。
表 26.6. 快照同步函数
| 名称 | 返回类型 | 描述 |
|---|---|---|
| text | 保存当前快照并返回它的标识符 |
函数pg_export_snapshot保存当前的快照并且返回一个text串标识该快照。该字符串必须被传递（到数据库外）给希望导入快照的客户端。直到导出快照的事务的末尾，快照都可以被导入。如果需要，一个事务可以导出多于一个快照。注意这样做只在 READ COMMITTED事务中有用，因为在REPEATABLE READ和更高隔离级别中，事务在它们的生命期中都使用同一个快照。一旦一个事务已经导出了任何快照，它不能使用PREPARE TRANSACTION。
关于如何使用一个已导出快照的细节请见SET TRANSACTION.
26.6 复制函数
表26.7中展示的函数 用于控制以及与复制特性交互。
表 26.7. 复制 SQL 函数
26.7 数据库对象管理函数
表26.8中展示的函数计算数据库对象使用的磁盘空间。
表 26.8. 数据库对象尺寸函数
| 名称 | 返回类型 | 描述 |
|---|---|---|
| int | 存储一个特定值（可能压缩过）所需的字节数 |
| bigint | 指定 OID 的数据库使用的磁盘空间 |
| bigint | 指定名称的数据库使用的磁盘空间 |
| bigint | 附加到指定表的索引所占的总磁盘空间 |
| bigint | 指定表或索引的指定分叉（'main'、'fsm'、'vm'或'init'）使用的磁盘空间 |
| bigint | pg_relation_size(..., 'main')的简写 |
| bigint | 把人类可读格式的带有单位的尺寸转换成字节数 |
| text | 将表示成一个 64位整数的字节尺寸转换为带尺寸单位的人类可读格式 |
| text | 将表示成一个数字值的字节尺寸转换为带尺寸单位的人类可读格式 |
| bigint | 被指定表使用的磁盘空间，排除索引（但包括 TOAST、空闲空间映射和可见性映射） |
| bigint | 指定 OID 的表空间使用的磁盘空间 |
| bigint | 指定名称的表空间使用的磁盘空间 |
| bigint | 指定表所用的总磁盘空间，包括所有的索引和TOAST数据 |
pg_column_size显示用于存储任意独立数据值的空间。
pg_total_relation_size接受一个表或 TOAST 表的 OID 或名称，并返回该表所使用的总磁盘空间，包括所有相关的索引。这个函数等价于pg_table_size + pg_indexes_size。
pg_table_size接受一个表的 OID 或名称，并返回该表所需的磁盘空间，但是排除索引（TOAST 空间、空闲空间映射和可见性映射包含在内）
pg_indexes_size接受一个表的 OID 或名称，并返回附加到该表的所有索引所使用的全部磁盘空间。
pg_database_size以及pg_tablespace_size接受数据库或者表空间的OID或者名称，并且返回它们使用的磁盘空间。要使用pg_database_size，用户必须具有指定数据库上的CONNECT权限（默认情况下已经被授予）或者是pg_read_all_stats角色的一个成员。要使用pg_tablespace_size，用户必须具有指定表空间上的CREATE权限或者是pg_read_all_stats角色的一个成员，除非该表空间是当前数据库的默认表空间。
pg_relation_size接受一个表、索引或 TOAST 表的 OID 或者名称，并且返回那个关系的一个分叉所占的磁盘空间的字节尺寸（注意 对于大部分目的，使用更高层的函数pg_total_relation_size 或者pg_table_size会更方便，它们会合计所有分叉的尺寸）。 如果只得到一个参数，它会返回该关系的主数据分叉的尺寸。提供第二个参数 可以指定要检查哪个分叉：
-
'main'返回该关系主数据分叉的尺寸。 -
'fsm'返回与该关系相关的空闲空间映射的尺寸。 -
'vm'返回与该关系相关的可见性映射的尺寸。 -
'init'返回与该关系相关的初始化分叉（如 果有）的尺寸。
pg_size_pretty可以用于把其它函数之一的结果格式化成一种人类易读的格式，可以根据情况使用字节、kB、MB、GB 或者 TB。
pg_size_bytes可以被用来从人类可读格式的字符串得到其中所表示的字节数。其输入可能带有的单位包括字节、kB、MB、GB 或者 TB，并且对输入进行解析时是区分大小写的。如果没有指定单位，会假定单位为字节。
注意：
函数
pg_size_pretty和pg_size_bytes所使用的单位 kB、MB、GB 和 TB 是用 2 的幂而不是 10 的幂来定义，因此 1kB 是 1024 字节，1MB 是 10242 = 1048576 字节，以此类推。
上述操作表和索引的函数接受一个regclass参数，它是该表或索引在pg_class系统目录中的 OID。你不必手工去查找该 OID，因为regclass数据类型的输入转换器会为你代劳。只写包围在单引号内的表名，这样它看起来像一个文字常量。为了与普通SQL名称的处理相兼容，该字符串将被转换为小写形式，除非其中在表名周围包含双引号。
如果一个 OID 不表示一个已有的对象并且被作为参数传递给了上述函数，将会返回 NULL。
表26.9中展示的函数帮助标识数据库对象相关的磁盘文件。
表 26.9. 数据库对象定位函数
| 名称 | 返回类型 | 描述 |
|---|---|---|
| oid | 指定关系的文件结点号 |
| text | 指定关系的文件路径名 |
| regclass | 查找与给定的表空间和文件节点相关的关系 |
pg_relation_filenode接受一个表、索引、序列或 TOAST 表的 OID 或名称，返回当前分配给它的“filenode”号。文件结点是关系的文件名的基本组件。对于大多数表结果和pg_class.relfilenode相同，但是对于某些系统目录relfilenode为零，并且必须使用此函数获取正确的值。 如果传递一个没有存储的关系（如视图），此函数将返回 NULL。
pg_relation_filepath与pg_relation_filenode类似，但是它返回关系的整个文件路径名（相对于数据库集簇的数据目录PGDATA）。
pg_filenode_relation是pg_relation_filenode的反向函数。给定一个“tablespace” OID 以及一个 “filenode”，它会返回相关关系的 OID。对于一个在数据库的默认表空间中的表，该表空间可以指定为 0。
表26.10列出了用来管理排序规则的函数。
表 26.10. 排序规则管理函数
| 名称 | 返回类型 | 描述 |
|---|---|---|
| text | 返回来自操作系统的排序规则的实际版本 |
| integer | 导入操作系统排序规则 |
pg_collation_actual_version返回当前安装在操作系统中的该排序规则对象的实际版本。如果这个版本与pg_collation.collversion中的值不同，则依赖于该排序规则的对象可能需要被重建。还可以参考ALTER COLLATION。
pg_import_system_collations基于在操作系统中找到的所有locale在系统目录pg_collation中加入排序规则。这是initdb会使用的函数。如果后来在操作系统上安装了额外的locale，可以再次运行这个函数加入新locale的排序规则。匹配pg_collation中现有项的locale将被跳过（但是这个函数不会移除以在操作系统中不再存在的locale为基础的排序规则对象）。schema参数通常是pg_catalog，但这不是一种要求，排序规则也可以被安装到其他的方案中。该函数返回其创建的新排序规则对象的数量。
26.8 索引维护函数
表26.11展示了可用于 索引维护任务的函数。这些函数不能在恢复期间执行。只有超级用户以及给定索引的拥有者才能是用这些函数。
表 26.11. 索引维护函数
| 名称 | 返回类型 | 描述 |
|---|---|---|
| integer | 对还没有建立概要的页面范围建立概要 |
| integer | 如果还没有对覆盖给定块的页面范围建立概要，则对其建立概要 |
| integer | 如果覆盖给定块的页面范围已经建立有概要，则去掉概要 |
| bigint | 把 GIN 待处理列表项移动到主索引结构中 |
brin_summarize_new_values接收一个 BRIN 索引的 OID 或者名称作为参数并且检查该索引以找到基表中当前还没有被该索引汇总的页面范围。对任意一个这样的范围，它将通过扫描那些表页面创建一个新的摘要索引元组。它会返回被插入到该索引的新页面范围摘要的数量。brin_summarize_range做同样的事情，不过它只对覆盖给定块号的范围建立概要。
gin_clean_pending_list接受一个 GIN 索引的 OID 或者名字，并且通过把指定索引的待处理列表中的项批量移动到主 GIN 数据结构来清理该索引的待处理列表。它会返回从待处理列表中移除的页数。注意如果其参数是一个禁用fastupdate选项构建的 GIN 索引，那么不会做清理并且返回值为 0，因为该索引根本没有待处理列表。
26.9 通用文件访问函数
表26.12中展示的函数提供了对数据库服务器所在机器上的文件的本地访问。只能访问数据库集簇目录以及log_directory中的文件，除非用户被授予了角色pg_read_server_files。 使用相对路径访问集簇目录里面的文件，以及匹配 log_directory配置设置的路径访问日志文件。
注意向用户授予pg_read_file()或者相关函数上的EXECUTE特权，函数会允许他们读取服务器上该数据库能读取的任何文件并且这些读取动作会绕过所有的数据库内特权检查。这意味着，除了别的之外，具有这种访问的用户能够读取pg_authid表中包含着认证信息的内容，也能读取数据库中的任意文件。因此，授予对这些函数的访问应该要很仔细地考虑。
表 26.12. 通用文件访问函数
| 名称 | 返回类型 | 描述 |
|---|---|---|
| setof text | 列出目录中的内容。默认仅限于超级用户使用，但是可以给其他用户授予EXECUTE让他们运行这个函数。 |
| setof record | 列出日志目录中文件的名称、尺寸以及最后修改时间。访问被授予给pg_monitor角色的成员，并且可以被授予给其他非超级用户角色。 |
| setof record | 列出WAL目录中文件的名称、尺寸以及最后修改时间。访问被授予给pg_monitor角色的成员，并且可以被授予给其他非超级用户角色。 |
| text | 返回一个文本文件的内容。默认仅限于超级用户使用，但是可以给其他用户授予EXECUTE让他们运行这个函数。 |
| bytea | 返回一个文件的内容。默认仅限于超级用户使用，但是可以给其他用户授予EXECUTE让他们运行这个函数。 |
| record | 返回关于一个文件的信息。默认仅限于超级用户使用，但是可以给其他用户授予EXECUTE让他们运行这个函数。 |
这些函数中的某些有一个可选的missing_ok参数， 它指定文件或者目录不存在时的行为。如果为true， 函数会返回 NULL （pg_ls_dir除外，它返回一个空 结果集）。如果为false，则发生一个错误。默认是 false。
pg_ls_dir返回指定目录中所有文件（以及目录和其他特殊文件） 的名称。include_dot_dirs指示结果集中是否包括 “.”和“..”。默认是排除它们（false），但是 当missing_ok为true时把它们包括在内是 有用的，因为可以把一个空目录与一个不存在的目录区分开。
pg_ls_logdir返回日志目录中每个文件的名称、尺寸以及最后的修改时间（mtime）。默认情况下，只有超级用户以及pg_monitor角色的成员能够使用这个函数。可以使用GRANT把访问授予给其他人。
pg_ls_waldir返回预写式日志（WAL）目录中每个文件的名称、尺寸以及最后的修改时间（mtime）。默认情况下，只有超级用户以及pg_monitor角色的成员能够使用这个函数。可以使用GRANT把访问授予给其他人。
pg_read_file返回一个文本文件的一部分，从给定的offset开始，返回最多length字节（如果先到达文件末尾则会稍短）。如果offset为负，它相对于文件的末尾。如果offset和length被忽略，整个文件都被返回。从文件中读的字节被使用服务器编码解释成一个字符串；如果它们在编码中不合法则抛出一个错误。
pg_read_binary_file与pg_read_file相似，除了前者的结果是一个bytea值；相应地，不会执行编码检查。通过与convert_from函数结合，这个函数可以用来读取一个指定编码的文件：
SELECT convert_from(pg_read_binary_file('file_in_utf8.txt'), 'UTF8');
pg_stat_file返回一个记录，其中包含文件尺寸、最后访问时间戳、最后修改时间戳、最后文件状态改变时间戳（只支持 Unix 平台）、文件创建时间戳（只支持 Windows）和一个boolean指示它是否为目录。通常的用法包括：
SELECT * FROM pg_stat_file('filename');
SELECT (pg_stat_file('filename')).modification;26.10 咨询锁函数
表26.13中展示的函数管理咨询锁。
表 26.13. 咨询锁函数
| 名称 | 返回类型 | 描述 |
|---|---|---|
| void | 获得排他会话级别咨询锁 |
| void | 获得排他会话级别咨询锁 |
| void | 获得共享会话级别咨询锁 |
| void | 获得共享会话级别咨询锁 |
| boolean | 释放一个排他会话级别咨询锁 |
| boolean | 释放一个排他会话级别咨询锁 |
| void | 释放当前会话持有的所有会话级别咨询锁 |
| boolean | 释放一个共享会话级别咨询锁 |
| boolean | 释放一个共享会话级别咨询锁 |
| void | 获得排他事务级别咨询锁 |
| void | 获得排他事务级别咨询锁 |
| void | 获得共享事务级别咨询锁 |
| void | 获得共享事务级别咨询锁 |
| boolean | 如果可能，获得排他会话级别咨询锁 |
| boolean | 如果可能，获得排他会话级别咨询锁 |
| boolean | 如果可能，获得共享会话级别咨询锁 |
| boolean | 如果可能，获得共享会话级别咨询锁 |
| boolean | 如果可能，获得排他事务级别咨询锁 |
| boolean | 如果可能，获得排他事务级别咨询锁 |
| boolean | 如果可能，获得共享事务级别咨询锁 |
| boolean | 如果可能，获得共享事务级别咨询锁 |
pg_advisory_lock锁住一个应用定义的资源，可以使用一个单一64位键值或两个32位键值标识（注意这些两个键空间不重叠）。如果另一个会话已经在同一个资源标识符上持有了一个锁，这个函数将等待直到该资源变成可用。该锁是排他的。多个锁请求会入栈，因此如果同一个资源被锁住三次，则它必须被解锁三次来被释放给其他会话使用。
pg_advisory_lock_shared的工作和pg_advisory_lock相同，不过该锁可以与其他请求共享锁的会话共享。只有想要排他的锁请求会被排除。
pg_try_advisory_lock与pg_advisory_lock相似，不过该函数将不会等待锁变为可用。它要么立刻获得锁并返回true，要么不能立即获得锁并返回false。
pg_try_advisory_lock_shared的工作和pg_try_advisory_lock相同，不过它尝试获得一个共享锁而不是一个排他锁。
pg_advisory_unlock将会释放之前获得的排他会话级别咨询锁。如果锁被成功释放，它返回true。如果锁没有被持有，它将返回false并且额外由服务器报告一个 SQL 警告。
pg_advisory_unlock_shared的工作和pg_advisory_unlock相同，除了它释放一个共享的会话级别咨询锁。
pg_advisory_unlock_all将释放当前会话所持有的所有会话级别咨询锁（这个函数隐式地在会话末尾被调用，即使客户端已经不雅地断开）。
pg_advisory_xact_lock的工作和pg_advisory_lock相同，不过锁是在当前事务的末尾被自动释放的并且不能被显式释放。
pg_advisory_xact_lock_shared的工作和pg_advisory_lock_shared相同，除了锁是在当前事务的末尾自动被释放的并且不能被显式释放。
pg_try_advisory_xact_lock的工作和pg_try_advisory_lock相同，不过锁（若果获得）是在当前事务的末尾被自动释放的并且不能被显式释放。
pg_try_advisory_xact_lock_shared的工作和pg_try_advisory_lock_shared相同，不过锁（若果获得）是在当前事务的末尾被自动释放的并且不能被显式释放。
二十七、触发器函数
当前PostgreSQL提供一个内建的触发器函数suppress_redundant_updates_trigger， 它将阻止任何不会实际更改行中数据的更新发生，这与正常的行为不管数据是否改变始终执行更新相反（这是正常的行为，使得更新运行速度更快，因为不需要检查，并在某些情况下也是有用的）。
理想的情况下，你通常应该避免运行实际上并没有改变记录中数据的更新。 冗余更新会花费大量不必要的时间，尤其是如果有大量索引要改变， 并将最终不得不清理被死亡行占用的空间。但是，在客户端代码中检测这种情况并不总是容易的，甚至不可能做到。 而写表达式来检测它们容易产生错误。作为替代，使用suppress_redundant_updates_trigger可以跳过不改变数据的更新。 但是，你需要小心使用它。触发器需要很短但不能忽略的时间来处理每条记录，所以如果大多数被一个更新影响的记录确实被更改，此触发器的使用将实际上使更新运行得更慢。
suppress_redundant_updates_trigger函数可以像这样被加到一个表：
CREATE TRIGGER z_min_update
BEFORE UPDATE ON tablename
FOR EACH ROW EXECUTE FUNCTION suppress_redundant_updates_trigger();在大部分情况下，你可能希望在最后为每行触发这个触发器。考虑到触发器是按照名字顺序被触发，你需要选择一个位于该表所有其他触发器之后的触发器名字。
有关创建触发器的更多信息请参考CREATE TRIGGER。
二十八、事件触发器函数
PostgreSQL提供了这些助手函数来从 事件触发器检索信息。
28.1 在命令结束处捕捉更改
当在一个ddl_command_end事件触发器的函数中调时， pg_event_trigger_ddl_commands返回被每一个用户动作 执行的DDL命令的列表。如果在其他任何环境中 调用这个函数，会发生错误。 pg_event_trigger_ddl_commands为每一个被执行的基本 命令返回一行，某些只有一个单一 SQL 句子的命令可能会返回多于一行。 这个函数返回下面的列：
| 名称 | 类型 | 描述 |
|---|---|---|
classid | oid | 对象所属的目录的 OID |
objid | oid | 对象本身的 OID |
objsubid | integer | 对象的子-id（例如列的属性号） |
command_tag | text | 命令标签 |
object_type | text | 对象的类型 |
schema_name | text | 该对象所属的模式的名称（如果有），如果没有则为NULL。 没有引号。 |
object_identity | text | 对象标识的文本表现形式，用模式限定。如果必要，出现在 该标识中的每一个标识符都会被引用。 |
in_extension | bool | 如果该命令是一个扩展脚本的一部分则为真 |
command | pg_ddl_command | 以内部格式表达的该命令的一个完整表现形式。这不能被直接输出， 但是可以把它传递给其他函数来得到有关于该命令不同部分的信息。 |
28.2 处理被 DDL 命令删除的对象
pg_event_trigger_dropped_objects返回其sql_drop事件中命令所删除的所有对象的列表。如果在任何其他环境中被调用，pg_event_trigger_dropped_objects将抛出一个错误。pg_event_trigger_dropped_objects返回下列列：
| 名称 | 类型 | 描述 |
|---|---|---|
classid | oid | 对象所属的目录的 OID |
objid | oid | 对象本身的 OID |
objsubid | integer | 对象的子ID（如列的属性号） |
original | bool | 如果这是删除中的一个根对象则为真 |
normal | bool | 指示在依赖图中有一个普通依赖关系指向该对象的标志 |
is_temporary | bool | 如果该对象是一个临时对象则为真 |
object_type | text | 对象的类型 |
schema_name | text | 对象所属模式的名称（如果存在）；否则为NULL。不应用引用。 |
object_name | text | 如果模式和名称的组合能被用于对象的一个唯一标识符，则是对象的名称；否则是NULL。不应用引用，并且名称不是模式限定的。 |
object_identity | text | 对象身份的文本表现，模式限定的。每一个以及所有身份中出现的标识符在必要时加引号。 |
address_names | text[] | 一个数组，它可以和object_type及 address_args 一起通过pg_get_object_address()函数在一台包含有 同类相同名称对象的远程服务器上重建该对象地址。 |
address_args | text[] | 上述address_names的补充。 |
pg_event_trigger_dropped_objects可以被这样用在一个事件触发器中：
CREATE FUNCTION test_event_trigger_for_drops()
RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
obj record;
BEGIN
FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
LOOP
RAISE NOTICE '% dropped object: % %.% %',
tg_tag,
obj.object_type,
obj.schema_name,
obj.object_name,
obj.object_identity;
END LOOP;
END
$$;
CREATE EVENT TRIGGER test_event_trigger_for_drops
ON sql_drop
EXECUTE FUNCTION test_event_trigger_for_drops();28.3 处理表重写函数
表28.1 中所示的函数提供刚刚被调用过table_rewrite 事件的表的信息。如果在任何其他环境中调用，会发生错误。
表 28.1. 表重写信息
| 名称 | 返回类型 | 描述 |
|---|---|---|
| oid | 要被重写的表的 OID。 |
| int | 解释重写原因的原因代码。这些代码的确切含义在单独的文档中。 |
可以在一个这样的事件触发器中使用 pg_event_trigger_table_rewrite_oid函数：
CREATE FUNCTION test_event_trigger_table_rewrite_oid()
RETURNS event_trigger
LANGUAGE plpgsql AS
$$
BEGIN
RAISE NOTICE 'rewriting table % for reason %',
pg_event_trigger_table_rewrite_oid()::regclass,
pg_event_trigger_table_rewrite_reason();
END;
$$;
CREATE EVENT TRIGGER test_table_rewrite_oid
ON table_rewrite
EXECUTE FUNCTION test_event_trigger_table_rewrite_oid();
扫一扫
1658
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
