CREATE FUNCTION - Amazon Redshift
所需的权限语法参数使用说明示例

CREATE FUNCTION

使用 SQL SELECT 子句或 Python 程序创建新的标量用户定义的函数 (UDF)。

有关更多信息以及示例，请参阅 创建用户定义的函数。

所需的权限

您必须通过以下方式之一获得权限，才能运行 CREATE OR REPLACE FUNCTION：

  • CREATE FUNCTION：

    • 超级用户可以使用可信和不受信任的语言来创建函数。

    • 具有 CREATE [ OR REPLACE ] FUNCTION 权限的用户可以使用可信语言创建函数。

  • REPLACE FUNCTION：

    • Superuser

    • 具有 CREATE [ OR REPLACE ] FUNCTION 权限的用户

    • 函数拥有者

语法

CREATE [ OR REPLACE ] FUNCTION f_function_name
( { [py_arg_name  py_arg_data_type |
sql_arg_data_type } [ , ... ] ] )
RETURNS data_type
{ VOLATILE | STABLE | IMMUTABLE }
AS $$
  { python_program | SELECT_clause }
$$ LANGUAGE { plpythonu | sql }

参数

OR REPLACE

指定如果某个函数与已存在的此函数具有相同的名称和输入参数数据类型或签名，则替换现有的函数。您只能将某个函数替换为定义一组相同数据类型的新函数。您必须是超级用户才能替换函数。

如果您定义的函数与现有函数具有相同的名称，但签名不同，则创建新的函数。换而言之，函数名称将会重载。有关更多信息，请参阅 重载函数名称。

f_function_name

函数的名称。如果您指定 schema 名称（例如 myschema.myfunction），则使用指定的 schema 创建函数。否则，将在当前 schema 中创建该函数。有关有效名称的更多信息，请参阅名称和标识符。

我们建议您将所有 UDF 名称添加前缀 f_。Amazon Redshift 保留 f_ 前缀供 UDF 名称使用。因此，使用 f_ 前缀，您可以确保您的 UDF 名称不会与现有或未来的 Amazon Redshift 内置 SQL 函数名称冲突。有关更多信息，请参阅 对 UDF 命名。

如果输入参数的数据类型不同，您可以定义多个具有相同函数名称的函数。换而言之，函数名称将会重载。有关更多信息，请参阅 重载函数名称。

py_arg_name py_arg_data_type | sql_arg_data 类型

对于 Python UDF，为参数名称和数据类型的列表。对于 SQL UDF，为数据类型的列表，不含参数名称。在 Python UDF 中，使用参数名称引用参数。在 SQL UDF 中，使用 $1、$2 等基于参数在参数列表中的顺序引用参数。

对于 SQL UDF，输入和返回数据类型可以是任何标准 Amazon Redshift 数据类型。对于 Python UDF，输入和返回数据类型可以为 SMALLINT、INTEGER、BIGINT、DECIMAL、REAL、DOUBLE PRECISION、BOOLEAN、CHAR、VARCHAR、DATE 或 TIMESTAMP。此外，Python 用户定义的函数 (UDF) 支持数据类型 ANYELEMENT。此数据类型会根据在运行时提供的相应参数的数据类型，自动转换为标准数据类型。如果多个参数使用 ANYELEMENT，则它们会根据列表中的第一个 ANYELEMENT 参数，在运行时全部解析为相同的数据类型。有关更多信息，请参阅Python UDF 数据类型和数据类型。

您可以指定最多 32 个参数。

RETURNS data_type

函数返回的值的数据类型。RETURNS 数据类型可以是任何标准的 Amazon Redshift 数据类型。此外，Python UDF 可以使用 ANYELEMENT 数据类型，它会根据在运行时提供的参数，自动转换为标准数据类型。如果您为返回数据类型指定 ANYELEMENT，则至少有一个参数必须使用 ANYELEMENT。在调用函数时，实际返回数据类型会匹配为 ANYELEMENT 参数提供的数据类型。有关更多信息，请参阅 Python UDF 数据类型。

VOLATILE | STABLE | IMMUTABLE

通知查询优化程序有关函数的不稳定性。

如果您将函数标记为其有效的最严格稳定性类别，您将获得最大程度的优化。但是，如果类别过于严格，则存在优化程序错误地忽略某些调用的风险，从而导致不正确的结果集。按照严格性顺序，从最不严格的开始，稳定性类别如下所示：

  • VOLATILE

  • STABLE

  • IMMUTABLE

VOLATILE

对于相同的参数，函数会对连续的调用返回不同的结果，甚至对于单个语句中的行也是如此。优化查询程序无法对不稳定函数的行为做出任何假设，因此使用不稳定函数的查询必须对每个输入行重新计算该函数。

STABLE

对于相同的参数，可保证函数对在单个语句内处理的所有行返回相同的结果。在不同的语句中调用时，函数可能会返回不同的结果。此类别使优化程序能够将单个语句内对该函数的多个调用优化为对该语句的单个调用。

IMMUTABLE

对于相同的参数，函数始终返回相同的结果。当查询使用常量参数调用 IMMUTABLE 函数时，优化程序会预先计算函数。

AS $$ statement $$

包含要执行的语句的构造。需要文字关键字 AS $$ 和 $$。

Amazon Redshift 要求您使用称为“美元引号”的格式，在您的函数中包含语句。包含的任何内容将按原样传递。您不必对任何特殊字符进行转义，因为字符串的内容是按照其字面涵义编写的。

通过美元引号 格式，您可以使用一对美元符号 ($$) 来指示要运行的语句的开头和结尾，如以下示例所示。

$$ my statement $$

（可选）在每对美元符号之间，可以指定字符串来帮助识别语句。您使用的字符串必须在括起字符对的开始和结束都是相同的。该字符串区分大小写，它遵循与不带括起字符的标识符相同的约束，但有一点除外，它不能包含美元符号。以下示例使用字符串 test。

$test$ my statement $test$

有关“美元引号”格式的更多信息，请参阅 PostgreSQL 文档的词法结构中的“使用美元符号括起的常量字符串”。

python_program

返回值的有效 Python 可执行程序。您在函数中传递的语句必须符合 Python 网站上的 Python 代码样式指南中规定的缩进要求。有关更多信息，请参阅 适用于 UDF 的 Python 语言支持。

SQL_clause

SQL SELECT 子句。

SELECT 子句不能包含以下任何类型的子句：

  • FROM

  • INTO

  • WHERE

  • GROUP BY

  • ORDER BY

  • LIMIT

LANGUAGE { plpythonu | sql }

对于 Python，指定 plpythonu。对于 SQL，指定 sql。您必须具有使用 SQL 或 plpythonu 语言的权限。有关更多信息，请参阅 UDF 安全性和权限。

使用说明

嵌套函数

您可以从一个 SQL UDF 中调用另一个 SQL 用户定义函数 (UDF)。当您运行 CREATE FUNCTION 命令时，嵌套函数必须存在。Amazon Redshift 不会跟踪 UDF 的依赖项，因此，如果您删除嵌套函数，Amazon Redshift 不会返回错误。但是，如果嵌套函数不存在，则 UDF 将失败。例如，以下函数调用 SELECT 子句中的 f_sql_greater 函数。

create function f_sql_commission (float, float )
  returns float
stable
as $$
  select f_sql_greater ($1, $2)
$$ language sql;

UDF 安全性和权限

要创建 UDF，您必须具有使用 SQL 或 plpythonu (Python) 语言的权限。默认情况下，向 PUBLIC 授予 USAGE ON LANGUAGE SQL。但是，您必须明确授予 USAGE ON LANGUAGE PLPYTHONU 权限才能指定用户或组。

要撤销 SQL 的使用权限，请先从 PUBLIC 撤销使用权限。然后，仅向允许创建 SQL UDF 的特定用户或组授予 SQL 使用权限。以下示例将从 PUBLIC 撤销对 SQL 的使用权限，然后向用户组 udf_devs 授予使用权限。

revoke usage on language sql from PUBLIC;
grant usage on language sql to group udf_devs;

要运行 UDF，您必须拥有每个函数的执行权限。默认情况下，向 PUBLIC 授予新 UDF 的执行权限。要限制使用，请从 PUBLIC 撤消函数的执行权限。然后向特定的个人或组授予权限。

以下示例从 PUBLIC 撤消了对函数 f_py_greater 的执行权限，然后向用户组 udf_devs 授予使用权限。

revoke execute on function f_py_greater(a float, b float) from PUBLIC;
grant execute on function f_py_greater(a float, b float) to group udf_devs;

默认情况下，超级用户拥有全部权限。

有关更多信息，请参阅GRANT和REVOKE。

示例

标量 Python UDF 示例

以下示例创建用于比较两个整数值并返回较大值的 Python UDF。

create function f_py_greater (a float, b float)
  returns float
stable
as $$
  if a > b:
    return a
  return b
$$ language plpythonu;

以下示例查询 SALES 表并调用新的 f_py_greater 函数，以返回 COMMISSION 和 PRICEPAID 的 20% 这两个值中较大的值。

select f_py_greater (commission, pricepaid*0.20) from sales;

标量 SQL UDF 示例

以下示例创建一个用于比较两个数并返回较大值的函数。

create function f_sql_greater (float, float)
  returns float
stable
as $$
  select case when $1 > $2 then $1
    else $2
  end
$$ language sql;

以下查询将调用新的 f_sql_greater 函数以查询 SALES 表，并返回 COMMISSION 或 PRICEPAID 的 20% (两个值中的较大者)。

select f_sql_greater (commission, pricepaid*0.20) from sales;