使用函数和操作符

使用函数和操作符¶

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

函数使用整体说明¶

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

例如，使用IMMUTABLE属性定义的函数可以在查询计划时执行，而使用VOLATILE属性定义的函数，必须对查询中每一行执行。

使用EXECUTE ON MASTER属性的函数仅在主实例上执行，而使用EXECUTE ON ALL SEGMENTS属性的函数在所有主executor实例（而不是主服务器）上执行。

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

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

表 1. SeaboxMPP数据库的函数易变性

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

表 2. SeaboxMPP数据库的EXECUTE ON函数属性

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

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

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

在SeaboxMPP数据库中，数据被划分为多个段-每个段都是一个不同的SeaboxSQL数据库。为了防止不一致或意外的结果，如果函数包含SQL命令或者任意修改数据库的方式，请不要在executor级别执行被分类为volatile的函数。

例如，不允许对SeaboxMPP数据库中的分布式数据setval()等函数，因为它们可能导致段实例之间的数据不一致。

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

注意: 隐藏的系统列（rowid，ctid，cmin，cmax，xmin，xmax和sc_dbindex_id）无法在复制表的用户查询中引用，因为它们没有单一的，明确的值。SeaboxMPP数据库为查询返回一个column does not exist错误。

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

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

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

SELECT * from foo();

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

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

用户自定义函数¶

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

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

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

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

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

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

内建函数和操作符整体说明¶

以下是出了SeaboxSQL支持的内建函数和操作符的分类。SeaboxMPP数据库中支持SeaboxSQL中所有的函数和操作符，除了STABLE以及VOLATILE函数之外，这两种服从在SeaboxMPP数据库中使用函数中记录的限制在SeaboxMPP数据库中使用函数。

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

逻辑操作符
VOLATILE函数: 无
STABLE函数: 无
限制: 无
比较操作符
VOLATILE函数: 无
STABLE函数: 无
限制: 无
数学函数和操作符
VOLATILE函数: random(),setseed()
STABLE函数: 无
限制: 无
字符串函数和操作符
VOLATILE函数: 所有内置转换操作符
STABLE函数: convert(),pg_client_encoding()
限制: 无
二进制串函数和操作符
VOLATILE函数: 无
STABLE函数: 无
限制: 无
位串函数和操作符
VOLATILE函数: 无
STABLE函数: 无
限制: 无
模式匹配
VOLATILE函数: 无
STABLE函数: 无
限制: 无
数据类型格式化函数
VOLATILE函数: 无
STABLE函数: to_char()、to_timestamp()
限制: 无
日期/时间函数和操作符
VOLATILE函数: timeofday()
STABLE函数: age()、current_date()、current_time()、current_timestamp()、localtime()、localtimestamp()、now()
限制: 无
枚举支持函数
VOLATILE函数: 无
STABLE函数: 无
限制: 无
几何函数和操作符
VOLATILE函数: 无
STABLE函数: 无
限制: 无
网络地址函数和操作符
VOLATILE函数: 无
STABLE函数: 无
限制: 无
序列操纵函数
VOLATILE函数: nextval()、setval()
STABLE函数: 无
限制: 无
条件表达式
VOLATILE函数: 无
STABLE函数: 无
限制: 无
数组函数和操作符
VOLATILE函数: 无
STABLE函数: 所有数组函数
限制: 无
聚集函数
VOLATILE函数: 无
STABLE函数: 无
限制: 无
子查询表达式
VOLATILE函数: 无
STABLE函数: 无
限制: 无
行及数组比较
VOLATILE函数: 无
STABLE函数: 无
限制: 无
集合返回函数
VOLATILE函数: generate_series()
STABLE函数: 无
限制: 无
系统信息函数
VOLATILE函数: 无
STABLE函数: 所有会话信息函数、所有访问权限查询函数、所有模式可见性查询函数、所有系统目录信息函数、所有注释信息函数、所有事务ID和快照
限制: 无
系统管理函数
VOLATILE函数: 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()
STABLE函数: current_setting()、所有数据库对象大小函数
限制: pg_column_size() 函数显示存储该值所需的字节，可能使用toast压缩
XML函数和类似函数的表达式
VOLATILE函数: 无
STABLE函数: cursor_to_xml() cursor_to_xmlschema() database_to_xml() database_to_xmlschema() database_to_xml_and_xmlschema() query_to_xml() query_to_xmlschema() query_to_xml_and_xmlschema() schema_to_xml() schema_to_xmlschema() schema_to_xml_and_xmlschema() table_to_xml() table_to_xmlschema() table_to_xml_and_xmlschema() xmlagg() xmlconcat() xmlelement() xmlexists() xmlforest) xml_is_well_formed() xml_is_well_formed_document() xml_is_well_formed_content() xmlparse ( ) xpath() xpath_exists() xmlpi() xmlroot() xmlserialize () xml() text() xmlcomment() xmlconcat2()
限制: 无

窗口函数¶

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

表窗口函数

函数	返回类型	描述
`cume_dist()`	`double precision`	计算一组值中一个值的累积分布。具有相等值的行总是具有相同的累积分布值。。
`dense_rank()`	`bigint`	计算一个有序行组中一行的无跳跃排名值的排名。具有相等值的行会得到相同的排名值。
`first_value()`	与输入一致	返回一个有续值集合中的第一个值。
`lag()`	与输入一致	在不做自连接的情况下，提供对于同一个表中多于一行的访问。给定一个查询返回的一系列行以及该游标的一个位置，LAG提供对位于该位置之前一个给定物理偏移量的行的访问。默认的offset为1。 default设置当偏移量超出窗口范围之外时要返回的值。如果没有指定default，默认值是空值。
`last_value(`)	与输入一致	返回一个有序值集合中的最后一个值。
`lead()`	与输入一致	在不做自连接的情况下，提供对于同一个表中多于一行的访问。给定一个查询返回的一系列行以及该游标的一个位置，lead提供对位于该位置之后一个给定物理偏移量的行的访问。如果没有指定offset，默认偏移量是1。default设置当偏移量超出窗口范围之外时要返回的值。如果没有指定default，默认值是空值。
`ntile()`	`bigint`	把一个有序数据集划分成一些桶（由expr)定义）并且为每一行分配一个桶号。
`percent_rank()`	`double precision`	计算一个假设行`R`的排名减1，然后除以被计算的行数（在一个窗口分区内）减1。
`rank()`	`bigint`	计算一行在一个有序值组中的排名。根据排名标准有相等值的行得到相同的排名。被占用的行数被加到排名数上来计算下一个排名值。在这种情况下，排名可能不是连续的数字。
`row_number()`	`bigint`	为窗口分区中的每一行或者查询中的每一行分配一个唯一的编号。

高级聚集函数¶

下列内建高级聚集函数是SeaboxMPP对SeaboxSQL数据库的扩展。这些函数都是属于immutable类型的。

SeaboxMPP数据库不支持SeaboxSQL有序集聚合函数。

注意: 用于分析的SeaboxMPP MADlib扩展提供了额外的高级函数来执行对SeaboxMPP数据库数据的统计分析和机器学习。

MEDIAN (expr)

语法: MEDIAN (expression)
返回类型: timestamp, timestamptz, interval, float
描述: 可以用一个二维数组作为输入。把这些数组当作矩阵。
示例: sql SELECT department_id, MEDIAN(salary) FROM employees GROUP BY department_id;

sum(array[])

语法: sum(array[[1,2],[3,4]])
返回类型: smallint[]int[], bigint[], float[]
描述: 执行矩阵求和。可以将被视为矩阵的二维数组作为输入。
示例: ``` sql 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)

语法: pivot_sum( array['A1','A2'], attr, value)
返回类型: int[], bigint[], float[]
描述: 使用sum来解决重复项的pivot聚集。

unnest (array[])

语法: unnest( array['one', 'row', 'per', 'item'])
返回类型: anyelement集合
描述: 把一个一维数组转换成行。返回一个anyelement（一种多态伪类型，请见SeaboxSQL中的伪类型）集合。