Développement de fonctions stockées PostgreSQL

Introduction

Dans ce laboratoire, vous apprendrez à développer des fonctions stockées PostgreSQL. Vous passerez par le processus de définition d'une fonction stockée de base, d'ajout de paramètres d'entrée avec des valeurs par défaut, d'exécution de la fonction dans une requête, et enfin, de suppression d'une fonction inutilisée pour maintenir votre base de données organisée.

Définir une fonction stockée de base

Dans cette étape, vous apprendrez à définir une fonction stockée de base dans PostgreSQL. Les fonctions stockées sont des blocs de code réutilisables qui effectuent une tâche spécifique et peuvent être exécutés au sein de la base de données.

Tout d'abord, ouvrez un terminal et connectez-vous à la base de données PostgreSQL en utilisant l'outil en ligne de commande psql. Vous effectuerez toutes les opérations de base de données dans ce shell psql.

sudo -u postgres psql

Vous devriez maintenant voir l'invite PostgreSQL, qui ressemble à postgres=#.

Maintenant, créez une fonction nommée get_total_products qui retourne une valeur entière fixe. Cette fonction simple vous aidera à comprendre la syntaxe de base. Exécutez la commande SQL suivante dans le shell psql :

CREATE FUNCTION get_total_products()
RETURNS INTEGER AS $$
BEGIN
  RETURN 100;
END;
$$ LANGUAGE plpgsql;

Analysons cette commande :

• CREATE FUNCTION get_total_products() : Ceci définit une nouvelle fonction nommée get_total_products sans paramètres d'entrée.
• RETURNS INTEGER : Ceci spécifie que la fonction retournera une seule valeur de type de données INTEGER.
• AS $$ ... $$ : La chaîne délimitée par des signes dollar $$ est utilisée pour encapsuler le corps de la fonction. C'est une pratique courante dans PostgreSQL pour éviter les problèmes avec les apostrophes simples dans le code de la fonction.
• BEGIN ... END; : Ce bloc contient la partie exécutable de la fonction.
• RETURN 100; : C'est la logique de notre fonction, qui retourne simplement l'entier 100.
• LANGUAGE plpgsql : Ceci spécifie que la fonction est écrite en plpgsql, le langage procédural de PostgreSQL.

Après avoir exécuté la commande, PostgreSQL confirmera la création de la fonction :

CREATE FUNCTION

Pour vérifier que la fonction a été créée, vous pouvez l'appeler dans une instruction SELECT :

SELECT get_total_products();

La sortie affichera la valeur retournée par la fonction :

 get_total_products
--------------------
                100
(1 row)

Cela confirme que votre première fonction stockée fonctionne correctement.

Créer une fonction avec des paramètres

Les fonctions stockées deviennent plus puissantes lorsqu'elles peuvent accepter des paramètres d'entrée. Dans cette étape, vous allez créer une nouvelle fonction qui prend deux nombres en entrée et retourne leur somme.

Assurez-vous d'être toujours dans le shell psql de l'étape précédente. Maintenant, créez une fonction nommée add_numbers qui accepte deux paramètres entiers.

CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

La syntaxe est similaire à l'étape précédente, avec une différence clé dans la signature de la fonction :

• add_numbers(a INTEGER, b INTEGER) : Ceci définit deux paramètres, a et b, tous deux de type INTEGER, qui doivent être fournis lors de l'appel de la fonction.

Après avoir exécuté la commande, vous verrez la confirmation CREATE FUNCTION.

Maintenant, testez la fonction en fournissant deux nombres comme arguments :

SELECT add_numbers(15, 25);

La fonction additionnera les deux nombres et retournera le résultat :

 add_numbers
-------------
          40
(1 row)

Vous pouvez également utiliser des fonctions avec des valeurs de paramètres par défaut. Créons une nouvelle fonction greet_user où le message de salutation a une valeur par défaut.

CREATE OR REPLACE FUNCTION greet_user(username VARCHAR, greeting VARCHAR DEFAULT 'Hello')
RETURNS TEXT AS $$
BEGIN
  RETURN greeting || ', ' || username || '!';
END;
$$ LANGUAGE plpgsql;

Ici, CREATE OR REPLACE mettra à jour la fonction si elle existe déjà. Le paramètre greeting se voit attribuer la valeur par défaut 'Hello'. L'opérateur || est utilisé pour la concaténation de chaînes de caractères.

Testez la fonction en fournissant uniquement le paramètre requis username :

SELECT greet_user('Alex');

La fonction utilise la salutation par défaut :

       greet_user
------------------------
 Hello, Alex!
(1 row)

Appelez-la maintenant à nouveau, mais cette fois, fournissez une salutation personnalisée :

SELECT greet_user('Alex', 'Welcome');

La sortie affiche maintenant votre message personnalisé :

      greet_user
-----------------------
 Welcome, Alex!
(1 row)

Exécuter une fonction dans une requête

Une utilisation courante des fonctions stockées est d'effectuer des calculs sur les données des tables. Dans cette étape, vous allez créer une table, la peupler de données, puis utiliser une fonction dans une requête sur cette table.

Tout d'abord, créez une table simple appelée products pour stocker les noms et les prix des produits.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  price NUMERIC(10, 2)
);

Vous verrez le message de confirmation CREATE TABLE.

Ensuite, insérez des données d'exemple dans la table products :

INSERT INTO products (name, price) VALUES
('Laptop', 1200.00),
('Mouse', 25.50),
('Keyboard', 75.00);

Vous verrez INSERT 0 3, indiquant que trois lignes ont été insérées.

Maintenant, créons une fonction pour calculer le prix avec la taxe de vente. Cette fonction prendra un prix en entrée et retournera le prix incluant une taxe de 7 %.

CREATE FUNCTION calculate_taxed_price(price NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
  RETURN price * 1.07;
END;
$$ LANGUAGE plpgsql;

Après avoir créé la fonction, vous pouvez l'utiliser directement dans une requête SELECT sur la table products. Cette requête affichera le prix d'origine et le prix incluant la taxe pour chaque produit.

SELECT name, price, calculate_taxed_price(price) AS taxed_price FROM products;

La sortie affichera les résultats de l'appel de la fonction pour chaque ligne :

   name   |  price  | taxed_price
----------+---------+-------------
 Laptop   | 1200.00 |    1284.0000
 Mouse    |   25.50 |      27.2850
 Keyboard |   75.00 |      80.2500
(3 rows)

Cela démontre comment vous pouvez encapsuler la logique métier dans une fonction et l'appliquer à vos données.

Supprimer une fonction stockée

Il est de bonne pratique de supprimer les objets de base de données qui ne sont plus nécessaires. Dans cette étape, vous apprendrez comment supprimer ou "dropper" une fonction stockée de votre base de données. Nous allons supprimer la fonction get_total_products créée lors de la première étape.

Tout d'abord, vous pouvez lister les fonctions de votre base de données pour confirmer que get_total_products existe.

\df

Vous verrez une liste de fonctions, y compris get_total_products.

Pour supprimer la fonction, utilisez la commande DROP FUNCTION. Vous devez spécifier le nom de la fonction. Si la fonction a des paramètres, vous devrez spécifier leurs types, mais comme get_total_products n'en a pas, vous pouvez simplement utiliser le nom.

DROP FUNCTION get_total_products();

PostgreSQL confirmera l'action :

DROP FUNCTION

Maintenant, si vous listez à nouveau les fonctions avec \df, vous verrez que get_total_products n'est plus dans la liste.

Il est également important de spécifier les types d'arguments si la fonction est surchargée (c'est-à-dire plusieurs fonctions avec le même nom mais des paramètres différents). Par exemple, pour supprimer la fonction add_numbers, vous devez spécifier ses paramètres entiers :

DROP FUNCTION add_numbers(INTEGER, INTEGER);

Enfin, pour nettoyer votre environnement, supprimez la table products créée à l'étape précédente.

DROP TABLE products;

Ce laboratoire est maintenant terminé. Pour quitter le shell psql, tapez \q et appuyez sur Entrée.

Résumé

Dans ce laboratoire, vous avez appris les bases du développement de fonctions stockées dans PostgreSQL. Vous avez créé une fonction de base, l'avez améliorée avec des paramètres et des valeurs par défaut, avez appliqué une fonction dans une requête sur des données de table, et enfin, avez nettoyé la base de données en supprimant les fonctions et les tables. Ces compétences sont essentielles pour créer des solutions de base de données plus modulaires et efficaces.