Greenplum中的自定义函数

转发自：https://blog.csdn.net/jmx_bigdata/article/details/84316863

CREATE FUNCTION

定义一个函数.

语法

CREATE [OR REPLACE] FUNCTION name

( [ [argmode] [argname] argtype [ { DEFAULT | = } defexpr ] [, …] ] )

[ RETURNS { [ SETOF ] rettype

| TABLE ([{ argname argtype | LIKE other table }

[, …]])

} ]

{ LANGUAGE langname

| IMMUTABLE | STABLE | VOLATILE

| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT

| [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINE

| COST execution_cost

| SET configuration_parameter { TO value | = value | FROM CURRENT }

| AS ‘definition’

| AS ‘obj_file’, ‘link_symbol’ } …

[ WITH ({ DESCRIBE = describe_function

} [, …] ) ]

描述

CREATE FUNCTION定义一个新的函数. CREATE OR REPLACE FUNCTION 要么创建一个新的函数，要么取代一个已经存在的函数。

新函数的名字不能与一个已经存在的并且具有相同参数的函数重名，不同参数的函数可以同名(重载)。

要更新一个已经在的函数的定义，需要使用CREATE OR REPLACE FUNCTION。使用这种方式不能改变一个函数的参数和函数名(因为这样相当于是在创建一个新的函数)。CREATE OR REPLACE FUNCTION不允许改变已经存在的函数的返回值类型，如果要这样做，那么需要将原函数删除，然后重新创建新的函数。如果删除函数去创建一个新的函数，必须先删除已经存在的依赖该函数的对象(rules, views, triggers等等)。使用CREATE OR REPLACE FUNCTION去改变函数的定义，不会改变其他的对象对于该函数的依赖。

使用VOLATILE和 STABLE 函数的限制

为了避免Greenplum中跨segemnt的数据不同步问题，任何被归为STABLE 和VOLATILE类型的函数不能再segment上执行，比如，函数random() 或者 timeofday()不能在greenplum中的分布式数据上执行，因为会导致segment实例之间的数据不一致。

为了保持数据的一致性，STABLE 和VOLATILE函数只有在master上执行时才可以被使用，比如下面的语句只会在master上执行(没有from语句)。

SELECT setval(‘myseq’, 201);

SELECT foo();

参数

name

函数的名字.

argmode

参数的模型: 可以选择的值为 IN, OUT, INOUT和 VARIADIC. 只有 OUT 参数可以跟一个声明为VARIADIC的参数. 如果省略，默认值为IN.

argname

参数的名字. 一些语言 (目前只有 PL/pgSQL) 允许你函数体内使用的名称。

For other languages the name of an input argument is just

extra documentation. 输出参数的名字是非常重要的，以为它决定了输出结果row的列名。

(如果省略了输出参数名字,系统会选择默认的列名)

argtype

函数参数的数据类型

defexpr

如果未指定参数，将用作默认值的表达式。这个表达式必须对参数的参数类型强制。只有IN和INOUT参数可以具有默认值。

rettype

返回的数据类型. 如果该函数不支持返回一个值，则使用void作为返回类型。只有OUT和INOUT参数时，RETURNS语句可以被省略。

SETOF修饰符表明函数会返回一个item集合, 而不是一个item。

Langname

函数使用的语言名称，可以为SQL，C或者用户自定义的语言

IMMUTABLE

STABLE

VOLATILE

该属性会告诉查询优化器关于函数的行为，如果省略, VOLATILE 为默认值.

IMMUTABLE说明函数不能够修改数据库，并且对于给定相同的参数值的情况总是返回相同的结果。

STABLE 说明函数不能修改数据库

VOLATILE 说明函数可以修改事件（event）, 所以没有做任何优化。

CALLED ON NULL INPUT

RETURNS NULL ON NULL INPUT

STRICT

CALLED ON NULL INPUT (默认值)说明当一些参数为null时该函数会被调用。

RETURNS NULL ON NULL INPUT和 STRICT 说明函数总是返回null，

[EXTERNAL] SECURITY INVOKER

[EXTERNAL] SECURITY DEFINER

SECURITY INVOKER (默认值) 说明具有调用该函数权限的用户才可以去执行。

.SECURITY DEFINER 说明该函数可以以创建它的用户的权限去执行。

COST execution_cost

一个正数，说明函数执行的消耗。如果没有指定，对于c语言和内置函数而言，默认值为1

, 如果使用的是其他的语言，默认值为100 。

configuration_parameter

value

进入函数时应用到session配置的值

definition

定义函数的字符串常亮;意味着所依赖的语言，可以是内部函数的名称，对象文件的路径，一个SQL命令，或者是过程化语言的文本文件等。

Examples

一个简单的加法函数:

CREATE FUNCTION add(integer, integer) RETURNS integer

AS ‘select $1 + $2;’

LANGUAGE SQL

IMMUTABLE

RETURNS NULL ON NULL INPUT;

增加一个整形数的值,使用参数的名字, 使用 PL/pgSQL:

CREATE OR REPLACE FUNCTION increment(i integer) RETURNS

integer AS $$

BEGIN

RETURN i + 1;

END;

$$ LANGUAGE plpgsql;

对于一个PL/pgSQL函数，增加每次查询时的segment主机内存：

CREATE OR REPLACE FUNCTION function_with_query() RETURNS

SETOF text AS $$

BEGIN

RETURN QUERY

EXPLAIN ANALYZE SELECT * FROM large_table;

END;

$$ LANGUAGE plpgsql

SET statement_mem=‘256MB’;