Catégories :

DDL pour les fonctions définies par l’utilisateur, les fonctions externes et les procédures stockées

CREATE FUNCTION

Crée une nouvelle UDF (fonction définie par l’utilisateur). Une UDF peut contenir soit une expression SQL soit un code JavaScript, et peut retourner des résultats scalaires ou tabulaires.

Voir aussi :

ALTER FUNCTION , SHOW USER FUNCTIONS

Syntaxe

CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
  [ [ NOT ] NULL ]
  [ LANGUAGE JAVASCRIPT ]
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  AS '<function_definition>'

Paramètres requis

nom ( [ nom_argument type_données_argument ] [ , ... ] )

Spécifie l’identificateur (et éventuellement un ou plusieurs arguments/entrées) pour l’UDF. L’identificateur n’a pas besoin d’être unique pour le schéma dans lequel l’UDF est créé parce que les UDFs est identifiés et résolus par leurs noms et types d’arguments.

Toutefois, l’identificateur doit commencer par un caractère alphabétique et ne peut pas contenir d’espaces ou de caractères spéciaux à moins que toute la chaîne d’identificateur soit délimitée par des guillemets doubles (p. ex. "My object"). Les identificateurs entre guillemets doubles sont également sensibles à la casse.

Pour plus de détails, voir Exigences relatives à l’identificateur.

RETURNS ...

Spécifie les résultats retournés par l’UDF, ce qui détermine le type UDF :

  • type_données_résultat : Crée un UDF scalaire qui retourne une valeur unique avec le type de données spécifié.

  • TABLE ( nom_colonne type_données_colonne , ... ) Crée un UDF de table qui retourne des résultats tabulaires avec la ou les colonnes et le ou les types de colonnes spécifiés.

AS définition_fonction

Définit le code exécuté par l’UDF. Le contenu dépend du type de l’UDF créé :

  • SQL UDF: Toute expression SQL valide. Pour plus de détails sur les UDFs SQL, y compris des exemples, voir Aperçu des UDFs.

  • JavaScript UDF: Tout JavaScript valide. Pour plus de détails sur les UDFs JavaScript, y compris des exemples, voir JavaScript UDFs.

Pour plus de détails, voir Notes sur l’utilisation (dans ce chapitre).

Paramètres facultatifs

SECURE

Précise que la fonction est sécurisée. Pour plus d’informations sur les fonctions sécurisées, voir UDFs sécurisés.

LANGUAGE JAVASCRIPT

Spécifie que définition_fonction contient du code JavaScript ; sinon, définition_fonction doit contenir une expression SQL.

Par défaut : Aucune valeur (c.-à-d. un UDF SQL est créé)

CALLED ON NULL INPUT ou . RETURNS NULL ON NULL INPUT | STRICT

Spécifie le comportement de l’UDF lorsqu’il est appelé avec des entrées « null ». Contrairement aux fonctions définies par le système, qui retournent toujours la valeur « null » lorsqu’une entrée est nulle, les UDFs peuvent gérer les entrées null, retournant des valeurs non nulles même lorsqu’une entrée est null :

  • CALLED ON NULL INPUT appellera toujours l’UDF avec des entrées null. Il appartient à l’UDF de traiter ces valeurs de manière appropriée.

  • RETURNS NULL ON NULL INPUT (ou son synonyme STRICT) n’appellera pas l’UDF si une entrée est null. En revanche, une valeur null sera toujours retournée pour cette ligne. Notez que l’UDF peut toujours retourner une valeur null pour les entrées non null.

Par défaut : CALLED ON NULL INPUT

VOLATILE | IMMUTABLE

Spécifie le comportement de l’UDF lors de l’affichage de résultats :

  • VOLATILE: l’UDF peut afficher des valeurs différentes pour différentes lignes, même pour la même entrée (par exemple, en raison du non-déterminisme et du statut).

  • IMMUTABLE: L’UDF suppose que la fonction, lorsqu’elle est appelée avec les mêmes entrées, renvoie toujours le même résultat. Cette garantie n’est pas vérifiée. Spécifier IMMUTABLE pour un UDF qui retourne des valeurs différentes pour la même entrée aura pour résultat un comportement indéfini.

Par défaut : VOLATILE

COMMENT = 'litéral_chaine'

Spécifie un commentaire pour l’UDF, qui est affiché dans la colonne DESCRIPTION de la sortie SHOW FUNCTIONS et de la sortie SHOW USER FUNCTIONS.

Par défaut : user-defined function

Notes sur l’utilisation

  • Vous pouvez utiliser une séquence de caractères autre qu’un guillemet simple pour délimiter le corps de l’UDF. Par exemple, vous pouvez utiliser une séquence de deux signes dollar (« $$ »). Certains des exemples ci-dessous utilisent « $$ » comme délimiteur.

  • Si le délimiteur du corps de la fonction est le caractère guillemet simple, tous les guillemets simples dans définition_fonction (par exemple, les chaînes de caractères) doivent être échappés par des guillemets simples.

  • définition_fonction a des restrictions de taille. La taille maximale permise est sujette à changement.

  • Snowflake ne valide pas le code JavaScript au moment de la création de l’UDF (autrement dit, la création de l’UDF réussit, que le code soit valide ou non). Si le code n’est pas valide, des erreurs s’afficheront lorsque l’UDF sera appelé au moment de la requête. Pour plus de détails, voir JavaScript UDFs.

  • La clause facultative [ [ NOT ] NULL ] indique si la fonction peut renvoyer des valeurs NULL ou doit uniquement renvoyer des valeurs NON-NULL. La valeur par défaut est NULL, c’est-à-dire que la fonction peut renvoyer NULL.

    Note

    Actuellement, la clause NOT NULL n’est appliquée que pour des UDFs JavaScript et non des UDFs SQL. Les UDFs SQL déclarées comme NOT NULL peuvent renvoyer des valeurs NULL. Snowflake recommande aux clients d’éviter d’utiliser NOT NULL pour des UDFs SQL à moins que le code de la fonction ne soit écrit pour garantir que les valeurs NULL ne sont jamais renvoyées.

Exemples

Créer un UDF scalaire SQL qui renvoie une approximation codée en dur de la constante mathématique pi :

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

Créer un UDF de tableau SQL simple qui renvoie des valeurs codées en dur :

CREATE FUNCTION simple_table_function ()
  RETURNS TABLE (x INTEGER, y INTEGER)
  AS
  $$
    SELECT 1, 2
    UNION ALL
    SELECT 3, 4
  $$
  ;

SELECT * FROM TABLE(simple_table_function());

Sortie :

+---+---+
| X | Y |
|---+---|
| 1 | 2 |
| 3 | 4 |
+---+---+

Créer un UDF de table SQL nommé get_countries_for_user qui retourne les résultats d’une requête :

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

Créer un UDF JavaScript nommé js_factorial :

CREATE OR REPLACE FUNCTION js_factorial(d double)
  RETURNS double
  LANGUAGE JAVASCRIPT
  STRICT
  AS '
  if (D <= 0) {
    return 1;
  } else {
    var result = 1;
    for (var i = 2; i <= D; i++) {
      result = result * i;
    }
    return result;
  }
  ';