Aperçu des UDFs¶

SQL¶

Une UDF SQL peut être soit scalaire soit tabulaire.

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

L’expression peut être soit une expression générale, soit une expression de requête :

• Cette UDF utilise l’expression générale pi() * radius * radius :

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

• Cette UDF utilise une expression de requête :

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

Pour les UDFs non tabulaires, l’expression de la requête doit garantir le retour d’au plus une ligne, contenant une colonne.

L’expression définissant une UDF peut faire référence aux arguments d’entrée de la fonction, comme le montrent les exemples ci-dessus.

L’expression définissant une UDF peut faire référence à des objets de base de données tels que des tables, des vues et des séquences, comme le montrent les exemples ci-dessus. Toutefois, les restrictions suivantes s’appliquent :

• L’UDF propriétaire doit avoir des privilèges appropriés sur tous les objets de la base de données auxquels l’UDF accède.

• Il n’est pas possible de faire référence à des objets de la base de données à l’aide de SQL dynamique. Par exemple, l’instruction suivante échoue, car IDENTIFIER(table_name_parameter) n’est pas autorisé :

  CREATE OR REPLACE FUNCTION profit2(table_name_parameter VARCHAR)
    RETURNS NUMERIC(11, 2)
    AS
    $$
      SELECT SUM((retail_price - wholesale_price) * number_sold)
          FROM IDENTIFIER(table_name_parameter)
    $$
    ;
  

L’expression définissant une UDF ne doit pas contenir de point-virgule comme terminateur d’instruction. Par exemple, notez que l’instruction SELECT de l’UDF d’expression de requête ci-dessus ne se termine pas par un point-virgule.

Une expression de définition d’UDF SQL peut faire référence à d’autres fonctions définies par l’utilisateur. Cependant, elle ne peut faire référence récursivement à elle-même, que ce soit directement ou indirectement (c’est-à-dire par l’intermédiaire d’une autre fonction faisant appel à elle).

Pour plus d’informations, voir Présentation des UDFs SQL.

JavaScript¶

Les UDFs JavaScript vous permettent de manipuler des données à l’aide du langage de programmation et de l’environnement d’exécution JavaScript. Les UDFs JavaScript sont créés de la même manière que les UDFs SQL, mais avec le paramètre LANGUAGE réglé sur JAVASCRIPT.

Comme les UDFs SQL, les UDFs JavaScript peuvent retourner un résultat scalaire ou tabulaire, selon la façon dont l’UDF est défini.

Une expression de définition d’UDF JavaScript peut se référer de manière récursive à elle-même, mais ne peut pas faire référence à d’autres fonctions définies par l’utilisateur.

Pour plus d’informations, voir Présentation des UDFs JavaScript.

Java¶

Les UDFs Java vous permettent de manipuler des données à l’aide du langage de programmation Java et de l’environnement d’exécution.

Comme les UDFs SQL et JavaScript, les UDFs peuvent retourner un résultat scalaire ou tabulaire, selon la façon dont l’UDF est définie.

Vous pouvez créer des UDFs Java de deux façons :

• En ligne : vous tapez le code Java dans le cadre de la commande CREATE FUNCTION.

• Fichier JAR : vous spécifiez l’emplacement d’un fichier JAR déjà compilé dans le cadre de la commande CREATE FUNCTION.

Pour plus d’informations, voir Introduction aux UDFs Java.

Python¶

Les UDFs Python vous permettent de manipuler des données à l’aide du langage de programmation et de l’environnement d’exécution Python.

Comme les UDFs Java, les UDFs Python peuvent retourner un résultat scalaire ou tabulaire, selon la façon dont l’UDF est défini.

Vous pouvez créer des UDFs Python de deux façons :

• En ligne : vous tapez le code Python dans le cadre de la commande CREATE FUNCTION.

• Comme un module copié sur une zone de préparation : vous spécifiez l’emplacement du fichier du module dans le cadre de la commande CREATE FUNCTION.

Pour plus d’informations, voir Introduction aux UDFs Python.

SELECT temporary_db_qualified_names_test.temporary_schema_1.udf_pi();

Conflits avec les fonctions définies par le système¶

Pour éviter les conflits lors de l’appel de fonctions, Snowflake ne permet pas de créer des UDFs avec le même nom que les fonctions définies par le système.

Surcharge de noms d’UDF¶

Snowflake prend en charge la surcharge de noms UDF SQL. Plusieurs UDFs SQL dans le même schéma peuvent porter le même nom, tant que leurs signatures d’arguments diffèrent, soit par le nombre d’arguments soit par leur type. Lorsqu’un UDF surchargé est appelé, Snowflake vérifie les arguments et appelle la fonction appropriée.

Prenons les exemples suivants, qui créent deux UDFs SQL nommés add5 :

CREATE OR REPLACE FUNCTION add5 (n number)
  RETURNS number
  AS 'n + 5';

CREATE OR REPLACE FUNCTION add5 (s string)
  RETURNS string
  AS 's || ''5''';

Important

Dans la deuxième fonction ADD5, les guillemets simples sont utilisés pour effectuer un échappement du littéral de chaîne '5'. Les guillemets simples utilisés dans une définition UDF doivent être échappés par des guillemets simples.

Si add5 est appelé avec un argument numérique, alors la première implémentation est choisie, tandis qu’un argument de type chaîne utilise la seconde implémentation. Si l’argument n’est ni un nombre ni une chaîne, alors l’implémentation dépend des règles implicites de conversion de types de Snowflake. Par exemple, un argument de type date serait converti en une chaîne, et l’implémentation de la chaîne serait sélectionnée, puisque la conversion de DATE à NUMBER n’est pas prise en charge.

Par exemple :

select add5(1);

+---------+
| ADD5(1) |
|---------|
|       6 |
+---------+

select add5('1');

+-----------+
| ADD5('1') |
|-----------|
| 15        |
+-----------+

select add5('hello');

+---------------+
| ADD5('HELLO') |
|---------------|
| hello5        |
+---------------+

select add5(to_date('2014-01-01'));

+-----------------------------+
| ADD5(TO_DATE('2014-01-01')) |
|-----------------------------|
| 2014-01-015                 |
+-----------------------------+

Soyez prudent lorsque vous utilisez la surcharge. La combinaison de la conversion de type automatique et de la surcharge peut favoriser la situation dans laquelle une erreur utilisateur mineure provoquerait des résultats inattendus. Pour un exemple, voir Surcharge des noms de fonction.

Surcharge des noms de fonction¶

Comme décrit dans Surcharge de noms d’UDF (dans ce chapitre), vous pouvez surcharger les noms de fonction.

L’exemple suivant de surcharge montre comment la combinaison de la surcharge et de la conversion de type automatique peut favoriser l’obtention de résultats inattendus :

Créez une fonction qui prend un paramètre FLOAT :

CREATE FUNCTION add_pi(PARAM_1 FLOAT)
    RETURNS FLOAT
    LANGUAGE SQL
    AS $$
        PARAM_1 + 3.1415926::FLOAT
    $$;

Appelez la fonction deux fois. La première fois, transmettez un FLOAT. La deuxième fois, transmettez un VARCHAR. Le VARCHAR est converti en un FLOAT et la sortie de chaque appel est identique :

SELECT add_pi(1.0), add_pi('1.0');
+-------------+---------------+
| ADD_PI(1.0) | ADD_PI('1.0') |
|-------------+---------------|
|   4.1415926 |     4.1415926 |
+-------------+---------------+

Maintenant, créez une fonction surchargée qui prend un paramètre VARCHAR :

CREATE FUNCTION add_pi(PARAM_1 VARCHAR)
    RETURNS VARCHAR
    LANGUAGE SQL
    AS $$
        PARAM_1 || ' + 3.1415926'
    $$;

Utilisez maintenant exactement les mêmes CALLs qu’avant. Notez la différence de sortie entre ces deux CALLs et les deux CALLs précédents.

SELECT add_pi(1.0), add_pi('1.0');
+-------------+-----------------+
| ADD_PI(1.0) | ADD_PI('1.0')   |
|-------------+-----------------|
|   4.1415926 | 1.0 + 3.1415926 |
+-------------+-----------------+