使用函数和操作符

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

Parent topic: 查询数据

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

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

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

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

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

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

高级聚集函数

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

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

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

例子:

  1. SELECT department_id, MEDIAN(salary)
  2. FROM employees
  3. GROUP BY department_id;
可以用一个二维数组作为输入。把这些数组当作矩阵。
sum(array[])smallint[]int[], bigint[], float[]sum(array[[1,2],[3,4]])

例子:

  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来解决重复项的pivot聚集。
unnest (array[])anyelement集合unnest( array[‘one’, ‘row’, ‘per’, ‘item’])把一个一维数组转换成行。返回一个anyelement(一种多态伪类型,请见PostgreSQL中的伪类型)集合。pseudo-type