SQL UDFs 简介

可以使用 SQL 编写用户定义函数 (UDF) 的处理程序。本部分中的主题描述如何设计和编写 SQL 处理程序。您还会发现一些例子。

For an introduction to UDFs, including a list of languages in which you can write a UDF handler, see 用户定义的函数概述.

After you have a handler, you create the UDF with SQL. For information about using SQL to create or call a UDF, see 创建用户定义的函数 or 执行 UDF.

备注

SQL 处理程序的工作原理

当用户调用 UDF 时，用户将 UDF 的名称和实参传递给 Snowflake。Snowflake 调用相关处理程序代码（如有实参则使用实参）来执行 UDF 的逻辑。然后，处理程序方法将输出返回给 Snowflake，Snowflake 再将其传回给客户端。

The function definition can be a SQL expression that returns either a scalar --- that is, single --- value or, if defined as a table function, a set of rows.

示例

以下示例中的代码创建一个名为 area_of_circle 的 UDF,该函数包含处理程序代码，可根据作为实参的 UDF 收到的半径值计算圆的面积。

CREATE FUNCTION area_of_circle(radius FLOAT)
  RETURNS FLOAT
  AS
  $$
    pi() * radius * radius
  $$
  ;
Copy

一般用法

A SQL UDF evaluates an arbitrary SQL expression and returns the results of the expression.

The function definition can be a SQL expression that returns either a scalar --- that is, single --- value or, if defined as a table function, a set of rows.

SQL UDFs 的安全/权限要求

If a function definition refers to an unqualified table, then that table is resolved in the schema containing the function. A reference to another schema object --- such as a table, view, or other function --- requires that the owner of the function has privileges to access that schema object. The invoker of the function need not have access to the objects referenced in the function definition, but only needs the privilege to use the function.

例如，管理员拥有一个名为 users 的表，该表包含通常无法访问的敏感数据，但是，管理员可以通过其他用户具有访问权限的函数公开用户总数：

USE ROLE dataadmin;

DESC TABLE users;
Copy
+-----------+--------------+--------+-------+---------+-------------+------------+--------+------------+---------+
| name      | type         | kind   | null? | default | primary key | unique key | check  | expression | comment |
|-----------+--------------+--------+-------+---------+-------------+------------+--------+------------+---------|
| USER_ID   | NUMBER(38,0) | COLUMN | Y     | [NULL]  | N           | N          | [NULL] | [NULL]     | [NULL]  |
| USER_NAME | VARCHAR(100) | COLUMN | Y     | [NULL]  | N           | N          | [NULL] | [NULL]     | [NULL]  |
  ...
  ...
  ...
+-----------+--------------+--------+-------+---------+-------------+------------+--------+------------+---------+

CREATE FUNCTION total_user_count() RETURNS NUMBER AS 'select count(*) from users';

GRANT USAGE ON FUNCTION total_user_count() TO ROLE analyst;

USE ROLE analyst;

-- This will fail because the role named "analyst" does not have the
-- privileges required in order to access the table named "users".
SELECT * FROM users;
Copy
FAILURE: SQL compilation error:
Object 'USERS' does not exist.

-- However, this will succeed.
SELECT total_user_count();
Copy
+--------------------+
| TOTAL_USER_COUNT() |
|--------------------+
| 123                |
+--------------------+

有关使用角色和权限来管理访问控制的更多信息，请参阅 访问控制概述。

语言: 中文