- 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 :
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 sont 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 une 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éé :
UDF SQL : toute expression SQL valide. Pour plus de détails sur les UDFs SQL, y compris des exemples, voir Aperçu des UDFs.
UDF JavaScript : 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. qu’une UDF SQL est créée)
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 synonymeSTRICT
) 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écifierIMMUTABLE
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¶
Les délimiteurs autour de la
définition_fonction
peuvent être des guillemets simples ou une paire de signes dollar. L’utilisation de$$
comme délimiteur facilite l’écriture des procédures stockées contenant des guillemets simples. 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 commeNOT NULL
peuvent renvoyer des valeurs NULL. Snowflake recommande d’éviterNOT NULL
pour des UDFs SQL à moins que le code de la fonction ne soit écrit pour garantir que les valeurs NULL ne soient 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 renvoie 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; } ';