表格 SQL UDFs (UDTFs)

Snowflake 支持此类 SQL UDFs：返回一组行，其中包含零行、一行或多行，并且每行有一列或多列。此类 UDFs 称为*表格 UDFs*、表 UDFs 或最常见的 *UDTFs*（用户定义的表函数）。

可以在查询的 FROM 子句中访问 UDTF。

本主题内容：

语法

CREATE OR REPLACE FUNCTION <name> ( [ <arguments> ] )
  RETURNS TABLE ( <output_col_name> <output_col_type> [, <output_col_name> <output_col_type> ... ] )
  AS '<sql_expression>'
Copy

有关所有 UDFs 的通用语法的更详细描述（包括 SQL UDTFs），请参阅 CREATE FUNCTION。

实参

name：

这应该是有效数据库对象名称（遵循:doc:`/sql-reference/identifiers-syntax`中描述的规则）。

arguments：

这必须是一个表达式，例如列名称、字面量或可计算为单个值的表达式。通常，函数接受一个实参，即列名称。可以传递多个值，例如，多个列名称，或者一个列名称和一个或多个字面量值。

可以传递常量值或根本不传递值。但是，在大多数情况下，如果每次的输入都相同，则每次的输出都会相同。

RETURNS TABLE(...)

指定 UDF 应返回表。在括号内，指定要包含在返回表中的列的名称类型对（如下所述）。

output_col_name：

要包含在返回表中的输出列的名称。必须至少有一个输出列。

output_col_type：

输出列的数据类型。

sql_expression：

一个有效的 SQL 表达式或语句，返回具有零行或多行的表，并且每行都有一列或多列。输出必须与 RETURNS 子句中指定的数字和数据类型匹配。

使用说明

  • SQL UDTF 的主体（也称为“定义”）必须是一个 SELECT 表达式。

  • 尽管 sql_expression 周围的分隔符通常是单引号，但可以使用一对美元符号 $$ 作为分隔符。结束分隔符必须与开始分隔符匹配。当 sql_expression 包含单引号时，使用一对美元符号会很方便。下面的示例部分包含了使用一对美元符号的示例。

    如果分隔符是单引号，并且主体包含单引号，则可以使用反斜杠字符 \ 作为转义字符来转义主体中的单引号。下面的示例部分包含一个示例。

  • 在 UDTF 中定义的列可以出现在可使用普通表列的任何位置。

  • 在 RETURNS 子句中指定的返回类型决定了表格结果中列的名称和类型，并且必须匹配位于函数主体中 SELECT 语句的相应位置的表达式类型。

  • 调用 UDTF 时，必须在 TABLE 关键字后面的括号内包含 UDTF 名称和实参。有关详细信息，请参阅:ref:label-udf_java_call_udtf。

备注

表格函数 (UDTFs) 的输入实参限制为 500 个，输出列限制为 500 列。

调用 SQL UDTF

在查询的 FROM 子句中调用 UDTF 时，请在 TABLE 关键字后面的括号内指定 UDTF 的名称和实参。

换言之，在调用 UDTF 时，对 TABLE 关键字使用如下形式：

SELECT ...
  FROM TABLE ( udtf_name (udtf_arguments) )
Copy

示例 SQL UDTFs

基本示例

这是一个人为简化的示例 UDTF，它对输出进行硬编码。此示例也展示了用作分隔符的 $$：

CREATE FUNCTION t()
    RETURNS TABLE(msg VARCHAR)
    AS
    $$
        SELECT 'Hello'
        UNION
        SELECT 'World'
    $$;
Copy
SELECT msg 
    FROM TABLE(t())
    ORDER BY msg;
+-------+
| MSG   |
|-------|
| Hello |
| World |
+-------+
Copy

以下示例与前一个示例类似，但它使用单引号作为分隔符，并使用 \ 转义字符对 UDTF 主体中的单引号进行转义：

CREATE FUNCTION t()
    RETURNS TABLE(msg VARCHAR)
    AS
    '
        SELECT \'Hello\'
        UNION
        SELECT \'World\'
    ';
Copy
SELECT msg
    FROM TABLE(t())
    ORDER BY msg;
+-------+
| MSG   |
|-------|
| Hello |
| World |
+-------+
Copy

以下是 UDTF 的另一个简单示例。它查询一个表，并从该表中返回两列：

create or replace table orders (
    product_id varchar, 
    quantity_sold numeric(11, 2)
    );

insert into orders (product_id, quantity_sold) values 
    ('compostable bags', 2000),
    ('re-usable cups',  1000);
Copy
create or replace function orders_for_product(PROD_ID varchar)
    returns table (Product_ID varchar, Quantity_Sold numeric(11, 2))
    as
    $$
        select product_ID, quantity_sold 
            from orders 
            where product_ID = PROD_ID
    $$
    ;
Copy
select product_id, quantity_sold
    from table(orders_for_product('compostable bags'))
    order by product_id;
+------------------+---------------+
| PRODUCT_ID       | QUANTITY_SOLD |
|------------------+---------------|
| compostable bags |       2000.00 |
+------------------+---------------+
Copy

也可以使用视图实现相同的功能。

使用联接的示例

创建并使用一个 SQL UDTF，其返回指定用户 ID 的国家/地区信息（COUNTRY_CODE 和 COUNTRY_NAME）：

create or replace table countries (country_code char(2), country_name varchar);
insert into countries (country_code, country_name) values 
    ('FR', 'FRANCE'),
    ('US', 'UNITED STATES'),
    ('SP', 'SPAIN');

create or replace table user_addresses (user_ID integer, country_code char(2));
insert into user_addresses (user_id, country_code) values 
    (100, 'SP'),
    (123, 'FR'),
    (123, 'US');
Copy
CREATE OR REPLACE FUNCTION get_countries_for_user ( id number )
  RETURNS TABLE (country_code char, country_name varchar)
  AS 'select distinct c.country_code, c.country_name
      from user_addresses a, countries c
      where a.user_id = id
      and c.country_code = a.country_code';
Copy
select *
    from table(get_countries_for_user(123)) cc
    where cc.country_code in ('US','FR','CA')
    order by country_code;
+--------------+---------------+
| COUNTRY_CODE | COUNTRY_NAME  |
|--------------+---------------|
| FR           | FRANCE        |
| US           | UNITED STATES |
+--------------+---------------+
Copy

创建一个 SQL UDTF，以返回指定年份最热门的颜色：

create or replace table favorite_years as
    select 2016 year
    UNION ALL
    select 2017
    UNION ALL
    select 2018
    UNION ALL
    select 2019;

 create or replace table colors as
    select 2017 year, 'red' color, true favorite
    UNION ALL
    select 2017 year, 'orange' color, true favorite
    UNION ALL
    select 2017 year, 'green' color, false favorite
    UNION ALL
    select 2018 year, 'blue' color, true favorite
    UNION ALL
    select 2018 year, 'violet' color, true favorite
    UNION ALL
    select 2018 year, 'brown' color, false favorite;

create or replace table fashion as
    select 2017 year, 'red' fashion_color
    UNION ALL
    select 2018 year, 'black' fashion_color
    UNION ALL
    select 2019 year, 'orange' fashion_color;
Copy
create or replace function favorite_colors(the_year int)
    returns table(color string) as
    'select color from colors where year=the_year and favorite=true';
Copy

在查询中使用 UDTF：

select color
    from table(favorite_colors(2017))
    order by color;
+--------+
| COLOR  |
|--------|
| orange |
| red    |
+--------+
Copy

在与另一个表的联接中使用 UDTF；请注意，表中的联接列将作为实参传递给函数。

select * 
    from favorite_years y join table(favorite_colors(y.year)) c
    order by year, color;
+------+--------+
| YEAR | COLOR  |
|------+--------|
| 2017 | orange |
| 2017 | red    |
| 2018 | blue   |
| 2018 | violet |
+------+--------+
Copy

使用 WHERE 子句而不是 ON 来表示其他谓词：

select * 
    from fashion f join table(favorite_colors(f.year)) fav
    where fav.color = f.fashion_color ;
+------+---------------+-------+
| YEAR | FASHION_COLOR | COLOR |
|------+---------------+-------|
| 2017 | red           | red   |
+------+---------------+-------+
Copy

在联接表达式中，将 UDTF 与常量一起使用；请注意，必须使用 WHERE 子句而不是 ON 来表示其他联接条件：

select fav.color as favorite_2017, f.*
    from fashion f JOIN table(favorite_colors(2017)) fav
    where fav.color = f.fashion_color
    order by year;
+---------------+------+---------------+
| FAVORITE_2017 | YEAR | FASHION_COLOR |
|---------------+------+---------------|
| red           | 2017 | red           |
| orange        | 2019 | orange        |
+---------------+------+---------------+
Copy
语言: 中文