Créer et utiliser une fonction dans Azure Database pour PostgreSQL

  • 5 minutes

Nous avons déjà appris que PostgreSQL prend en charge différents langages. Les fonctions peuvent être classées en quatre types différents :

  • Fonctions écrites dans SQL.
  • Fonctions de langage procédural, écrites dans un langage procédural pris en charge, tel que PL.pgSQL.
  • Fonctions internes.
  • Fonctions de langage C.

En outre, l’objectif de la fonction peut également être classé comme volatile, immuable ou stable.

Une fonction volatile (valeur par défaut) peut modifier la base de données et ne pas nécessairement retourner le même résultat avec les mêmes paramètres d’entrée à chaque fois. Ainsi, chaque fois que cette fonction est appelée, elle doit être réévaluée.

Une fonction stable ne peut pas modifier la base de données et retourne le même résultat si les mêmes arguments ont été transmis et exécutés dans la même instruction. Si cette fonction est appelée plusieurs fois, l’optimiseur de requête peut utiliser les résultats de la dernière fois qu’elle a été appelée.

Une fonction immuable ne peut pas modifier la base de données et retourne les mêmes résultats s’il a passé les mêmes arguments, quelle que soit la requête qui l’appelle.

La volatilité d’une fonction fait une grande différence avec l’efficacité avec laquelle l’optimiseur de requête le gère.

Créer une fonction

Une fonction retourne une valeur unique et peut être utilisée dans une instruction SELECT.

La syntaxe de création d’une fonction est la suivante :

CREATE [OR REPLACE] FUNCTION
myfunction ([inputparam] type {default})
RETURNS returntype AS
$$
SQL body
$$
LANGUAGE 'language_name';
CREATE FUNCTION

Comme pour les procédures stockées, le symbole $$ est utilisé pour démarrer et mettre fin à la chaîne.

Les fonctions prennent les paramètres suivants :

  • nom : incluez éventuellement le nom du schéma.
  • argmode : mode de l’argument. Peut être IN, OUT, INOUT ou VARIADIC. La valeur par défaut est IN. VARDIAC est un nombre non défini d’arguments d’entrée du même type, et est suivi d’arguments OUT. Les arguments OUT et INOUT ne peuvent pas être utilisés avec les notations RETURNS TABLE.
  • argname - nom de l’argument.
  • argtype : type de données d’argument. Peut être un type de base, composite ou de domaine, ou faire référence à un type de colonne de table. Le type de colonne est écrit en tant que table_name.column_name%TYPE. Ce type de données peut aider à apporter une fonction indépendante des modifications apportées à la définition de table.
  • t_expr - Valeur par défaut (du même type) si le paramètre n’est pas spécifié. Seuls les paramètres IN et INOUT ont une valeur par défaut. Les paramètres d’entrée qui suivent un paramètre avec une valeur par défaut doivent également avoir des valeurs par défaut.
  • rettype : type de données de retour - qui peut être un type de base, composite ou de domaine, ou bien référencer un type de colonne de table. Si la fonction ne retourne pas de valeur, spécifiez le type de retour comme void. Lorsqu’il existe des paramètres OUT ou INOUT, la clause RETURNS peut être omise. S’il est présent, il doit accepter le type de résultat implicite par les paramètres de sortie : RECORD s’il existe plusieurs paramètres de sortie ou le même type que le paramètre de sortie unique. Le modificateur SETOF indique que la fonction retourne un ensemble d’éléments, plutôt qu’un seul élément. Le type d’une colonne est référencé en écrivant table_name.
  • column_name : nom d’une colonne de sortie dans la syntaxe RETURNS TABLE. Ce paramètre déclare un paramètre OUT nommé, sauf que RETURNS TABLE implique également RETURNS SETOF.
  • column_type : type de données d’une colonne de sortie dans la syntaxe RETURNS TABLE.
  • lang_name - la langue utilisée pour écrire la procédure. La valeur par défaut est sql si sql_body est spécifiée. Peut être sql, c, interne ou le nom d’un langage procédural défini par l’utilisateur, par exemple plpgsql.

Utilisez les mots clés IMMUTABLE, STABLE ou VOLATILE comme indicateur pour l’optimiseur de requête sur la fonction. VOLATILE est la valeur par défaut.

Appeler une fonction

Une fonction peut être utilisée dans une requête en lui transmettant tous les paramètres pertinents. Par exemple:

SELECT myfunction(3), CatID, CatName
    FROM myCats

Fonctions intégrées

PostgreSQL inclut de nombreuses fonctions intégrées que vous pouvez utiliser dans vos requêtes. Celles-ci couvrent les comparaisons, l’agrégation des données, les fonctions mathématiques, etc. Consultez la documentation en ligne pour une liste complète des fonctions PostgreSQL.

Un exemple de fonction de chaîne intégrée est sous-chaîne.

substring (*string* text [ FROM *start* integer ] [ FOR *count* integer ] ) → text

Cette fonction prend trois paramètres d’entrée :

  • Chaîne (type texte)
  • À PARTIR DE start (type entier)
  • NOMBRE FOR (type entier)

La sous-chaîne retourne une partie du texte d'entrée, en commençant au caractère de départ et en s'arrêtant après nombre de caractères. Par exemple:

substring('Thomas' from 2 for 3) → hom
substring('Thomas' from 3) → omas
substring('Thomas' for 2) → Th

Cette fonction est la même que substr :

substr ( *string* text, *start* integer [, *count* integer ] ) → text
substr('alphabet', 3) → phabet
substr('alphabet', 3, 2) → ph

Note

Si vous connaissez les fonctions, vous avez remarqué que la première version utilise des mots clés plutôt que des virgules pour séparer les arguments. PostgreSQL fournit les deux versions de ces fonctions.