Fonctions de table définies par l’utilisateur (UDTFs SQL)

Un UDTF renvoie un ensemble de lignes au lieu d’une seule valeur scalaire, et est accessible dans la clause FROM d’une requête. Snowflake prend en charge à la fois les UDTFs JavaScript et SQL. Cette chapitre traite des UDTFs SQL.

Pour plus d’informations sur les UDTFs JavaScript, voir Fonctions de table définies par l’utilisateur (UDTFs JavaScript).

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

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

nom :

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.

nom_col_sortie :

Le nom de la colonne de sortie. Il doit y avoir au moins une colonne de sortie.

type_col_sortie :

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

expression_sql :

Il doit s’agir d’une expression ou d’une instruction SQL valide qui renvoie une table, c’est-à-dire 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.

  • Les types de retour 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.

  • Bien que les délimiteurs entourant expression_sql soient généralement des guillemets simples, vous pouvez utiliser d’autres délimiteurs, tels qu’une chaîne de deux signes dollar ou plus (« $$ »). Le délimiteur de fermeture doit correspondre au délimiteur d’ouverture. Une chaîne de signes dollar est pratique lorsque expression_sql contient des guillemets simples. Un exemple utilisant des signes de double dollar est inclus dans la section Exemples ci-dessous.

  • Un UDTF SQL est généralement appelé dans la clause FROM d’une requête, en utilisant le mot clé TABLE. Les colonnes définies dans l’UDTF peuvent apparaître partout où une colonne de table normale peut être utilisée.

Exemples d’UDTFs SQL

Exemples de base

Ceci est un exemple artificiellement simple d’un UDTF ; ceci code en dur la sortie :

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

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

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

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

Utilisez l’UDTF dans une requête :

select * from table(favorite_colors(2017));
+--------+
| COLOR  |
|--------|
| red    |
| orange |
+--------+

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;
+------+--------+
| YEAR | COLOR  |
|------+--------|
| 2017 | red    |
| 2017 | orange |
| 2018 | blue   |
| 2018 | violet |
+------+--------+

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

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;
+---------------+------+---------------+
| FAVORITE_2017 | YEAR | FASHION_COLOR |
|---------------+------+---------------|
| red           | 2017 | red           |
| orange        | 2019 | orange        |
+---------------+------+---------------+