8. 函数和操作符
瀚高数据库为内建的数据类型提供了大量的函数和操作符。用户也可以定义它们自己的函数和操作符,如第 V 部分所述。psql命令\df和\do可以分别被用于显示所有可用的函数和操作符的列表。
如果你关心移植性,那么请注意,我们在本章描述的大多数函数和操作符,除了最琐碎的算术和比较操作符以及一些做了明确标记的函数以外,都没有在SQL标准里声明。某些这种扩展的功能也出现在许多其它SQL数据库管理系统中,并且在很多情况下多个实现的这种功能是相互兼容的和一致的。
8.1. 逻辑操作符
常用的逻辑操作符有:
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 |
a | NOT a |
---|---|
TRUE | FALSE |
FALSE | TRUE |
NULL | NULL |
操作符AND和OR是可交换的,也就是说,你可以交换左右操作数而不影响结果。
8.2. 比较函数和操作符
常见的比较操作符都可用,如下表所示。
表 8.1 比较操作符
操作符 | 描述 |
---|---|
< | 小于 |
> | 大于 |
<= | 小于等于 |
>= | 大于等于 |
= | 等于 |
<> or != | 不等于 |
注意: |
---|
!=操作符在分析器阶段被转换成<>。二者效果一致。 |
比较操作符可以用于所有可以比较的数据类型。所有比较操作符都是双目操作符,它们返回boolean类型;类似于1 < 2 < 3的表达式是非法的(因为没有<操作符可以比较一个布尔值和3)。
如下表所示,也有一些比较谓词。它们的行为和操作符很像,但是具有 SQL 标准所要求的特殊语法。
表 8.2 比较谓词
谓词 | 描述 |
---|---|
a BETWEEN x AND y | 在x和y之间 |
a NOT BETWEEN x AND y | 不在x和y之间 |
a BETWEEN SYMMETRIC x AND y | 在对比较值排序后位于x和y之间 |
a NOT BETWEEN SYMMETRIC x AND y | 在对比较值排序后不位于x和y之间 |
a IS DISTINCT FROM b | 不等于,空值被当做一个普通值 |
a IS NOT DISTINCT FROM b | 等于,空值被当做一个普通值 |
expression IS NULL | 是空值 |
expression IS NOT NULL | 不是空值 |
expression ISNULL | 是空值(非标准语法) |
expression NOTNULL | 不是空值(非标准语法) |
boolean_expression IS TRUE | 为真 |
boolean_expression IS NOT TRUE | 为假或未知 |
boolean_expression IS FALSE | 为假 |
boolean_expression IS NOT FALSE | 为真或者未知 |
boolean_expression IS UNKNOWN | 值为未知 |
boolean_expression IS NOT UNKNOWN | 为真或者为假 |
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相似,不过BETWEEN SYMMETRIC不要求AND左边的参数小于或等于右边的参数。如果左参数不是小于等于右参数,这两个参数会自动被交换,这样总是会应用一个非空范围。
当有一个输入为空时,普通的比较操作符会得到空(表示“未知”),而不是真或假。例如,7 = NULL得到空,7 <> NULL也一样。如果这种行为不符合使用场景,可以使用IS [ NOT ]
DISTINCT FROM谓词:
a IS DISTINCT FROM b
a IS NOT DISTINCT FROM b
对于非空输入,IS DISTINCT FROM和<>操作符一样。不过,如果两个输入都为空,它会返回假。而如果只有一个输入为空,它会返回真。类似地,IS NOT DISTINCT FROM对于非空输入的行为与=相同,但是当两个输入都为空时它返回真,并且当只有一个输入为空时返回假。因此,这些谓词实际上把空值当作一种普通数据值而不是“unknown”。
要检查一个值是否为空,使用下面的谓词:
expression IS NULL
expression IS NOT NULL
或者等效,但并不标准的谓词:
expression ISNULL
expression NOTNULL
不要写expression = NULL,因为NULL是不“等于”NULL的(控制代表一个未知的值,因此我们无法知道两个未知的数值是否相等)。
提示: |
---|
有些应用可能要求表达式expression = NULL在expression得出空值时返回真。我们强烈建议这样的应用修改成遵循 SQL 标准的。但是,如果这样修改不可能完成,那么我们可以使用配置变量transform_null_equals。如果打开它,瀚高数据库将把x = NULL子句转换成x IS NULL。 |
如果expression是行值,那么当行表达式本身为非空值或者行的所有域为非空时IS NULL为真。由于这种行为,IS NULL和IS NOT NULL并不总是为行值表达式返回反转的结果,特别是,一个同时包含 NULL 和非空值的域将会对两种测试都返回假。在某些情况下,写成row IS DISTINCT FROM NULL或者row IS NOT DISTINCT FROM NULL会更好,它们只会检查整个行值是否为空而不需要在行的域上做额外的测试。
布尔值也可以使用下列谓词进行测试:
boolean_expression IS TRUE
boolean_expression IS NOT TRUE
boolean_expression IS FALSE
boolean_expression IS NOT FALSE
boolean_expression IS UNKNOWN
boolean_expression IS NOT UNKNOWN
这些谓词将总是返回真或假,从来不返回空值,即使操作数是空也如此。空值输入被当做逻辑值“未知”。请注意实际上IS UNKNOWN和IS NOT UNKNOWN分别与IS NULL和IS NOT NULL相同,只是输入表达式必须是布尔类型。
如下表中所示,也有一些比较相关的函数可用。
表 8.3 比较函数
函数 | 描述 | 例子 | 例子结果 |
---|---|---|---|
num_nonnulls(VARIADIC “any”) | 返回非空参数的数量 | num_nonnulls(1, NULL, 2) | 2 |
num_nulls(VARIADIC “any”) | 返回空参数的数量 | num_nulls(1, NULL, 2) | 1 |
8.3. 数学函数和操作符
瀚高数据库为很多类型提供了数学操作符。对于那些没有标准数学表达的类型(如日期/时间类型),我们将在后续小节中描述其实际的行为。
下表展示了所有可用的数学操作符。
表 8.4 数学操作符
操作符 | 描述 | 例子 | 结果 |
---|---|---|---|
+ | 加 | 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 |
& | 按位与 | 91 & 15 | 11 |
| | 按位或 | 32 | 3 | 35 |
# | 按位异或 | 17 # 5 | 20 |
~ | 按位求反 | ~1 | -2 |
<< | 按位左移 | 1 << 4 | 16 |
>> | 按位右移 | 8 >> 2 | 2 |
按位操作操作符只能用于整数数据类型,而其它的操作符可以用于全部数字数据类型。按位操作的操作符还可以用于位串类型bit和bit varying。
下表显示了可用的数学函数。在该表中,dp表示double precision。这些函数中有许多都有多种不同的形式,区别是参数不同。除非特别指明,任何特定形式的函数都返回和它的参数相同的数据类型。处理double precision数据的函数大多数是在宿主系统的 C 库基础上实现的;因此,边界情况下的准确度和行为是根据宿主系统而变化的。
表 8.5 数学函数
函数 | 返回类型 | 描述 | 例子 | 结果 |
---|---|---|---|---|
abs(x) | (和输入相同) | 绝对值 | abs(-17.4) | 17.4 |
cbrt(dp) | dp | 立方根 | cbrt(27.0) | 3 |
ceil(dp or numeric) | (和输入相同) | 不小于参数的最近的整数 | ceil(-42.8) | -42 |
ceiling(dp or numeric) | (和输入相同) | 不小于参数的最近的整数 (ceil的别名) | ceiling(-95.3) | -95 |
degrees(dp) | dp | 把弧度转为角度 | degrees(0.5) | 28.6478897565412 |
div(y numeric, x numeric) | numeric | y/x的整数商 | div(9,4) | 2 |
exp(dp or numeric) | (和输入相同) | 指数 | exp(1.0) | 2.71828182845905 |
floor(dp or numeric) | (和输入相同) | 不大于参数的最近的整数 | floor(-42.8) | 43 |
ln(dp or numeric) | (和输入相同) | 自然对数 | ln(2.0) | 0.693147180559945 |
log(dp or numeric) | (和输入相同) | 以10为底的对数 | log(100.0) | 2 |
log10(dp or numeric) | (和输入相同) | 以10为底的对数 | log10(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 |
round(dp or numeric) | (和输入相同) | 四舍五入到最近的整数 | round(42.4) | 42 |
round(v numeric, s int) | numeric | 把 v 四舍五入到 s 位小数 | round(42.4382, 2) | 42.44 |
scale(numeric) | integer | 参数的精度(小数点后的位数) | scale(8.41) | 2 |
sign(dp or numeric) | (和输入相同) | 参数的符号(-1, 0, +1) | sign(-8.4) | -1 |
sqrt(dp or numeric) | (和输入相同) | 平方根 | sqrt(2.0) | 1.4142135623731 |
trunc(dp or numeric) | (和输入相同) | 截断(向零靠近) | trunc(42.8) | 42 |
trunc(v numeric, s int) | numeric | 截断为s位小数位置的数字 | trunc(42.4382, 2) | 42.43 |
width_bucket(op dp, b1 dp, b2 dp, count int) | int | 返回包含count等宽柱的柱状图中operand所在的柱的编号,范围从low到high。范围内的输入则返回0或计数+1。 | width_bucket(5.35, 0.024, 10.06, 5) | |
width_bucket(op numeric, b1 numeric, b2 numeric, count int) | int | 返回包含count等宽柱的柱状图中operand所在的柱的编号,范围从low到high。超出该范围的输入则返回0或计数+1。 | width_bucket(5.35, 0.024, 10.06, 5) | |
width_bucket(operand anyelement, thresholds anyarray) | int | 返回一个柱号,这个柱是在给定数组中operand将被分配的柱。对于一个低于第一个下界的输入返回0。operand和数组元素可以是具有标准比较操作符的任何类型。thresholds数组必须被排好序,最小的排在最前面,否则将会得到意外的结果。 | width_bucket(now(), array[‘yesterday’, ‘today’, ‘tomorrow’]::timestamptz[]) |
表 8.6展示了用于产生随机数的函数。
表 8.6 随机函数
函数 | 返回类型 | 描述 |
---|---|---|
random() | dp | 范围 0.0 <= x < 1.0 中的随机值 |
setseed(dp) | void | 为后续的random()调用设置(值为于 -1.0 和 1.0 之间,包括边界值) |
random()函数使用了一个简单的线性共轭算法。它的速度很快,但不适合于密码学应用;
关于更安全的替代方案,请参阅 pgcrypto模块。如果setseed()被调用,那么当前会话中的后续random()调用的结果可以通过使用相同的参数重新发布setseed()来重复。
下表显示了可用的三角函数。所有这些函数都有类型为double precision的参数和返回类型。每一种三角函数都有两个变体,一个以弧度度量角,另一个以角度度量角。
表 8.7 三角函数
函数(弧度) | 函数(角度) | 描述 |
---|---|---|
acos(x) | acosd(x) | 反余弦 |
asin(x) | asind(x) | 反正弦 |
atan(x) | atand(x) | 反正切 |
atan2(y, x) | atan2d(y, x) | y/x的反正切 |
cos(x) | cosd(x) | 余弦 |
cot(x) | cotd(x) | 余切 |
sin(x) | sind(x) | 正弦 |
tan(x) | tand(x) | 正切 |
注意: |
---|
另一种使用以角度度量的角的方法是使用早前展示的单位转换函数radians()和degrees()。不过推荐使用基于角度的三角函数,因为这类方法能避免sind(30)等特殊情况下的舍入偏差。 |
下表显示的是可用的双曲函数。所有这些函数接收参数,并返回类型为double precision的值。
表 8.8 双曲函数
函数 | 描述 | 举例 | 结果 |
---|---|---|---|
sinh(x) | 双曲正弦 | sinh(0) | 0 |
cosh(x) | 双曲余弦 | cosh(0) | 1 |
tanh(x) | 双曲切线 | tanh(0) | 0 |
asinh(x) | 反双曲正弦 | asinh(0) | 0 |
acosh(x) | 反双曲余弦 | acosh(1) | 0 |
atanh(x) | 反双曲切线 | atanh(0) | 0 |
8.4. 字符串函数和操作符
本节描述了用于检查和操作字符串值的函数和操作符。在这个环境中的串包括所有类型character、character varying和text的值。除非另外说明,所有下面列出的函数都可以处理这些类型,不过要小心的是,在使用character类型的时候,它有自动填充空白的潜在影响。有些函数还可以处理位串类型。
SQL定义了一些字符串函数,它们使用关键字,而不是逗号来分隔参数。详情请见下表,瀚高数据库也提供了这些函数使用正常函数调用语法的版本。
注意: |
---|
字符串串接操作符( |
表 8.9 SQL字符串函数和操作符
函数 | 返回类型 | 描述 | 例子 | 结果 |
---|---|---|---|---|
string || string | text | 串连接 | ‘High’ || ‘Go’ | HighGo |
string || string or nonstring || string | text | 使用一个非字符 串输入的串连接 | ‘Value: ‘ || 42 | Value: 42 |
bit_length(string) | int | 串中的位数 | bit_length(‘jose’) | 32 |
char_length(string) or 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的开头、结尾或者两端(both是默认值)移除只包含characters(默认是一个空格)中字符的最长字符串 | trim(both ‘xyz’ from ‘yxTomxx’) | Tom |
trim([leading | trailing | both] [from] string [,characters] ) | text | trim()的非标准版本 | trim(both from ‘xTomxx’, ‘x’) | Tom |
upper(string) | text | 将字符串转换成大写形式 | upper(‘tom’) | TOM |
还有额外的串操作函数可以用,它们在下表中列出。它们有些在内部用于实现上表列出的SQL标准字符串函数。
表 8.10 其他字符串函数
函数 | 返回类型 | 描述 | 例子 | 结果 |
---|---|---|---|---|
ascii(string) | int | 参数第一个字符的ASCII代码。对于UTF8返回该字符的Unicode代码点。对于其他多字节编码,该参 数必须是一个 ASCII字符。 | ascii(‘x’) | 120 |
btrim(string text [, characters text]) | text | 从string的开头或结尾删除最长的只包含characters(默认是一个空格)的串 | btrim(‘xyxtrimyyx’, ‘xyz’) | trim |
chr(int) | text | 给定代码的字符。对于UTF8该参数被视作一个 Unicode代码点。对于其他多字节编码该参数必须指定一个ASCII字符。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 | 将字符串转换为dest_encoding。原始编码由src_encoding指定。string在这个编码中必须可用。转换可以使用CREATE CONVERSION定义。也有一些预定义的转换。 | convert(‘text_in_utf8’, ‘UTF8’, ‘LATIN1’) | 用Latin-1 encoding (ISO 8859-1) 表示 的text_in_utf8 |
convert_from(string bytea, src_encoding name) | text | 将字符串转换为数据库编码。原始编码由src_encoding指定。string在这个编码中必须可用。 | convert_from(‘text_in_utf8’, ‘UTF8’) | 用当前数据库编 码表示 的text |
convert_to(string text, dest_encoding name) | bytea | 将字符串转换为dest_encoding | convert_to(‘some text’, ‘UTF8’) | 用UTF8编码表达 的some text |
decode(string text, format text) | bytea | 从string中的文本表达解码二进制数据。format的选项和encode中的一样。 | decode(‘MTIzAAE=’, ‘base64’) | 3132330001 |
encode(data bytea, format text) | text | 将二进制数据编码成一个文本表达。支持的格式有:base64、hex、escape。escape将 零字节和高位组字节转换为八进制序列(\nnn)和双写的反斜线。 | encode(‘123\000\001’, ‘base64’) | TIzAAE |
format(formatstr text [, formatarg “any” [, …] ]) | text | 根据一个格式字符串格式化参数。该函数和C函数sprintf相似。 | 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 | 个字符之外的所有字符。 |
length(string) | int | string中的字符数 | length(‘jose’) | 4 |
length(string bytea, encoding name ) | text | string在给定编码中的字符数。string必须在这个编码中有效。 | length(‘jose’, ‘UTF8’) | 4 |
lpad(string text, length int [,fill text]) | text | 将string通过前置字符fill(默认是一个空格)填充到长度length。如果string已经长于length,则它被(从右边)截断。 | lpad(‘hi’, 5, ‘xy’) | xyxhi |
ltrim(string text [, characters text]) | text | 从string的开头删除最长的只包含characters(默认是一个空格)的串 | ltrim(‘zzzytest’, ‘xyz’) | test |
md5(string) | text | 计算string的MD5 哈希,返回十六进制的结果 | md5(‘abc’) | 900150983cd24fb0 d6963f7d28e17f72 |
parse_ident(qualified_identifier text [, strictmode boolean DEFAULT true ] ) | text[] | 把qualified_identifier分成一个标识符数组,移除单个标识符上的任何引号。默认情况下,最后一个标识符后面的多余字符会被当做错误。但是如果第二个参数为false,那么这一类多余的字符会被忽略(这种行为对于解析函数之类的对象名称有用)。注意这个函数不会截断超长标识符。如果想要进行截断,可以把结果转换成name[]。 | parse_ident(‘“SomeSchema”.someTable’) | {SomeSchema,sometable} |
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语句字符串中被用作一个字符串文字。嵌入的引号会被正确地双写。注意quote_literal对空输入返回空;如果参数可能为空,quote_nullable通常更合适。 | quote_literal(E’O 'Reilly’) | ‘Reilly’ |
quote_literal(value anyelement) | text | 强迫给定值为文本并且接着将它用引号包围作为一个文本。嵌入的单引号和反斜线被正确的双写。 | quote_literal() | 42.5’ |
quote_nullable(string text) | text | 将给定字符串返回成合适的引用形式,使它可以在一个SQL语句字符串中被用作一个字符串文字;或者,如果参数为空,返回NULL。嵌入的引号会被正确地双写。 | quote_nullable(NULL) | NULL |
quote_nullable(value anyelement) | text | 强迫给定值为文本并且接着将它用引号包围作为一个文本;或者,如果参数为空,返回NULL。嵌入的单引号和反斜线被正确的双写。 | quote_nullable(42.5) | ‘42.5’ |
regexp_match(string text, pattern text [, flags text]) | text[] | 返回一个POSIX正则表达式与string的第一个匹配得到的子串。 | regexp_match(‘foobarbequebaz’, ‘(bar)(beque)’) | ‘barbequebaz’ |
regexp_matches(string text, pattern text [, flags text]) | setof text[] | 返回一个POSIX正则表达式与string匹配得到的子串。 | regexp_matches(‘foobarbequebaz’, ‘ba.’, ‘g’) | {bar} {baz} |
regexp_replace(string text, pattern text, replacement text [, flags text]) | 替换匹配一个POSIX正则表达式的子串。 | regexp_replace(‘Thomas’, ‘.[mN]a.’, ‘M’) | Thomas | |
regexp_split_to_array(string text, pattern text [, flags text ]) | text[] | 使用一个POSIX正则表达式作为分隔符划分string。 | regexp_split_to_array(‘hello world’, ‘\s+’) | array(‘hello) |
regexp_split_to_table(string text, pattern text [, flags text]) | setof text | 使用一个POSIX正则表达式作为分隔符划分string。 | regexp_split_to_table(‘hello world’, ‘\s+’) | Table world |
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 | 将string通过增加字符fill(默认为一个空格)填充到长度length。如果string已经长于length则它会被截断。 | rpad(‘hi’, 5, ‘xy’) | hixyx |
rtrim(string text [, characters text]) | text | 从string的结尾删除最长的只包含characters(默认是一个空格)的串 | rtrim(‘testxxzx’, ‘xyz’) | test |
split_part(string text, delimiter text, field int) | text | 按delimiter划分string并返回给定域(从1开始 计算) | split_part(‘abc |
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 | ph |
starts_with(string, prefix) | bool | 如果string以prefix开始则返回真。 | starts_with(‘alphabet’, ‘alph’) | t |
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关键字的数组进行传递(见第 37.5.5 节)。数组的元素被当作函数的独立普通参数一样处理。如果可变数组参数为 NULL,concat和concat_ws返回 NULL,但format把NULL 当作一个零元素数组。
还可以参阅第 8.20 节中的string_agg。
表 8.11 内建转换
转换名 | 源编码 | 目标编码 |
---|---|---|
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 | |
iso_8859_10_to_utf8 | LATIN6 | |
iso_8859_13_to_utf8 | LATIN7 | |
iso_8859_14_to_utf8 | LATIN8 | |
iso_8859_15_to_utf8 | LATIN9 | |
iso_8859_16_to_utf8 | LATIN10 | |
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_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_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_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 |
sjis_to_euc_jp | SJIS | EUC_JP |
sjis_to_mic | SJIS | MULE_INTERNAL |
sjis_to_utf8 | SJIS | 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_uhc | UTF8 | UHC |
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 |
转换名遵循一种标准命名模式:将全部非字母数字字符替换为下划线的源编码的官方名称,后面跟上_to_,最后是按照相似方式处理过的目标编码名称。因此,名称可能会不同于习惯的编码名称。
8.4.1. format
函数format根据一个格式字符串产生格式化的输出,其形式类似于 C 函数sprintf。
format(formatstr text [, formatarg “any” [, …] ])
formatstr是一个格式字符串,它指定了结果应该如何被格式化。格式字符串中的文本被直接复制到结果中,除了使用格式说明符的地方。格式说明符在字符串中扮演着占位符的角色,它定义后续的函数参数如何被格式化及插入到结果中。每一个formatarg参数会被根据其数据类型的常规输出规则转换为文本,并接着根据格式说明符被格式化和插入到结果字符串中。
格式说明符由一个%字符开始并且有这样的形式
%[position][flags][width]type
其中的各组件域是:
position(可选)
一个形式为n$的字符串,其中n是要打印的参数的索引。索引 1 表示formatstr之后的第一个参数。如果position被忽略,默认会使用序列中的下一个参数。
flags(可选)
控制格式说明符的输出如何被格式化的附加选项。当前唯一支持的标志是一个负号(-),它将导致格式说明符的输出会被左对齐(left-justified)。除非width域也被指定,否者这个域不会产生任何效果。
width(可选)
指定用于显示格式说明符输出的最小字符数。输出将被在左部或右部(取决于-标志)用空格填充以保证充满该宽度。太小的宽度设置不会导致输出被截断,但是会被简单地忽略。宽度可以使用下列形式之一指定:一个正整数;一个星号(*)表示使用下一个函数参数作为宽度;或者一个形式为*n$的字符串表示使用第n个函数参数作为宽度。
如果宽度来自于一个函数参数,则参数在被格式说明符的值使用之前就被消耗掉了。如果宽度参数是负值,结果会在长度为abs(width)的域中被左对齐(如果-标志被指定)。
type(必需)
格式转换的类型,用于产生格式说明符的输出。支持下面的类型:
• s将参数值格式化为一个简单字符串。一个控制被视为一个空字符串。
• I将参数值视作 SQL 标识符,并在必要时用双写引号包围它。如果参数为空,将会是一个错误(等效于quote_ident)。
• L将参数值引用为 SQL 文字。一个空值将被显示为不带引号的字符串NULL(等效于quote_nullable)。
除了以上所述的格式说明符之外,要输出一个文字形式的%字符,可以使用特殊序列%%。
下面有一些基本的格式转换的例子:
SELECT format(‘Hello %s’, ‘World’);
结果:Hello World
SELECT format(‘Testing %s, %s, %s, %%’, ‘one’, ‘two’, ‘three’);
结果:Testing one, two, three, %
SELECT format(‘INSERT INTO %I VALUES(%L)’, ‘Foo bar’, E’O'Reilly’);
结果:INSERT INTO “Foo bar” VALUES(‘O’’Reilly’)
SELECT format(‘INSERT INTO %I VALUES(%L)’, ‘locations’, ‘C:\Program Files’);
结果:INSERT INTO locations VALUES(E’C:\Program Files’)
下面是使用width域和-标志的例子:
SELECT format(‘|%10s|’, ‘foo’);
结果:| foo|
SELECT format(‘|%-10s|’, ‘foo’);
结果:|foo |
SELECT format(‘|%*s|’, 10, ‘foo’);
结果:| foo|
SELECT format(‘|%*s|’, -10, ‘foo’);
结果:|foo |
SELECT format(‘|%-*s|’, 10, ‘foo’);
结果:|foo |
SELECT format(‘|%-*s|’, -10, ‘foo’);
结果:|foo |
这些例子展示了position域的例子:
SELECT format(‘Testing %3$s, %2$s, %1$s’, ‘one’, ‘two’, ‘three’);
结果:Testing three, two, one
SELECT format(‘|%*2$s|’, ‘foo’, 10, ‘bar’);
结果:| bar|
SELECT format(‘|%1$*2$s|’, ‘foo’, 10, ‘bar’);
结果:| foo|
不同于标准的 C 函数sprintf,瀚高数据库的format函数允许将带有或者不带有position域的格式说明符被混在同一个格式字符串中。一个不带有position域的格式说明符总是使用最后一个被消耗的参数的下一个参数。另外,format函数不要求所有函数参数都被用在格式字符串中。例如:
SELECT format(‘Testing %3$s, %2$s, %s’, ‘one’, ‘two’, ‘three’);
结果:Testing three, two, three
对于安全地构造动态 SQL 语句,%I和%L格式说明符特别有用。
8.5. 二进制串函数和操作符
本节描述那些检查和操作类型为bytea的值的函数和操作符。
SQL定义了一些使用关键字而不是逗号来分割参数的串函数。详情请见下表 。瀚高数据库也提供了这些函数使用常规函数调用语法的版本(参阅后表)。
注意: |
---|
本页中显示的示例结果是假设服务器参数bytea_output已被设置为escape格式。 |
表 8.12 SQL二进制串函数和操作符
函数 | 返回类型 | 描述 | 例子 | 结果 |
---|---|---|---|---|
string || string | bytea | 串连接 | ‘\High’::bytea || ‘\047go\ \000’::bytea | \\High’go\000 |
octet_length(string) | int | 二进制串中的字 节数 | octet_length(‘jo \000se’::bytea) | 5 |
overlay(string placing string from int [for int]) | bytea | 替换子串 | overlay(‘Th \000omas’::bytea placing ‘\002\003’::bytea from 2 for 3) | T\002\003mas |
position(substring in string) | int | 指定子串的位置 | position(‘\000om’ in ‘Th \000omas’::bytea) | ‘::bytea |
substring(string [from int] [for int]) | bytea | 提取子串 | substring(‘Th \000omas’::bytea from 2 for 3) | h\000o |
trim([both] bytes from string) | bytea | 从string的开头或结尾删除只包含出现在bytes中字节的最长串 | trim(‘\000\001’::bytea from ‘\000Tom \001’::bytea) |
还有一些二进制串处理函数可以使用,在下表列出。其中有一些是在内部使用,用于实现上表列出的 SQL 标准串函数。
表 8.13 其他二进制串函数
函数 | 返回类型 | 描述 | 例子 | 结果 |
---|---|---|---|---|
btrim(string bytea, bytes bytea) | bytea | 从string的开头或结尾删除只由出现在bytes中字节组成的最长串 | btrim(‘\000trim \001’::bytea, ‘\000\001’::bytea) | trim |
decode(string text, format text) | bytea | 从string中的文 本表示解码二进 制数据。format的参数和在encode中 一样。 | decode(‘123\000456’, ‘escape’) | 000456 |
encode(data bytea, format text) | text | 将二进制数据编 码为一个文本表 示。支持的格式有:base64、hex、escape。escape将 零字节和高位组 字节转换为八进 制序列(\nnn) 和双反斜线。 | encode(‘123\000456’::bytea, ‘escape’) | ::bytea, |
get_bit(string, offset) | int | 从串中抽取位 | get_bit(‘Th \000omas’::bytea, 45) | 1 |
get_byte(string, offset) | int | 从串中抽取字节 | get_byte(‘Th \000omas’::bytea, 4) | 109 |
length(string) | int | 二进制串的长度 | length(‘jo \000se’::bytea) | 5 |
md5(string) | text | 计算string的MD5哈希码,以十六进制形式返回结 果 | md5(‘Th \000omas’::bytea) | 8ab2d3c9689aaf18 b4958c334c82d8b1 |
set_bit(string, offset, newvalue) | bytea | 设置串中的位 | set_bit(‘Th \000omas’::bytea, 45, 0) | Th\000omAs |
set_byte(string, offset, newvalue) | bytea | 设置串中的字节 | set_byte(‘Th \000omas’::bytea, 4, 64) | Th\000o@as |
sha224(bytea) | bytea | SHA-224哈希 | sha224(‘abc’) | \x23097d223405d8228642a477bda2 55b32aadbce4bda0b3f7e36c9da7 |
sha256(bytea) | bytea | SHA-256哈希 | sha256(‘abc’) | \xba7816bf8f01cfea414140de5dae2223 b00361a396177a9cb410ff61f20015 |
sha384(bytea) | bytea | SHA-384哈希 | sha384(‘abc’) | \xcb00753f45a35e8bb5a03d699ac65007 272c32ab0eded1631a8b605a43ff5bed 8086072ba1e7cc2358baeca134c825 |
sha512(bytea) | bytea | SHA-512哈希 | sha512(‘abc’) | \xddaf35a193617abacc417349ae204131 12e6fa4e89a97ea20a9eeee64b55d39a 2192992a274fc1a836ba3c23a3feebbd 454d4423643ce80e2a9ac94fa54ca4 |
get_byte和set_byte把一个二进制串中的一个字节计数为字节 0。get_bit和set_bit在每一个字节中从右边起计数位;例如位 0 是第一个字节的最低有效位,而位 15 是第二个字节的最高有效位。
注意由于历史原因,函数md5返回的是一个十六进制编码的text值,而SHA-2函数返回类型bytea。可以使用函数encode和decode在两者之间转换,例如encode(sha256(‘abc’), ‘hex’)可以得到一个十六进制编码的文本表示。
参见第 8.20 节中的聚集函数string_agg以及第 34.4 节中的大对象函数。
8.6. 位串函数和操作符
本节描述用于检查和操作位串的函数和操作符,也就是操作类型为bit和bit varying的值的函数和操作符。除了常用的比较操作符之外,还可以使用下表里显示的操作符。&、|和#的位串操作数必须等长。在移位的时候,保留原始的位串的的长度,如例子所示。
表 8.14 位串操作符
操作符 | 描述 | 例子 | 结果 |
---|---|---|---|
连接 | |||
& | 按位与 | B’10001’ & B’01101’ | 00001 |
按位或 | B’10001’ | ||
# | 按位异或 | 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),因此只会转换整数的最低有效位。
注意: |
---|
把一个整数转换成bit(n)将拷贝整数的最右边的n位。把一个整数转换成比整数本身长的位串,就会在最左边扩展符号。 |
8.7. 模式匹配
瀚高数据库提供了三种独立的实现模式匹配的方法:SQL LIKE操作符、更近一些的SIMILAR TO操作符(SQL:1999 里添加进来的)和POSIX-风格的正则表达式。除了这些基本的操作符外,还有一些函数可用于提取或替换匹配子串并在匹配位置分离一个串。
提示: |
---|
如果你的模式匹配的要求超出了这些,请考虑用 Perl 或 Tcl 写一个用户定义的函数。 |
注意: |
---|
虽然大部分的正则表达式搜索都能被很快地执行,但是正则表达式仍可能被人为地弄成需要任意长的时间和任意量的内存进行处理。要当心非法的来源接受正则表达式搜索模式。如果必须这样做,建议加上语句超时限制。 使用SIMILAR TO模式的搜索具有同样的安全性危险,因为SIMILAR TO提供了很多和 POSIX-风格正则表达式相同的能力。LIKE搜索比其他两种选项简单得多,因此在使用非法的模式来源时要更安全些。 |
这三种类型的模式匹配算子都不支持非确定性的排序规则。如果需要的话,可以在表达式中应用不同的排序规则来绕过这个限制。
8.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’ false
LIKE模式匹配总是覆盖整个串。因此,要匹配在串内任何位置的序列,该模式必须以百分号开头和结尾。
要匹配文本的下划线或者百分号,而不是匹配其它字符,在pattern里相应的字符必须前导转义字符。缺省的转义字符是反斜线,但是你可以用ESCAPE子句指定一个不同的转义字符。要匹配转义字符本身,要写两个转义字符。
注意: |
---|
如果你关掉了standard_conforming_strings,你在文串常量中写的任何反斜线都需要被双写。详见第 4.1.2.1 节。 |
请注意反斜线在串文本里已经有特殊含义了,所以如果你写一个包含反斜线的模式常量,那你就要在 SQL 语句里写两个反斜线。因此,写一个匹配单个反斜线的模式实际上要在语句里写四个反斜线。你可以通过用 ESCAPE 选择一个不同的转义字符 来避免这样;这样反斜线就不再是 LIKE 的特殊字符了。但仍然是字符文本分析器的特殊字符,所以你还是需要两个反斜线。) 我们也可以通过写ESCAPE ‘’的方式不选择转义字符,这样可以有效地禁用逃逸机制,但是没有办法关闭下划线和百分号在模式中的特殊含义。
关键字ILIKE可以用于替换LIKE,它令该匹配根据活动区域成为大小写无关。这个不属于 SQL标准而是一个瀚高数据库扩展。
操作符等效于LIKE,而*对应ILIKE。还有 !和!*操作符分别代表NOT LIKE和NOT ILIKE。所有这些操作符都是瀚高数据库特有的。
在仅需要从字符串的开始部分搜索的情况,还有前缀操作符^@和相应的starts_with函数可以使用。
8.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次。
• 可以使用圆括号()把多个项组合成一个逻辑项。
• 一个方括号表达式[…]声明一个字符类,就像 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函数可以提取匹配SQL正则表达式模式的子字符串。该函数可以按照SQL99语法编写。
substring(string from pattern for escape-character)
或作为一个普通的三参数函数:
substring(string, pattern, escape-character)
与SIMILAR TO一样,指定的模式必须与整个数据字符串匹配,否则函数失败并返回空值。为了表示匹配的数据子字符串的模式中,模式中应该包含两个转义字符的出现,并在后面加上一个双引号(“)。匹配成功后,将返回与这些分隔符之间的模式部分匹配的文本。
转义-双引号分隔符实际上是将子字符串的模式分成三个独立的正则表达式;例如,竖条 (|) 三节中的任何一节只影响到该节。此外,第一节和第三种正则表达式的定义是为了匹配关于有多少数据字符串符合哪种模式。
作为对SQL标准的扩展,瀚高数据库只允许有一个转义双引号分隔符,在这种情况下,第三个正则表达式被视为空;或者没有分隔符,在这种情况下,第一个和第三个正则表达式被视为空。
一些例子,使用#”定界返回串:
substring(‘foobar’ from ‘%#”o_b#”%’ for ‘#’) oob
substring(‘foobar’ from ‘#”o_b#”%’ for ‘#’) NULL
8.7.3. POSIX正则表达式
下表列出了所有可用于 POSIX 正则表达式模式匹配的操作符。
表 8.15 正则表达式匹配操作符
操作符 | 描述 | 例子 |
---|---|---|
~ | 匹配正则表达式,大小写敏感 | ‘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)’ false
POSIX模式语言的详细描述见下文。
带两个参数的substring函数,即substring(string from pattern),提供了抽取一个匹配 POSIX 正则表达式模式的子串的方法。如果没有匹配它返回空值,否则就是文本中匹配模式的那部分。但是如果该模式包含任何圆括号,那么将返回匹配第一对子表达式(对应第一个左圆括号的)的文本。如果你想在表达式里使用圆括号而又不想导致这个例外,那么你可以在整个表达式外边放上一对圆括号。如果你需要在想抽取的子表达式前有圆括号,参阅后文描述的非捕获性圆括号。
一些例子:
substring(‘foobar’ from ‘o.b’) oob
substring(‘foobar’ from ‘o(.)b’) o
regexp_replace函数提供了将匹配 POSIX 正则表达式模式的子串替换为新文本的功能。
它的语法是
regexp_replace(source,
pattern,
replacement [,
flags ])。
如果没有匹配pattern,那么返回不加修改的source串。如果有匹配,则返回的source串里面的匹配子串将被replacement串替换掉。replacement串可以包含\n,其中\n是 1 到 9,表明源串里匹配模式里第n个圆括号子表达式的子串应该被插入,并且它可以包含&表示应该插入匹配整个模式的子串。如果你需要放一个文字形式的反斜线在替换文本里,那么写\。flags参数是一个可选的文本串,它包含另个或更多单字母标志,这些标志可以改变函数的行为。标志i指定大小写无关的匹配,而标志g指定替换每一个匹配的子串而不仅仅是第一个。支持的标志(但不是g)在下表中描述。
一些例子:
regexp_replace(‘foobarbaz’, ‘b..’, ‘X’)
fooXbaz
regexp_replace(‘foobarbaz’, ‘b..’, ‘X’, ‘g’)
fooXX
regexp_replace(‘foobarbaz’, ‘b(..)’, ‘X\1Y’, ‘g’)
fooXarYXazY
regexp_match返回一个文本数组,它包含一个POSIX正则表达式模式与一个字符串第一个匹配所得到的子串。其语法是regexp_match(string, pattern [, flags ])。如果没有匹配,则结果为NULL。如果找到一个匹配并且pattern不包含带括号的子表达式,那么结果是一个单一元素的文本数组,其中包含匹配整个模式的子串。如果找到一个匹配并且pattern含有带括号的子表达式,那么结果是一个文本数组,其中第n个元素是与pattern的第n个圆括号子表达式匹配的子串(“非捕获”圆括号不计入在内,详见下文)。flags参数是一个可选的文本字符串,它包含零个或者更多个可以改变该函数行为的单字母标志。所支持的标志在下表中介绍。
一些例子:
SELECT regexp_match(‘foobarbequebaz’, ‘bar.*que’);
regexp_match
-————-
{barbeque}
(1 row)
SELECT regexp_match(‘foobarbequebaz’, ‘(bar)(beque)’);
regexp_match
-————-
{bar,beque}
(1 row)
在通常情况下,人们只是想要的大整个匹配的子串或者NULL(没有匹配),可以写成这样。
SELECT (regexp_match(‘foobarbequebaz’, ‘bar.*que’))[1];
regexp_match
-————-
barbeque
(1 row)
regexp_matches函数返回一个文本数组的集合,其中包含着一个POSIX正则表达式模式与一个字符串匹配得到的子串。它和regexp_match具有相同的语法。如果没有匹配,这个函数不会返回行。如果有一个匹配并且给定了g标志,则返回一行。如果有N个匹配并且给定了g标志,则返回N行。每一个返回的行都是一个文本数组,其中含有整个匹配的子串或者匹配pattern的圆括号子表达式的子串,这和上面对regexp_match的介绍一样。regexp_matches接受下表中展示的所有标志,外加令它返回所有匹配而不仅仅是第一个匹配的g标志。
一些例子:
SELECT regexp_matches(‘foo’, ‘not there’);
regexp_matches
-—————
(0 rows)
SELECT regexp_matches(‘foobarbequebazilbarfbonk’, ‘(b[^b]+)(b[^b]+)’, ‘g’);
regexp_matches
-—————
{bar,beque}
{bazil,barf}
(2 rows)
提示: |
---|
在大部分情况下,regexp_matches()应该与g标志一起使用,因为如果只是想要第一个匹配,使用regexp_match()会更加简单高效。 |
regexp_split_to_table把一个 POSIX 正则表达式模式当作一个定界符来分离一个串。它的语法形式是regexp_split_to_table(string,
pattern [,
flags ])。如果没有与pattern的匹配,该函数返回string。如果有至少有一个匹配,对每一个匹配它都返回从上一个匹配的末尾(或者串的开头)到这次匹配开头之间的文本。当没有更多匹配时,它返回从上一次匹配的末尾到串末尾之间的文本。flags参数是一个可选的文本串,它包含零个或更多单字母标志,这些标识可以改变该函数的行为。regexp_split_to_table能支持的标志在后表中描述。
regexp_split_to_array函数的行为和regexp_split_to_table相同,不过regexp_split_to_array会把它的结果以一个text数组的形式返回。它的语法是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’, ‘\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’, ‘\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’, ‘\s*‘) AS foo;
foo
-—-
t
h
e
q
u
i
c
k
b
r
o
w
n
f
o
x
(16 rows)
正如上一个例子所示,正则表达式分离函数会忽略零长度的匹配,这种匹配发生在串的开头或结尾或者正好发生在前一个匹配之后。这和正则表达式匹配的严格定义是相悖的,后者由regexp_match和regexp_matches实现,但是通常前者是实际中最常用的行为。其他软件系统如Perl也使用相似的定义。
8.7.3.1. 正则表达式细节
正则表达式(RE),在POSIX 1003.2 中定义,它有两种形式:扩展的RE或者是 ERE(大概地说就是那些在egrep里的),基本的RE或者是BRE(大概地说就是那些在ed里的)。瀚高数据库支持两种形式,并且还实现了一些POSIX标准中没有但是在类似 Perl 或者Tcl 这样的语言中得到广泛应用的一些扩展。使用了那些非POSIX扩展的RE叫高级RE,或者本文档里说的ARE。ARE 几乎完全是 ERE 的超集,但是 BRE 有几个符号上的不兼容(以及更多的限制)。我们首先描述 ARE 和 ERE 形式,描述那些只适用于 ARE 的特性,然后描述BRE 的区别是什么。
注意: |
---|
瀚高数据库初始时总是推测一个正则表达式遵循 ARE 规则。但是,可以通过为 RE 模式预置一个embedded option来选择限制更多的 ERE 或 BRE 规则,如第 8.7.3.4 节中所述。这对为期望准确的POSIX 1003.2 规则的应用提供兼容性很有用。 |
一个正则表达式被定义为一个或更多分支,它们之间被|分隔。只要能匹配其中一个分支的东西都能匹配正则表达式。
一个分支是一个或多个量化原子或者约束连接而成。一个原子匹配第一个,然后后面的原子匹配第二个,以此类推;一个空分支匹配空串。
一个量化原子是一个原子,后面可能跟着一个量词。没有量词的时候,它匹配一个原子,有量词的时候,它可以匹配若干个原子。一个原子可以是在下表里面显示的任何可能。
可能的量词和它们的含义在下表里显示。
一个约束匹配一个空串,但只是在满足特定条件下才匹配。约束可以在能够使用原子的地方使用,只是它不能跟着量词。简单的约束在后表显示;更多的约束稍后描述。
表 8.16 正则表达式原子
原子 | 描述 |
---|---|
(re) | (其中re是任何正则表达式) 匹配一个对re的匹配,匹配将为可能的报告被记下 |
(?:re) | 同上,但是匹配不会为了报告而被记下 (一个“非捕获”圆括号集) (只对 ARE) |
. | 匹配任意单个字符 |
[chars] | 一个方括号表达式,匹配chars中的任意一个(详见第 8.7.3.2 节) |
\k | (其中k是一个非字母数字字符) 匹配一个被当作普通字符看待的特定字符,例如,\ \匹配一个反斜线字符 |
\c | 其中c是一个字母数字 (可能跟着其它字符),它是一个逃逸,参阅第8.7.3.3 节(仅对 ARE; 在 ERE 和 BRE中,它匹配c) |
{ | 如果后面跟着一个字符,而不是数字,那么就匹配左花括弧{;如果跟着一个数字,那么它是range的开始(见下文) |
x | 其中x是一个没有其它意义的单个字符,则匹配该字符 |
RE 不能以反斜线(\)结尾。
注意: |
---|
如果你关掉了standard_conforming_strings,任何你写在文字串常量中的反斜线都需要被双写。详见第 4.1.2.1 节。 |
表 8.17 正则表达式量词
量词 | 匹配 |
---|---|
* | 一个由原子的 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 中可用)匹配对应的正常 (贪婪)模式,区别是它寻找最少的匹配,而不是最多的匹配。详见第 8.7.3.5 节。
注意: |
---|
一个量词不能紧跟在另外一个量词后面,例如**是非法的。量词不能作为表达式或者子表达式的开头,也不能跟在^或者 |
表 8.18 正则表达式约束
约束 | 描述 |
---|---|
^ | 串开头的匹配 |
$ | 串末尾的匹配 |
(?=re) | 在匹配re的子串开始的任何点的positive lookahead匹配(只对 ARE) |
(?!re) | 在匹配re的子串开始的任何点的negative lookahead匹配(只对 ARE) |
(?<=re) | 只要有一个点上有一个子串匹配re端,positive lookbehind就在这个点上匹配(只对 ARE) |
(?<!re) | 只要有一个点上没有子串匹配re端,negative lookbehind就在这个点上匹配(只对 ARE) |
lookahead 和 lookbehind 约束不能包含后引用 (参阅第 8.7.3.3 节),并且其中的所有圆括号都被认为是非捕获的。
8.7.3.2. 方括号表达式
方括号表达式是一个包围在[]中的字符列表。它通常匹配列表中的任意单个字符(但见下文)。如果列表以^开头,它匹配任意单个不在该列表参与部分中的字符。如果该列表中两个字符用-隔开,那它就是那两个字符(包括在内)之间的所有字符范围的缩写,例如,在 ASCII中[0-9]匹配任何十进制数字。两个范围共享一个端点是非法的,例如,a-c-e。范围与字符集关系密切,可移植的程序应该避免依靠它们。
想在列表中包含文本],可以让它做列表的首字符(如果使用了^,需要放在其后)。想在列表中包含文本-,可以让它做列表的首字符或者尾字符,或者一个范围的第二个端点。想在列表中把文本-当做范围的起点,把它用[.和.]包围起来,这样它就成为一个排序元素(见下文)。除了这些字符本身、一些用[的组合(见下段)以及逃逸(只在 ARE 中有效)以外,所有其它特殊字符在方括号表达式里都失去它们的特殊含义。特别是,在 ERE 和 BRE 规则下\不是特殊的,但在 ARE 里,它是特殊的(引入一个逃逸)。
在一个方括号表达式里,一个排序元素(一个字符、一个被当做一个单一字符排序的多字符序列或者一个表示上面两种情况的排序序列名称)包含在[.和.]里面的时候表示该排序元素的字符序列。该序列被当做该方括号列表的一个单一元素。这允许一个包含多字符排序元素的方括号表达式去匹配多于一个字符,例如,如果排序序列包含一个ch排序元素,那么RE [[.ch.]]*c匹配chchcc的头五个字符。
注意: |
---|
瀚高数据库当前不支持多字符排序元素。这些信息描述了将来可能有的行为。 |
在方括号表达式里,包围在[=和=]里的排序元素是一个等价类,代表等效于那一个的所有排序元素的字符序列,包括它本身(如果没有其它等效排序元素,那么就好象封装定界符是[.和 .])。例如,如果o和^是一个等价类的成员,那么[[=o=]]、[[=^=]]和[o^]都是同义的。一个等价类不能是一个范围的端点。
在方括号表达式里,在[:和:]里面封装的字符类的名字代表属于该类的所有字符的列表。
字符类不能作为范围的端点使用。POSIX标准定义了这些字符类的名称:alnum (字符和数字), alpha (字符), blank (空格和制表符tab), cntrl (控制符), digit (数位数), graph (空格除外可打印字符), lower (小写字母), print (包含空格可打印字符), punct (标点符号), space (空白), upper (大写字母), 和 xdigit (十六进制数). 对于7位ASCII字符集中的字符来说,这些标准字符类的行为在不同平台上一般是一致的。一个给定的非ASCII字符是否被认为属于这些类别中的一个,取决于正则表达式函数或运算符使用的collation,或者默认情况下取决于数据库的LC_CTYPE locale设置。非ASCII字符的分类在不同的平台上会有不同的分类,即使是在类似命名的locale中也是如此。(但C locale从不认为任何非ASCII字符属于上述任何一类)。除了这些标准字符类之外,瀚高数据库定义了ascii字符类,它完全包含7位ASCII字符集。
方括号表达式里有两个特例:方括号表达式[[:<:]]和[[:>:]]是约束,分别匹配一个单词开头和结束的空串。单词定义为一个单词字符序列,前面和后面都没有其它单词字符。单词字符是一个alnum字符(和如上所述POSIX字符类中定义的一样) 或者一个下划线。这是一个扩展,兼容POSIX 1003.2,但那里面并没有说明,而且在准备移植到其他系统里去的软件里一定要小心使用。通常下文描述的约束逃逸更好些(它们并非更标准,但是更容易键入)。
8.7.3.3. 正则表达式逃逸
逃逸是以\开头,后面跟着一个字母数字字符得特殊序列。逃逸有好几种变体:字符项、类缩写、约束逃逸以及后引用。在 ARE 里,如果一个\后面跟着一个字母数字,但是并未组成一个合法的逃逸,那么它是非法的。在ERE中没有逃逸:在方括号表达式之外,一个后面跟着字母数字字符的\只是表示该字符是一个普通的字符,而且在一个方括号表达式里,\是一个普通的字符(后者实际上在 ERE 和ARE 不兼容)。
字符项逃逸用于便于我们在 RE 中声明那些不可打印的或其他习惯的字符。
类缩写逃逸用来提供一些常用的字符类缩写。
约束逃逸是一个约束,如果满足特定的条件,它匹配该空串。
后引用(\n)匹配数字\n指定的被前面的圆括号子表达式匹配的同一个串。例如,([bc])\1匹配bb或者cc,但是不匹配bc或者cb。RE 中子表达式必须完全在后引用前面。子表达式以它们的先导圆括号的顺序编号。
非捕获圆括号并不定义子表达式。
表 8.19 正则表达式字符项逃逸
逃逸 | 描述 |
---|---|
\a | 警告(响铃)字符,和 C 中一样 |
\b | 退格,和 C 中一样 |
\B | 反斜线(\)的同义词,用来减少双写反斜线 |
\cX | (其中X是任意字符)低序5位和X相同的字符,它的其他位都是零 |
\e | 排序序列名为ESC的字符,如果无法做到该字符为八进制值 033 |
\f | 换页,和 C 中一样 |
\n | 新行,和 C 中一样 |
\r | 回车,和 C 中一样 |
\t | 水平制表符,和 C 中一样 |
\uwxyz | (其中wxyz正好是四个十六进制位)十六进制值为0xwxyz的字符 |
\Ustuvwxyz | (其中stuvwxyz正好是八个十六进制位)十六进制值为0xstuvwxyz的字符 |
\v | 垂直制表符,和 C 中一样 |
\xhhh | (其中hhh是十六进制位的任意序列)十六进制值为0xhhh的字符(一个单一字符,不管用了多少个十六进制位) |
\0 | 值为0(空字节)的字符 |
\xy | (其中xy正好是两个八进制位,并且不是一个后引用)八进制值为0xy的字符 |
\xyz | (其中xyz正好是三个八进制位,并且不是一个后引用)八进制值为0xyz的字符 |
十六进制位是0-9、a-f和A-F。八进制位是0-7。
指定 ASCII 范围(0-127)之外的值的数字字符项转义的含义取决于数据库编码。当编码是UTF-8 时,转义值等价于 Unicode 代码点,例如 \u1234表示字符U+1234。对于其他多字节编码,字符项转义通常只是指定该字符的字节值的串接。如果该转义值不对应数据库编码中的任何合法字符,将不会发生错误,但是它不会匹配任何数据。
字符项逃逸总是被当作普通字符。例如,\135是 ASCII 中的],但\135并不终止一个方括号表达式。
表 8.20 正则表达式类缩写逃逸
逃逸 | 描述 |
---|---|
\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:]]的,也是非法的)。
表 8.21 正则表达式约束逃逸
逃逸 | 描述 |
---|---|
\A | 只在串开头匹配(与^的不同请参见第 9.7.3.5 节) |
\m | 只在一个词的开头匹配 |
\M | 只在一个词的末尾匹配 |
\y | 只在一个词的开头或末尾匹配 |
\Y | 只在一个词的不是开头或末尾的点上匹配 |
\Z | 只在串的末尾匹配(与$的不同请参见第 8.7.3.5 节) |
一个词被定义成在上面[[:<:]]和[[:>:]]中的声明。在方括号表达式里,约束逃逸是非法的。
表 8.22 正则表达式后引用
逃逸 | 描述 |
---|---|
\m | (其中m是一个非零位)一个到第m个子表达式的后引用 |
\mnn | (其中m是一个非零位,并且nn是一些更多的位,并且十六进制值mnn不超过目前能看到的封闭捕获圆括号的数目)一个到第mnn个子表达式的后引用 |
注意: |
---|
在八进制字符项逃逸和后引用之间有一个历史继承的歧义存在,这个歧义是通过下面的启发式规则解决的,像上面描述地那样。前导零总是表示这是一个八进制逃逸。而单个非零数字,如果没有跟着任何其它位,那么总是被认为后引用。一个多位的非零开头的序列也被认为是后引用,只要它出现在合适的子表达式后面 (也就是说,在后引用的合法范围中的数),否则就被认为是一个八进制。 |
8.7.3.4. 正则表达式元语法
除了上面描述的主要语法之外,还有几种特殊形式和杂项语法。
如果一个 RE 以***:开头,那么剩下的 RE 都被当作 ARE(这在瀚高数据库中通常是无效的,因为 RE 被假定为 ARE,但是如果 ERE 或 BRE 模式通过flags参数被指定为一个正则表达式函数时,它确实能产生效果)。如果一个 RE 以***=开头,那么剩下的 RE 被当作一个文本串,所有的字符都被认为是一个普通字符。
一个 ARE 可以以嵌入选项开头:一个序列(?xyz)(这里的xyz是一个或多个字母字符)声明影响剩余 RE 的选项。这些选项覆盖任何前面判断的选项 — 特别地,它们可以覆盖一个正则表达式操作符隐含的大小写敏感的行为,或者覆盖flags参数中的正则表达式函数。可用的选项字母在下表中显示。注意这些同样的选项字母也被用在正则表达式函数的flags参数中。
表 8.23 ARE 嵌入选项字母
选项 | 描述 |
---|---|
b | RE的剩余部分是一个BRE |
c | 大小写敏感的匹配(覆盖操作符类型) |
e | RE的剩余部分是一个ERE |
i | 大小写不敏感的匹配(见第8.7.3.5 节)(覆盖操作符类型) |
m | n的历史原因的同义词 |
n | 新行敏感的匹配(见第8.7.3.5 节) |
p | 部分新行敏感的匹配(见第8.7.3.5 节) |
q | RE的剩余部分是一个文字(“quoted”) 串,全部是普通字符 |
s | 非新行敏感的匹配(默认) |
t | 紧语法(默认,见下文) |
w | 逆部分新行敏感(“怪异”)的匹配(见第 8.7.3.5 节) |
x | 扩展语法(见下文) |
嵌入选项在)终止序列时发生作用。它们只在 ARE 的开始处起作用 (在任何可能存在的***:控制器后面)。
除了通常的(紧)RE 语法(这种情况下所有字符都有效),还有一种扩展语法,可以通过声明嵌入的x选项获得。在扩展语法里,RE 中的空白字符被忽略,就像那些在#和其后的新行(或 RE 的末尾)之间的字符一样。这样就允许我们给一个复杂的 RE 分段和注释。不过这个基本规则有三种例外:
• 空白字符或前置了\的#将被保留
• 方括号表达式里的空白或者#将被保留
• 在多字符符号里面不能出现空白和注释,例如(?:
为了这个目的,空白是空格、制表符、新行和任何属于空白字符类的字符。
最后,在 ARE 里,方括号表达式外面,序列(?#ttt)(其中ttt是任意不包含一个))的文本)是一个注释,它被完全忽略。同样,这样的东西是不允许出现在多字符符号的字符中间的,例如 (?:。这种注释更像是一种历史产物而不是一种有用的设施,并且它们的使用已经被废弃;请使用扩展语法来替代。
如果声明了一个初始的***=控制器,那么所有这些元语法扩展都不能使用,因为这样表示把用户输入当作一个文字串而不是 RE 对待。
8.7.3.5. 正则表达式匹配规则
在 RE 可以在给定串中匹配多于一个子串的情况下,RE 匹配串中最靠前的那个子串。如果 RE 可以匹配在那个位置开始的多个子串,要么是取最长的子串,要么是最短的,具体哪种,取决于 RE 是贪婪的还是非贪婪的。
一个 RE 是否贪婪取决于下面规则:
• 大多数原子以及所有约束,都没有贪婪属性(因为它们毕竟无法匹配个数变化的文本)。
• 在一个 RE 周围加上圆括号并不会改变其贪婪性。
• 带一个固定重复次数量词 ({m}或者{m}?)的量化原子和原子自身具有同样的贪婪性(可能是没有)。
• 一个带其他普通的量词(包括{m,n}中m等于n的情况)的量化原子是贪婪的(首选最长匹配)。
• 一个带非贪婪量词(包括{m,n}?中m等于 n的情况)的量化原子是非贪婪的(首选最短匹配)。
• 一个分支 — 也就是说,一个没有顶级|操作符的 RE — 和它里面的第一个有贪婪属性的量化原子有着同样的贪婪性。
• 一个由|操作符连接起来的两个或者更多分支组成的 RE 总是贪婪的。
上面的规则所描述的贪婪属性不仅仅适用于独立的量化原子,而且也适用于包含量化原子的分支和整个 RE。这里的意思是,匹配是按照分支或者整个 RE 作为一个整体匹配最长或者最短的可能子串。一旦整个匹配的长度确定,那么匹配任意特定子表达式的部分就基于该子表达式的贪婪属性进行判断,在 RE 里面靠前的子表达式的优先级高于靠后的子表达式。
一个相应的例子:
SELECT SUBSTRING(‘XY1234Z’, ‘Y*([0-9]{1,3})’);
结果:123
SELECT SUBSTRING(‘XY1234Z’, ‘Y*?([0-9]{1,3})’);
结果:1
在第一个例子里,RE 作为整体是贪婪的,因为Y*是贪婪的。它可以匹配从Y开始的东西,并且它匹配从这个位置开始的最长的串,也就是,Y123。输出是这里的圆括号包围的部分,或者说是123。在第二个例子里,RE 总体上是一个非贪婪的 RE,因为Y*?是非贪婪的。它可以匹配从Y开始的最短的子串,也就是说Y1。子表达式[0-9]{1,3}是贪婪的,但是它不能修改总体匹配长度的决定; 因此它被迫只匹配1。
简而言之,如果一个 RE 同时包含贪婪和非贪婪的子表达式,那么总的匹配长度要么是尽可能长,要么是尽可能短,这取决于给整个 RE 赋予的属性。给子表达式赋予的属性只影响在这个匹配里,各个子表达式之间相互允许“吃掉”的多少。
量词{1,1}和{1,1}?可以分别用于在一个子表达式 或者整个 RE 上强制贪婪或者非贪婪。当需要整个 RE 具有不同于从其元素中 推导出的贪婪属性时,这很有用。例如,假设我们尝试将一个包含一些数字的 字符串分隔成数字以及在它们之前和之后的部分,我们可能会尝试这样做:
SELECT regexp_matches(‘abc01234xyz’, ‘(.*)(\d+)(.*)’);
Result: {abc0123,4,xyz}
这不会有用:第一个.*是贪婪的,因此它会“吃掉”尽可能多的字符而留下\d+去匹配在最后一个可能位置上的最后一个数字。我们可能会通过让它变成非贪婪来修复:
SELECT regexp_matches(‘abc01234xyz’, ‘(.*?)(\d+)(.*)’);
Result: {abc,0,””}
这也不会有用:因为现在 RE 作为整体来说是非贪婪的,因此它会尽快结束 全部的匹配。我们可以通过强制 RE 整体是贪婪的来得到我们想要的:
SELECT regexp_matches(‘abc01234xyz’, ‘(?:(.*?)(\d+)(.*)){1,1}’);
Result: {abc,01234,xyz}
独立于 RE 的组件的贪婪性之外控制 RE 的整体贪婪性为处理变长模式提供了 很大的灵活性。
在决定更长或者更短的匹配时,匹配长度是以字符衡量的,而不是排序元素。一个空串会被认为比什么都不匹配长。例如:bb*匹配abbbc的中间三个字符;(week|wee)(night| knights)匹配weeknights的所有十个字符; 而(.*).*匹配 abc的时候,圆括号包围的子表达式匹配所有三个字符;当(a*)*被拿来匹配bc时,整个 RE 和圆括号 子表达式都匹配一个空串。
如果声明了大小写无关的匹配,那么效果就好像所有大小写区别在字母表中消失了。如果在多个情况中一个字母以一个普通字符的形式出现在方括号表达式外面,那么它实际上被转换成 一个包含大小写的方括号表达式,也就是说,x 变成 [xX]。如果它出现在一个方括号表达式里面,那么它的所有大小写的同族都被加入 方括号表达式中,也就是说,x变成[xX]。
当它出现在一个方括号表达式内时,它的所有大小写副本都被加入到方括号表达式中,例如,[x]会变成[xX],而[^x]会变成[^xX]。
如果指定了新行敏感的匹配,.和使用^的方括号表达式 将永远不会匹配新行字符(这样,匹配就绝对不会跨越新行,除非 RE 显式地安排了这样的情况)并且^和$除了分别匹配串开头和结尾之外,还将分别匹配新行后面和前面的空串。但是 ARE 逃逸\A和\Z仍然只匹配串的开头和结尾。
如果指定了部分新行敏感的匹配,那么它影响.和方括号表达式,这个时候和新行敏感的匹配一样,但是不影响^和$。
如果指定了逆新行敏感匹配,那么它影响^和$,其作用和在新行敏感的匹配里一样,但是不影响.和方括号表达式。这个并不是很有用,只是为了满足对称性而提供的。
8.7.3.6. 限制和兼容性
在这个实现里,对 RE 的长度没有特别的限制。但是,那些希望高移植性的程序应该避免使用长度超过 256 字节的 RE,因为 POSIX 兼容 的实现可以拒绝接受这样的 RE。
ARE 实际上和 POSIX ERE 不兼容的唯一的特性是在方括号表达式里\并不失去它特殊的含义。所有其它 ARE 特性都使用在 POSIX ERE 里面是非法或者是未定义、未声明效果的语法;
指示器的***就是在 POSIX 的 BRE 和 ERE 之外的语法。
许多 ARE 扩展都是从 Perl 那里借来的(但是有些被做了修改来清理它们),以及一些 Perl 里没有出现的扩展。要注意的不兼容性包括\b、\B、对结尾的新行缺乏特别的处理、对那些被新行敏感匹配的东西附加的补齐方括号表达式、在 lookahead/lookbehind 约束里对圆括号和后引用的限制以及最长/最短匹配(而不是第一匹配)的语义。
8.7.3.7. 基本正则表达式
BRE 在几个方面和 ERE 不太一样。在 BRE 中,|、+和?都是普通字符并且没有与它们功能等价的东西。范围的定界符是{和},因为 {和}本身是普通字符。嵌套的子表达式的圆括号是(和),因为(和)自身是普通字符。除非在 RE 开头或者是圆括号子表达式开头,^都是一个普通字符。除非在 RE 结尾或者是圆括号子表达式的结尾,$是一个普通字符。如果*出现在 RE 开头或者是圆括号封装的子表达式开头 (前面可能有^),那么它是个普通字符。最后,可以用单数字的后引用,<和>分别是[[:<:]]和[[:>:]]的同义词;在 BRE 中没有其它可用的逃逸。
8.7.3.8. 与XQuery的区别 (LIKE_REGEX)
从SQL:2008开始,SQL标准中包含了一个LIKE_REGEX操作符,它根据XQuery正则表达式标准执行模式匹配。瀚高数据库还没有实现这个操作符,但是你可以使用regexp_match()函数获得非常类似的行为,因为XQuery正则表达式非常接近于上面描述的ARE语法。
与现有的基于POSIX的 正则表达式功能和XQuery正则表达式包括。
• 不支持XQuery字符类减法。这个功能的一个例子是使用下面的例子,只匹配英文辅音。[a-z-[aeiou]]。
• XQuery字符类速记c。不支持C、i和I。
• 不支持使用p{UnicodeProperty}或反过来的P{UnicodeProperty}的XQuery字符类元素。
• POSIX根据当前的locale来解释字符类,如/w (见表 9.20)(你可以通过在操作符或函数中附加一个COLLATE子句来控制)。XQuery通过引用Unicode字符属性来指定这些类,因此只有遵循Unicode规则的locale才能获得等效的行为。
• SQL标准(而不是XQuery本身)试图满足更多的需求。“newline的变体”比POSIX的变体。上面描述的对新行敏感的匹配选项只考虑ASCII NL (n)是新行,但SQL会让我们把CR (r)、CRLF (r/n),以及一些Unicode唯一的字符,如LINE SEPARATOR (U+2028)也视为新行。值得注意的是,.和s应该算作一个字符,而不是按照 SQL的规定算作两个字符。
• 在表 9.19中描述的字符输入转义中,XQuery只支持n、r和t。
• XQuery不支持[::name:]]语法,不支持括号表达式中的字符类。
• XQuery没有 lookahead 或 lookbehind 约束,也没有任何在 表 8.21.
• 第 9.7.3.4 节中描述的metasyntax形式在XQuery中不存在。
• 由XQuery定义的正则表达式标志字母与POSIX的选项字母相关,但不一样。表 8.23)。
虽然i和q选项的行为是一样的,但其他的选项却不一样。
• XQuery的s(允许点匹配换行)和m(允许^和$在换行处匹配)标志提供了与POSIX的n相同的行为。p和w标志,但它们与POSIX的s和m标志的行为不匹配。特别要注意的是,点匹配-newline是POSIX中的默认行为,但不是XQuery。
• XQuery的x(忽略模式中的空格)标志与POSIX的扩展模式标志明显不同。POSIX的x标
8.8. 数据类型格式化函数
瀚高数据库格式化函数提供一套强大的工具用于把各种数据类型 (日期/时间、整数、浮点、数字) 转换成格式化的字符串以及反过来从格式化的字符串转换成 指定的数据类型。表8.24列出了这些函数。这些函数都遵循一个公共的调用规范: 第一个参数是待格式化的值,而第二个是一个定义输出或输入格式的模板。
表 8.24 格式化函数
函数 | 返回类型 | 描述 | 例子 |
---|---|---|---|
to_char(timestamp, text) | text | 把时间戳转成字符串 | to_char(current_timestamp, ‘HH12:MI:SS’) |
to_char(interval, text) | 把间隔转成字符串 | to_char(interval ‘15h 2m 12s’, ‘HH24:MI:SS’) | |
to_char(int, text) | 把整数转成字符串 | to_char(125, ‘999’) | |
to_char(double precision, text) | 把实数或双精度转成字符串 | to_char(125.8::real, ‘999D9’) | |
to_char(numeric, text) | 把数字转成字符串 | to_char(-125.8, ‘999D99S’) | |
to_date(text,text) | date | 把字符串转成日期 | to_date(‘05 Dec 2000’, ‘DD Mon YYYY’) |
to_number(text, text) | numeric | 把字符串转成数字 | to_number(‘12,454.8-‘, ‘99G999D9S’) |
to_timestamp(text, text) | timestamp with time zone | 把字符串转成时间戳 | to_timestamp(‘05 Dec 2000’, ‘DD Mon YYYY’) |
注意: |
---|
还有一个单一参数的to_timestamp函数。 |
提示: |
---|
to_timestamp和to_date存在的目的是为了处理无法用简单造型转换的输入格式。对于大部分标准的日期/时间格式,简单地把源字符串造型成所需的数据类型是可以的,并且简单很多。类似地,对于标准的数字表示形式,to_number也是没有必要的。 |
在一个to_char输出模板串中,一些特定的模式可以被识别并且被替换成基于给定值的被恰当地格式化的数据。任何不属于模板模式的文本都简单地照字面拷贝。同样,在一个输入模板串里(对其他函数),模板模式标识由输入数据串提供的值。如果在模板字符串中有不是模板模式的字符,输入数据字符串中的对应字符会被简单地跳过(不管它们是否等于模板字符串字符)。
下表展示了可以用于格式化日期和时间值的模版。
表 8.25 用于日期/时间格式化的模板模式
模式 | 描述 |
---|---|
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.26 用于日期/时间格式化的模板模式修饰语
修饰语 | 描述 | 例子 |
---|---|---|
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抑制前导的零或尾随的空白,否则会把它们增加到输入从而把一个模式的输出变成固定宽度。在瀚高数据库中,FM只修改下一个声明,而在 Oracle 中,FM影响所有随后的声明,并且重复的FM修饰语将触发填充模式开和关。
• TM不包括结尾空白。to_timestamp和to_date会忽略TM修饰语。
• to_timestamp和to_date跳过了输入字符串开头和日期和时间值周围的多个空格,除非使用了FX选项。例如,to_to_timestamp(‘ 2000 JUN’, ‘YYYMON’)和to_timestamp(‘2000 - JUN’, ‘YYY-MON’)都能工作,但to_timestamp(‘2000 JUN’, ‘FXYYYY-MON’) 返回一个错误,因为to_timestamp只期望一个空格。FX必须指定为模板中的第一个项目。
• to_timestamp和to_date的模板字符串中的分隔符(一个空格或非字母/非数字字符)与输入字符串中的任何一个分隔符相匹配,或者被跳过,除非使用了FX选项。例如,to_to_timestamp(‘2000JUN’, ‘YYY///MON’)和to_timestamp(‘2000/JUN’, ‘YYY/ MON’)可以工作,但to_timestamp(‘2000/JUN’, ‘YYYY/MON’)返回一个错误,因为输入字符串中的分隔符数量超过了模板中的分隔符数量。
如果指定了FX,模板字符串中的分隔符正好与输入字符串中的一个字符匹配。但要注意的是,输入字符串中的字符不需要与模板字符串中的分隔符相同。例如,to_timestamp(‘2000/JUN’, ‘FXYYYY MON’)可以工作,但是to_timestamp(‘2000/JUN’, ‘FXYYYY MON’)返回错误,因为模板字符串中的第二个空格会消耗掉输入字符串中的字母J。
• TZH模板模式可以匹配一个有符号的数字。如果没有FX选项,减号可能是模糊的,可能被解释为分隔符。这种模棱两可的问题可以通过以下方式解决。如果模板字符串中TZH前的分隔符的数量小于输入字符串中减号前的分隔符数量,则减号被解释为TZH的一部分。否则,减号被认为是值之间的分隔符。例如,to_timestamp(‘’2000 -10’, ‘YYY TZH’)与-10匹配,但to_timestamp(‘2000 -10’, ‘YYYY TZH’)匹配10到TZH。
• 在to_char模板里可以有普通文本,并且它们会被照字面输出。你可以把一个子串放到双引号里强迫它被解释成一个文本,即使它里面包含模板模式也如此。例如,在’”Hello Year “YYYY’中,YYYY将被年份数据代替,但是Year中单独的Y不会。
在to_date、to_number以及to_timestamp中,文本和双引号字符串会导致跳过该字符串中所包含的字符数量,例如”XX”会跳过两个输入字符(不管它们是不是XX)。
提示: |
---|
在瀚高安全版数据库4.5之前,可以使用非字母或非数字字符跳过输入字符串中的任意文本。例如,to_timestamp(‘2000y6m1d’, ‘yyyyy-MM-DD’)以前是有效的。现在,你只能使用字母字符来实现这个目的。例如,to_timestamp(‘’2000y6m1d’, ‘yyyytMMtDDt’)和to_timestamp(‘2000y6m1d’, ‘yyyyy”y”MM”m”DD”d”‘)跳过y、m和d。 |
• 如果你想在输出里有双引号,那么你必须在它们前面放反斜线,例如 ‘"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 周没有意义。用户应当避免混合使用格里高利。
注意: |
---|
虽然to_date将会拒绝混合使用格里高利和 ISO 周编号日期的域,to_char却不会,因为YYYY-MM-DD (IYYY-IDDD) 这种输出格式也会有用。但是避免写类似IYYY-MM-DD的东西,那会得到在起始年附近意外的结果(详见第 8.9.1 节)。 |
• 在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.27 用于数字格式化的模板模式
模式 | 描述 |
---|---|
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不会转换小于零的数值,也不会转换小数。
• 在to_number中,如果没有使用L或TH之类的非数据模板模式,相应数量的输入字符会被跳过,不管它们是否匹配模板模式,除非它们是数据字符(也就是数位、负号、小数点或者逗号)。例如,TH会跳过两个非数据字符。
• 带有to_char的V会把输入值乘上10^n,其中n是跟在V后面的位数。带有to_number的V以类似的方式做除法。to_char和to_number不支持使用结合小数点的V(例如,不允许99.9V99)。
• EEEE(科学记数法)不能和任何其他格式化模式或修饰语(数字和小数点模式除外)组合在一起使用,并且必须位于格式化字符串的最后(例如8.99EEEE是一个合法的模式)。
某些修饰语可以被应用到任何模板来改变其行为。例如,FM99.99是带有FM修饰语的99.99模式。
下表中展示了用于数字格式化模式修饰语。
表 8.28 用于数字格式化的模板模式修饰语
修饰语 | 描述 | 例子 |
---|---|---|
FM prefix | 填充模式(抑制拖尾零和填充的空白) | FM99.99 |
TH suffix | 大写序数后缀 | 999TH |
th suffix | 小写序数后缀 | 999th |
下表展示了一些使用to_char函数的例子。
表 8.29 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’ |
8.9. 时间/日期函数和操作符
下边展示了可用于处理日期/时间值的函数,其细节在随后的小节中描述。同时演示了基本算术操作符 (+、*等)的行为。
所有下文描述的接受time或timestamp输入的函数和操作符实际上都有两种变体: 一种接收time with time zone或timestamp with time zone,另外一种接受time without time zone或者 timestamp without time zone。为了简化,这些变种没有被独立地展示。此外,+和*操作符都是可交换的操作符对(例如,date + integer 和 integer + date);我们只显示其中一个。
表 8.30 日期/时间操作符
操作符 | 例子 | 结果 |
---|---|---|
+ | 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’ |
表 8.31 日期/时间函数
函数 | 返回类型 | 描述 | 例子 | 结果 |
---|---|---|---|---|
age(timestamp, timestamp) | interval | 减去参数,生成一个使用年、月(而不是只用日)的“符号化”的结果 | age(timestamp ‘2001-04-10’, timestamp ‘1957-06-13’) | 43 年 9 月 27 日 |
age(timestamp) | interval | 从current_date(在午夜)减去 | age(timestamp ‘1957-06-13’) | 43 years 8 mons 3 days |
clock_timestamp() | timestamp with time zone | 当前日期和时间(在语句执行期间变化); 见第 8.9.4 节 | ||
current_date | date | 当前日期; 见第8.9.4 节 | ||
current_time | time with time zone | 当前时间(一天中的时间); 见第 8.9.4 节 | ||
current_timestamp | timestamp with time zone | 当前日期和时间(当前事务开始时); 见第 8.9.4 节 | ||
date_part(text, timestamp) | double precision | 获得子域(等价于extract); 见第 8.9.1 节 | date_part(‘hour’, timestamp’2001-02-16 20:38:40’) | 20 |
date_part(text, interval) | interval) double precision | 获得子域(等价于extract); 见第 8.9.1 节 | date_part(‘month’, interval ‘2 years 3 months’) | |
date_trunc(text, timestamp) | timestamp | 截断到指定精度; 见第 8.9.2 节 | date_trunc(‘hour’, timestamp ‘2001-02-16 20:38:40’) | 2001-02-16 20:00:00 |
date_trunc(text, timestamp with time zone, text) | timestamp with time zone | 在指定的时区截断到指定的精度;参见 第 8.9.2 节 | date_trunc(‘day’, timestamptz ‘2001-02-16 20:38:40+00’, ‘Australia/ Sydney’) | 2001-02-16 13:00:00+00 |
date_trunc(text, interval) | interval | 截断到指定精度; 见第8.9.2 节 | date_trunc(‘hour’, interval ‘2 days 3 hours 40 minutes’) | 2 days 03:00:00 |
extract(field from timestamp) | double precision | 获得子域; 见第 8.9.1 节 | extract(hour from timestamp ‘2001-02-16 20:38:40’) | 20 |
extract(field from interval) | double precision | 获得子域; 见第 8.9.1 节 | extract(month from interval ‘2 years 3 months’) | 3 |
isfinite(date) | boolean | 测试有限日期 (不是+/-无限) | isfinite(date ‘2001-02-16’) | true |
isfinite(timestamp) | boolean | 测试有限日期 (不是+/-无限) | isfinite(timestamp ‘2001-02-16 21:28:30’) | true |
isfinite(interval) | boolean | 测试有限间隔 | isfinite(interval ‘4 hours’) | true |
justify_days(interval) | interval | 调整间隔这样30天时间周期可以表示为月 | justify_days(interval ‘35 days’) | 1 mon 5 days |
justify_hours(interval) | interval | 调整间隔这样24小时时间周期可以表示为日 | justify_hours(interval ‘27 hours’) | 1 day 03:00:00 |
justify_interval(interval) | interval | 使用justify_days和justify_hours调整间隔,使用额外的符号调整 | justify_interval | 9 days 23:00:00 |
localtime | time | 当前时间(一天中的时间);见第 8.9.4 节 | ||
localtimestamp | timestamp | 当前日期和时间(当前事务的开始);见第 8.9.4 节 | ||
make_date(year int, month int, day int) | date | 从年、月、日域创建日期 | make_date(2013, 7, 15) | 2013-07-15 |
make_interval(years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0) | interval | 从年、月、周、日、时、分、秒 域创建 interval | make_interval(days => 10) | 10 days |
make_time(hour int, min int, sec double precision) | time | 从时、分、秒域创建时间 | make_time(8, 15, 23.5) | 08:15:23.5 |
make_timestamp(year int, month int, day int, hour int, min int, sec double precision) | timestamp | 从年、月、日、时、分、秒域创建时间戳 | make_timestamp(2013, 7, 15, 8, 15, 23.5) | 2013-07-15 08:15:23.5 |
make_timestamptz(year int, month int, day int, hour int, min int, sec double precision, [ timezone text]) | timestamp with | 从年、月、日、时、分、秒域创建带时区的时间戳。如果没有指 定timezone,则使用当前时区。 | make_timestamptz(2013, 7, 15, 8, 15, 23.5) | 2013-07-15 08:15:23.5+01 |
now() | timestamp with time zone | 当前日期和时间(当前事务的开始); 见第 8.9.4 节 | ||
statement_timestamp() | timestamp with time zone | 当前日期和时间(当前事务的开始); 见第 8.9.4 节 | ||
timeofday() | text | 当前日期和时间(像clock_timestamp,但是作为一个text字符串);见第 8.9.4 节 | ||
transaction_timestamp() | timestamp with time zone | time zone 当前日期和时间(当前事务的开始); 见第 8.9.4 节 | ||
to_timestamp(double precision) | timestamp with time zone | 把 Unix 时间 (从 1970-01-01 00:00:00+00 开始的秒)转换成timestamp | to_timestamp(1284352323) | 2010-09-13 04:32:03+00 |
除了这些函数以外,还支持 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返回的月数域可能有歧义,因为不同的月份有不同的天数。瀚高数据库的方法是当计算部分月数时,采用两个日期中较早的月。例如: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 mons
8.9.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’);
结果:40
decade
年份域除以10
SELECT EXTRACT(DECADE FROM TIMESTAMP ‘2001-02-16 20:38:40’);
结果:200
dow
一周中的日,从周日(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’);
结果:47
epoch
对于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+00
hour
小时域(0 - 23)
SELECT EXTRACT(HOUR FROM TIMESTAMP ‘2001-02-16 20:38:40’);
结果:20
isodow
一周中的日,从周一(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域。
microseconds
秒域,包括小数部分,乘以 1,000,000。请注意它包括全部的秒
SELECT EXTRACT(MICROSECONDS FROM TIME ‘17:12:28.5’);
结果:28500000
millennium
千年
SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP ‘2001-02-16 20:38:40’);
结果:3
19xx的年份在第二个千年里。第三个千年从 2001 年 1 月 1 日开始。
milliseconds
秒域,包括小数部分,乘以 1000。请注意它包括完整的秒。
SELECT EXTRACT(MILLISECONDS FROM TIME ‘17:12:28.5’);
结果:28500
minute
分钟域(0 - 59)
SELECT EXTRACT(MINUTE FROM TIMESTAMP ‘2001-02-16 20:38:40’);
结果:38
month
对于timestamp值,它是一年里的月份数(1 - 12); 对于interval值,它是月的数目,然后对 12 取模(0 - 11)
SELECT EXTRACT(MONTH FROM TIMESTAMP ‘2001-02-16 20:38:40’);
结果:2
SELECT EXTRACT(MONTH FROM INTERVAL ‘2 years 3 months’);
结果:3
SELECT EXTRACT(MONTH FROM INTERVAL ‘2 years 13 months’);
结果:1
quarter
该天所在的该年的季度(1 - 4)
SELECT EXTRACT(QUARTER FROM TIMESTAMP ‘2001-02-16 20:38:40’);
结果:1
second
秒域,包括小数部分(0 - 591)
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 西边的时区(从技术上来看,瀚高数据库不使用 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’);
结果:7
year
年份域。要记住这里没有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。 |
extract函数主要的用途是做计算性处理。对于用于显示的日期/时间值格式化,参阅第 8.8 节。
在传统的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’);
结果:4
8.9.2. date_trunc
date_trunc函数在概念上和用于数字的trunc函数类似。
date_trunc(field, source [, time_zone ])
source是类型timestamp或interval的值表达式(类型date和
time的值都分别被自动转换
成timestamp, timestamp with time zone,或者interval)。field选择对输入值选用什么样的精度进行截断。返回的值是timestamp, timestamp with time zone,类型或者所有小于选定的 精度的域都设置为零(或者一,对于日期和月份)的interval。
field的有效值是∶
microseconds
milliseconds
second
minute
hour
day
week
month
quarter
year
decade
century
millennium
当输入值的类型为timestamp with time zone时。截断是针对特定时区进行的。例如,截断为day,产生的值是 是该区域的午夜。默认情况下,截断是在以下方面进行的 到当前的TimeZone设置,但在当前的可以提供可选的time_zone参数。以指定不同的时区。可以指定时区名称第 8.5.3 节中描述的任何一种方式。
当处理timestamp without time zone 或interval输入时,不能指定时区。这些总是按表面值来处理。
示例 (假设当地时区为 America/New_York):
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:00
SELECT date_trunc(‘day’, TIMESTAMP WITH TIME ZONE ‘2001-02-16 20:38:40+00’);
Result: 2001-02-16 00:00:00-05
SELECT date_trunc(‘day’, TIMESTAMP WITH TIME ZONE ‘2001-02-16 20:38:40+00’,
‘Australia/Sydney’);
Result: 2001-02-16 08:00:00-05
SELECT date_trunc(‘hour’, INTERVAL ‘3 days 02:47:33’);
Result: 3 days 02:00:00
8.9.3. AT TIME ZONE
AT TIME ZONE把时间戳without time zone转换成时间戳with time zone或者反过来,并且把time值转换成不同的时区。下表展示了它的变体。
表 8.32 AT TIME ZONE变体
表达式 | 返回类型 | 描述 |
---|---|---|
timestamp without time zone AT TIME ZONE zone | timestamp with time zone | 把给定的不带时区的时间戳当作位于指定时区的时间对待 |
timestamp with time zone AT TIME ZONE zone | timestamp without time zone | 把给定的带时区的时间戳转换到新的时区,不带时区指定 |
time with time zone AT TIME ZONE zone | time with time zone | 把给定的带时区的时间转换到新时区 |
在这些表达式里,我们需要的时区zone可以指定为文本串(例如,’America/
Los_Angeles’)或者一个间隔 (例如,INTERVAL ‘-08:00’)。在文本情况下,可用的时区名字可以用第 8.5.3 节中描述的任何方式指定。
例子(假设本地时区是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)等效于 SQL 兼容的结构timestamp AT TIME ZONE zone。
8.9.4. 当前日期/时间
瀚高数据库提供了许多返回当前日期和时间的函数。这些 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
因为这些函数全部都按照当前事务的开始时刻返回结果,所以它们的值在事务运行的整个期间内都不改变。我们认为这是一个特性:目的是为了允许一个事务在“当前”时间上有一致的概念,这样在同一个事务里的多个修改可以保持同样的时间戳。
注意: |
---|
许多其它数据库系统可能会更频繁地推进这些值。 |
瀚高数据库同样也提供了返回当前语句开始时间的函数,它们会返回函数被调用时的真实当前时间。这些非 SQL 标准的函数列表如下:
transaction_timestamp()
statement_timestamp()
clock_timestamp()
timeofday()
now()
transaction_timestamp()等价于CURRENT_TIMESTAMP,但是其命名清楚地反映了它的返回值。statement_timestamp()返回当前语句的开始时刻(更准确的说是收到 客户端最后一条命令的时间)。statement_timestamp()和transaction_timestamp()在一个事务的第一条命令期间返回值相同,但是在随后的命令中却不一定相同。clock_timestamp()返回真正的当前时间,因此它的值甚至在同一条 SQL 命令中都会变化。
和clock_timestamp()相似,timeofday()也返回真实的当前时间,但是它的结果是一个格式化的text串,而不是timestamp with time zone值。now()是瀚高数据库的一个传统,等效于transaction_timestamp()。
所有日期/时间类型还接受特殊的文字值now,用于指定当前的日期和时间(重申,被解释为当前事务的开始时刻)。因此,下面三个都返回相同的结果:
SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP ‘now’; – 对于和 DEFAULT 一起使用是不正确的
提示: |
---|
在创建表期间指定一个DEFAULT子句时,你不会希望使用第三种形式。系统将在分析这个常量的时候把now转换为一个timestamp,这样需要默认值时就会得到创建表的时间!而前两种形式要到实际使用默认值的时候才被计算,因为它们是函数调用。因此它们可以给出每次插入行的时刻。 |
8.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或者其变体时,你的会话没有持有不必要的锁。否则其它会话可能必须等待你的休眠会话,因而减慢整个系统速度。 |
8.10. 枚举支持函数
对于枚举类型(在第 7.7 节中描述),有一些函数允许更清洁的编码,而不需要为一个枚举类型硬写特定的值。它们被列在下表中。本例假定一个枚举类型被创建为:
CREATE TYPE rainbow AS ENUM (‘red’, ‘orange’, ‘yellow’, ‘green’, ‘blue’, ‘purple’);
表 8.33 枚举支持函数
函数 | 描述 | 例子 | 例子结果 |
---|---|---|---|
enum_first(anyenum) | 返回输入枚举类型的第一个值 | enum_first(null::rainbow) | bow |
enum_last(anyenum) | 返回输入枚举类型的最后一个值 | enum_last(null::rainbow) | purple |
enum_range(anyenum) | 将输入枚举类型的所有值作为一个有序的数组返回 | enum_range(null::rainbow) | {red,orange,yellow,green,blue,purp |
enum_range(anyenum, anyenum) | 以一个数组返回在给定两个枚举值之间的范围。值必须来自相同的枚举类型。如果第一个参数为空,其结果将从枚举类型的第一个值开始。如果 第二参数为空,其结果将以枚举类型的最后一个值结束。 | enum_range(‘orange’::rainbow, ‘green’::rainbow) enum_range(NULL,’green’::rainbow) enum_range(‘orange’::rainbow, NULL) |
{orange,yellow,green} {red,orange,yellow,green} {orange,yellow,green,blue,purple} |
请注意,除了双参数形式的enum_range外,这些函数忽略传递给它们的具体值,它们只关心声明的数据类型。空值或类型的一个特定值可以通过,并得到相同的结果。这些函数更多地被用于一个表列或函数参数,而不是一个硬写的类型名,如例子中所建议。
8.11. 几何函数和操作符
几何类型point、box、 lseg、line、path、 polygon和circle有一大堆本地支持函数和操作符,如下文中所示。
注意: |
---|
请注意“same as”操作符(~=),表示point、box、polygon和circle类型的一般相等概念。这些类型中的某些还有一个=操作符,但是=只比较相同的面 积。其它的标量比较操作符 (<=等等)也是为这些类型比较面积。 |
表 8.34 几何操作符
操作符 | 描述 | 例子 |
---|---|---|
+ | 平移 | 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))’ |
<< | 严格在下 | |
>> | 严格在上 | |
&< | 没有延展到上面 | |
&> | 没有延展到下面 | |
<^ | 在下面(允许相切) | 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)’ |
? | 垂直 | |
? | 垂直对齐 | |
?- | 相互垂直 | |
? | ||
@> | 包含 | circle ‘((0,0),2)’ @> point ‘(1,1)’ |
<@ | 包含在内或在上 | point ‘(1,1)’ <@ circle ‘((0,0),2)’ |
~= | 相同 | polygon ‘((0,0),(1,1))’ ~= polygon ‘((1,1),(0,0))’ |
注意: |
---|
在瀚高数据库之前,包含操作符@>和<@被分别称为~和@。这些名字仍然可以使用,但是已被废除并且最终将被移除。 |
表 8.35 几何函数
函数 | 返回类型 | 描述 | 例子 |
---|---|---|---|
area(object) | double precision | 面积 | area(box ‘((0,0), (1,1))’) |
center(object) | point | 中心 | center(box’((0,0), (1,2))’) |
diameter(circle) | double precision | 圆的直径 | diameter(circle ‘((0,0),2.0)’) |
height(box) | double precision | 方框的垂直尺寸 | height(box’((0,0), (1,1))’) |
isclosed(path) | boolean | 一个封闭路径 | isclosed(path ‘((0,0),(1,1), (2,0))’) |
isopen(path) | boolean | 一个开放路径 | isopen(path ‘[(0,0),(1,1), (2,0)]’) |
length(object) | double precision | 长度 | length(path ‘((-1,0),(1,0))’) |
npoints(path) | int | 点数 | npoints(path ‘[(0,0),(1,1), (2,0)]’) |
npoints(polygon) | int | 点数 | npoints(polygon ‘((1,1),(0,0))’) |
pclose(path) | path | 将路径转换成封闭的 | pclose(path ‘[(0,0),(1,1), (2,0)]’) |
popen(path) | path | 将路径转换成开放 | popen(path ‘((0,0), (1,1),(2,0))’) |
radius(circle) | double precision | 圆的半径 | radius(circle ‘((0,0),2.0)’) |
width(box) | double precision | 方框的水平尺寸 | width(box ‘((0,0), (1,1))’) |
表 8.36 几何类型转换函数
函数 | 返回类型 | 描述 | 例子 |
---|---|---|---|
box(circle) | box | 圆到方框 | box(circle’((0,0),2.0)’) |
box(point) | box | 点到空方框 | box(point ‘(0,0)’) |
box(point, point) | box | 点到方框 | box(point ‘(0,0)’, point ‘(1,1)’) |
box(polygon) | box | 多边形到方框 | box(polygon’((0,0),(1,1), (2,0))’) |
bound_box(box, box) | box | 方框到外包框 | bound_box(box’((0,0),(1,1))’, box ‘((3,3), (4,4))’) |
circle(box) | circle | 方框到圆 | circle(box ‘((0,0), (1,1))’) |
circle(point, double precision) | circle | 中心和半径到圆 | circle(point ‘(0,0)’, 2.0) |
circle(polygon) | circle | 多边形到圆 | circle(polygon ‘((0,0),(1,1), (2,0))’) |
line(point, point) | line | 点到线 | line(point ‘(-1,0)’, point ‘(1,0)’) |
lseg(box) | lseg | 方框对角线到线段 | lseg(box ‘((-1,0), (1,0))’) |
lseg(point, point) | lseg | 点到线段 | lseg(point ‘(-1,0)’, point ‘(1,0)’) |
path(polygon) | path | 多边形到路径 | path(polygon ‘((0,0),(1,1), (2,0))’) |
point(double precision, double precision) | point | 构造点 | point(23.4, -44.5) |
point(box) | point | 方框的中心 | point(box ‘((-1,0), (1,0))’) |
point(circle) | point | 圆的中心 | point(circle ‘((0,0),2.0)’) |
point(lseg) | point | 线段的中心 | point(lseg’((-1,0),(1,0))’) |
point(polygon) | point | 多边形的中心 | point(polygon ‘((0,0),(1,1), (2,0))’) |
polygon(box) | polygon | 方框到4点多边形 | polygon(box ‘((0,0),(1,1))’) |
polygon(circle) | polygon | 圆到12点多边形 | polygon(circle ‘((0,0),2.0)’) |
polygon(npts, circle) | polygon | 点到npts点多边形 | polygon(12, circle ‘((0,0),2.0)’) |
polygon(path) | polygon | 路径到多边形 | polygon(path ‘((0,0),(1,1), (2,0))’) |
我们可以把一个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都画在一张图纸上,你就明白了。
8.12. 网络地址函数和操作符
下表展示了可以用于cidr和 inet类型的操作符。操作符<<、<<=、 >>、>>=和 &&测试用于子网包含。它们只考虑两个地址的网 络部分(忽略任何主机部分),然后判断其中一个网络部分是等于另外一个或者是另外一个的子网。
表 8.37 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’ + 25 |
- | 减 | inet ‘192.168.1.43’ - 36 |
- | 减 | inet ‘192.168.1.43’ - inet ‘192.168.1.19’ |
下表展示了所有可以用于cidr和inet类型的函数。函数abbrev、host和text主要是为了提供可选的显示格式用的。
表 8.38 cidr和inet函数
函数 | 返回类型 | 描述 | 例子 | 结果 |
---|---|---|---|---|
abbrev(inet) | text | 缩写显示格式文本 | abbrev(inet ‘10.1.0.0/16’) | 10.1.0.0/16 |
abbrev(cidr) | text | 缩写显示格式文本 | abbrev(cidr ‘10.1.0.0/16’) | 10.1/16 |
broadcast(inet) | inet | 网络广播地址 | broadcast(‘192.168.1.5/24’) | 192.168.1.255/24 |
family(inet) | int | 抽取地址族;4为 IPv4,6为 IPv6 | family(‘::1’) | 6 |
host(inet) | text | 抽取 IP 地址为文本 | host(‘192.168.1.5/24’) | 192.168.1.5/24 |
hostmask(inet) | inet | 为网络构造主机掩码 | hostmask(‘192.168.23.20/30’) | 192.168.23.20 |
masklen(inet) | int | 抽取网络掩码长度 | masklen(‘192.168.1.5/24’) | 192.168.1.5 |
netmask(inet) | inet | 为网络构造网络掩码 | netmask(‘192.168.1.5/24’) | 255.255.255.0 |
network(inet) | cidr | 抽取地址的网络部分 | network(‘192.168.1.5/24’) | 192.168.1.0/24 |
set_masklen(inet, int) | inet | 为inet值设置网络掩码长度 | set_masklen(‘192.168.1.5/24’, 16) | 192.168.1.5/16 |
set_masklen(cidr, int) | cidr | 为cidr值设置网络掩码长度 | set_masklen(‘192.168.1.0/24’::cidr, 16) | 192.168.1.0 |
text(inet) | text | 抽取 IP 地址和网络掩码长度为 文本 | text(inet ‘192.168.1.5’) | 192.168.1.5/32 |
inet_same_family(inet, inet) | boolean | 地址是来自于同一个家族吗? | inet_same_family(‘192.168.1.5/24’, ‘::1’) | 192.168.1.5/24 |
inet_merge(inet, inet) | cidr | 包括给定网络的最小网络 | inet_merge(‘192.168.1.5/24’, ‘192.168.2.5/24’) | 192.168.0.0/22 |
任何cidr值都能够被隐式或显式地转换为inet值,因此上述能够操作inet值的函数也同样能够操作cidr值(也有独立的用于inet和cidr的函数,因为它的行为应当和这两种情况不同)。inet值也可以转换为cidr值。完成时,该网络掩码右侧的任何位都将无声地转换为零以获得一个有效的cidr值。另外,你还可以使用常规的造型语法将一个文本字符串转换为inet或cidr值:例如,inet(expression)或colname::cidr。
下表展示了可以用于macaddr类型的函数。函数trunc(macaddr)返回一个 MAC 地址,该地址的最后三个字节设置为零。这样可以把剩下的前缀与一个制造商相关联。
表 8.39 macaddr函数
函数 | 返回类型 | 描述 | 例子 | 结果 |
---|---|---|---|---|
trunc(macaddr) | macaddr | 设置最后3个字节为零 | trunc(macaddr ‘12:34:56:78:90:ab’) | 12:34:56:00:00:00 |
macaddr类型还支持标准关系操作符 (>、<=等) 用于编辑次序,并且按位算术操作符(~、&和|)用于 NOT、AND 和 OR。
下表中展示了可以用于macaddr8类型的函数。函数trunc(macaddr8)返回一个后五个字节设置为零的MAC地址。这可以被用来为一个制造商关联一个前缀。
表 8.40 macaddr8函数
函数 | 返回类型 | 描述 | 例子 | 结果 |
---|---|---|---|---|
trunc(macaddr8) | macaddr8 | 设置最后五个字节为零 | trunc(macaddr8 ‘12:34:56:78:90:ab:cd:ef’) | 12:34:56:00:00:00:00:00 |
macaddr8_set7bit(macaddr8) | macaddr8 | 设置第7位为一,也被称为修改版的EUI-64,用于内含在IPv6地址中 | macaddr8_set7bit(macaddr8 ‘00:34:56:ab:cd:ef’) | 02:34:56:ff:fe:ab:cd:ef |
macaddr8类型也支持用于排序的标准关系操作符(>、<=等)以及用于NOT、AND和OR的位运算操作符(~、&和|)。
8.13. 文本搜索函数和操作符
下边几张表总结了为全文搜索提供的函数和操作符。瀚高数据库的文本搜索功能的详细解释可参考第 11 章。
表 8.41 文本搜索操作符
操作符 | 返回类型 | 描述 | 例子 | 结果 |
---|---|---|---|---|
@@ | 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 | |||
&& | tsquery | 将tsquery用 AND连接起来 | ‘fat | rat’::tsquery && ‘cat’::tsquery |
tsquery | 将tsquery用 OR连接起来 | |||
!! | 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比较操作符(=、<等)。它们对于文本搜索不是很有用,但是允许使用。例如,建在这些类型列上的唯一索引。
表 8.42 文本搜索函数
函数 | 返回类型 | 描述 | 例子 | 结果 |
---|---|---|---|---|
array_to_tsvector(text[]) | tsvector | 把词位数组转换 成tsvector | array_to_tsvector(‘{fat,cat,rat}’::text[]) | ‘cat’ ‘fat’ ‘rat’ |
get_current_ts_config() | regconfig | 获得默认文本搜 索配置 | get_current_ts_config() | english |
length(tsvector) | integer | tsvector中的词位数 | length(‘fat:2,4 cat:3 rat:5A’::tsvector) | 3 |
numnode(tsquery) | integer | tsquery中词位外 加操作符的数目 | numnode(‘(fat & rat) | cat’::tsquery) |
plainto_tsquery([ config regconfig , ] query text) | tsquery | 产生tsquery但忽略标点符号 | plainto_tsquery(‘english’, ‘The Fat Rats’) | ‘fat’ & ‘rat’ |
phraseto_tsquery([ config regconfig , ] query text) | tsquery | 产生忽略标点搜 索短语的tsquery | phraseto_tsquery(‘english’, ‘The Fat Rats’) | ‘fat’ <-> ‘rat’ |
websearch_to_tsquery([ config regconfig , ] query text) | tsquery | 从一个Web搜索风格的查询产生tsquery | websearch_to_tsquery(‘english’, ‘“fat rat” or rat’) | ‘fat’ <-> ‘rat’ |
querytree(query tsquery) | text | 获得一个tsquery的可索引部分 | querytree(‘foo & ! bar’::tsquery) | ‘foo’ |
setweight(vector tsvector, weight “char”) | tsvector | 获得一个tsquery的可索引部分 | querytree(‘foo & ! bar’::tsquery) | ‘foo’ |
setweight(vector tsvector, weight “char”) | tsvector | 为vector的每一 个元素分配权重 | setweight(‘fat:2,4 cat:3 rat:5B’::tsvector, ‘A’) | ‘cat’:3A ‘fat’:2A,4A ‘rat’:5A |
setweight(vector tsvector, weight “char”, lexemes text[]) | tsvector | 为lexemes中列出的vector的元素分配权重 | setweight(‘fat:2,4 cat:3 rat:5B’::tsvector, ‘A’, ‘{cat,rat}’) | ‘cat’:3A ‘fat’:2,4 ‘rat’:5A |
strip(tsvector) | tsvector | 从tsvector中移除位置和权重 | strip(‘fat:2,4 cat:3 rat:5A’::tsvector) | ‘cat’ ‘fat’ ‘rat’ |
to_tsquery([ config regconfig , ] query text) | tsquery | 规范化词并转换 成tsquery | ||
to_tsvector([ config regconfig , ] document text) | tsvector | 缩减文档文本 成tsvector | to_tsvector(‘english’, ‘The Fat Rats’) | ‘fat’:2 ‘rat’:3 |
to_tsvector([ config regconfig , ] document json(b)) | tsvector | 把该文档中的每个字符串值缩减 成一个tsvector,然后将它们按在文档中的顺序串接起来形成一个tsvector | to_tsvector(‘english’, ‘{“a”: “The Fat Rats”}’::json) | ‘fat’:2 ‘rat’:3 |
json(b)_to_tsvector([ config regconfig, ] document json(b), filter json(b)) | tsvector | 把filter指定的文档中的每个值 缩减为一个tsvector,然后把它们按照文档中的顺序串接起来形成一个tsvector。filter是 一个jsonb数组,它列举哪些种类的元素需要被包括在结果tsvector中。filter的可能值是”string”(包括所有字符串值)、”numeric”(包括所有字符串格式的数字值)、”boolean”(包括所有字符串格式”true”/ “false”的布尔值)、”key”(包括所有键)或者”all”(包括上述所有)。这些值可以被组合在一起,例如用来包括所有的字符串和数字值。 | json_to_tsvector(‘english’, ‘{“a”: “The Fat Rats”, “b”: 123}’::json, ‘[“string”, “numeric”]’) | ‘123’:5 ‘fat’:2 ‘rat’:3 |
ts_delete(vector tsvector, lexeme text) | tsvector | 从vector中移除 给定的lexeme | ts_delete(‘fat:2,4 cat:3 rat:5A’::tsvector, ‘fat’) | ‘cat’:3 ‘rat’:5A |
ts_delete(vector tsvector, lexemes text[]) | tsvector | 从vector中移除lexemes中词位的任何出现 | ts_delete(‘fat:2,4 cat:3 rat:5A’::tsvector, ARRAY[‘fat’,’rat’]) | cat’:3 |
ts_filter(vector tsvector, weights “char”[]) | tsvector | 从vector中只选择带有给定权重的元素 | ts_filter(‘fat:2,4 cat:3b rat:5A’::tsvector, ‘{a,b}’) | ‘cat’:3B ‘rat’:5A |
ts_headline([ config regconfig, ] document text, query tsquery [, options text ]) | text | 显示一个查询匹配 | ts_headline(‘x y z’, ‘z’::tsquery) | x y <b>z</b> |
ts_headline([ config regconfig, ] document json(b), query tsquery [, options text ]) | text | 显示一个查询匹配 | ts_headline(‘{“a”:”x y z”}’::json, ‘z’::tsquery) | {“a”:”x y <b>z</b>”} |
ts_rank([ weights float4[], ] vector tsvector, query tsquery [,normalization integer ]) | float4 | 为查询排名文档 | ts_rank(textsearch, query) | 0.818 |
ts_rank_cd([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) | float4 | 使用覆盖密度为 查询排名文档 | ts_rank_cd(‘{0.1, 0.2, 0.4, 1.0}’, textsearch, query) | 2.01317 |
ts_rewrite(query tsquery, target tsquery, substitute tsquery) | tsquery | 在查询内用substitute替换target | ts_rewrite(‘a & b’::tsquery, ‘a’::tsquery, ‘foo | bar’::tsquery) |
ts_rewrite(query tsquery, select text) | tsquery | 使用来自一个SELECT的目标和替换者进行替换 | SELECT ts_rewrite(‘a & b’::tsquery, ‘SELECT t,s FROM aliases’) | ‘b’ & ( ‘foo’ |
tsquery_phrase(query1 tsquery, query2 tsquery) | tsquery | 制造搜索后面跟 着query2的query1的查询(和<->操作符相同) | tsquery_phrase(to_tsquery(‘fat’), to_tsquery(‘cat’)) | fat’ <-> ‘cat’ |
tsquery_phrase(query1 tsquery, query2 tsquery, distance integer) | tsquery | 制造查询来搜索 在query1后面最 大距离distance上跟着query2 的情况 | tsquery_phrase(to_tsquery(‘fat’), to_tsquery(‘cat’), 10) | fat’ <10> ‘cat’ |
tsvector_to_array(tsvector) | text[] | 把tsvector转换为词位数组 | tsvector_to_array(‘fat:2,4 cat:3 rat:5A’::tsvector) | cat,fat,rat} |
tsvector_update_trigger() | trigger | 用于自动tsvector列更新的触发器函数 | CREATE TRIGGER … tsvector_update_trigger(tsvcol, ‘pg_catalog.swedish’, title, body) | |
tsvector_update_trigger_column() | trigger | 用于自动tsvector列更新的触发器函数 | CREATE TRIGGER … tsvector_update_trigger_column(tsvcol, configcol, title, body) | |
unnest(tsvector, OUT lexeme text, OUT positions smallint[], OUT weights text) | setof record | 把一个 tsvector 扩展成一组行 | unnest(‘fat:2,4 cat:3 rat:5A’::tsvector) | (cat,{3}, {D}) … |
注意: |
---|
所有接受一个可选的regconfig参数的文本搜索函数在该参数被忽略时,使用由default_text_search_config指定的配置。 |
下表中的函数被单独列出,因为它们通常不被用于日常的文本搜索操作。它们有助于开发和调试新的文本搜索配置。
表 8.43 文本搜索调试函数
函数 | 返回类型 | 描述 | 例子 | 结果 |
---|---|---|---|---|
ts_debug([ config regconfig, ] document text, OUT alias text, OUT description text, OUT token text, OUT dictionaries regdictionary[], OUT dictionary regdictionary, OUT lexemes text[]) | setof record | 测试一个配置 | ts_debug(‘english’, ‘The Brightest supernovaes’) | (asciiword,”Word, all ASCII”,The, {english_stem},english_stem, {}) . |
ts_lexize(dict regdictionary, token text) | text[] | 测试一个字典 | ts_lexize(‘english_stem’, ‘stars’) | sh_ste |
ts_parse(parser_name text, document text, OUT tokid integer, OUT token text) | setof record | 测试一个解析器 | ts_parse(‘default’, ‘foo - bar’) | (1,foo) … |
ts_parse(parser_oid oid, document text, OUT tokid integer, OUT token text) | setof record | 测试一个解析器 | ts_parse(3722, ‘foo - bar’) | (1,foo) … |
ts_token_type(parser_name text, OUT tokid integer, OUT alias text, OUT description text) | setof record | 获得解析器定义 的记号类型 | ts_token_type(‘default’) | (1,asciiword,”Word, all ASCII”) … |
ts_token_type(parser_oid oid, OUT tokid integer, OUT alias text, OUT description text) | setof record | 获得解析器定义 的记号类型 | ts_token_type(3722) | (1,asciiword,”Word, all ASCII”) … |
ts_stat(sqlquery text, [ weights text, ] OUT word text, OUT ndoc integer, OUT nentry integer) | setof record | 获得一 个tsvector列的 统计 | ts_stat(‘SELECT vector from apod’) | (foo,10,15) … |
8.14. XML 函数
本节中描述的函数以及类函数的表达式都在类型xml的值上操作。类型xml的详细信息请参见第7.13 节。用于在值和类型xml之间转换的类函数的表达式xmlparse和xmlserialize记录在这里,而不是在本节中。
产生 XML 内容
有一组函数和类函数的表达式可以用来从 SQL 数据产生 XML 内容。它们特别适合于将查询结果格式化成 XML 文档以便于在客户端应用中处理。
8.14.1.1. xmlcomment
xmlcomment(text)
函数xmlcomment创建了一个 XML 值,它包含一个使用指定文本作为内容的 XML 注释。该文本不包含“–”或者也不会以一个“-”结尾,这样结果的结构是一个合法的 XML 注释。如果参数为空,结果也为空。
例子:
SELECT xmlcomment('hello'); |
8.14.1.2. xmlconcat
xmlconcat(xml[, …])
函数xmlconcat将由单个 XML 值组成的列表串接成一个单独的值,这个值包含一个 XML 内容片断。空值会被忽略,只有当没有参数为非空时结果才为空。
例子:
SELECT 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" |
8.14.1.3. xmlelement
xmlelement(name name [, xmlattributes(value [AS attname] [, … ])] [,
content, …])
表达式xmlelement使用给定名称、属性和内容产生一个 XML 元素。
例子:
SELECT xmlelement(name foo); |
不是合法 XML 名字的元素名和属性名将被逃逸,逃逸的方法是将违反的字符用序列_xHHHH_替换,其中HHHH是被替换字符的 Unicode 代码点的十六进制表示。例如:
SELECT xmlelement(name "foo$bar", xmlattributes('xyz' as "a&b")); |
如果属性值是一个列引用,则不需要指定一个显式的属性名,在这种情况下列的名字将被默认用于属性的名字。在其他情况下,属性必须被给定一个显式名称。因此这个例子是合法的:
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), |
其他类型的内容将被格式化为合法的 XML 字符数据。这意味着字符 <, >, 和 & 将被转换为实体。二进制数据(数据类型bytea)将被表示成base64 或十六进制编码,具体取决于配置参数xmlbinary的设置。为了使瀚高数据库的映射与SQL:2006及以后的SQL:2006中指定的映射保持一致,个别数据类型的特殊行为将不断发展,正如第 D.3.1.3 节中讨论的那样。
8.14.1.4. xmlforest
xmlforest(content [AS name] [, …])
表达式xmlforest使用给定名称和内容产生一个元素的 XML 森林(序列)。
例子:
SELECT xmlforest('abc' AS foo, 123 AS bar); |
如我们在第二个例子中所见,如果内容值是一个列引用,元素名称可以被忽略,这种情况下默认使用列名。否则,必须指定一个名字。
如上文xmlelement所示,非法 XML 名字的元素名会被逃逸。相似地,内容数据也会被逃逸来产生合法的 XML 内容,除非它已经是一个xml类型。
注意如果 XML 森林由多于一个元素组成,那么它不是合法的 XML 文档,因此在xmlelement中包装xmlforest表达式会有用处。
8.14.1.5. xmlpi
xmlpi(name target [, content])
表达式xmlpi创建一个 XML 处理指令。如果存在内容,内容不能包含字符序列?>。
例子:
SELECT xmlpi(name php, 'echo "hello world";'); |
8.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>'), |
8.14.1.7. xmlagg
xmlagg(xml)
和这里描述的其他函数不同,函数xmlagg是一个聚集函数。它将聚集函数调用的输入值串接起来,非常像xmlconcat所做的事情,除了串接是跨行发生的而不是在单一行的多个表达式上发生。聚集表达式的更多信息请见第 9.20 节。
例子:
CREATE TABLE test (y int, x xml); |
为了决定串接的顺序,可以为聚集调用增加一个ORDER BY子句,如第 4.2.7 节中所述。例如:
SELECT xmlagg(x ORDER BY y DESC) FROM test; |
我们推荐在以前的版本中使用下列非标准方法,并且它们在特定情况下仍然有用:
SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab; |
8.14.2. XML 谓词
这一节描述的表达式检查xml值的属性。
8.14.2.1. IS DOCUMENT
xml IS DOCUMENT
如果参数 XML 值是一个正确的 XML 文档,则IS DOCUMENT返回真,如果不是则返回假(即它是一个内容片断),或者是参数为空时返回空。文档和内容片断之间的区别请见第 7.13 节。
8.14.2.2. IS NOT DOCUMENT
xml IS NOT DOCUMENT
如果参数中的XML值是一个正确的XML文档,那么表达式IS NOT DOCUMENT返回假,否则返回真(也就是说它是一个内容片段),如果参数为空则返回空。
8.14.2.3. XMLEXISTS
XMLEXISTS(text PASSING [BY { REF | VALUE }] xml [BY { REF | VALUE }])
函数xmlexists评价一个XPath 1.0表达式(第一个参数),以传递的XML值作为其上下文项。如果评价的结果产生一个空节点集,该函数返回false,如果产生任何其他值,则返回 true。如果任何参数为空,则函数返回null。作为上下文项传递的非空值必须是一个XML文档,而不是内容片段或任何非XML值。
例子:
SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY VALUE |
8.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; |
最后一个例子显示了这些检查也包括名字空间是否正确地匹配。
8.14.3. 处理 XML
要处理数据类型xml的值,瀚高数据库提供了函数xpath和xpath_exists,它们计算 XPath表达式以及XMLTABLE表函数。
8.14.3.1. xpath
xpath(xpath, xml [, nsarray])
函数xpath在 XML 值 xml上计算 XPath 1.0 表达式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>', |
要处理默认(匿名)命名空间,做这样的事情:
SELECT xpath('//mydefns:b/text()', '<a xmlns="http://example.com"><b>test</b></ |
8.14.3.2. xpath_exists
xpath_exists(xpath, xml [, nsarray])
函数xpath_exists是xpath函数的一种特殊形式。这个函数不是返回满足 XPath 1.0 表达式的单一 XML 值,它返回一个布尔值表示查询是否被满足(具体来说,它是否产生了空节点集以外的任何值)。这个函数等价于标准的XMLEXISTS谓词,不过它还提供了对一个名字空间映射参数的支持。
例子:
SELECT xpath_exists('/my:a/text()', '<my:a xmlns:my="http://example.com">test</ |
8.14.3.3. xmltable
xmltable( [XMLNAMESPACES(namespace uri AS namespace name[, …]), ]
row_expression PASSING [BY { REF | VALUE }] document_expression [BY
{ REF | VALUE }]
COLUMNS name { type [PATH column_expression]
[DEFAULT default_expression] [NOT NULL | NULL]
| FOR ORDINALITY }
[, …]
)
xmltable函数基于给定的XML值产生一个表、一个抽取行的XPath过滤器以及一个列定义集合。
可选的XMLNAMESPACES子句是一个逗号分隔的名字空间列表。它指定文档中使用的XML名字空间极其别名。当前不支持默认的名字空间说明。
所需的row_expression参数是一个XPath 1.0表达式,通过传递document_expression作为其上下文项,得到一组XML节点。这些节点就是xmltable转换为输出行的内容。如果document_expression为空,或者row_expression产生空节点集或节点集以外的任何值,则不会产生行。
强制需要的COLUMNS子句指定输出表中的列列表。如果COLUMNS子句被省略,每一项描述一个列。格式请见上面的语法综述。列名和类型是必需的,路径、默认值以及为空性子句是可选的。
被标记为FOR ORDINALITY的列将按照从row_expression的结果节点集中检索到的节点的顺序,从1开始,填充行号。最多只能有一个列被标记为FOR ORDINALITY。
注意: |
---|
XPath 1.0 并没有为节点集中的节点指定顺序,因此依赖特定结果顺序的代码将取决于实现。 |
列的column_expression是一个XPath 1.0表达式,它对每一行都要进行求值,并以row_expression结果中的当前节点作为其上下文项,以找到列的值。如果没有给出column_expression,那么列名被用作隐式路径。
如果一个列的XPath表达式返回一个非XML值(在XPath 1.0中仅限于string、boolean或 double),而该列的瀚高数据库类型不是xml,那么该列将被设置为将值的字符串表示法分配给瀚高数据库类型。(如果值是布尔值,如果输出列的类型类别是数字,那么它的字符串表示方式将被认为是1或0,否则true或 false。)
如果一个列的XPath表达式返回一个非空的XML节点集,并且该列的瀚高数据库类型是xml,那么如果该列是文档或内容形式的,那么该列将被精确地分配表达式结果。分配给xml输出列的非XML结果会产生内容,一个带有结果字符串值的单个文本节点。分配给任何其他类型的列的XML结果不能有一个以上的节点,否则会产生错误。如果正好有一个节点,则该列将被设置为将该节点的字符串值(如XPath 1.0 string函数定义的那样)分配给瀚高数据库类型。
一个XML元素的字符串值是字符串值的协整,按文档的顺序。该元素中包含的所有文本节点及其子节点。字符串 元素的值是一个没有下级文本节点的元素的值是一个 空字符串(不是NULL)。任何xsi:nil属性都会被忽略。请注意,两个非文本之间的text()节点只用空格,而两个非文本 元素,并且保留了text()上的前导白格。节点不被扁平化。XPath 1.0中的string函数可以参考XPath 1.0中的定义其他XML节点类型和非XML值的字符串值的规则。
这里介绍的转换规则并不完全是SQL标准中的转换规则。
如果路径表达式为给定行返回一个空节点集(通常情况下,当它不匹配时),该列将被设置为NULL,除非指定了default_expression;然后使用评价该表达式产生的值。
列可能会被标记为NOT NULL。如果一个NOT NULL列的column_expression不匹配任何东西并且没有DEFAULT或者default_expression也计算为空,则会报告一个错误。
default_expression,而不是在调用xmltable时立即被评价,而是在每次需要列的默认值时,都会被评价。如果表达式符合稳定或不可更改的条件,则可以跳过重复评价。这意味着,你可以在default_expression中使用像nextval这样的不稳定函数。
例子:
CREATE TABLE xmldata AS SELECT |
8.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> |
如果tableforest为真,结果是一个看起来像这样的 XML 内容片断:
<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> |
其中一个表映射的格式取决于上文解释的tableforest参数。
一个数据库内容映射的结果看起来像这样:
<dbname> |
其中的模式映射如上所述。
转换 SQL/XML 输出到 HTML 的 XSLT 样式表
|
8.15. JSON 函数和操作符
本节描述:
• 用于处理和创建JSON数据的函数和运算器
• SQL/JSON路径语言
8.15.1. 处理和创建JSON数据
下表展示了可以用于 JSON 数据类型(见第 7.14 节)的操作符。
表 8.44 json和jsonb 操作符
操作符 | 右操作数类型 | 返回类型 | 描述 | 例子 | 例子结果 |
---|---|---|---|---|---|
-> | int | json or jsonb | 获得 JSON 数组元素(索引从 0 开始,负整数从末尾开始计) | ‘[{“a”:”foo”}, {“b”:”bar”}, {“c”:”baz”}]’::json- >2 | {“c”:”baz”} |
-> | text | json or jsonb | 通过键获得 JSON 对象域 | ‘{“a”: {“b”:”foo”}}’::json- >’a’ | {“b”:”foo”} |
->> | int | text | 以text形式获得 JSON 数组元素 | ‘[1,2,3]’::json- >>2 | 3 |
->> | text | text | 以text形式获得 JSON 对象域 | ‘{“a”:1,”b”:2}’::json- >>’b’ | 2 |
#> | text[] | json or jsonb | 获取在指定路径的 JSON 对象 | ‘{“a”: {“b”: {“c”: “foo”}}}’::json#>’{a,b}’ | {“c”: “foo”} |
#>> | text[] | text | 以text形式获取在指定路径的 JSON 对象 | ‘{“a”: [1,2,3],”b”: [4,5,6]}’::json#>>’{a,2}’ | 3 |
注意: |
---|
对json和jsonb类型,这些操作符都有其并行变体。域/元素/路径抽取操作符返回与其左手输入(json或jsonb)相同的类型,不过那些被指定为返回text的除外,它们的返回值会被强制 为文本。如果该 JSON 输入没有匹配请求的正确结构(例如那样的元素不存在),这些域/元素/路径抽取操作符会返回 NULL 而不是失败。接受整数 JSON 数组下标的域/元素/路径抽取操作符都支持表示从数组末尾开始的负值下标形式。 |
如下表中所示,还存在一些只适合 jsonb的操作符。这些操作符中的很多可以用jsonb 操作符类索引。jsonb包含和存在语义的完整描述可参见第 7.14.3 节。第 7.14.4 节描述了如何用这些操作符来有效地索引jsonb数据。
表 8.45 额外的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 |
- | 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}’ |
@? | jsonpath | JSON路径是否返回指定的JSON值的任何项目? | ‘{“a”: [1,2,3,4,5]}’::jsonb @? ‘$.a[*] ? (@ > 2)’ |
@@ | jsonpath | 返回指定的JSON路径谓词检查结果。只考虑结果的第一项。如果结果不是布尔值,那么返回 null 。 | ‘{“a”: [1,2,3,4,5]}’::jsonb @@ ‘$.a[*] > 2’ |
注意: |
---|
||操作符将其每一个操作数的顶层的元素串接起来。它不会递归操作。例如,如果两个操作数都是具有公共域名称的对象,结果中的域值将 只是来自右手操作数的值。 |
注意: |
---|
@?和@@@操作符会抑制以下错误:缺乏对象字段或数组元素、意外的JSON项类型和数字错误。当搜索不同结构的JSON文档集合时,这种行为可能会有帮助。 |
下表展示了可用于创建 json 和 jsonb值的函数(没有用于 jsonb的与row_to_json和array_to_json等价的函数。不过,to_jsonb函数提供了这些函数的很大一部分相同的功能)。
表 8.46 JSON 创建函数
|**函数**|**描述**|**例子**|**例子结果**| | :-: | :-: | :-: | :-: | |to\_json(anyelement) to\_jsonb(anyelement)|把该值返回为json或者jsonb。数组和组合会被(递归)转换成数组和对象;对于不是数组和组合的值,如果有 从该类型到json的造型,造型函数将被用来执行该转换;否则将产生一个标量值。对于任何不是数字、布尔、空值的标 量类型,将使用文本表达,在这种风格下它是一个合法的json或者jsonb值|to\_json('Fred said "Hi."'::text)|"Fred said \"Hi.\""| |array\_to\_json(anyarray [, pretty\_bool])|把数组作为一个 JSON数组返回。一个瀚高数据库 多维数组会成为一个数组 的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\_build\_array(VARIADIC "any") jsonb\_build\_array(VARIADIC "any")|从一个可变参数列表构造一个可能包含异质类型的 JSON 数组|json\_build\_array(1,2,'3',4,5) |[1, 2, "3", 4, 5]| |json\_build\_object(VARIADIC "any") jsonb\_build\_object(VARIADIC "any")|从一个可变参数列表构造一个 JSON 对象。通过转换,该参数列表由交替出现的键和值构成。|json\_build\_object('foo',1,'bar',2) |{"foo": 1, "bar": 2}| |json\_object(text[]) jsonb\_object(text[])|从一个文本数组构造一个 JSON 对象。该数组必须可以是具有偶数个成员的一维数组(成员被当做交替出现的键/值对),或者是一个二维数组(每一个内部数组刚好有 2 个元素,可以被看做是键/值对)。|json\_object('{a, 1, b, "def", c, 3.5}') json\_object('{{a, 1},{b, "def"},{c, 3.5}}')|{"a": "1", "b": "def", "c": "3.5"}| |json\_object(keys text[], values text[]) jsonb\_object(keys text[], values text[])|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 对象,而不是原始字符串值。| 表 8.47展示了可用来处理json和jsonb值的函数。 表 8.47 JSON 处理 |**函数**|**返回值**|**描述**|**例子**|**例子结果**| | :-: | :-: | :-: | :-: | :-: | |json\_array\_length(json) jsonb\_array\_length(jsonb)|Int|返回最外层 JSON数组中的元素数量。|json\_array\_length('[1,2,3, {"f1":1,"f2": [5,6]},4]')|5| |json\_each(json) jsonb\_each(jsonb)|setof key text, value json setof key text, value jsonb|扩展最外层的JSON 对象成为一组键/值对。|select \* from json\_each('{"a":"foo", "b":"bar"}')|key | value -----+------- a | "foo" b | "bar| |json\_each\_text(json) jsonb\_each\_text(jsonb)| setof key text, value text|扩展最外层的JSON 对象成为一组键/值对。返回值将是text类型。|select \* from json\_each\_text('{"a":"foo", "b":"bar"}')|key | value -----+-------a | foo b | bar| |json\_extract\_path(from\_json json, VARIADIC path\_elems text[]) jsonb\_extract\_path(from\_json jsonb, VARIADIC path\_elems text[])|jsonb|返回由path\_elems指向的 JSON 值(等效于#>操作符)|json\_extract\_path('{"f2": {"f3":1},"f4": {"f5":99,"f6":"foo"}}','f4')|{"f5":99,"f6":"foo"}| |json\_extract\_path\_text(from\_json json, VARIADIC path\_elems text[]) jsonb\_extract\_path\_text(from\_json jsonb, VARIADIC path\_elems text[])||以text返回由path\_elems指向的 JSON 值(等效于#>>操作符)。|json\_extract\_path\_text('{"f2": {"f3":1},"f4": {"f5":99,"f6":"foo"}}','f4', 'f6')|| |json\_object\_keys(json) jsonb\_object\_keys(jsonb)|text|返回最外层 JSON对象中的键集合。|json\_object\_keys('{"f1":"abc","f2": {"f3":"a", "f4":"b"}}')|json\_object\_keys ------------------ f1 f2| |json\_populate\_record(base anyelement, from\_json json) jsonb\_populate\_record(base anyelement, from\_json jsonb)|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| c ---+----------- +------------- 1 | {2,"a b"} | (4,"a b c")| |json\_populate\_recordset(base anyelement, from\_json json) jsonb\_populate\_recordset(base anyelement, from\_json jsonb)|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\_array\_elements(json) jsonb\_array\_elements(jsonb) |setof json setof jsonb|把一个 JSON 数组扩展成一个JSON 值的集合。|select \* from json\_array\_elements('[1,true, [2,false]]')|alue ----------- 1 true [2,false]| |json\_array\_elements\_text(json) jsonb\_array\_elements\_text(jsonb)|setof json setof jsonb|把一个 JSON 数组扩展成一个text值集合。|select \* from json\_array\_elements\_text('["foo", "bar"]')|----------- foo bar| |son\_typeof(json) jsonb\_typeof(jsonb)|text|把最外层的 JSON值的类型作为一个文本字符串返回。可能的类型是:object、array、string、number、boolean以及null。|son\_typeof('-123.4') |number| |json\_to\_record(json) jsonb\_to\_record(jsonb|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)|c | d | r ---+--------- +---------+--- +--------------- 1 | [1,2,3] | {1,2,3} | | (123,"a b c")| |json\_to\_recordset(json) jsonb\_to\_recordset(jsonb)|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 || |json\_strip\_nulls(from\_json json) jsonb\_strip\_nulls(from\_json jsonb)|jsonb|返回from\_json,其中所有具有空值的 对象域都被省略。其他空值不动。|json\_strip\_nulls('[{"f1":1,"f2":null},2,null,3|[{"f1":1,"f2":null},| |jsonb\_set(target jsonb, path text[], new\_value jsonb[,create\_missing boolean])|jsonb|返回target,其中由 path指定的节用new\_value替换,如果 path指定的项不存在并且create\_missing为真(默认为true)则加上new\_value。正如面向路径的 操作符一样,出现在path中的 负整数表示从 JSON数组的末尾开始数。|jsonb\_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false) jsonb\_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]|{"f1": [2,3,4],"f2":null},2,null,3] [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]| |sonb\_insert(target jsonb, path text[], new\_value jsonb[, insert\_after boolean])|jsonb|返回被插入了new\_value的target。如果path指定的target节在一个 JSONB 数组中,new\_value将被插入到目标之前(insert\_after为false,默认情况)或者之后(insert\_after为真)。如果path指定的target节在一个 JSONB 对象内,则只有当target不存在时才插入new\_value。对于面向路径的操作符来说,出现在path中的负整数表示从 JSON数组的末尾开始计数。|jsonb\_insert('{"a": [0,1,2]}', '{a, 1}', '"new\_value"') jsonb\_insert('{"a": [0,1,2]}', '{a, 1}', '"new\_value"', true)|{"a": [0, "new\_value", 1, 2]} {"a": [0, 1, "new\_value", 2]}| |jsonb\_pretty(from\_json jsonb)|text|把from\_json返回成一段 缩进后的JSON 文本。|jsonb\_pretty('[{"f1":1,"f2":null},2,null,3]') |[ { "f1": 1, "f2": null }, 2, null, 3 ]| |jsonb\_path\_exists(target jsonb, path jsonpath [, vars jsonb [, silent bool]])|targe|检查JSON路径是否为指定的JSON值返回任何项目。|jsonb\_path\_exists('{"a": [1,2,3,4,5]}', '$.a[\*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}')|ture| |jsonb\_path\_match(target jsonb, path jsonpath [, vars jsonb silent bool]])|bool|返回指定的json路径谓词检查结果。只考虑结果的第一项。如果结果不是布尔值,则返回null。|jsonb\_path\_match('{"a": [1,2,3,4,5]}', 'exists($.a[\*] ? (@>= $min && @ <= $max))', '{"min":2,"max":4}')|ture| |jsonb\_path\_query(target jsonb, path jsonpath [, vars jsonb silent bool]])|jsonb|获取指定的json值的json路径返回的所有json项。|select \* from jsonb\_path\_query('{"a": [1,2,3,4,5]}', '$.a[\*] ? (@>= $min && @ <= 2 3 $max)', '{"min":2,"max":4}')|jsonb\_path\_query ------------------ 4| |jsonb\_path\_query\_array(target jsonb, path jsonpath [, vars jsonb silent bool]])|jsonb|获取指定json路径返回的所有json项,并将结果封装成数组。|jsonb\_path\_query\_array('{"a": [1,2,3,4,5]}', '$.a[\*] ? (@>= $min && @ <= $max)', '{"min":2,"max":4}')|[2, 3, 4]| |jsonb\_path\_query\_first(target jsonb, path jsonpath [, vars jsonb silent bool]])|jsonb|获取指定的json值的第一个json路径返回的json项。如果没有结果,则返回null。|jsonb\_path\_query\_first('{"a": [1,2,3,4,5]}', '$.a[\*] ? (@>= $min && @ <= $max)', '{"min":2,"max":4}')|2|