UDFs tabulares de SQL (UDTFs)¶

O Snowflake oferece suporte para UDFs de SQL que retornam um conjunto de linhas consistindo de 0, 1, ou várias linhas, cada uma com 1 ou mais colunas. Tais UDFs são chamadas de UDFs tabulares, UDFs de tabela ou, mais frequentemente, UDTFs (funções de tabela definidas pelo usuário).

Uma UDTF pode ser acessada na cláusula FROM de uma consulta.

Sintaxe¶

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

Para obter uma descrição mais detalhada da sintaxe geral para todas as UDFs, incluindo UDTFs de SQL, consulte CREATE FUNCTION.

Argumentos¶

name:

Deve ser um nome válido de objeto de banco de dados que segue as regras descritas em: Requisitos para identificadores.

arguments:

Deve ser uma expressão, por exemplo, um nome de coluna, um literal ou uma expressão que possa ser avaliada como um único valor. Normalmente, uma função requer um argumento, que é um nome de coluna. Você pode passar mais de um valor, por exemplo, mais de um nome de coluna, ou um nome de coluna e um ou mais valores literais.

É possível passar uma constante ou nenhum valor. Entretanto, na maioria dos casos, se a entrada for sempre a mesma, a saída será sempre a mesma.

RETURNS TABLE(...)

Especifica que a UDF deve retornar uma tabela. Dentro dos parênteses, especifique pares de nomes e tipos para colunas (como descrito abaixo) a serem incluídos na tabela retornada.

output_col_name:: O nome de uma coluna de saída a ser incluída na tabela retornada. Deve haver pelo menos uma coluna de saída.
output_col_type:: O tipo de dados da coluna de saída.

sql_expression:

Uma expressão ou instrução válida de SQL que retorna uma tabela com zero ou mais linhas, cada uma delas com uma ou mais colunas. As saídas devem corresponder ao número e aos tipos de dados especificados na cláusula RETURNS.

Notas de uso¶

O corpo principal (ou seja, a “definição”) de uma UDTF de SQL deve ser uma expressão SELECT.
Embora os delimitadores em torno da sql_expression geralmente sejam aspas simples, você pode usar um par de cifrões $$ como delimitador. O delimitador de fechamento deve corresponder ao delimitador de abertura. É conveniente usar um par de cifrões quando a sql_expression contém aspas simples. Um exemplo usando um par de cifrões está incluído na seção Exemplos abaixo.

Se o delimitador for aspas simples e o corpo contiver uma única citação, você poderá escapar a citação única no corpo usando o caractere de barra invertida \ como caractere de escape. Um exemplo está incluído na seção Exemplos abaixo.
As colunas definidas na UDTF podem aparecer em qualquer lugar onde uma coluna de tabela normal pode ser usada.
Os tipos de retorno especificados na cláusula RETURNS determinam os nomes e tipos das colunas nos resultados tabulares e devem corresponder aos tipos das expressões nas posições correspondentes da instrução SELECT no corpo da função.
Ao chamar uma UDTF, você deve incluir o nome e os argumentos da UDTF dentro dos parênteses depois da palavra-chave TABLE. Para obter mais informações, consulte Como chamar uma UDTF de SQL.

Nota

Funções tabulares (UDTFs) têm um limite de 500 argumentos de entrada e 500 colunas de saída.

Como chamar uma UDTF de SQL¶

Ao chamar uma UDTF na cláusula FROM de uma consulta, especifique o nome e os argumentos da UDTF dentro dos parênteses que seguem a palavra-chave TABLE.

Em outras palavras, use um formulário como o seguinte para a palavra-chave TABLE quando chamar uma UDTF:

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

Exemplos de UDTFs de SQL¶

Exemplos básicos¶

Esse é um exemplo artificialmente simples de uma UDTF com uma saída embutida no código. Ele também ilustra o uso de $$ como delimitador:

CREATE FUNCTION t()
    RETURNS TABLE(msg VARCHAR)
    AS
    $$
        SELECT 'Hello'
        UNION
        SELECT 'World'
    $$;

SELECT msg 
    FROM TABLE(t())
    ORDER BY msg;
+-------+
| MSG   |
|-------|
| Hello |
| World |
+-------+

Este exemplo é semelhante ao exemplo anterior, mas usa aspas simples como delimitador e usa o caractere de escape \ para escapar as aspas simples no corpo da UDTF:

CREATE FUNCTION t()
    RETURNS TABLE(msg VARCHAR)
    AS
    '
        SELECT \'Hello\'
        UNION
        SELECT \'World\'
    ';

SELECT msg
    FROM TABLE(t())
    ORDER BY msg;
+-------+
| MSG   |
|-------|
| Hello |
| World |
+-------+

Este é outro exemplo básico de uma UDTF. Ele consulta uma tabela e retorna duas das colunas dessa tabela:

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);

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
    $$
    ;

select product_id, quantity_sold
    from table(orders_for_product('compostable bags'))
    order by product_id;
+------------------+---------------+
| PRODUCT_ID       | QUANTITY_SOLD |
|------------------+---------------|
| compostable bags |       2000.00 |
+------------------+---------------+

Essa mesma funcionalidade também pode ser implementada usando uma exibição.

Exemplos com junções¶

Criar e usar uma UDTF de SQL que retorna informações do país (COUNTRY_CODE e COUNTRY_NAME) para uma ID de usuário especificada:

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'),
    ('ES', 'SPAIN');

create or replace table user_addresses (user_ID integer, country_code char(2));
insert into user_addresses (user_id, country_code) values 
    (100, 'ES'),
    (123, 'FR'),
    (123, 'US');

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';

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 |
+--------------+---------------+

Criar uma UDTF de SQL que retorna a cor favorita para um ano específico:

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;

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';

Usar a UDTF em uma consulta:

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

Usar a UDTF em uma junção com outra tabela; observe que a coluna de junção da tabela é passada como argumento para a função.

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 |
+------+--------+

Usar uma cláusula WHERE, em vez de ON, para predicatos adicionais:

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

Usar a UDTF com uma constante em uma expressão de junção; observe que uma cláusula WHERE, no lugar de ON, deve ser usada para condições adicionais de junção:

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        |
+---------------+------+---------------+