CREATE FUNCTION

CREATE FUNCTIONdéfinit une nouvelle fonction.

Important

Dans Amazon Aurora DSQL, CREATE FUNCTION seules les SQL-language fonctions (LANGUAGE sql) sont prises en charge.

Syntaxe prise en charge

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE sql
    | { IMMUTABLE | STABLE | VOLATILE }
    | { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
    | { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST execution_cost
    | ROWS result_rows
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | RETURN expression
    | BEGIN ATOMIC statement; [ statement; ... ] END
  } ...

where LANGUAGE must be sql

Description

CREATE FUNCTIONdéfinit une nouvelle fonction. CREATE OR REPLACE FUNCTIONcréera une nouvelle fonction ou remplacera une définition existante. Pour pouvoir définir une fonction, vous devez avoir le USAGE privilège sur le langage.

Si un nom de schéma est inclus, la fonction est créée dans le schéma spécifié. Sinon, la fonction est créée dans le schéma en cours. Le nom de la nouvelle fonction ne doit correspondre à aucune fonction existante avec les mêmes types d'arguments d'entrée dans le même schéma. Cependant, les fonctions ayant des types d'arguments différents peuvent partager un nom (c'est ce qu'on appelle la surcharge).

Pour remplacer la définition actuelle d'une fonction existante, utilisezCREATE OR REPLACE FUNCTION. Vous ne pouvez pas modifier le nom ou le type d'argument d'une fonction de cette façon (cela créerait en fait une nouvelle fonction distincte). De plus, vous ne pouvez pas l'utiliser CREATE OR REPLACE FUNCTION pour modifier le type de retour d'une fonction existante. Pour ce faire, vous devez supprimer et recréer la fonction.

Lorsqu'il CREATE OR REPLACE FUNCTION est utilisé pour remplacer une fonction existante, le propriétaire et les autorisations de la fonction ne changent pas. Toutes les autres propriétés de fonction se voient attribuer les valeurs spécifiées ou implicites dans la commande. Vous devez être propriétaire de la fonction pour la remplacer (cela inclut le fait d'être membre du rôle propriétaire).

Si vous supprimez puis recréez une fonction, la nouvelle fonction n'est pas la même entité que l'ancienne ; vous devrez supprimer les règles, les vues, etc. existantes qui font référence à l'ancienne fonction. CREATE OR REPLACE FUNCTIONÀ utiliser pour modifier la définition d'une fonction sans casser les objets qui font référence à la fonction.

Vous devenez propriétaire d'une fonction que vous avez créée.

Pour créer une fonction, vous devez disposer de USAGE privilèges sur les types d'arguments et sur le type de retour.

Une SQL fonction encapsule une ou plusieurs SQL instructions dans une unité appelable réutilisable. Utilisez les fonctions pour centraliser les expressions ou les calculs courants, appliquer une logique cohérente à plusieurs requêtes et simplifier le code de l'application en appelant une seule fonction au lieu de la répéterSQL.

Parameters

name

Le nom (éventuellement qualifié par un schéma) de la fonction à créer.

argmode

Le mode d'un argument : INOUT,INOUT, ouVARIADIC. En cas d'omission, la valeur par défaut estIN. VARIADICdoit être le dernier paramètre d'entrée ; seuls OUT les arguments peuvent le suivre. OUTet INOUT les arguments ne peuvent pas être combinés avec la RETURNS TABLE notation.

argname

Le nom d'un argument. Dans une fonction SQL, vous pouvez faire référence à un argument par son nom dans le corps de la fonction. Le nom d'un INOUT argument OUT or est également significatif, car il définit le nom de colonne correspondant dans le type de ligne de résultat.

argtype

Type de données d'un argument. Il peut s'agir de n'importe quel type de DSQL-supported base, de baie ou de domaine Aurora. Pour plus d’informations, consultez Types de données pris en charge dans Aurora DSQL. Vous pouvez également référencer le type d'une colonne de table existante à l'aide de la %TYPE notation.

default_expr

Expression à utiliser comme valeur par défaut si l'argument n'est pas spécifié. L'expression doit être coercitive par rapport au type d'argument du paramètre. Seuls IN les INOUT arguments peuvent avoir une valeur par défaut. Chaque argument après un argument avec une valeur par défaut doit également avoir une valeur par défaut.

rettype

Type de données de retour. Il peut s'agir de n'importe quel type de DSQL-supported base, de baie ou de domaine Aurora. Pour plus d’informations, consultez Types de données pris en charge dans Aurora DSQL. Pour renvoyer un ensemble de lignes, utilisez SETOF rettype ouRETURNS TABLE. Si la fonction n'est pas censée renvoyer de valeur, void spécifiez-la comme type de retour. Lorsqu'il existe OUT des INOUT paramètres, la RETURNS clause peut être omise et la fonction renvoie un record ou le type de ligne correspondant.

RETURNS TABLE ( column_name column_type [, ...] )

Spécifie que la fonction renvoie un ensemble de lignes avec les colonnes nommées et leurs types. Cela revient à déclarer un ou plusieurs OUT paramètres et à les spécifier SETOF comme type de retour.

LANGUAGE sql

Langage utilisé pour implémenter la fonction. Dans Aurora DSQL, cela doit être sql le cas. Si vous omettez la LANGUAGE clause et que vous en fournissez un BEGIN ATOMIC ou RETURN le corps, sql c'est supposé. Une constante de chaîne body (AS 'definition') nécessite une LANGUAGE sql clause explicite ; son omission échoue avec l'erreur. no language specified

IMMUTABLE / STABLE / VOLATILE

Ces attributs informent l'optimiseur de requêtes du comportement de la fonction. Vous pouvez en spécifier au plus un. Si vous omettez les trois, la fonction prend par défaut la valeur. VOLATILE

• IMMUTABLEindique que la fonction ne peut pas modifier la base de données et renvoie toujours le même résultat lorsqu'on lui donne les mêmes valeurs d'arguments.

• STABLEindique que la fonction ne peut pas modifier la base de données. Au cours d'une analyse de table unique, il renvoie systématiquement le même résultat pour les mêmes valeurs d'arguments, mais le résultat peut changer d'une instruction SQL à l'autre.

• VOLATILEindique que la valeur de la fonction peut changer même au cours d'une seule analyse de table, de sorte qu'aucune optimisation ne peut être effectuée.

CALLED ON NULL INPUT / RETURNS NULL ON NULL INPUT / STRICT

CALLED ON NULL INPUT(valeur par défaut) indique que la fonction est appelée normalement lorsque certains de ses arguments sont nuls. RETURNS NULL ON NULL INPUTou STRICT indique que la fonction renvoie toujours null lorsque l'un de ses arguments est nul. Si cela est spécifié, Aurora DSQL n'exécute pas la fonction lorsqu'un argument est nul ; au lieu de cela, la fonction renvoie automatiquement null.

[ EXTERNAL ] SECURITY INVOKER / [ EXTERNAL ] SECURITY DEFINER

SECURITY INVOKER(valeur par défaut) indique que la fonction doit être exécutée avec les privilèges de l'utilisateur qui l'appelle. SECURITY DEFINERindique que la fonction doit être exécutée avec les privilèges de l'utilisateur qui la possède. Le mot clé EXTERNAL est autorisé pour la conformité SQL, mais il est facultatif.

PARALLEL { UNSAFE | RESTRICTED | SAFE }

Aurora DSQL accepte et stocke le PARALLEL marquage. Toutefois, ce marquage n'a aucune incidence sur l'exécution des requêtes.

COST execution_cost

Nombre positif indiquant le coût d'exécution estimé de la fonction. Si la fonction renvoie un ensemble, cette valeur est le coût estimé par ligne renvoyée. Le planificateur de requêtes utilise cette estimation lorsqu'il n'intègre pas la fonction.

ROWS result_rows

Nombre positif indiquant le nombre estimé de lignes que le planificateur de requêtes devrait s'attendre à ce que la fonction renvoie. Cela n'est autorisé que lorsque la fonction est déclarée pour renvoyer un ensemble. L'hypothèse par défaut est de 1 000 lignes.

SET configuration_parameter { TO value | = value | FROM CURRENT }

La SET clause fait en sorte que le paramètre de configuration spécifié soit défini à la valeur spécifiée lorsque la fonction est entrée, puis rétabli à sa valeur précédente lorsque la fonction se termine. SET FROM CURRENTenregistre la valeur du paramètre actuel lors CREATE FUNCTION de l'exécution en tant que valeur à appliquer lors de la saisie de la fonction.

AS 'definition'

Une constante de chaîne définissant la fonction ; la signification dépend de la langue. Pour une SQL-language fonction, ce sont les instructions SQL qui constituent le corps de la fonction.

RETURN expression

Définit le corps de la fonction en utilisant la SQL-standard syntaxe d'une fonction qui renvoie une seule expression. Il s'agit d'une alternative à la forme de constante de chaîne.

BEGIN ATOMIC statement; [ ... ] END

Définit le corps de la fonction à l'aide de la SQL-standard syntaxe. Le corps se compose d'une ou de plusieurs instructions SQL. Ce formulaire n'est valable que pourLANGUAGE sql. Les instructions d'un corps atomique sont suivies en tant que dépendances de la fonction. Par exemple, un tableau référencé dans le corps ne peut pas être supprimé sans supprimer également la fonction ou sans utiliserDROP ... CASCADE.

Remarques

Utilisez l'DROP FUNCTIONinstruction pour supprimer une fonction. Vous pouvez également accorder et révoquer des privilèges sur une fonction avec GRANT et REVOKE (par exemple,REVOKE ALL ON FUNCTION).

Toute fonction qui lit ou modifie des données dans des tables est soumise au même comportement de transaction et de simultanéité que les instructions SQL équivalentes exécutées directement. Les fonctions ont déclaré IMMUTABLE ou ne STABLE doivent pas modifier la base de données.

Pour mettre à jour la définition d'une fonction existante, utilisezCREATE OR REPLACE FUNCTION. Vous ne pouvez pas modifier le type d'argument ou le type de retour d'une fonction de cette façon ; supprimez et recréez la fonction.

Pour renommer une fonction, changer son propriétaire ou la déplacer vers un autre schéma, utilisezALTER FUNCTION. Pour modifier tout autre attribut, utilisez-le CREATE OR REPLACE FUNCTION avec les modifications souhaitées. Pour plus d’informations, consultez ALTER FUNCTION.

La surcharge de fonctions est prise en charge : vous pouvez définir plusieurs fonctions portant le même nom tant que leurs types d'arguments d'entrée sont différents. Une fonction définie par l'utilisateur peut également appeler d'autres fonctions et masquer une fonction intégrée portant le même nom et le même type d'argument.

Pour une fonction qui renvoie un ensemble (SETOFouRETURNS TABLE) dont le corps contient plusieurs instructions, seul le résultat de l'instruction finale est renvoyé. La fonction exécute toujours les instructions précédentes, mais ignore leurs résultats. Il s'agit du comportement standard des SQL-language fonctions. Structurez la fonction de manière à ce que son instruction finale produise le jeu de résultats complet.

Une fonction de retour d'ensembles ne garantit aucun ordre de ligne particulier. Pour contrôler l'ordre des lignes, incluez une ORDER BY clause dans la requête.

Considérations

Les CREATE FUNCTION fonctionnalités suivantes renvoient l'erreur affichée.

LEAKPROOF

Renvoie le message d'erreuronly superuser can define a leakproof function. NOT LEAKPROOF(valeur par défaut) est acceptée.

TRANSFORM FOR TYPE

Renvoie le message d'erreurtransform for type type language "sql" does not exist.

SUPPORT(fonction d'assistance au planificateur)

Renvoie le message d'erreurfunction name(internal) does not exist.

User-defined types composites (CREATE TYPE ... AS), y compris les fonctions qui renvoient un type composite

Renvoie le message d'erreurunsupported statement: CompositeType.

Aurora DSQL ne prend pas en chargeCREATE PROCEDURE, ce qui échoue avec l'erreurPROCEDURE is not supported.

L'WINDOWattribut est accepté syntaxiquement mais n'est pas utile pour les SQL-language fonctions. Évitez d'utiliser cet attribut.

Exemples

Créez une fonction SQL simple qui ajoute deux entiers, en utilisant un corps de chaîne constante :

CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'SELECT $1 + $2;'
    LANGUAGE sql
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

Créez la même fonction en utilisant le SQL-standard RETURN corps et les arguments nommés :

CREATE FUNCTION add(a integer, b integer) RETURNS integer
    LANGUAGE sql
    IMMUTABLE
    RETURN a + b;

Créez une fonction qui renvoie un ensemble de lignes en utilisant RETURNS TABLE et un BEGIN ATOMIC corps :

CREATE FUNCTION employees_in_dept(dept_id integer)
    RETURNS TABLE (id integer, name text)
    LANGUAGE sql
    STABLE
    BEGIN ATOMIC
        SELECT id, name FROM employees WHERE department_id = dept_id;
    END;

Créez une fonction qui s'exécute avec les privilèges de son propriétaire et définissez un paramètre de configuration pour la durée de l'appel :

CREATE FUNCTION current_department() RETURNS text
    LANGUAGE sql
    STABLE
    SECURITY DEFINER
    SET search_path = app
    AS 'SELECT current_setting(''app.department'');';

Compatibilité

Une CREATE FUNCTION commande est définie dans le standard SQL. L'implémentation de PostgreSQL peut être utilisée de manière compatible mais possède de nombreuses extensions. À l'inverse, le standard SQL spécifie un certain nombre de fonctionnalités facultatives qui ne sont pas implémentées dans PostgreSQL.

Les problèmes de compatibilité importants suivants sont les suivants :

• OR REPLACEest une extension PostgreSQL.

• Pour des raisons de compatibilité avec d'autres systèmes de base de données, il argmode peut être écrit avant ou aprèsargname. Mais seule la première méthode est conforme aux normes.

• Pour les paramètres par défaut, le standard SQL spécifie uniquement la syntaxe avec le mot DEFAULT clé. La syntaxe avec = est utilisée dans T-SQL et Firebird.

• Le SETOF modificateur est une extension PostgreSQL.

• Seul le langage SQL est standardisé.

• Tous les autres attributs, sauf CALLED ON NULL INPUT et ne RETURNS NULL ON NULL INPUT sont pas standardisés.

• Pour le corps des LANGUAGE sql fonctions, le standard SQL spécifie uniquement le sql_body formulaire.

LANGUAGE sqlLes fonctions simples peuvent être écrites d'une manière à la fois conforme aux normes et transférables à d'autres implémentations. Les fonctions plus complexes utilisant des fonctionnalités avancées, des attributs d'optimisation ou d'autres langages seront nécessairement spécifiques à PostgreSQL de manière significative.