内建函数摘要

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 子句的语句):

  1. SELECT setval('myseq', 201);
  2. SELECT foo();

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

  1. 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

 
日期/时间函数和操作符timeofdayage

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->>23
->>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”}’)
  1.  key | value
  2. ——-+———-
  3.  a   | “foo”
  4.  b   | “bar”
json_each_text(json)setof key text, value text将最外层的JSON对象扩展为一组键/值对。返回的值是类型 text。select from json_each_text(‘{“a”:”foo”, “b”:”bar”}’)
  1.  key | value
  2. ——-+———-
  3.  a   | foo
  4.  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”}}’)
  1.  json_object_keys
  2. —————————
  3.  f1
  4.  f2
json_populate_record(base anyelement, from_json json)anyelement扩展对象from_json 其列与由base定义的记录类型匹配。见注。select from json_populate_record(null::myrowtype, ‘{“a”:1,”b”:2}’)
  1.  a | b
  2. —-+—-
  3.  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}]’)
  1.  a | b
  2. —-+—-
  3.  1 | 2
  4.  3 | 4
json_array_elements(json)setof json将JSON数组扩展为一组JSON值。select * from json_array_elements(‘[1,true, [2,false]]’)
  1.    value
  2. —————-
  3.  1
  4.  true
  5.  [2,false]

注意： 许多这些函数和操作符将Unicode字符串中的Unicode转义转换为常规字符。这些函数为不能在数据库编码中表示的字符引发错误。

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

窗口函数

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

表 6. 窗口函数
函数返回类型完整语法描述
cume_dist()double precisionCUME_DIST() OVER ( [PARTITION BY expr ] ORDER BY expr )计算一组值中的值的累积分布。具有相等值的行总是计算相同的累积分布值。
dense_rank()bigintDENSE_RANK () OVER ( [PARTITION BY expr ] ORDER BY expr )计算有序的一组行中的行的排名，而不跳过排名。具有相等值的行具有相同的等级值。
first_value(expr)same as input expr typeFIRST_VALUE( expr ) OVER ( [PARTITION BY expr ] ORDER BY expr [ROWS|RANGE frame_expr ] )返回有序值集中的第一个值。
lag(expr [,offset] [,default])same as input expr typeLAG( expr [, offset ] [, default ]) OVER ( [PARTITION BY expr ] ORDER BY expr )提供访问同一个表的多行，而不进行自我连接。 给定从查询返回的一系列行和光标的位置， LAG在该位置之前提供对给定物理偏移量的行的访问。默认 offset 是1. 默认 设置如果偏移量超出窗口范围则返回的值。 如果默认 没被指定，默认值为null。
last_value(expr)same as input expr typeLAST_VALUE(expr) OVER ( [PARTITION BY expr] ORDER BY expr [ROWS|RANGE frame_expr] )返回有序值集中的最后一个值。
lead(expr [,offset] [,default])same as input expr typeLEAD(expr [,offset] [,exprdefault]) OVER ( [PARTITION BY expr] ORDER BY expr )提供访问同一个表的多行，而不进行自我连接。给定从查询返回的一系列行和光标的位置， lead在该位置之后提供对给定物理偏移量的行的访问。 如果未指定偏移量 ， 则默认偏移量为 1。默认 设置如果偏移超出窗口范围则返回的值。 If 默认 如果没被指定，默认值为 null.
ntile(expr)bigintNTILE(expr) OVER ( [PARTITION BY expr] ORDER BY expr )将有序数据集划分为多个桶 (由 expr定义) 并为每一行分配一个桶号。
percent_rank()double precisionPERCENT_RANK () OVER ( [PARTITION BY expr] ORDER BY expr )计算假设行的排名R 减1， 除了被评估的行数（在窗口分区内）除以1。
rank()bigintRANK () OVER ( [PARTITION BY expr] ORDER BY expr )计算有序的一组值中的一行的排名。排名标准相等值的行获得相同的排名。绑定行的数量被添加到等级号以计算下一个等级值。在这种情况下，排名可能不是连续的数字。
row_number()bigintROW_NUMBER () OVER ( [PARTITION BY expr] ORDER BY expr )为其应用的每一行分配唯一的编号（窗口分区中的每一行或查询的每一行）。

高级聚集函数

以下内置的高级分析功能是PostgreSQL数据库的Greenplum扩展。分析功能是 不可变的。

注意 注意：用于分析的Greenplum MADlib扩展提供了其他高级功能，可以使用Greenplum数据库数据执行统计分析和机器学习。请参阅 用于分析的Greenplum MADlib扩展。

表 7. 高级聚集函数
函数返回类型完整语法描述
MEDIAN (expr)timestamp, timestamptz, interval, floatMEDIAN (expression)

Example:

  1. SELECT department_id, MEDIAN(salary) 
  2. FROM employees 
  3. GROUP BY department_id;
可以采用二维数组作为输入。将这样的数组视为矩阵。
PERCENTILE_CONT (expr) WITHIN GROUP (ORDER BY expr [DESC/ASC])timestamp, timestamptz, interval, floatPERCENTILE_CONT(percentage) WITHIN GROUP (ORDER BY expression)

Example:

  1. SELECT department_id,
  2. PERCENTILE_CONT (0.5) WITHIN GROUP (ORDER BY salary DESC)
  3. “Median_cont”; 
  4. FROM employees GROUP BY department_id;
执行假定连续分布模型的逆分布函数。它需要百分位数值和排序规范，并返回与参数的数字数据类型相同的数据类型。该返回值是执行线性插值后的计算结果。在此计算中将忽略空值。
PERCENTILE_DISC (expr) WITHIN GROUP (ORDER BY expr [DESC/ASC])timestamp, timestamptz, interval, floatPERCENTILE_DISC(percentage) WITHIN GROUP (ORDER BY expression)

Example:

  1. SELECT department_id, 
  2. PERCENTILE_DISC (0.5) WITHIN GROUP (ORDER BY salary DESC)
  3. “Median_desc”; 
  4. FROM employees GROUP BY department_id;
执行一个假设离散分布模型的逆分布函数。它需要一个百分位数值和一个排序规格。这个返回值是集合中的一个元素。在此计算中将忽略空值。
sum(array[])smallint[]int[], bigint[], float[]sum(array[[1,2],[3,4]])

Example:

  1. CREATE TABLE mymatrix (myvalue int[]);
  2. INSERT INTO mymatrix VALUES (array[[1,2],[3,4]]);
  3. INSERT INTO mymatrix VALUES (array[[0,1],[1,0]]);
  4. SELECT sum(myvalue) FROM mymatrix;
  5.  sum 
  6. ———————-
  7.  {{1,3},{4,4}}
执行矩阵求和。可以将二维数组作为输入，作为矩阵处理。
pivot_sum (label[], label, expr)int[], bigint[], float[]pivot_sum( array[‘A1’,’A2’], attr, value)使用sum来解析重复条目的枢轴聚合。
unnest (array[])set of anyelementunnest( array[‘one’, ‘row’, ‘per’, ‘item’])将一维数组转换成行。返回一组 anyelement（一种多态的PostgreSQL中的伪类型。