这是本节的多页打印视图。 点击此处打印.

返回本页常规视图.

用户定义的 SQL 函数

用户定义的 SQL 函数允许您以函数形式定义和存储常用的 SQL 表达式。用户定义的 SQL 函数对于执行复杂的查询和组合 Vertica 内置函数很有用。您只要调用在查询中指定的函数的名称。

只要可以在该位置使用普通 SQL 表达式，您就可以在查询中的任意位置使用用户定义的 SQL 函数，但不能在表分区子句或投影分段子句中使用。

有关用于本节中讨论的命令和系统表的语法与参数，请参阅以下主题：

1 - 创建用户定义的 SQL 函数

除了不能在表分区子句或投影分段子句中使用以外，用户定义的 SQL 函数可以在能够使用普通 SQL 表达式的任何查询中使用。

要创建 SQL 函数，用户必须拥有架构的 CREATE 权限。要使用 SQL 函数，用户必须拥有架构的 USAGE 权限和定义的函数的 EXECUTE 权限。

下面的语句可创建名为 myzeroifnull 的 SQL 函数，该函数接受 INTEGER 实参并返回 INTEGER 结果。

=> CREATE FUNCTION myzeroifnull(x INT) RETURN INT
   AS BEGIN
     RETURN (CASE WHEN (x IS NOT NULL) THEN x ELSE 0 END);
   END;

只要使用普通 SQL 表达式，便可以使用新的 SQL 函数 (myzeroifnull)。例如，创建一个简单表：

=> CREATE TABLE tabwnulls(col1 INT);
=> INSERT INTO tabwnulls VALUES(1);
=> INSERT INTO tabwnulls VALUES(NULL);
=> INSERT INTO tabwnulls VALUES(0);
=> SELECT * FROM tabwnulls;
 a
---
 1
 0
(3 rows)

使用 myzeroifnull 函数（在 SELECT 语句中），其中函数从表 tabwnulls 中调用 col1：

=> SELECT myzeroifnull(col1) FROM tabwnulls;
 myzeroifnull
--------------
          1
          0
          0
(3 rows)

使用 myzeroifnull 函数（在 GROUP BY 子句中）：

=> SELECT COUNT(*) FROM tabwnulls GROUP BY myzeroifnull(col1);
 count
-------
     2
     1
(2 rows)

如果要更改用户定义的 SQL 函数的主体，请使用 CREATE OR REPLACE 语法。以下命令修改了 CASE 表达式：

=> CREATE OR REPLACE FUNCTION myzeroifnull(x INT) RETURN INT
   AS BEGIN
     RETURN (CASE WHEN (x IS NULL) THEN 0 ELSE x END);
   END;

要查看此信息在 Vertica 编录中的存储方式，请参阅查看有关 SQL 函数的信息。

另请参阅

2 - 更改并删除用户定义的 SQL 函数

Vertica 允许具有不同实参类型的多个函数共用相同名称。因此，如果您在未指定参数数据类型的情况下尝试更改或删除 SQL 函数，系统将返回错误消息，以防止删除不正确的函数：

=> DROP FUNCTION myzeroifnull();
ROLLBACK:  Function with specified name and parameters does not exist: myzeroifnull

更改用户定义的 SQL 函数

使用 ALTER FUNCTION（标量） 命令，可以为用户定义的函数分配新名称并将该函数移到其他架构。

在上一个主题中，您创建了名为 myzeroifnull 的 SQL 函数。以下命令可将 myzeroifnull 函数重命名为 zerowhennull：

=> ALTER FUNCTION myzeroifnull(x INT) RENAME TO zerowhennull;
ALTER FUNCTION

以下命令可将已重命名的函数移到名为 macros 的新架构中：

=> ALTER FUNCTION zerowhennull(x INT) SET SCHEMA macros;
ALTER FUNCTION

删除 SQL 函数

DROP FUNCTION 命令可从 Vertica 编录中删除 SQL 函数。

与 ALTER FUNCTION 一样，您必须指定参数数据类型，否则系统会返回以下错误消息：

=> DROP FUNCTION zerowhennull();
ROLLBACK:  Function with specified name and parameters does not exist: zerowhennull

指定参数类型：

=> DROP FUNCTION macros.zerowhennull(x INT);
DROP FUNCTION

Vertica 不会检查依赖项，因此，如果删除其他对象（例如视图或其他 SQL 函数）所引用的 SQL 函数，Vertica 会在使用这些对象时（而非删除该函数时）返回错误。

另请参阅

3 - 管理对 SQL 函数的访问

用户必须拥有架构的 USAGE 权限和定义的函数的 EXECUTE 权限才能执行用户定义的 SQL 函数。只有超级用户或所有者可以授予/撤销函数的 EXECUTE 权限。

要向用户 Fred 授予 myzeroifnull 函数的 EXECUTE 权限，请执行下列操作：

=> GRANT EXECUTE ON FUNCTION myzeroifnull (x INT) TO Fred;

要从用户 Fred 撤销 myzeroifnull 函数的 EXECUTE 权限，请执行下列操作：

=> REVOKE EXECUTE ON FUNCTION myzeroifnull (x INT) FROM Fred;

另请参阅

4 - 查看有关用户定义的 SQL 函数的信息

只要拥有 EXECUTE 权限，您就可以访问有关用户定义的 SQL 函数的信息。可以从系统表 USER_FUNCTIONS 访问此信息，也可以通过 vsql 元命令 \df 访问此信息。

要查看您拥有 EXECUTE 权限的所有用户定义的 SQL 函数，请查询 USER_FUNCTIONS：

=> SELECT * FROM USER_FUNCTIONS;
-[ RECORD 1 ]----------+---------------------------------------------------
schema_name            | public
function_name          | myzeroifnull
function_return_type   | Integer
function_argument_type | x Integer
function_definition    | RETURN CASE WHEN (x IS NOT NULL) THEN x ELSE 0 END
volatility             | immutable
is_strict              | f

如果要更改用户定义的 SQL 函数的主体，请使用 CREATE OR REPLACE 语法。以下命令修改了 CASE 表达式：

=> CREATE OR REPLACE FUNCTION myzeroifnull(x INT) RETURN INT
   AS BEGIN
     RETURN (CASE WHEN (x IS NULL) THEN 0 ELSE x END);
   END;

现在，当您查询 USER_FUNCTIONS 时，可在 function_definition 列中查看所做更改：

=> SELECT * FROM USER_FUNCTIONS;
-[ RECORD 1 ]----------+---------------------------------------------------
schema_name            | public
function_name          | myzeroifnull
function_return_type   | Integer
function_argument_type | x Integer
function_definition    | RETURN CASE WHEN (x IS NULL) THEN 0 ELSE x END
volatility             | immutable
is_strict              | f

如果使用 CREATE OR REPLACE 语法以便仅更改参数名称或参数类型（或两者），系统会维护函数的两个版本。例如，以下命令将指示函数为 myzeroifnull 函数接受并返回数字数据类型而非整数。

=> CREATE OR REPLACE FUNCTION myzeroifnull(z NUMERIC) RETURN NUMERIC
   AS BEGIN
     RETURN (CASE WHEN (z IS NULL) THEN 0 ELSE z END);
   END;

现在，当您查询 USER_FUNCTIONS 表时，可在 Record 2 中查看 myzeroifnull 的第二个实例，还可以在 function_return_type、function_argument_type 和 function_definition 列中查看所做更改。 

=> SELECT * FROM USER_FUNCTIONS;
-[ RECORD 1 ]----------+------------------------------------------------------------
schema_name            | public
function_name          | myzeroifnull
function_return_type   | Integer
function_argument_type | x Integer
function_definition    | RETURN CASE WHEN (x IS NULL) THEN 0 ELSE x END
volatility             | immutable
is_strict              | f
-[ RECORD 2 ]----------+------------------------------------------------------------
schema_name            | public
function_name          | myzeroifnull
function_return_type   | Numeric
function_argument_type | z Numeric
function_definition    | RETURN (CASE WHEN (z IS NULL) THEN (0) ELSE z END)::numeric
volatility             | immutable
is_strict              | f

由于 Vertica 允许具有不同实参类型的函数共用相同名称，因此您必须在更改或删除函数时指定实参类型。否则，系统将返回错误消息：

=> DROP FUNCTION myzeroifnull();
ROLLBACK:  Function with specified name and parameters does not exist: myzeroifnull

5 - 迁移内置 SQL 函数

如果您有来自其他 RDBMS 的内置 SQL 函数，并且这些函数未映射到 Vertica 支持的函数，您可以使用用户定义的 SQL 函数将这些函数迁移到 Vertica 数据库中。

下面的示例脚本显示了如何为以下 DB2 内置函数创建用户定义的函数：

  • UCASE()

  • LCASE()

  • LOCATE()

  • POSSTR()

UCASE()

该脚本为 UCASE() 函数创建了一个用户定义的 SQL 函数：

=> CREATE OR REPLACE FUNCTION UCASE (x VARCHAR)
   RETURN VARCHAR
   AS BEGIN
   RETURN UPPER(x);
   END;

LCASE()

该脚本为 LCASE() 函数创建了一个用户定义的 SQL 函数：

=> CREATE OR REPLACE FUNCTION LCASE (x VARCHAR)
   RETURN VARCHAR
   AS BEGIN
   RETURN LOWER(x);
   END;

LOCATE()

该脚本为 LOCATE() 函数创建了一个用户定义的 SQL 函数：

=> CREATE OR REPLACE FUNCTION LOCATE(a VARCHAR, b VARCHAR)
   RETURN INT
   AS BEGIN
   RETURN POSITION(a IN b);
   END;

POSSTR()

该脚本为 POSSTR() 函数创建了一个用户定义的 SQL 函数：

=> CREATE OR REPLACE FUNCTION POSSTR(a VARCHAR, b VARCHAR)
   RETURN INT
   AS BEGIN
   RETURN POSITION(b IN a);
   END;