使用函数和操作符

用户定义和内置函数和运算符的说明。

在Greenplum数据库中使用函数
用户定义的函数
内建函数和操作符
窗口函数
高级聚集函数

在Greenplum数据库中使用函数

在Greenplum数据库中调用函数时，函数属性控制函数的执行。易变性属性 (IMMUTABLE, STABLE, VOLATILE) 和EXECUTE ON 属性控制函数执行的两个不同方面。一般来说，易变性指示函数执行时间， EXECUTE ON 指示执行位置。易变性属性是基于PostgreSQL的属性, EXECUTE ON 属性是Greenplum的属性。

例如，使用IMMUTABLE属性定义的函数可以在查询计划时执行，而使用VOLATILE属性定义的函数，必须对查询中每一行执行。使用EXECUTE ON MASTER属性的函数仅在主实例上执行，而使用EXECUTE ON ALL SEGMENTS属性的函数在所有主segment实例（而不是主服务器）上执行。

这些表总结了Greenplum数据库基于这些属性，对函数执行的假设。

Table 1. Greenplum数据库的函数易变性
函数属性	Greenplum支持	描述	注释
IMMUTABLE	支持	仅直接依赖信息参数列表。如果给定相同的参数值，则始终返回同样的结果。
STABLE	大部分情况下支持	在一个单一表扫描中，相同的参数值返回相同的结果，但是不同的SQL语句结果会改变。	结果依赖于数据库查找或者参数值。`current_timestamp`函数族是`STABLE`的，在一次执行中值不会改变。
VOLATILE	受限	在一个单一表扫描中函数值可能会改变。例如：`random()`, `timeofday()`，这是默认属性。	任何有副作用的函数都是volatile，即使它的结果是可预测的也一样。例如：`setval()`。

Table 2. Greenplum数据库的EXECUTE ON函数属性
函数属性	描述	注释
EXECUTE ON ANY	表示函数可以在master或任意segment实例上执行，并且返回相同的结果，而不管它在何处执行。这是默认属性。	Greenplum数据库决定函数的执行位置。
EXECUTE ON MASTER	表示必须在master实例上执行函数。	如果用户定义函数执行查询以访问表，请指定此属性。
EXECUTE ON ALL SEGMENTS	表示对于每个调用，函数必须在所有主segment实例上执行，而不是在master上执行。

您可以使用psql \df+ function命令显示函数的易变性和EXECUTE ON属性。

更多有关Greenplum数据库函数易变性分类的信息，请参考PostgreSQL文档函数易变性分类

更多关于EXECUTE ON属性的信息, 参看 创建函数.

在Greenplum数据库中，数据被划分为多个段-每个段都是一个不同的PostgreSQL数据库。为了防止不一致或意外的结果，如果函数包含SQL命令或者任意修改数据库的方式，请不要在segment级别执行被分类为volatile的函数。例如，不允许对Greenplum数据库中的分布式数据setval()等函数，因为它们可能导致段实例之间的数据不一致。

函数可以对segment上的复制表（DISTRIBUTED REPLICATED）执行只读查询，但修改数据的任何SQL命令都必须在master实例上执行。

为了确保数据的一致性，您可以在主服务器上安全地运行语句来调用使用VOLATILE和STABLE的函数。例如，下面的语句在master上运行（不带FROM子句的语句）：

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

如果语句有一个包含分布式表的FROM子句，而FROM子句中的函数返回一组行，则该语句可以在以下segment上运行：

SELECT * from foo();

Greenplum数据库不支持返回表引用（rangeFuncs）的函数或使用refCursor数据类型的函数。

函数易变性和计划缓冲

对于计划和立即执行的简单交互式查询，STABLE和IMMUTABLE的函数易变性类别之间的差异相对较小。无论是在计划期间执行一次函数，还是在查询执行启动期执行一次函数，都无关紧要。但是，当您保存计划并稍后重用它时，会有很大的不同。如果把一个函数误标为IMMUTABLE，Greenplum数据库可能在规划时会过早地把它折叠成一个常量，可能会在计划的后续执行期间重用过时的值。当使用 PREPARE 语句或使用PL/pgSQL之类的语言缓存计划时，可能会遇到这种危险。

用户定义的函数

Greenplum数据库支持用户定义的函数。更多信息请见PostgreSQL文档中的 Extending SQL

使用CREATE FUNCTION语句注册用户定义的函数，它们可以按在在Greenplum数据库中使用函数中所述的方式被使用。默认情况下，用户定义的函数被声明为VOLATILE，因此如果用户定义的函数是IMMUTABLE或者STABLE，必须在注册该函数时指定正确的易变性级别。

默认情况下，用户定义的函数被声明为EXECUTE ON ANY。只有当函数在master实例上执行时，才支持执行查询以访问表的函数，除非函数可以执行仅访问segment实例上复制表的SELECT命令。访问散列分布表或随机分布表的函数必须用EXECUTE ON MASTER属性定义。否则，在复杂的查询中使用函数时，函数可能返回不正确的结果。如果没有该属性，规划器优化可能会将函数调用推送到segment实例中执行。

在创建用户定义的函数时，避免使用致命错误或者破坏性的调用。Greenplum数据库可能会用突然的关闭或者重启来应对这些错误。

在Greenplum数据库中，用户定义的函数的共享库必须位于Greenplum数据库阵列的所有主机（Master、Segment以及镜像）上的同一库路径地址。

还可以创建并执行用Greenplum数据库过程语言（例如PL/pgSQL）编写的匿名代码块。匿名块作为短暂的匿名函数运行。有关创建和执行匿名块的信息，请见 DO命令。

内建函数和操作符

下面的表格列出了PostgreSQL支持的内建函数和操作符的分类。Greenplum数据库中支持PostgreSQL中所有的函数和操作符，除了STABLE以及VOLATILE函数之外，这两种服从在Greenplum数据库中使用函数中记录的限制在Greenplum数据库中使用函数。更多有关这些内建函数和操作符的信息请见PostgreSQL文档中的函数和操作符。

Greenplum数据库包括操作json数据类型值的JSON处理函数。有关JSON数据的信息，请见使用JSON数据使用JSON数据。

Table 3. 内建函数和操作符
操作符/函数分类	VOLATILE函数	STABLE函数	限制
逻辑操作符
比较操作符
数学函数和操作符	random setseed
字符串函数和操作符	所有内置转换操作符	convert pg_client_encoding
二进制串函数和操作符
位串函数和操作符
模式匹配
数据类型格式化函数		to_char to_timestamp
日期/时间函数和操作符	timeofday	age current_date current_time current_timestamp localtime localtimestamp now
枚举支持函数
几何函数和操作符
网络地址函数和操作符
序列操纵函数	nextval() setval()
条件表达式
数组函数和操作符		所有数组函数
聚集函数
子查询表达式
行及数组比较
集合返回函数	generate_series
系统信息函数		所有会话信息函数所有访问权限查询函数所有模式可见性查询函数所有系统目录信息函数所有注释信息函数所有事务ID和快照
系统管理函数	set_config pg_cancel_backend pg_terminate_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 所有数据库对象大小函数	Note: `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)

窗口函数

下列内建窗口函数是Greenplum对于PostgreSQL数据库的扩展。所有的窗口函数都是immutable。更多有关窗口函数的信息，请见窗口表达式.

Table 4. 窗口函数
函数	返回类型	完整语法	描述
`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。 default设置当偏移量超出窗口范围之外时要返回的值。如果没有指定default，默认值是空值。
`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提供对位于该位置之后一个给定物理偏移量的行的访问。如果没有指定offset，默认偏移量是1。default设置当偏移量超出窗口范围之外时要返回的值。如果没有指定default，默认值是空值。
`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 )`	为窗口分区中的每一行或者查询中的每一行分配一个唯一的编号。

高级聚集函数

下列内建高级聚集函数是Greenplum对PostgreSQL数据库的扩展。这些函数都是immutable。 Greenplum数据库不支持PostgreSQL有序集聚合函数。

Note: 用于分析的Greenplum MADlib扩展提供了额外的高级函数来执行对Greenplum数据库数据的统计分析和机器学习。请见 Greenplum数据库参考指南中的 Greenplum的MADlib分析扩展。

Table 5. 高级聚集函数
函数	返回类型	完整语法	描述
`MEDIAN (expr)`	`timestamp, timestamptz, interval, float`	`MEDIAN (expression)` 例子： SELECT department_id, MEDIAN(salary) FROM employees GROUP BY department_id;	可以用一个二维数组作为输入。把这些数组当作矩阵。
`sum(array[])`	`smallint[]int[], bigint[], float[]`	`sum(array[[1,2],[3,4]])` 例子： 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来解决重复项的pivot聚集。
`unnest (array[])`	`anyelement`集合	`unnest( array['one', 'row', 'per', 'item'])`	把一个一维数组转换成行。返回一个anyelement（一种多态伪类型，请见PostgreSQL中的伪类型）集合。pseudo-type