Écrire des fonctions PL/pgSQL

Introduction

PostgreSQL est bien plus qu’un moteur de SGBDR. C’est une plateforme de développement. Il fournit un langage de programmation très puissant et flexible appelé PL/pgSQL. Grâce à ce langage, vous pouvez écrire vos propres fonctions définies par l’utilisateur pour atteindre des niveaux d’abstraction et des calculs procéduraux qu’il serait difficile d’obtenir avec du SQL pur (et parfois impossible sans changer de contexte avec l’application).

Écrire des fonctions

Dans le schéma public, faites un clic droit sur le nœud Functions (Fonctions) et cliquez sur Create Function (Créer une fonction). Un onglet interne SQL Query (Requête SQL) s’ouvre, contenant déjà un modèle SQL pour vous aider à créer votre première fonction PL/pgSQL.

Vous pouvez vous référer à la documentation PostgreSQL pour savoir comment écrire des fonctions définies par l’utilisateur. Il suffit de faire un clic droit sur le nœud Functions et de cliquer sur Doc : Functions — la page correspondante de la documentation PostgreSQL s’ouvre dans votre navigateur web (le navigateur par défaut du système dans l’application de bureau, ou un nouvel onglet lorsqu’elle fonctionne en tant qu’OmniDB Server accessible depuis un navigateur).

Pour l’instant, remplaçons entièrement ce modèle SQL par le code source ci-dessous :

CREATE OR REPLACE FUNCTION public.fnc_count_vowels (p_input text)
RETURNS integer LANGUAGE plpgsql AS
$function$
DECLARE
  str text;
  ret integer;
  i   integer;
  len integer;
BEGIN
  str := upper(p_input);
  ret := 0;
  i := 1;
  len := length(p_input);
  WHILE i <= len LOOP
    IF substr(str, i, 1) in ('A', 'E', 'I', 'O', 'U') THEN
      ret := ret + 1;
    END IF;
    i := i + 1;
  END LOOP;
  RETURN ret;
END;
$function$

Cela va créer une fonction appelée fnc_count_vowels dans le schéma public. Cette fonction prend un argument texte appelé p_input et compte le nombre de voyelles présentes dans cette chaîne de caractères. Elle retourne ensuite ce nombre.

Pour créer la fonction, exécutez la commande dans l’onglet interne SQL Query. En cas de succès, la fonction apparaîtra sous le nœud d’arborescence Functions (vous pouvez l’actualiser par un clic droit puis en cliquant sur Refresh). En développant également le nœud de la fonction, vous pouvez voir son type de retour et son argument.

Exécutons maintenant cette nouvelle fonction pour la première fois. Ouvrez un simple onglet interne SQL Query et exécutez la requête SQL suivante :

SELECT public.fnc_count_vowels('The quick brown fox jumps over the lazy dog.')

Remarquez comment la requête retourne une seule valeur, contenant le nombre de voyelles dans le texte.

En faisant un clic droit sur le nœud de la fonction, vous verrez qu’il existe des actions pour la modifier, la sélectionner et la supprimer. Comme vous l’aurez probablement deviné, chaque action ouvrira des onglets internes SQL Query avec des modèles SQL pratiques déjà remplis.