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

  • 5 minutes

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

  • Fonctions écrites en SQL.
  • Fonctions de langage procédural, écrites dans un langage procédural pris en charge, comme 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 (par défaut) peut modifier la base de données et peut 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 elle est exécutée avec les mêmes arguments et la même instruction. Si cette fonction est appelée plusieurs fois, l’optimiseur de requête peut utiliser les résultats du dernier appel.

Une fonction immuable ne peut pas modifier la base de données et retourne les mêmes résultats si elle reçoit les mêmes arguments, quelle que soit la requête qui l’appelle.

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

Créer une fonction

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

Voici la syntaxe pour créer une fonction :

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 :

  • name : incluez éventuellement le nom du schéma.
  • argmode : mode de l’argument. Doit ê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, , 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 de l’argument. Il peut s’agir de types de base, composites ou de domaine, ou d’une référence à un type de colonne de table. Le type de colonne est écrit sous la forme nom_table.nom_colonne%TYPE. Ce type de données peut aider à rendre une fonction indépendante des modifications de 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 une référence à un type de colonne de table. Si la fonction ne retourne pas de valeur, spécifiez le type de retour void. Lorsqu’il existe des paramètres OUT ou INOUT, la clause RETURNS peut être omise. Si elle est présente, elle doit correspondre au type de résultat implicite des 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 unique é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é, à la différence 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 : langage utilisé pour écrire la procédure. La valeur par défaut est sql si sql_body est spécifié. 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 obtenir la liste complète des fonctions PostgreSQL.

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

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

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

  • String (type texte)
  • FROM start (type entier)
  • NB FOR (type entier)

substring retourne une partie du texte d’entrée, en commençant au caractère start et en arrêtant après count caractères. Par exemple :

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

Cette fonction est semblable à substr :

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

Remarque

Si vous êtes familiarisé avec 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.