内置函数摘要
Greenplum数据库支持内置函数和运算符,包括可用于窗口表达式的分析函数和窗口函数。 有关使用内置Greenplum数据库函数的信息,请参阅Greenplum数据库管理员指南中的“使用函数和运算符”。
Parent topic: Greenplum数据库参考指南
Greenplum数据库函数类型
Greenplum数据库评估SQL表达式中使用的函数和运算符。 某些函数和运算符只允许在master服务器上执行,因为它们可能导致Greenplum数据库segment实例中的不一致。 该表描述了Greenplum数据库函数类型。
Table 1. Greenplum数据库中的函数
函数类型 | Greenplum是否支持 | 描述 | 注释 |
---|---|---|---|
IMMUTABLE | 是 | 仅依赖于其参数列表中的信息。给定相同的参数值,始终返回相同的结果。 | |
STABLE | 在大多数情况下是的 | 在单个表扫描中,对相同的参数值返回相同的结果,但结果将通过SQL语句进行更改。 | 结果取决于数据库查找或参数值。 current_timestamp 系列函数是STABLE ; 值在执行中不会改变。 |
VOLATILE | 受限制的 | 函数值可以在单个表扫描中更改。例如: random() , timeofday() 。 |
任何具有副作用的函数都是易变的,即使其结果是可预测的。 例如:setval() 。 |
在Greenplum数据库中,数据跨segment分割 - 每个segment是不同的PostgreSQL数据库。
为了防止出现不一致或意外的结果,如果它们包含SQL命令或以任何方式修改数据库,则不要在segment级别执行归类为VOLATILE
的函数。
例如,不允许在Greenplum数据库中对分布式数据执行诸如setval()
之类的函数,因为它们可能导致segment实例之间的数据不一致。
为确保数据一致性,可以在master服务器上评估和运行的语句中安全地使用VOLATILE
和STABLE
函数。
例如,以下语句在master上运行(没有FROM
子句的语句):
SELECT setval('myseq', 201);
SELECT foo();
如果语句具有包含分布式表的FROM
子句,并且FROM
子句中的函数返回一组行,则该语句可以在segment上运行:
SELECT * from foo();
Greenplum数据库不支持返回表引用(rangeFuncs
)的函数或使用refCursor
数据类型的函数。
内置函数和运算符
下表列出了PostgreSQL支持的内置函数和运算符的类别。
Greenplum数据库支持所有函数和运算符,如PostgreSQL,但STABLE
和VOLATILE
函数除外,它们受Greenplum数据库函数类型中的限制。
有关这些内置函数和运算符的更多信息,请参阅PostgreSQL文档的函数和运算符部分。
Table 2. 内置函数和运算符
操作符/函数类别 | VOLATILE函数 | STABLE函数 | 限制 | ||||
---|---|---|---|---|---|---|---|
逻辑操作符 | |||||||
比较操作符 | |||||||
数学函数和操作符 | randomsetseed | ||||||
字符串函数和操作符 | 所有内建转换函数 | convertpg_client_encoding | |||||
二进制字符串和函数操作符 | |||||||
位串函数和操作符 | |||||||
模式匹配 | |||||||
数据类型格式函数 | to_charto_timestamp | ||||||
日期/时间函数和操作符 | timeofday | agecurrent_date current_time current_timestamp localtime localtimestamp now | |||||
枚举支持函数 | |||||||
几何函数和操作符 | |||||||
网络地址函数和操作符 | |||||||
序列操作函数 | nextval()setval() | ||||||
条件表达式 | |||||||
数组函数和操作符 | 所有数组函数 | ||||||
聚合函数 | |||||||
子查询表达式 | |||||||
行和数组比较 | |||||||
返回集合函数 | generate_series | ||||||
系统信息函数 | 所有会话信息函数 所有访问特权查询函数 所有模式可见性查询函数 所有系统目录信息函数 所有注释信息函数 所有事务ID和快照 | ||||||
系统管理函数 | set_configpg_cancel_backend pg_reload_conf pg_rotate_logfile pg_start_backup pg_stop_backup pg_size_pretty pg_ls_dir pg_read_file pg_stat_file | current_setting所有数据库对象尺寸函数 | 注意: 函数pg_column_size 显示存储值所需的字节,可能使用TOAST压缩。 |
||||
XML函数和类似函数的表达式 | cursor_to_xml(cursor refcursor, count int, nulls boolean, tableforest boolean, targetns text) cursor_to_xmlschema(cursor refcursor, 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) query_to_xml(query text, nulls boolean, tableforest boolean, targetns text) query_to_xmlschema(query text, 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) table_to_xml(tbl regclass, nulls boolean, tableforest boolean, targetns text) table_to_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns text) table_to_xml_and_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns text) xmlagg(xml) xmlconcat(xml[, ...]) xmlelement(name name [, xmlattributes(value [AS attname] [, ... ])] [, content, ...]) xmlexists(text, xml) xmlforest(content [AS name] [, ...]) xml_is_well_formed(text) xml_is_well_formed_document(text) xml_is_well_formed_content(text) xmlparse ( { DOCUMENT \ | CONTENT } value) xpath(text, xml) xpath(text, xml, text[]) xpath_exists(text, xml) xpath_exists(text, xml, text[]) xmlpi(name target [, content]) xmlroot(xml, version text \ | no value [, standalone yes\ | no\ | no value]) xmlserialize ( { DOCUMENT \ | CONTENT } value AS type ) xml(text) text(xml) xmlcomment(xml) xmlconcat2(xml, xml) |
JSON函数和操作符
Greenplum数据库包含用于创建和操作JSON数据的内置函数和运算符。
Note: 对于json
数据类型值,即使JSON对象包含重复键,也会保留所有键/值对。
对于重复键,JSON处理函数将最后一个值视为可操作的值。
对于jsonb
数据类型,不保留重复的对象键。
如果输入包含重复键,则仅保留最后一个值。
参考
关于JSON数据。
JSON操作符
此表描述了可用于json
和jsonb
数据类型的运算符。
Table 3. json和jsonb
操作符
操作符 | 右操作数类型 | 描述 | 示例 | 示例结果 |
---|---|---|---|---|
-> |
int |
获取JSON数组元素(从零索引)。 | '[{ "a":"foo"},{ "b":"bar"},{ "c":"baz"}]'::json->2 |
{ "c":"baz"} |
-> |
text |
通过键获取JSON对象字段。 | '{ "a": { "b":"foo"}}'::json->'a' |
{ "b":"foo"} |
->> |
int |
获取JSON数组元素作为text 。 |
'[1,2,3]'::json->>2 |
3 |
->> |
text |
获取JSON对象字段作为text 。 |
'{ "a":1,"b":2}'::json->>'b' |
2 |
#> |
text[] |
在指定的路径获取JSON对象。 | '{ "a": { "b":{ "c": "foo"}}}'::json#>'{ a,b} ' |
{ "c": "foo"} |
#>> |
text[] |
以指定路径获取JSON对象作为text 。 |
'{ "a":[1,2,3],"b":[4,5,6]}'::json#>>'{ a,2}' |
3 |
Note: 对于json
和jsonb
数据类型,这些运算符都有并行变体。
字段,元素和路径提取运算符返回与其左侧输入(json
或jsonb
)相同的数据类型,但指定为返回text
的那些除外,它将值强制转换为text
。
如果JSON输入没有与请求匹配的正确结构,则字段,元素和路径提取操作符将返回NULL
,而不是失败; 例如,如果不存在这样的元素。
需要jsonb
数据类型作为左操作数的运算符如下表所述。
其中许多运算符可以由jsonb
运算符类索引。
有关jsonb
包含和存在语义的完整描述,请参阅jsonb容器与存在。
有关如何使用这些运算符有效地索引jsonb
数据的信息,请参阅jsonb索引
。
Table 4. jsonb
操作符
操作符 | 右操作数类型 | 描述 | 示例 | ||
---|---|---|---|---|---|
@> |
jsonb |
左JSON值是否包含正确的值? | '{ "a":1, "b":2}'::jsonb @> '{ "b":2}'::jsonb |
||
<@ |
jsonb |
左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
数据类型,不适用于json
数据类型。
它们遵循jsonb索引中描述的B树操作的排序规则
。
Table 5. jsonb
比较操作符
操作符 | 描述 |
---|---|
< |
小于 |
> |
大于 |
<= |
小于等于 |
>= |
大于等于 |
= |
等于 |
<> 或!= |
不等于 |
Note: !=
运算符在解析器阶段转换为<>
。
不可能实现执行不同操作的!=
和<>
运算符。
JSON创建函数
此表描述了创建json
数据类型值的函数。
(目前,jsonb
没有等效函数,但您可以将其中一个函数的结果转换为jsonb
。)
Table 6. JSON创建函数
函数 | 描述 | 示例 | 示例结果 |
---|---|---|---|
to_json(anyelement) |
以JSON对象的形式返回值。 数组和复合体以递归方式处理,并转换为数组和对象。 如果输入包含从类型到json 的强制转换,则使用强制转换函数执行转换; 否则,将生成JSON标量值。 对于除数字,布尔值或空值之外的任何标量类型,将使用正确引用和转义文本表示,以使其成为有效的JSON字符串。 |
to_json('Fred said "Hi."'::text) |
"Fred said \"Hi.\"" |
array_to_json(anyarray [, pretty_bool]) |
将数组作为JSON数组返回。 多维数组成为数组的JSON数组。 如果pretty_bool 为true,则会在dimension-1元素之间添加换行符。 |
array_to_json('{ { 1,5},{ 99,100}}'::int[]) |
[[1,5],[99,100]] |
row_to_json(record [, pretty_bool]) |
将行作为JSON对象返回。 如果pretty_bool 为true,则会在level-1元素之间添加换行符。 |
row_to_json(row(1,'foo')) |
{ "f1":1,"f2":"foo"} |
json_build_array(VARIADIC "any" ) |
从VARIADIC 参数列表构建可能异构类型的JSON数组。 |
json_build_array(1,2,'3',4,5) |
[1, 2, "3", 4, 5] |
json_build_object(VARIADIC "any") |
从VARIADIC 参数列表中构建JSON对象。 参数列表按顺序获取并转换为一组键/值对。 |
json_build_object('foo',1,'bar',2) |
{ "foo": 1, "bar": 2} |
json_object(text[]) |
从文本数组中构建JSON对象。 该数组必须是一维或二维数组。 一维数组必须具有偶数个元素。 元素被视为键/值对。 对于二维数组,每个内部数组必须具有恰好两个元素,这些元素被视为键/值对。 | 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[]) |
从文本数组中构建JSON对象。 这种形式的json_object 从两个独立的数组中成对获取键和值。 在所有其他方面,它与单参数形式相同。 |
json_object('{ a, b}', '{ 1,2}') |
{ "a": "1", "b": "2"} |
Note: array_to_json
和row_to_json
与to_json
具有相同的行为,
除了提供漂亮的打印选项。
为to_json
描述的行为同样适用于由其他JSON创建函数转换的每个单独的值。
Note: hstore扩展具有从hstore
到json
的强制转换,
因此通过JSON创建函数转换的hstore
值将表示为JSON对象,而不是原始字符串值。
JSON聚合函数
此表显示函数将记录聚合到JSON对象数组和值对聚合到JSON对象
Table 7. JSON聚合函数
函数 | 参数类型 | 返回类型 | 描述 |
---|---|---|---|
json_agg(record) |
record |
json |
将记录聚合为JSON对象数组。 |
json_object_agg(name, value) |
("any", "any") |
json |
将名称/值对聚合为JSON对象。 |
JSON处理函数
此表显示可用于处理json
和jsonb
值的函数。
许多这些处理函数和运算符将JSON字符串中的Unicode转义转换为适当的单个字符。
如果输入数据类型是jsonb
,则这不是问题,因为转换已经完成。
但是,对于json
数据类型输入,这可能会导致抛出错误。
请参阅
关于JSON数据。
Table 8. 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[]) |
json 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') |
foo |
||||
json_object_keys(json) jsonb_object_keys(jsonb) |
setof 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定义的记录类型匹配的行。 见注1注1。 |
select * from json_populate_record(null::myrowtype, '{ "a":1,"b":2}') |
``` a | b ---+--- 1 | 2 ``` | ||
json_populate_recordset(base anyelement, from_json json) jsonb_populate_recordset(base anyelement, from_json jsonb) |
setof anyelement |
将from_json 中最外层对象的数组扩展为一组行,这些行的列与base定义的记录类型匹配。 见注1。 |
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]]') |
value ----------- 1 true [2,false] |
||||
json_array_elements_text(json) jsonb_array_elements_text(jsonb) |
setof text |
将JSON数组扩展为一组文本值。 | select * from json_array_elements_text('["foo", "bar"]') |
value ----------- foo bar |
||||
json_typeof(json)``jsonb_typeof(jsonb) |
text |
以文本字符串形式返回最外层JSON值的类型。 可能的类型是object ,array ,string ,number ,boolean 和null 。 见注2 |
json_typeof('-123.4') |
number |
||||
json_to_record(json)``jsonb_to_record(jsonb) |
record |
从JSON对象构建任意记录。 见注1。 与返回记录的所有函数一样,调用者必须使用AS 子句显式定义记录的结构。 |
select * from json_to_record('{ "a":1,"b":[1,2,3],"c":"bar"}') as x(a int, b text, d text) |
``` a | b | d ---+---------+--- 1 | [1,2,3] | ``` |
json_to_recordset(json) jsonb_to_recordset(jsonb) |
setof record |
从JSON对象数组构建任意记录集请参见注1。 与返回记录的所有函数一样,调用者必须使用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 | ``` |
Note:
- 函数
json_populate_record()
,json_populate_recordset()
,json_to_record()
和json_to_recordset()
的示例使用常量。 但是,典型的用法是引用FROM
子句中的表,并使用其中一个json
或jsonb
列作为函数的参数。 然后可以在查询的其他部分中引用所提取的键值。 例如,可以在WHERE
子句和目标列表中引用该值。 以这种方式提取多个值可以提高性能,而不是使用每个键操作符单独提取它们。 JSON键与目标行类型中的相同列名匹配。 这些函数的JSON类型强制转换可能不会产生某些类型的期望值。 将不会在输出中省略未出现在目标行类型中的JSON字段,并且与任何JSON字段不匹配的目标列将为NULL
。 - 不应将
json_typeof
函数null返回值null
与SQL NULL混淆。 调用json_typeof('null'::json)
将返回null
, 调用json_typeof(NULL::json)
将返回SQLNULL
。
窗口函数
以下是Greenplum数据库内置窗口函数。 所有窗口函数都是不可变的。 有关窗口函数的更多信息,请参阅Greenplum数据库管理员指南中的“窗口表达式”。
Table 9. 窗口函数
函数 | 返回类型 | 完整语法 | 描述 | |
---|---|---|---|---|
cume_dist() |
double precision |
CUME_DIST() OVER ( [PARTITION BY expr ] ORDER BY expr ) |
计算一组值中的值的累积分布。 具有相等值的行始终评估为相同的累积分布值。 | |
dense_rank() |
bigint |
DENSE_RANK () OVER ( [PARTITION BY expr ] ORDER BY expr ) |
计算有序行组中行的等级而不跳过等级值。 具有相等值的行被赋予相同的等级值。 | |
first_value(expr) |
与输入expr类型相同 | FIRST_VALUE( expr ) OVER ( [PARTITION BY expr ] ORDER BY expr `[ROWS |
RANGEframe_expr ] )` |
返回有序值集中的第一个值。 |
lag(expr [,offset] [,default]) |
与输入expr类型相同 | LAG( expr [, offset ] [, default ]) OVER ( [PARTITION BY expr ] ORDER BY expr ) |
提供对同一个表的多个行的访问,而无需进行自连接。 给定从查询返回的一系列行和光标的位置,LAG 提供对该位置之前给定物理偏移处的行的访问。 默认offset 为1。 default设置当偏移量超出窗口范围时返回的值。 如果未指定default,则默认值为null。 |
|
last_value(expr ) |
与输入expr类型相同 | `LAST_VALUE(expr) OVER ( [PARTITION BY expr] ORDER BY expr [ROWS | RANGE frame_expr] )` | 返回有序值集中的最后一个值。 |
lead(expr [,offset] [,default]) |
与输入expr类型相同 | LEAD(expr [,offset] [,exprdefault]) OVER ( [PARTITION BY expr] ORDER BY expr ) |
提供对同一个表的多个行的访问,而无需进行自连接。 给定从查询返回的一系列行和光标的位置,lead 提供对该位置之后给定物理偏移处的行的访问。 如果未指定offset,则默认偏移量为1。 default设置当偏移量超出窗口范围时返回的值。 如果未指定default,则默认值为null。 |
|
ntile(expr) |
bigint |
NTILE(expr) OVER ( [PARTITION BY expr] ORDER BY expr ) |
将有序数据集划分为多个存储桶(由expr定义),并为每行分配存储桶编号。 | |
percent_rank() |
double precision |
PERCENT_RANK () OVER ( [PARTITION BY expr] ORDER BY expr ) |
计算假定行R 减1的等级,除以小于被评估的行数(在窗口分区内)的1。 |
|
rank() |
bigint |
RANK () OVER ( [PARTITION BY expr] ORDER BY expr ) |
计算有序值组中行的等级。 排名标准具有相同值的行具有相同的排名。 绑定行的数量被添加到等级编号以计算下一个等级值。 在这种情况下,排名可能不是连续的数字。 | |
row_number() |
bigint |
ROW_NUMBER () OVER ( [PARTITION BY expr] ORDER BY expr ) |
为应用它的每一行分配一个唯一的编号(窗口分区中的每一行或查询的每一行)。 |
高级聚合函数
以下内置高级分析函数是PostgreSQL数据库的Greenplum扩展。 分析函数是不可变的。
Note: Greenplum MADlib分析扩展提供了额外的高级功能,可以使用Greenplum数据库数据执行统计分析和机器学习。 请参阅Greenplum MADlib分析扩展。
Table 10. 高级聚合函数
函数 | 返回类型 | 完整语法 | 描述 |
---|---|---|---|
MEDIAN (expr) |
timestamp, timestamptz, interval, float |
MEDIAN (expression) Example: SELECT department_id, MEDIAN(salary) FROM employees GROUP BY department_id; |
可以将二维数组作为输入。 将这些数组视为矩阵。 |
PERCENTILE_CONT (expr) WITHIN GROUP (ORDER BY expr [DESC/ASC]) |
timestamp, timestamptz, interval, float |
PERCENTILE_CONT(percentage) WITHIN GROUP (ORDER BY expression) Example: SELECT department_id, PERCENTILE_CONT (0.5) WITHIN GROUP (ORDER BY salary DESC) "Median_cont"; FROM employees GROUP BY department_id; |
执行假设连续分布模型的逆分布函数。 它采用百分位值和排序规范,并返回与参数的numeric数据类型相同的数据类型。 该返回值是执行线性插值后的计算结果。 在此计算中忽略Null。 |
PERCENTILE_DISC (expr) WITHIN GROUP (ORDER BY expr [DESC/ASC]) |
timestamp, timestamptz, interval, float |
PERCENTILE_DISC(percentage) WITHIN GROUP (ORDER BY expression) Example: SELECT department_id, PERCENTILE_DISC (0.5) WITHIN GROUP (ORDER BY salary DESC) "Median_desc"; FROM employees GROUP BY department_id; |
执行假设离散分布模型的逆分布函数。 它需要百分位值和排序规范。 此返回值是集合中的元素。 在此计算中忽略Null。 |
sum(array[]) |
smallint[]int[], bigint[], float[] |
sum(array[[1,2],[3,4]]) Example: CREATE TABLE mymatrix (myvalue int[]); INSERT INTO mymatrix VALUES (array[[1,2],[3,4]]); INSERT INTO mymatrix VALUES (array[[0,1],[1,0]]); SELECT sum(myvalue) FROM mymatrix; sum --------------- { { 1,3},{ 4,4}} |
执行矩阵求和。 可以将被视为矩阵的二维数组作为输入。 |
pivot_sum (label[], label, expr) |
int[], bigint[], float[] |
pivot_sum( array['A1','A2'], attr, value) |
使用sum的数据透视聚合来解决重复的条目。 |
unnest (array[]) |
set of anyelement |
unnest( array['one', 'row', 'per', 'item']) |
将一维数组转换为行。 返回一组anyelement , 多态的PostgreSQL中的假型。 |
文本搜索函数和操作符
下表总结了为全文搜索提供的函数和运算符。 有关Greenplum数据库文本搜索工具的详细说明, 请参阅使用全文搜索。
Table 11. 文本搜索运算符
操作符 | 描述 | 示例 | 结果 | |||||||
---|---|---|---|---|---|---|---|---|---|---|
@@ |
tsvector 匹配tsquery ? |
to_tsvector('fat cats ate rats') @@ to_tsquery('cat & rat') |
t |
|||||||
@@@ |
@@ 的弃用同义词 |
to_tsvector('fat cats ate rats') @@@ to_tsquery('cat & rat') |
t |
|||||||
` | ` | 连接 tsvector s |
`'a:1 b:2'::tsvector | 'c:1 d:2 b:3'::tsvector` | 'a':1 'b':2,5 'c':3 'd':4 |
|||||
&& |
AND tsquery s 一起 |
`'fat | rat'::tsquery && 'cat'::tsquery` | `( 'fat' | 'rat' ) & 'cat'` | |||||
` | ` | OR tsquery s 一起 |
`'fat | rat'::tsquery | 'cat'::tsquery` | `( 'fat' | 'rat' ) | 'cat'` | ||
!! |
否定一个 tsquery |
!! 'cat'::tsquery |
!'cat' |
|||||||
@> |
tsquery 是否包含另一个? |
'cat'::tsquery @> 'cat & rat'::tsquery |
f |
|||||||
<@ |
tsquery 是否包含在? |
'cat'::tsquery <@ 'cat & rat'::tsquery |
t |
Note: tsquery
包含运算符仅考虑两个查询中列出的词位,忽略组合运算符。
除了表中显示的运算符之外,还为类型tsvector
和tsquery
定义了普通的B树比较运算符(=, \<, 等)。
这些对于文本搜索不是很有用,但允许在这些类型的列上构建唯一索引。
Table 12. 文本搜索函数
函数 | 返回类型 | 描述 | 示例 | 结果 | ||
---|---|---|---|---|---|---|
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) | 5 | |
plainto_tsquery([ config regconfig , ] querytext) |
tsquery | 产生忽略标点符号的tsquery | plainto_tsquery('english', 'The Fat Rats') | 'fat' \& 'rat' | ||
querytree(query tsquery) |
text | 获得tsquery的可索引部分 | querytree('foo \& ! bar'::tsquery) | 'foo' | ||
setweight(tsvector, "char") |
tsvector | 为tsvector的每个元素赋予权重 | setweight('fat:2,4 cat:3 rat:5B'::tsvector, 'A') | 'cat':3A 'fat':2A,4A '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_tsquery('english', 'The \& Fat \& Rats') | 'fat' \& 'rat' | ||
to_tsvector([ config regconfig , ] documenttext) |
tsvector | 将文档文本缩减为tsvector | to_tsvector('english', 'The Fat Rats') | 'fat':2 'rat':3 | ||
ts_headline([ config regconfig, ] documenttext, query tsquery [, options text ]) |
text | 显示查询匹配 | ts_headline('x y z', 'z'::tsquery) | x y \z\</b> | ||
ts_rank([ weights float4[], ] vector tsvector,query tsquery [, normalization integer ]) |
float4 | 用于查询的排名文档 | ts_rank(textsearch, query) | 0.818 | ||
ts_rank_cd([ weights float4[], ] vectortsvector, query tsquery [, normalizationinteger ]) |
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替换目标 | ts_rewrite('a \& b'::tsquery, 'a'::tsquery, 'foo\ | bar'::tsquery) | 'b' \& ( 'foo' \ | 'bar' ) |
ts_rewrite(query tsquery, select text) |
tsquery | 使用targets和substitutes从SELECT命令中替换 | SELECT ts_rewrite('a \& b'::tsquery, 'SELECT t,s FROM aliases') | 'b' \& ( 'foo' \ | 'bar' ) | |
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) |
Note: 接受可选的regconfig
参数的所有文本搜索函数将在省略该参数时使用default_text_search_config指定的配置。
下表中的功能单独列出,因为它们通常不用于日常文本搜索操作。 它们有助于开发和调试新的文本搜索配置。
Table 13. 文本搜索调试函数
函数 | 返回类型 | 描述 | 示例 | 结果 |
---|---|---|---|---|
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') |
{ star} |
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 |
获取解析器定义的token类型 | 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 |
获取解析器定义的token类型 | ts_token_type(3722) |
(1,asciiword,"Word, all ASCII") ... |
ts_stat(sqlquery text, [ weights text, ] OUT word text, OUT ndocinteger, OUT nentry integer) |
setof record |
获取tsvectorcolumn的统计信息 | ts_stat('SELECT vector from apod') |
(foo,10,15) ... |
范围函数和运算符
有关范围类型的概述,请参阅范围类型。
下表显示了可用于范围类型的运算符。
Table 14. 范围运算符
运算符 | 描述 | 示例 | 结果 | ||
---|---|---|---|---|---|
= |
相等 | int4range(1,5) = '[1,4]'::int4range |
t |
||
<> |
不相等 | numrange(1.1,2.2) <> numrange(1.1,2.3) |
t |
||
< |
小于 | int4range(1,10) < int4range(2,3) |
t |
||
> |
大于 | int4range(1,10) > int4range(1,5) |
t |
||
<= |
小于等于 | numrange(1.1,2.2) <= numrange(1.1,2.2) |
t |
||
>= |
大于等于 | numrange(1.1,2.2) >= numrange(1.1,2.0) |
t |
||
@> |
包含范围 | int4range(2,4) @> int4range(2,3) |
t |
||
@> |
包含元素 | '[2011-01-01,2011-03-01)'::tsrange @> '2011-01-10'::timestamp |
t |
||
<@ |
范围包含在 | int4range(2,4) <@ int4range(1,7) |
t |
||
<@ |
元素包含在 | 42 <@ int4range(1,7) |
f |
||
&& |
重叠(有共同点) | int8range(3,7) && int8range(4,12) |
t |
||
<< |
严格小于 | int8range(1,10) << int8range(100,110) |
t |
||
>> |
严格大于 | int8range(50,60) >> int8range(20,30) |
t |
||
&< |
没有超越右边 | int8range(1,20) &< int8range(18,20) |
t |
||
&> |
没有超越左边 | int8range(7,20) &> int8range(5,10) |
t |
||
`- | -` | 毗邻 | `numrange(1.1,2.2) - | - numrange(2.2,3.3)` | t |
+ |
并集 | numrange(5,15) + numrange(10,20) |
[5,20) |
||
* |
交集 | int8range(5,15) * int8range(10,20) |
[10,15) |
||
- |
差集 | int8range(5,15) - int8range(10,20) |
[5,10) |
简单比较运算符<
,>
,<=
和>=
首先比较下限,只有在相等的情况下,才比较上限。
这些比较通常对范围不是很有用,但提供的是允许在范围上构建B树索引。
当涉及空范围时,左/右/邻接运算符总是返回false; 也就是说,空范围不被认为是在任何其他范围之前或之后。
如果结果范围需要包含两个不相交的子范围,则并集和差异运算符将失败,因为无法表示这样的范围。
下表显示了可用于范围类型的函数。
Table 15. 范围函数
函数 | 返回类型 | 描述 | 示例 | 结果 |
---|---|---|---|---|
lower(anyrange) |
范围的元素类型 | 范围的下限 | lower(numrange(1.1,2.2)) |
1.1 |
upper(anyrange) |
范围的元素类型 | 范围的上限 | upper(numrange(1.1,2.2)) |
2.2 |
isempty(anyrange) |
boolean |
范围是否空? | isempty(numrange(1.1,2.2)) |
false |
lower_inc(anyrange) |
boolean |
是否包含下限? | lower_inc(numrange(1.1,2.2)) |
true |
upper_inc(anyrange) |
boolean |
是否包含上限? | upper_inc(numrange(1.1,2.2)) |
false |
lower_inf(anyrange) |
boolean |
下限是否无穷小? | lower_inf('(,)'::daterange) |
true |
upper_inf(anyrange) |
boolean |
上限是否无穷大? | upper_inf('(,)'::daterange) |
true |
range_merge(anyrange, anyrange) |
anyrange |
包括两个给定范围的最小范围 | range_merge('[1,2)'::int4range, '[3,4)'::int4range) |
[1,4) |
如果范围为空或请求的边界为无限,则lower
和upper
函数返回null。
对于空范围,lower_inc
,upper_inc
,lower_inf
和upper_inf
函数都返回false。