SQL UDFs

Ce chapitre couvre des concepts et des détails d’utilisation spécifiques aux UDFs SQL (fonctions définies par l’utilisateur).

Pour une introduction aux UDFs, et pour des informations qui s’appliquent à tous les types d’UDFs, voir Aperçu des UDFs.

Dans ce chapitre :

Utilisation générale

Un UDF SQL évalue une expression arbitraire SQL et retourne les résultats de l’expression.

La définition de la fonction peut être une expression SQL qui retourne soit une valeur scalaire (c’est-à-dire unique), soit, si elle est définie comme fonction de table, un ensemble de lignes. Par exemple, voici un exemple de base d’un UDF scalaire qui calcule l’aire d’un cercle :

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

Sortie :

+---------------------+
| AREA_OF_CIRCLE(1.0) |
|---------------------|
|         3.141592654 |
+---------------------+

L’expression peut être une expression de requête (une expression SELECT). Par exemple :

CREATE FUNCTION profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
    SELECT SUM((retail_price - wholesale_price) * number_sold) FROM purchases
  $$
  ;

Lorsque vous utilisez une expression de requête dans un UDF SQL, vous n’avez pas besoin d’inclure un point-virgule dans le corps de l’UDF pour terminer l’expression de la requête.

Vous ne pouvez inclure qu’une seule expression de requête. L’expression peut inclure UNION [ALL].

Note

Bien que le corps d’un UDF puisse contenir une instruction SELECT complète, il ne peut pas contenir d’instructions DDL ni toute instruction DML autre que SELECT.

Exemples

Exemple(s) UDF scalaire SQL de base

Cet exemple renvoie une approximation codée en dur de la constante mathématique pi.

CREATE FUNCTION pi_udf()
  RETURNS FLOAT
  AS '3.141592654::FLOAT'
  ;
SELECT pi_udf();   

Sortie :

+-------------+
|    PI_UDF() |
|-------------|
| 3.141592654 |
+-------------+

Exemples SQL courants

Cet exemple utilise une expression de requête (c’est-à-dire une instruction SELECT) :

Créez la table et les données à utiliser :

CREATE TABLE purchases (number_sold INTEGER, wholesale_price NUMBER(7,2), retail_price NUMBER(7,2));
INSERT INTO purchases (number_sold, wholesale_price, retail_price) VALUES 
   (3,  10.00,  20.00),
   (5, 100.00, 200.00)
   ;

Créez l’UDF :

CREATE FUNCTION profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
    SELECT SUM((retail_price - wholesale_price) * number_sold) FROM purchases
  $$
  ;

Appelez l’UDF dans une requête :

SELECT profit();

Sortie :

+----------+
| PROFIT() |
|----------|
|   530.00 |
+----------+

Cet exemple utilise l’UDF dans une clause WITH :

CREATE TABLE circles (diameter FLOAT);

INSERT INTO circles (diameter) VALUES
    (2.0),
    (4.0);

CREATE FUNCTION diameter_to_radius(f FLOAT) 
  RETURNS FLOAT
  AS 
  $$ f / 2 $$
  ;
WITH
    radii AS (SELECT diameter_to_radius(diameter) AS radius FROM circles)
  SELECT radius FROM radii
    ORDER BY radius
  ;

Sortie :

+--------+
| RADIUS |
|--------|
|      1 |
|      2 |
+--------+

Cet exemple utilise une requête plus complexe, qui inclut une opération JOIN :

Créez la table et les données à utiliser :

CREATE TABLE orders (product_ID varchar, quantity integer, price numeric(11, 2), buyer_info varchar);
CREATE TABLE inventory (product_ID varchar, quantity integer, price numeric(11, 2), vendor_info varchar);
INSERT INTO inventory (product_ID, quantity, price, vendor_info) VALUES 
  ('X24 Bicycle', 4, 1000.00, 'HelloVelo'),
  ('GreenStar Helmet', 8, 50.00, 'MellowVelo'),
  ('SoundFX', 5, 20.00, 'Annoying FX Corporation');
INSERT INTO orders (product_id, quantity, price, buyer_info) VALUES 
  ('X24 Bicycle', 1, 1500.00, 'Jennifer Juniper'),
  ('GreenStar Helmet', 1, 75.00, 'Donovan Liege'),
  ('GreenStar Helmet', 1, 75.00, 'Montgomery Python');

Créez l’UDF :

CREATE FUNCTION store_profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
  SELECT SUM( (o.price - i.price) * o.quantity) 
    FROM orders AS o, inventory AS i 
    WHERE o.product_id = i.product_id
  $$
  ;

Appelez l’UDF dans une requête :

SELECT store_profit();

Sortie :

+----------------+
| STORE_PROFIT() |
|----------------|
|         550.00 |
+----------------+

La rubrique CREATE FUNCTION contient des exemples supplémentaires.

Utilisation d’UDFs dans différentes clauses

Un UDF scalaire peut être utilisé partout où une expression scalaire peut être utilisée. Par exemple :

-- ----- These examples show a UDF called from different clauses ----- --

select MyFunc(column1) from table1;

select * from table1 where column2 > MyFunc(column1);

Utilisation des variables SQL dans un UDF

Cet exemple montre comment définir une variable SQL et utiliser cette variable dans un UDF :

SET id_threshold = (SELECT COUNT(*) FROM table1) / 2;
CREATE OR REPLACE FUNCTION my_filter_function()
RETURNS TABLE (id int)
AS
$$
SELECT id FROM table1 WHERE id > $id_threshold
$$
;

Dépannage

Message d’erreur : Unsupported subquery type

Cause

Si un UDF contient une expression de requête, l’UDF peut agir comme une sous-requête. Si une sous-requête reçoit un nom de colonne, la sous-requête peut agir comme une sous-requête corrélée. Si une sous-requête corrélée enfreint les règles Snowflake pour les sous-requêtes corrélées, l’utilisateur reçoit le message d’erreur Unsupported subquery type. L’exemple ci-dessous montre une sous-requête corrélée non valide et comment un UDF peut agir comme une sous-requête corrélée non valide similaire.

Créer une paire de tables et y charger des données :

CREATE TABLE stores (store_ID INTEGER, city VARCHAR);
CREATE TABLE employees (employee_ID INTEGER, store_ID INTEGER, sales NUMERIC(10,2), 
    sales_date DATE);
INSERT INTO stores (store_ID, city) VALUES 
    (1, 'Winnipeg'),
    (2, 'Toronto');
INSERT INTO employees (employee_ID, store_ID, sales, sales_date) VALUES 
    (1001, 1, 9000.00, '2020-01-27'),
    (1002, 1, 2000.00, '2020-01-27'),
    (2001, 2, 6000.00, '2020-01-27'),
    (2002, 2, 4000.00, '2020-01-27'),
    (2002, 2, 5000.00, '2020-01-28')
    ;

L’instruction SQL suivante contient une sous-requête corrélée qui ne suit pas les règles Snowflake. Ce code provoque une erreur Unsupported subquery type :

SELECT employee_ID,
       store_ID,
       (SELECT city FROM stores WHERE stores.store_ID = employees.store_ID)
    FROM employees;

Le code ci-dessous crée puis appelle un UDF de sous-requête de manière à créer une sous-requête corrélée similaire à celle illustrée ci-dessus :

CREATE FUNCTION subquery_like_udf(X INT)
    RETURNS VARCHAR
    LANGUAGE SQL
    AS
    $$
        SELECT city FROM stores WHERE stores.store_ID = X
    $$;
SELECT employee_ID, subquery_like_udf_2(employees.store_ID)
    FROM employees;
Solution numéro 1

Si l’UDF contient une expression de requête, appelez l’UDF uniquement de manière cohérente avec les règles des sous-requêtes.

Par exemple, l’instruction suivante appelle l’UDF avec une constante plutôt qu’avec un nom de colonne, donc l’UDF n’agit pas comme une sous-requête corrélée :

SELECT subquery_like_udf(1);
+----------------------+
| SUBQUERY_LIKE_UDF(1) |
|----------------------|
| Winnipeg             |
+----------------------+
Solution numéro 2

Dans certains cas, vous pouvez réécrire l’UDF pour atteindre le même objectif d’une manière différente. Une sous-requête corrélée est autorisée si la sous-requête peut être déterminée statiquement pour renvoyer une ligne. L’UDF suivant utilise une fonction d’agrégation et ne renvoie donc qu’une seule ligne :

CREATE FUNCTION subquery_like_udf_2(X INT)
    RETURNS VARCHAR
    LANGUAGE SQL
    AS
    $$
        SELECT ANY_VALUE(city) FROM stores WHERE stores.store_ID = X
    $$;
SELECT employee_ID, subquery_like_udf_2(employees.store_ID)
    FROM employees;
+-------------+-----------------------------------------+
| EMPLOYEE_ID | SUBQUERY_LIKE_UDF_2(EMPLOYEES.STORE_ID) |
|-------------+-----------------------------------------|
|        1001 | Winnipeg                                |
|        1002 | Winnipeg                                |
|        2001 | Toronto                                 |
|        2002 | Toronto                                 |
|        2002 | Toronto                                 |
+-------------+-----------------------------------------+