CREATE FUNCTION

Permet de créer une fonction scalaire définie par l’utilisateur à l’aide d’une clause SELECT SQL ou d’un programme Python.

Pour plus d’informations et d’exemples, consultez Création de fonctions définies par l'utilisateur.

Privilèges requis

Vous devez être autorisé par l'une des méthodes suivantes pour exécuter CREATE OR REPLACE FUNCTION :

• Pour CREATE FUNCTION :

  • Le super-utilisateur peut utiliser à la fois des langages fiables et non fiables pour créer des fonctions.

  • Les utilisateurs disposant du privilège CREATE [ OR REPLACE ] FUNCTION peuvent créer des fonctions avec des langages fiables.

• Pour REPLACE FUNCTION :

  • Superuser

  • Utilisateurs disposant du privilège CREATE [ OR REPLACE ] FUNCTION

  • Propriétaire de la fonction

Syntaxe

CREATE [ OR REPLACE ] FUNCTION f_function_name
( { [py_arg_name  py_arg_data_type |
sql_arg_data_type } [ , ... ] ] )
RETURNS data_type
{ VOLATILE | STABLE | IMMUTABLE }
AS $$
  { python_program | SELECT_clause }
$$ LANGUAGE { plpythonu | sql }

Paramètres

OR REPLACE

Spécifie que si une fonction ayant le même nom et les mêmes types de données pour les arguments en entrée, ou signature, existe déjà, la fonction existante est remplacée. Vous pouvez uniquement remplacer une fonction par une nouvelle fonction qui définit un ensemble identique de types de données. Vous devez être un super-utilisateur pour remplacer une fonction.

Si vous définissez une fonction avec le même nom qu’une fonction existante, mais avec une signature différente, vous créez une nouvelle fonction. En d’autres termes, le nom de la fonction est surchargé. Pour plus d'informations, consultez Surcharge des noms de fonctions.

f_function_name

Nom de la fonction. Si vous spécifiez un nom de schéma (tel que myschema.myfunction), la fonction est créée à l’aide du schéma spécifié. Sinon, la fonction est créée dans le schéma en cours. Pour plus d’informations sur les noms valides, consultez Noms et identificateurs.

Nous vous recommandons d’utiliser le préfixe f_ pour tous les noms de fonctions UDF. Comme Amazon Redshift réserve le préfixe f_ pour les noms UDF, si vous utilisez le préfixe f_, vous vous assurez que votre nom UDF n’est pas conflit avec un nom existant ou futur de fonction SQL intégré à Amazon Redshift. Pour plus d'informations, consultez Attribution d'un nom aux fonctions UDF.

Vous pouvez définir plus d’une fonction portant le même nom de fonction si les types de données des arguments en entrée sont différents. En d’autres termes, le nom de la fonction est surchargé. Pour plus d'informations, consultez Surcharge des noms de fonctions.

py_arg_name py_arg_data_type | sql_arg_data type

Pour une fonction Python définie par l’utilisateur, une liste des noms d’arguments en entrée et des types de données. Pour une fonction SQL définie par l’utilisateur, une liste des types de données, sans nom d’argument. Dans une fonction Python définie par l’utilisateur, faites référence aux arguments à l’aide des noms d’arguments. Dans une fonction SQL définie par l’utilisateur, faites référence aux arguments à l’aide de $1, $2, et ainsi de suite, en fonction de l’ordre des arguments dans la liste d’arguments.

Pour une fonction SQL définie par l’utilisateur, les types de données d’entrée et de retour peuvent être de n’importe quel type de données Amazon Redshift standard. Pour une fonction Python définie par l’utilisateur, les types de données entrant et de retour peuvent être SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE PRECISION, BOOLEAN, CHAR, VARCHAR, DATE ou TIMESTAMP. En outre, les fonctions Python définies par l’utilisateur (UDF) prennent en charge le type de données ANYELEMENT. Ce type de données est automatiquement converti en un type de données standard en fonction du type de données de l’argument correspondant fourni lors de l’exécution. Si plusieurs arguments utilisent ANYELEMENT, ils se résolvent dans le même type de données lors de l’exécution, en fonction du premier argument ANYELEMENT de la liste. Pour plus d’informations, consultez Types de données de fonctions Python définies par l'utilisateur et Types de données.

Vous pouvez spécifier un maximum de 32 arguments.

RETURNS type_données

Type de données de la valeur renvoyée par la fonction. Le type de données RETURNS peut être n’importe quel type de données Amazon Redshift standard. En outre, les fonctions Python définies par l’utilisateur peuvent utiliser un type de données ANYELEMENT, qui est automatiquement converti en un type de données standard en fonction de l’argument fourni lors de l’exécution. Si vous spécifiez ANYELEMENT pour le type de données retournées, au moins un argument doit utiliser ANYELEMENT. Le type de données retournées correspond au type de données fourni pour l’argument ANYELEMENT lors de l’appel de la fonction. Pour plus d'informations, consultez Types de données de fonctions Python définies par l'utilisateur.

VOLATILE | STABLE | IMMUTABLE

Informe l’optimiseur de requête à propos de l’instabilité de la fonction.

Vous obtiendrez la meilleure optimisation possible si vous qualifiez votre fonction avec la catégorie d’instabilité la plus stricte qui s’y applique. Toutefois, si la catégorie est trop stricte, il existe un risque que l’optimiseur ignore par erreur certains appels, d’où un jeu de résultats incorrect. En matière de rigueur, en commençant par la moins stricte, les catégories d’instabilité sont les suivantes :

• VOLATILE

• STABLE

• IMMUTABLE

VOLATILE

Soit les mêmes arguments, la fonction peut renvoyer des résultats différents sur des appels successifs, y compris pour les lignes d’une même instruction. Comme l’optimiseur de requête ne peut pas effectuer d’hypothèse sur le comportement d’une fonction volatile, une requête qui utilise une fonction volatile doit réévaluer la fonction pour chaque ligne d’entrée.

STABLE

Soit les mêmes arguments, la fonction est garantie renvoyer les mêmes résultats pour toutes les lignes traitées au sein d’une même instruction. La fonction peut renvoyer des résultats différents lorsqu’elle est appelée dans différents instructions. Cette catégorie permet à l’optimiseur d’optimiser plusieurs appels de la fonction au sein d’une même instruction en un seul appel de l’instruction.

IMMUTABLE

Soit les mêmes arguments, la fonction renvoie toujours le même résultat, indéfiniment. Quand une requête appelle une fonction IMMUTABLE avec des arguments constants, l’optimiseur pré-évalue la fonction.

AS $$ instruction $$

Construction qui englobe l’instruction à exécuter. Les mots-clés littéraux AS $$ et $$ sont obligatoires.

Amazon Redshift requiert que vous placiez l’instruction dans votre fonction à l’aide d’un format appelé guillemets dollar. Tout ce qui se trouve dans l’encadrement est passé exactement comme tel. Vous n’avez pas besoin de définir de séquence d’échappement pour les caractères spéciaux, car les contenus de la chaîne sont écrits littéralement.

Avec les guillemets dollar, vous utilisez une paire de symboles dollar ($$) pour marquer le début et la fin de l’instruction à exécuter, comme illustré dans l’exemple suivant.

$$ my statement $$

Le cas échéant, entre les symboles dollar de chaque paire, vous pouvez spécifier une chaîne pour aider à identifier l’instruction. La chaîne que vous utilisez doit être la même au début et à la fin des paires d’encadrement. Cette chaîne est sensible à la casse et suit les mêmes contraintes qu’un identificateur sans guillemets, sauf que celui-ci ne peut pas contenir de symboles dollar. L’exemple suivant utilise la chaîne test.

$test$ my statement $test$

Pour plus d’informations sur les guillemets dollar, consultez relative aux constantes de chaîne avec guillemet dollar dans Lexical Structure dans la documentation PostgreSQL.

python_program

Programme exécutable Python valide qui renvoie une valeur. L’instruction que vous transmettez avec la fonction doit être conforme aux exigences de mise en retrait telles que spécifiées dans le Guide de style pour Python Code sur le site web Python. Pour plus d'informations, consultez Prise en charge du langage Python pour les fonctions UDF.

clause_SQL

Clause SELECT SQL.

La clause SELECT ne peut pas inclure les types de clause suivants :

• FROM

• INTO

• WHERE

• GROUP BY

• ORDER BY

• LIMIT

LANGUAGE { plpythonu | sql }

Pour Python, spécifiez plpythonu. Pour SQL, spécifiez sql. Vous devez avoir l’autorisation pour USAGE ON LANGUAGE pour SQL ou plpythonu. Pour plus d'informations, consultez Privilèges et sécurité des fonctions UDF.

Notes d’utilisation

Fonctions imbriquées

Vous pouvez appeler une autre fonction SQL définie par l’utilisateur à partir d’une fonction SQL définie par l’utilisateur. La fonction imbriquée doit exister lorsque vous exécutez la commande CREATE FUNCTION. Amazon Redshift ne suit pas les dépendances des fonctions UDF, donc si vous supprimez la fonction imbriquée, Amazon Redshift ne renvoie pas d’erreur. Toutefois, la fonction définie par l’utilisateur échoue si la fonction imbriquée n’existe pas. Par exemple, la fonction suivante appelle la fonction f_sql_greater dans la clause SELECT.

create function f_sql_commission (float, float )
  returns float
stable
as $$
  select f_sql_greater ($1, $2)
$$ language sql;

Privilèges et sécurité des fonctions UDF

Pour créer une fonction définie par l’utilisateur, vous devez avoir l’autorisation pour USAGE ON LANGUAGE pour SQL ou plpythonu (Python). Par défaut, USAGE ON LANGUAGE SQL est accordé à PUBLIC. Toutefois, vous devez accorder explicitement USAGE ON LANGUAGE PLPYTHONU à des utilisateurs ou des groupes spécifiques.

Pour révoquer le privilège USAGE pour SQL, révoquez d’abord USAGE de PUBLIC. Ensuite, accordez le privilège USAGE pour SQL uniquement aux utilisateurs ou groupes spécifiques autorisés à créer des fonctions SQL définies par l’utilisateur. L’exemple suivant révoque le privilège USAGE pour SQL de PUBLIC, puis accorde USAGE au groupe d’utilisateurs udf_devs.

revoke usage on language sql from PUBLIC;
grant usage on language sql to group udf_devs;

Pour exécuter une fonction définie par l’utilisateur, vous devez disposer de l’autorisation d’exécution pour chaque fonction. Par défaut, l’autorisation d’exécution pour les nouvelles fonctions définies par l’utilisateur est accordée à PUBLIC. Pour limiter l’utilisation, révoquez l’autorisation d’exécution de PUBLIC pour la fonction. Ensuite, accordez le privilège à des individus ou à des groupes spécifiques.

L’exemple ci-dessous révoque l’autorisation d’exécution de la fonction f_py_greater de PUBLIC, puis accorde USAGE au groupe d’utilisateurs udf_devs.

revoke execute on function f_py_greater(a float, b float) from PUBLIC;
grant execute on function f_py_greater(a float, b float) to group udf_devs;

Les super-utilisateurs ont tous les privilèges par défaut.

Pour plus d’informations, consultez GRANT et REVOKE.

Exemples

Exemple de fonction scalaire Python définie par l’utilisateur

L’exemple suivant crée une fonction Python définie par l’utilisateur qui compare deux entiers et renvoie la valeur la plus grande.

create function f_py_greater (a float, b float)
  returns float
stable
as $$
  if a > b:
    return a
  return b
$$ language plpythonu;

L’exemple suivant interroge la table SALES et appelle la nouvelle fonction f_py_greater pour renvoyer COMMISSION ou 20 % du PRICEPAID, quelle que soit la valeur la plus grande.

select f_py_greater (commission, pricepaid*0.20) from sales;

Exemple de fonction scalaire SQL définie par l’utilisateur

L’exemple suivant crée une fonction qui compare deux nombres et renvoie la valeur la plus grande.

create function f_sql_greater (float, float)
  returns float
stable
as $$
  select case when $1 > $2 then $1
    else $2
  end
$$ language sql;

La requête suivante appelle la nouvelle fonction f_sql_greater pour interroger la table SALES et renvoie COMMISSION ou 20 % du PRICEPAID, quelle que soit la valeur la plus grande.

select f_sql_greater (commission, pricepaid*0.20) from sales;