内建函数摘要

内建函数摘要

Greenplum数据库支持内置函数和操作符，包括可用于窗口表达式的分析函数和窗口函数。有关使用内置Greenplum数据库功能的信息， 参阅 Greenplum数据库管理员指南中的“使用功能和操作符”。
上级主题： Greenplum数据库参考指南

Greenplum数据库函数类型

Greenplum数据库评估SQL表达式中使用的函数和操作符。一些功能和操作符只允许在主机上执行，因为它们可能会导致Greenplum数据库段实例中的不一致。本表介绍了Greenplum数据库函数类型。

表 1. Greenplum数据库中的函数
函数类型 Greenplum 是否支持 描述 注释
IMMUTABLE 是 仅依赖于其参数列表中的信息。给定相同的参数值，始终返回相同的结果。  
STABLE 在大多数情况下是的 在单个表扫描中，对相同的参数值返回相同的结果，但结果将通过SQL语句进行更改。 结果取决于数据库查找或参数值。 current_timestamp 系列函数是 STABLE; 值在执行中不会改变。
VOLATILE 受限制的 函数值可以在单个表扫描中更改。 例如: random(), currval(), timeofday(). 任何具有副作用的函数的都不稳定的，即使其结果是可预测的。例如: setval().

在Greenplum数据库中，数据通过字段被区分——没一个字段是一个独立的Postgre数据库。为了防止不一致或意外的结果，如果它们包含SQL命令或以任何方式修改数据库， 则不要在该子段级别执行分类为VOLATILE 的函数。例如， 不允许在Greenplum数据库中的分布式数据上执行setval() 等函数，因为它们可能导致段实例之间的数据不一致。

为了确保数据的一致性，用户可以在主服务器上计算和运行的语句中安全地使用VOLATILE和 STABLE 函数。 例如，以下语句在master上运行(没有 FROM 子句的语句):

SELECT setval('myseq', 201);
SELECT foo();

如果语句具有包含分布式表的 FROM 子句，并且 FROM 子句中的函数返回一组行，则语句可以在字段上运行：

SELECT * from foo();

GGreenplum数据库不支持返回表引用函数 (rangeFuncs) 或使用 refCursor 数据类型的函数。

内建函数和操作符

下表列出了PostgreSQL支持的内置函数和操作符的类别。除了 STABLE 和VOLATILE 函数外, postgreSQL中的所有函数和操作符在Greenplum数据库中都被支持，但是他们 受到Greenplum数据库函数类型中所述的限制。 有关这些内置函数和操作符的更多信息，请参阅PostgreSQL文档的 Functions and Operators部分。

表 2. 内建函数和操作符
操作符/函数类别 VOLATILE 函数 STABLE 函数 限制
逻辑操作符      
比较操作符      
数学函数和操作符 random

setseed

    
字符串函数和操作符 所有内建转换函数 convert

pg_client_encoding

  
二进制字符串函数和操作符      
位串函数和操作符      
模式匹配      
数据类型格式化功能   to_char

to_timestamp

  
日期/时间函数和操作符 timeofday age

current_date

current_time

current_timestamp

localtime

localtimestamp

now

  
枚举支持功能      
几何函数和操作符      
网络地址功能和操作符      
序列操作功能 currval

lastval

nextval

setval

    
条件表达式      
数组函数和操作符   所有数组函数  
聚合函数      
子查询表达式      
行和数组比较      
设置返回功能 generate_series    
系统信息功能   所有会话信息函数

所有访问特权查询函数

所有方案可见性查询函数

所有系统目录信息函数

所有注释信息函数

所有事务ID和快照

 
系统管理功能 set_config

pg_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函数和操作符

用于创建和操作JSON数据的内置函数和操作符。
注解： 对于json 的值， 对象包含重复的键/值对，也保留所有键/值对。处理功能将最后一个值视为可操作的。

JSON操作符

该表描述了可用于的操作符 json的数据类型。

表 3. json 操作符
操作符 右操作数类型 描述 示例 示例结果
-> int 获取JSON数组元素（从零索引）。 '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 {"c":"baz"}
-> text 通过键获取JSON对象字段。 '{"a": {"b":"foo"}}'::json->'a' {"b":"foo"}
->> int 获取JSON数组元素作为文本。 '[1,2,3]'::json->>2 3
->> text 获取JSON对象字段作为文本。 '{"a":1,"b":2}'::json->>'b' 2
#> text[] 在指定的路径获取JSON对象。 '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' {"c": "foo"}
#>> text[] 以指定路径获取JSON对象作为文本。 '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' 3

JSON创建函数

此表描述了创建的函数的 json值。

表 4. JSON创建函数
函数 描述 示例 示例结果
to_json(anyelement) 将值作为有效的JSON对象返回。数组和复合类型被递归处理并转换为数组和对象。 如果输入包含类型为json的造型, 则使用造型 函数执行 转换; 否则，将生成JSON标量值。对于除数字，布尔值或空值之外的任何标量类型，将使用文本表示，正确引用和转义，以使其为有效的JSON字符串。 to_json('Fred said "Hi."'::text) "Fred said \"Hi.\""
array_to_json(anyarray [, pretty_bool]) 将数组作为JSON数组返回。Greenplum数据库的多维数组成为数组的JSON数组。

如果 pretty_bool 是true，则在第一个元素之间添加换行符。

 array_to_json('{{1,5},{99,100}}'::int[]) [[1,5],[99,100]]
row_to_json(record [, pretty_bool]) 将该行作为JSON对象返回。

如果 elements if pretty_bool 是true则在第1级元素之间添加换行符。

 row_to_json(row(1,'foo')) {"f1":1,"f2":"foo"}
注解：除了提供精细打印选项外， array_to_json和row_to_json具有与 to_json 相同的行为。 对于 to_json描述的行为也适用于由其他JSON创建函数转换的单个值。

JSON 处理函数

此表描述处理json 值的函数。

表 5. JSON处理函数
函数 返回类型 描述 示例 示例结果
json_each(json) 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) 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[]) json 返回指定为的JSON值 path_elems。 相当于 #> 操作符。 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[])

 text 返回指定为的JSON值path_elems 作为文本。 相当于 #>> 操作符。 json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6') foo
json_object_keys(json) 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) anyelement 扩展对象from_json 其列与由base定义的记录类型匹配。见注。 select * from json_populate_record(null::myrowtype, '{"a":1,"b":2}') 
 a | b
---+---
 1 | 2
json_populate_recordset(base anyelement, from_json json) setof anyelement 扩展最外层的对象数组from_json 到一组行，其列与定义的记录类型 base相匹配 。见 注。 select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]') 
 a | b
---+---
 1 | 2
 3 | 4
json_array_elements(json) setof json 将JSON数组扩展为一组JSON值。 select * from json_array_elements('[1,true, [2,false]]') 
   value
-----------
 1
 true
 [2,false]
注意： 许多这些函数和操作符将Unicode字符串中的Unicode转义转换为常规字符。这些函数为不能在数据库编码中表示的字符引发错误。

对于json_populate_record和 json_populate_recordset，从JSON类型强制是尽力而为，可能不会导致某些类型的期望值。 JSON键与目标行类型中的相同列名匹配。输出中不会出现未显示在目标行类型中的JSON字段， 并且不匹配任何JSON字段的目标列返回 NULL。

窗口函数

以下内置窗口函数是PostgreSQL数据库的Greenplum扩展。所有窗口函数都是 不可变的。有关窗口函数的更多信息，请参阅Greenplum数据库管理员指南。

表 6. 窗口函数
函数 返回类型 完整语法 描述
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) same as input expr type FIRST_VALUE( expr ) OVER ( [PARTITION BY expr ] ORDER BY expr [ROWS|RANGE frame_expr ] ) 返回有序值集中的第一个值。
lag(expr [,offset] [,default]) same as input expr type LAG( expr [, offset ] [, default ]) OVER ( [PARTITION BY expr ] ORDER BY expr ) 提供访问同一个表的多行，而不进行自我连接。 给定从查询返回的一系列行和光标的位置， LAG在该位置之前提供对给定物理偏移量的行的访问。默认 offset 是1. 默认 设置如果偏移量超出窗口范围则返回的值。 如果默认 没被指定，默认值为null。
last_value(expr) same as input expr type LAST_VALUE(expr) OVER ( [PARTITION BY expr] ORDER BY expr [ROWS|RANGE frame_expr] ) 返回有序值集中的最后一个值。
lead(expr [,offset] [,default]) same as input expr type LEAD(expr [,offset] [,exprdefault]) OVER ( [PARTITION BY expr] ORDER BY expr ) 提供访问同一个表的多行，而不进行自我连接。给定从查询返回的一系列行和光标的位置， lead在该位置之后提供对给定物理偏移量的行的访问。 如果未指定偏移量 ， 则默认偏移量为 1。默认 设置如果偏移超出窗口范围则返回的值。 If 默认 如果没被指定，默认值为 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扩展。分析功能是 不可变的。

注意 注意：用于分析的Greenplum MADlib扩展提供了其他高级功能，可以使用Greenplum数据库数据执行统计分析和机器学习。请参阅 用于分析的Greenplum MADlib扩展。
表 7. 高级聚集函数
函数 返回类型 完整语法 描述
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;
 执行假定连续分布模型的逆分布函数。它需要百分位数值和排序规范，并返回与参数的数字数据类型相同的数据类型。该返回值是执行线性插值后的计算结果。在此计算中将忽略空值。
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;
 执行一个假设离散分布模型的逆分布函数。它需要一个百分位数值和一个排序规格。这个返回值是集合中的一个元素。在此计算中将忽略空值。
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中的伪类型。