CREATE FUNCTION

定义一个新函数。

概要

  1. CREATE [OR REPLACE] FUNCTION name    
  2.     ( [ [argmode] [argname] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
  3.       [ RETURNS rettype
  4.         | RETURNS TABLE ( column_name column_type [, ...] ) ]
  5.     { LANGUAGE langname
  6.     | WINDOW
  7.     | IMMUTABLE | STABLE | VOLATILE | [NOT] LEAKPROOF
  8.     | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
  9.     | [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER
  10.     | EXECUTE ON { ANY | MASTER | ALL SEGMENTS }
  11.     | COST execution_cost
  12.     | SET configuration_parameter { TO value | = value | FROM CURRENT }
  13.     | AS 'definition'
  14.     | AS 'obj_file', 'link_symbol' } ...
  15.     [ WITH ({ DESCRIBE = describe_function
  16.            } [, ...] ) ]

描述

CREATE FUNCTION定义一个新函数。 CREATE OR REPLACE FUNCTION要么创建新函数，要么替换现有定义。

新函数的名称不得与任何在同一模式中具有相同输入参数类型的现有函数匹配。 但是，不同参数类型的函数可能共享一个名称（重载）。

要更新现有函数的定义，请使用CREATE OR REPLACE FUNCTION。 不能以这种方式更改函数的名称或参数类型（这实际上会创建一个新的，不同的函数）。 同样，CREATE OR REPLACE FUNCTION将不允许您更改现有函数的返回类型。 为此，必须删除并重新创建该函数。 使用OUT参数时，这意味着您不能更改任何OUT参数的类型，除非删除该函数。 如果删除然后重新创建函数，则必须删除引用旧函数的现有对象（规则，视图，触发器等）。 使用CREATE OR REPLACE FUNCTION来更改函数定义，而不会破坏引用该函数的对象。

创建函数的用户将成为该函数的所有者。

为了能够创建函数，您必须对参数类型和返回类型具有USAGE特权。

有关创建函数的更多信息，请参见PostgreSQL文档的用户定义函数部分。

限制使用VOLATILE和STABLE函数

为了防止数据在Greenplum数据库的各个segment之间变得不同步， 如果分类为STABLE或VOLATILE的任何函数包含SQL或以任何方式修改了数据库， 则不能在segment级别执行该函数。 例如，不允许在Greenplum数据库中的分布式数据上执行诸如random()或timeofday()之类的函数， 因为它们可能会导致segment实例之间的数据不一致。

为了确保数据的一致性，可以在master数据库上评估并执行的语句中安全地使用VOLATILE和STABLE函数。 例如，以下语句始终在master数据库上执行（没有FROM子句的语句）：

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

如果语句的FROM子句包含一个分布式表， 并且FROM子句中使用的函数仅返回一组行，则可以在这些segment上执行：

  1. SELECT * FROM foo();

此规则的一个例外是返回表引用（rangeFuncs）的函数或使用refCursor数据类型的函数。 请注意，您无法从Greenplum数据库中的任何类型的函数返回refcursor。

函数易变性和EXECUTE ON属性

易变性属性（IMMUTABLE，STABLE，VOLATILE）和EXECUTE ON属性指定函数执行的两个不同方面。 通常，易变性表示执行该函数的时间，EXECUTE ON表示执行该函数的位置。

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

请参阅Greenplum数据库管理员指南中的使用函数和运算符。

函数和复制表

在复制表上仅执行SELECT命令的用户定义函数可以在segment上运行。 使用DISTRIBUTED REPLICATED子句创建的复制表将其所有行存储在每个segment上。 函数在segment上读取它们是安全的，但是对复制表的更新必须在master实例上执行。

参数

name

要创建的函数的名称（可以由模式指定）。

argmode

参数的模式：IN，OUT，INOUT或VARIADIC。 如果省略，则默认值为IN。 只有OUT参数可以跟随声明为VARIADIC的参数。 同样，OUT和INOUT参数不能与RETURNS TABLE表示法一起使用。

argname

参数的名称。 某些语言（当前仅SQL和PL/pgSQL）允许您在函数主体中使用名称。 对于其他语言，就函数本身而言，输入参数的名称只是额外的文档。 但是您可以在调用函数时使用输入参数名称来提高可读性。 在任何情况下，输出参数的名称都是有效的，因为它在结果行类型中定义了列名称。 （如果省略输出参数的名称，则系统将选择默认列名称。）

argtype

函数参数的数据类型（可以由模式指定）（如果有）。 参数类型可以是基本类型，复合类型或域类型，或者可以引用表列的类型。

根据实现语言的不同，还可以允许指定伪类型，例如cstring。 伪类型表示实际参数类型未完全指定，或者位于普通SQL数据类型集之外。

通过编写tablename.columnname%TYPE来引用列的类型。 使用此函数有时可以使函数独立于表定义的更改。

default_expr

如果未指定参数，则用作默认值的表达式。 该表达式必须对参数的参数类型具有强制性。 只有IN和INOUT参数可以具有默认值。 参数列表中紧随默认值的参数之后的每个输入参数也必须具有默认值。

rettype

返回数据类型（可以由模式指定）。 返回类型可以是基本类型，复合类型或域类型，或者可以引用表列的类型。 根据实现语言的不同，还可以允许指定伪类型，例如cstring。 如果该函数不应该返回值，则将void指定为返回类型。

当有OUT或INOUT参数时，可以省略RETURNS子句。 如果存在，则必须与输出参数隐含的结果类型相符： RECORD，如果有多个输出参数，或者与单个输出参数具有相同的类型。

SETOF修饰符表示该函数将返回一组项目，而不是单个项目。

通过编写tablename.columnname%TYPE来引用列的类型。

column_name

RETURNS TABLE语法中的输出列的名称。 这实际上是声明命名OUT参数的另一种方法， 除了RETURNS TABLE还暗含RETURNS SETOF。

column_type

RETURNS TABLE语法中输出列的数据类型。

langname

函数所使用的语言的名称。 可以是SQL，C，internal或用户定义的过程语言的名称。 有关Greenplum数据库中支持的过程语言，请参见CREATE LANGUAGE。 为了向后兼容，名称可以用单引号引起来。

WINDOW

WINDOW表示该函数是窗口函数，而不是普通函数。 当前这仅对用C编写的函数有用。 替换现有函数定义时，不能更改WINDOW属性。

IMMUTABLE

STABLE

VOLATILE

LEAKPROOF

这些属性将有关函数的行为通知查询优化器。 最多可以指定一种选择。 如果这些都不出现，则默认为VOLATILE。 由于Greenplum数据库当前对VOLATILE函数的使用受到限制， 因此，如果一个函数确实是IMMUTABLE，则必须将其声明为可以不受限制地使用。

IMMUTABLE指示该函数无法修改数据库，并且在给定相同的参数值时始终返回相同的结果。 它不进行数据库查找，或者使用其参数列表中不直接存在的信息。 如果指定了此选项，则可以使用函数值立即替换具有全常数参数的任何函数调用。

STABLE表示该函数无法修改数据库，并且在单个表扫描中它将针对相同的参数值一致地返回相同的结果， 但其结果可能会在SQL语句之间发生变化。 对于结果取决于数据库查找，参数值（例如当前时区）等等的函数，这是适当的选择。 还要注意，current_timestamp系列函数符合稳定条件，因为它们的值在事务中不会更改。

VOLATILE表示该函数值即使在一次表扫描中也可以更改，因此无法进行优化。 从这个意义上说，相对来说很少有数据库函数是易变的。 一些示例是random()，timeofday()。 但是请注意，任何具有副作用的函数都必须归类为易失性， 即使其结果是可以预测的，也可以防止调用被优化掉。 一个示例是setval()。

LEAKPROOF表示该函数没有副作用。 除了返回值以外，它不显示有关其参数的任何信息。 例如，对于某些参数值而不是其他参数值抛出错误消息的函数， 或者在任何错误消息中包含参数值的函数，都不是防漏的。 查询优化器可以将防泄漏函数（但不能禁止其他功能）推入使用security_barrier选项创建的视图中。 请参阅CREATE VIEW和CREATE RULE。 该选项只能由超级用户设置。

CALLED ON NULL INPUT

RETURNS NULL ON NULL INPUT

STRICT

CALLED ON NULL INPUT（默认值）表示该函数的某些参数为null时将正常调用该函数。 然后，函数作者有责任检查空值（如有必要）并做出适当响应。 RETURNS NULL ON NULL INPUT或STRICT表示该函数在任何参数为null时始终返回null。 如果指定了此参数，则在参数为空时不执行该函数；而是自动假定为空结果。

[EXTERNAL] SECURITY INVOKER

[EXTERNAL] SECURITY DEFINER

SECURITY INVOKER（默认值）指示该函数将以调用该函数的用户权限执行。 SECURITY DEFINER指定要使用创建该函数的用户的特权来执行该函数。 允许使用EXTERNAL关键字来实现SQL一致性，但它是可选的， 因为与SQL不同，此功能不仅适用于外部函数，还适用于所有函数。

EXECUTE ON ANY

EXECUTE ON MASTER

EXECUTE ON ALL SEGMENTS

EXECUTE ON属性指定在查询执行过程中调用函数时在何处（master或segment实例）执行。

EXECUTE ON ANY（默认值）表示该函数可以在master或任何segment实例上执行， 并且无论在何处执行，它均返回相同的结果。 Greenplum数据库确定函数在哪里执行。

EXECUTE ON MASTER表示该函数只能在master实例上执行。

EXECUTE ON ALL SEGMENTS表明对于每个调用，该函数必须在所有primary实例上执行，但不能在master实例上执行。 该函数的总结果是所有segment实例的结果的UNION ALL。

有关使用EXECUTE ON属性的信息，请参阅注释。

COST execution_cost

一个正数，标识函数的估计执行成本， 以cpu_operator_cost为单位。 如果函数返回一个集合，则execution_cost会标识每个返回行的成本。 如果未指定成本，则C语言和内部函数默认为1个单位，而其他语言的函数默认为100个单位。 当您指定较大的execution_cost值时，优化器将尝试较少地评估函数。

configuration_parameter

value

进入该函数时，SET子句将一个值应用于会话配置参数。 函数退出时，配置参数将恢复为其先前的值。 SET FROM CURRENT会将执行CREATE FUNCTION时当前的参数值保存为进入该函数时要应用的值。

definition

定义函数的字符串常量；含义取决于语言。 它可以是内部函数名称，目标文件的路径，SQL命令或过程语言的文本。

obj_file, link_symbol

当C语言源代码中的函数名称与SQL函数的名称不同时，这种形式的AS子句用于可动态加载的C语言函数。 字符串obj_file是包含动态可加载对象的文件的名称，而link_symbol是C语言源代码中的函数的名称。 如果省略链接符号，则假定它与所定义的SQL函数的名称相同。 所有函数的C名称必须不同，因此必须为重载的SQL函数赋予不同的C名称（例如，将参数类型用作C名称的一部分）。 建议相对于$libdir（位于$GPHOME/lib）或通过动态库路径（由dynamic_library_path服务器配置参数设置）定位共享库。 如果新安装位于其他位置，则可以简化版本升级。

describe_function

解析调用此函数的查询时要执行的回调函数的名称。 回调函数返回一个表示结果类型的元组描述符。

注解

用于自定义函数的所有已编译代码（共享库文件）必须放在Greenplum数据库阵列（master和所有segment）中每个主机的相同位置。 此位置也必须位于LD_LIBRARY_PATH中，以便服务器可以找到文件。 建议在Greenplum阵列中的所有master实例上， 相对于$libdir（位于$GPHOME/lib）或通过动态库路径（由dynamic_library_path服务器配置参数设置）定位共享库。

输入参数和返回值允许使用完整的SQL类型语法。 但是，类型规范的某些详细信息（例如，类型为numeric的精度字段）是基础函数实现的职责， 而CREATE FUNCTION命令无法识别或强制执行。

Greenplum数据库允许函数重载。 相同的名称可以用于多个不同的函数，只要它们具有不同的输入参数类型即可。 但是，所有函数的C名称都必须不同，因此必须为重载的C函数赋予不同的C名称（例如，将参数类型用作C名称的一部分）。

如果两个函数具有相同的名称和输入参数类型，而忽略任何OUT参数，则认为它们是相同的。 因此，例如，这些声明冲突：

  1. CREATE FUNCTION foo(int) ...
  2. CREATE FUNCTION foo(int, out text) ...

具有不同参数类型列表的函数在创建时不会被认为是冲突的， 但是如果提供了参数默认值，则它们在使用中可能会发生冲突。 例如，考虑：

  1. CREATE FUNCTION foo(int) ...
  2. CREATE FUNCTION foo(int, int default 42) ...

由于不清楚应调用哪个函数，因此调用foo(10)将失败。

当重复的CREATE FUNCTION调用引用相同的目标文件时，该文件仅被加载一次。 要卸载并重新加载文件，请使用LOAD命令。

您必须对一种语言具有USAGE特权，才能使用该语言定义函数。

使用美元引号而不是常规的单引号语法编写函数定义字符串通常会很有帮助。 如果不使用美元引号，则必须通过将它们加倍来转义函数定义中的任何单引号或反斜杠。 用美元引用的字符串常量由一个美元符号（$）， 一个零个或多个字符的可选标记，另一个美元符号，构成字符串内容的任意字符序列， 一个美元符号以及与开始此美元引用相同的标记和一个美元符号组成。 在用美元引用的字符串中，可以使用单引号，反斜杠或任何字符而无需转义。 字符串内容始终按原义书写。 例如，以下是两种使用美元引号指定字符串”Dianne’s horse”的方法：

  1. $$Dianne's horse$$
  2. $SomeTag$Dianne's horse$SomeTag$

如果将SET子句附加到函数， 则在函数内部针对相同变量执行的SET LOCAL命令的作用仅限于该函数； 函数退出时，配置参数的先前值仍会恢复。 但是，普通的SET命令（不包含LOCAL）会覆盖CREATE FUNCTION SET子句， 就像以前的SET LOCAL命令所使用的一样。 除非当前事务回滚，否则该命令的效果将在函数退出后继续存在。

如果将带有VARIADIC参数的函数声明为STRICT， 则严格性检查将测试可变参数数组整体是否为非空。 如果数组具有空元素，PL/pgSQL仍将调用该函数。

用CREATE OR REPLACE FUNCTION替换现有函数时，更改参数名称存在限制。 您无法更改已分配给任何输入参数的名称（尽管您可以将名称添加到以前没有输入参数的参数中）。 如果有多个输出参数，则不能更改输出参数的名称，因为这将更改描述函数结果的匿名复合类型的列名称。 进行这些限制是为了确保函数的现有调用在被替换时不会停止工作。

将函数与查询一起用于分布式数据

在某些情况下，如在FROM子句中指定的表中的数据分布在Greenplum数据库segment上， Greenplum数据库不支持在查询中使用函数。 例如，此SQL查询包含函数func()：

  1. SELECT func(a) FROM table1;

如果满足以下所有条件，则该函数不支持在查询中使用：

  • 表table1的数据分布在Greenplum数据库segment上。
  • 函数func()从分布式表中读取或修改数据。
  • 函数func()返回多个行或采用来自table1的参数（a）。

如果不满足任何条件，则支持该函数。 具体来说，如果满足以下任一条件，则支持该函数：

  • 函数func()不会访问分布式表中的数据， 也不会访问仅在Greenplum数据库master上的数据。
  • 表table1是仅在master上。
  • 函数func()仅返回一行，并且仅接受常量值的输入参数。 如果可以将其更改为不需要输入参数，则支持该函数。

使用EXECUTE ON属性

大多数执行查询以访问表的函数只能在master上执行。 但是，仅对复制表执行SELECT查询的函数可以在segment上运行。 如果函数访问哈希分布表或随机表，则应使用EXECUTE ON MASTER属性定义该函数。 否则，在复杂的查询中使用该函数时，该函数可能会返回错误的结果。 如果没有该属性，优化器优化可能会确定将函数调用推到segment实例将是有益的。

这些是使用EXECUTE ON MASTER或EXECUTE ON ALL SEGMENTS属性定义的函数的限制：

  • 该函数必须是返回集合的函数。
  • 该函数不能在查询的FROM子句中。
  • 该函数不能在带有FROM子句的查询的SELECT列表中。
  • 包含该函数的查询从GPORCA退回到Postgres查询计划程序。

示例

一个非常简单的加法函数：

  1. CREATE FUNCTION add(integer, integer) RETURNS integer
  2.    AS 'select $1 + $2;'
  3.    LANGUAGE SQL
  4.    IMMUTABLE
  5.    RETURNS NULL ON NULL INPUT;

在PL/pgSQL中使用参数名称递增一个整数：

  1. CREATE OR REPLACE FUNCTION increment(i integer) RETURNS
  2. integer AS $$
  3.         BEGIN
  4.                 RETURN i + 1;
  5.         END;
  6. $$ LANGUAGE plpgsql;

为PL/pgSQL函数增加每个查询的默认segment主机内存：

  1. CREATE OR REPLACE FUNCTION function_with_query() RETURNS
  2. SETOF text AS $$
  3.         BEGIN
  4.                 RETURN QUERY
  5.                 EXPLAIN ANALYZE SELECT * FROM large_table;
  6.         END;
  7. $$ LANGUAGE plpgsql
  8. SET statement_mem='256MB';

使用多态类型返回ENUM数组：

  1. CREATE TYPE rainbow AS ENUM('red','orange','yellow','green','blue','indigo','violet');
  2. CREATE FUNCTION return_enum_as_array( anyenum, anyelement, anyelement )
  3.     RETURNS TABLE (ae anyenum, aa anyarray) AS $$
  4.     SELECT $1, array[$2, $3]
  5. $$ LANGUAGE SQL STABLE;
  7. SELECT * FROM return_enum_as_array('red'::rainbow, 'green'::rainbow, 'blue'::rainbow);

返回包含多个输出参数的记录：

  1. CREATE FUNCTION dup(in int, out f1 int, out f2 text)
  2.    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
  3.    LANGUAGE SQL;
  5. SELECT * FROM dup(42);

您可以使用明确命名的复合类型来更详细地执行相同的操作：

  1. CREATE TYPE dup_result AS (f1 int, f2 text);
  2. CREATE FUNCTION dup(int) RETURNS dup_result
  3.     AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
  4.     LANGUAGE SQL;
  6. SELECT * FROM dup(42);

返回多列的另一种方法是使用TABLE函数：

  1. CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
  2.    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
  3.    LANGUAGE SQL;
  5. SELECT * FROM dup(4);

该函数在EXECUTE ON ALL SEGMENTS中定义，以在所有primary实例上运行。 SELECT命令执行返回在每个segment实例上运行时间的函数。

  1. CREATE FUNCTION run_on_segs (text) returns setof text as $$
  2.   begin
  3.     return next ($1 || ' - ' || now()::text );
  4.   end;
  5. $$ language plpgsql VOLATILE EXECUTE ON ALL SEGMENTS;
  7. SELECT run_on_segs('my test');

此功能在parts表中查找part名称。 parts表是复制表，因此该函数可以在master或primary上执行。

  1. CREATE OR REPLACE FUNCTION get_part_name(partno int) RETURNS text AS
  2. $$
  3. DECLARE
  4.    result text := ' ';
  5. BEGIN
  6.     SELECT part_name INTO result FROM parts WHERE part_id = partno;
  7.     RETURN result;
  8. END;
  9. $$ LANGUAGE plpgsql;

如果在master上执行SELECT get_part_name(100);，该函数在master上执行。 （master实例将查询定向到单个primary。） 如果orders是分布式表，并且您执行以下查询，则get_part_name()函数将在primary上执行。

  1. SELECT order_id, get_part_name(orders.part_no) FROM orders;

兼容性

CREATE FUNCTION在SQL：1999及更高版本中定义。 Greenplum数据库版本相似，但不完全兼容。 这些属性不是可移植的，不同的可用语言也不是。

为了与某些其他数据库系统兼容，可以在argname之前或之后写入argmode。 但是只有第一种方法是符合标准的。

对于参数默认值，SQL标准仅使用DEFAULT关键字指定语法。 在T-SQL和Firebird中使用\=语法。

另见

ALTER FUNCTION , DROP FUNCTION , LOAD

Parent topic: SQL Command Reference