UDFs SQL tabulaires (UDTFs)

Snowflake prend en charge les UDFs SQL qui renvoient un ensemble de lignes, composé de 0, 1, ou de plusieurs lignes, chacune d’entre elles ayant au moins une colonne. Ces UDFs sont appelées UDFs tabulaires, UDFs de table ou, le plus souvent, UDTFs (fonctions de table définies par l’utilisateur).

On peut accéder à une UDTF dans la clause FROM d’une requête.

Dans ce chapitre :

Syntaxe

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

Pour une description plus détaillée de la syntaxe générale pour tous les UDFs, y compris les UDTFs SQL, voir CREATE FUNCTION.

Arguments

name :

Ce nom d’objet de base de données doit être valide et respecter les règles décrites à l’adresse : Exigences relatives à l’identificateur.

arguments :

Il doit s’agir d’une expression, par exemple un nom de colonne, un littéral ou une expression pouvant correspondre à une valeur unique. En règle générale, une fonction prend un argument, qui est un nom de colonne. Vous pouvez transmettre plusieurs valeurs, par exemple plusieurs noms de colonne ou un nom de colonne et une ou plusieurs valeurs littérales.

Il est possible de transmettre une valeur constante ou aucune. Cependant, dans la plupart des cas, si l’entrée est la même à chaque fois, la sortie est la même à chaque fois.

RETURNS TABLE(...)

Spécifie que l’UDF doit retourner une table. À l’intérieur des parenthèses, spécifiez les paires nom et type des colonnes (comme décrit ci-dessous) à inclure dans la table renvoyée.

output_col_name :

Le nom d’une colonne de sortie à inclure dans la table retournée. Il doit y avoir au moins une colonne de sortie.

output_col_type :

Le type de données de la colonne de sortie.

sql_expression :

Une expression ou une instruction SQL valide qui renvoient une table avec zéro ou plusieurs lignes, chacune contenant une ou plusieurs colonnes. Les sorties doivent correspondre au nombre et aux types de données spécifiés dans la clause RETURNS.

Notes sur l’utilisation

  • Le corps principal (alias « définition ») d’un UDTF SQL doit être une expression SELECT.

  • Bien que les délimiteurs autour de l’expression sql_expression soient généralement des guillemets simples, vous pouvez utiliser une paire de signes de dollar $$ comme délimiteur. Le délimiteur de fermeture doit correspondre au délimiteur d’ouverture. Une paire de signes dollar est pratique lorsque sql_expression contient des guillemets simples. Un exemple utilisant des paires de signes dollar est inclus dans la section Exemples ci-dessous.

    Si le délimiteur est un guillemet simple et que le corps contient un guillemet simple, vous pouvez échapper le guillemet simple dans le corps en utilisant le caractère de barre oblique inversée \ comme caractère d’échappement. Un exemple est inclus dans la section Exemples ci-dessous.

  • Les colonnes définies dans l’UDTF peuvent apparaître partout où une colonne de table normale peut être utilisée.

  • Les types de retours spécifiés dans la clause RETURNS déterminent les noms et les types des colonnes dans les résultats tabulaires et doivent correspondre aux types des expressions dans les positions correspondantes de l’instruction SELECT dans le corps de fonction.

  • Lorsque vous appelez une UDTF, vous devez inclure le nom de l’UDTF et les arguments entre parenthèses après le mot-clé TABLE. Pour plus d’informations, voir Appel d’une UDTF SQL.

Note

Les fonctions tabulaires (UDTFs) ont une limite de 500 arguments d’entrée et 500 colonnes de sortie.

Appel d’une UDTF SQL

Lorsque vous appelez une UDTF dans la clause FROM d’une requête, spécifiez le nom et les arguments de l’UDTF à l’intérieur des parenthèses qui suivent le mot-clé TABLE.

En d’autres termes, utilisez une forme telle que la suivante pour le mot-clé TABLE lorsque vous appelez une UDTF :

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

Exemples d’UDTFs SQL

Exemples de base

Ceci est un exemple artificiellement simple d’un UDTF, qui code en dur la sortie. Ceci illustre également l’utilisation de $$ comme délimiteur :

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

Cet exemple est similaire à l’exemple précédent, mais il utilise des guillemets simples comme délimiteur, et utilise le caractère d’échappement \ pour échapper les guillemets simples dans le corps de l’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

Ceci est un autre exemple de base d’un UDTF. Il interroge une table et retourne deux des colonnes de cette table :

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

Cette même fonctionnalité peut également être implémentée à l’aide d’une vue.

Exemples avec jointures

Créer et utiliser un UDTF SQL qui renvoie des informations de pays (COUNTRY_CODE et COUNTRY_NAME) pour l’ID d’un utilisateur spécifié :

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

Créer un UDTF SQL qui renvoie la couleur préférée d’une année spécifiée :

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

Utilisez l’UDTF dans une requête :

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

Utilisez l’UDTF dans une jointure avec une autre table ; notez que la colonne de jointure de la table est transmise comme argument à la fonction.

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

Utilisez une clause WHERE, plutôt que ON, pour des prédicats supplémentaires :

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

Utilisez l’UDTF avec une constante dans une expression de jointure ; notez qu’une clause WHERE, plutôt que ON, doit être utilisée pour des conditions de jointure supplémentaires :

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
Langage: Français