L'une des fonctionnalités les plus remarquables de PostgreSQL est sa prise en charge étendue des fonctions et des types de données définis par l'utilisateur. Cela permet aux développeurs de créer des fonctions de conversion, d'opérateur et d'agrégation personnalisées. Les agrégats offrent un moyen puissant d'effectuer des calculs et des transformations complexes sur les données, allant au-delà des fonctions d'agrégation SQL standard telles que SUM, AVG et COUNT. Navicat for PostgreSQL et Navicat Premium facilitent l'écriture de fonctions et d'agrégats personnalisés qui s'intègrent parfaitement à la base de données, grâce à leur interface utilisateur graphique (GUI) spécialisée. Tout ce que nous avons à faire est de fournir quelques détails et Navicat produit l'instruction pgSQL pour nous ! Dans le blog d'aujourd'hui, nous allons créer un agrégat pour fonctionner avec la base de données de location de DVD qui concatène les titres de films par catégorie.
À propos des agrégats
Les agrégats sont une fonctionnalité fondamentale de SQL qui vous permet d'effectuer des calculs ou des transformations sur un ensemble de lignes et de renvoyer un résultat unique. Les fonctions d'agrégation les plus courantes sont SUM, AVG, COUNT, MIN et MAX, qui vous permettent de résumer rapidement des données en calculant respectivement des totaux, des moyennes, des comptages, des valeurs minimales et des valeurs maximales.
Cependant, les fonctions d'agrégation intégrées fournies par SQL ne répondent pas toujours aux besoins spécifiques d'une application. C'est là que la possibilité de créer des agrégats personnalisés devient utile. Les agrégats personnalisés vous permettent de définir votre propre logique pour résumer et transformer les données, au-delà de l'ensemble standard d'agrégats SQL. Le processus implique généralement la définition d'une fonction de transition d'état, qui est appelée pour chaque ligne afin de mettre à jour un accumulateur, ainsi qu'une fonction finale facultative qui est appelée pour produire le résultat global final de l'agrégat.
Générer les fonctions de transition et les fonctions finales
Notre fonction de transition, array_append_state(), sera appelée pour chaque ligne afin de mettre à jour l'état de l'agrégat.
Pour accéder à l'éditeur de fonctions de Navicat, cliquez sur le bouton Fonction dans la barre de boutons principale, puis cliquez sur « Nouvelle fonction » dans la barre d'outils Objets :
Navicat va commencer par la définition de la fonction principale. A partir de là, nous fournirons le nom de la fonction, les paramètres d'entrée et le corps de la fonction :
CREATE FUNCTION "public"."array_append_state" (current_state text[], new_value text) RETURNS text[] AS $BODY$ BEGIN RETURN array_append(current_state, new_value); END $BODY$ LANGUAGE 'plpgsql' VOLATILE;
Une fois terminé, nous pouvons cliquer sur Enregistrer pour créer la fonction.
Nous allons maintenant revenir à l'onglet Objets et cliquer sur « Nouvelle fonction » pour créer la fonction finale.
La fonction array_to_comma_string() prendra un tableau de titres de films et insérera une virgule entre chaque élément :
CREATE FUNCTION "public"."array_to_comma_string" (state text[]) RETURNS text AS $BODY$ BEGIN RETURN array_append(state, ', '); END $BODY$ LANGUAGE 'plpgsql' VOLATILE;
Création de la fonction d'agrégation comma_concat()
Nous pouvons maintenant connecter nos deux fonctions à l'éditeur d'agrégation de Navicat. Nous pouvons accéder à l'éditeur en cliquant sur le bouton Autres dans la barre de boutons principale, puis en sélectionnant « Agrégation » dans le menu contextuel :
Dans le formulaire, nous allons définir le type d'entrée sur « texte », saisir un type d'état « texte[] » et fournir nos fonctions État et Final. . Assurez-vous également que la condition initiale est un tableau vide (« {} ») :
Nous pouvons voir le SQL généré en cliquant sur l'onglet Aperçu :
CREATE AGGREGATE "public"."Untitled" (In "pg_catalog"."text")
(
SFUNC = "public"."array_append_state",
STYPE = "pg_catalog"."text[]",
FINALFUNC = "public"."array_to_comma_string",
INITCOND = "{}",
PARALLEL = UNSAFE
);
ALTER AGGREGATE "public"."Untitled"("pg_catalog"."text") OWNER TO "postgres";
Vous pouvez remarquer que le nom de l'agrégat est « Sans titre ». Navicat nous demandera le nom lorsque nous appuierons sur le bouton Enregistrer et exécutera la commande avec le nom que nous aurons fourni.
Utilisation de notre agrégat personnalisé
Nous pouvons désormais invoquer notre fonction d'agrégation comme n'importe quelle autre fonction. Voici une requête qui récupère une liste de films par catégorie :
SELECT
c.name AS category,
comma_concat(f.title) AS movies
FROM category c
JOIN film_category fc ON c.category_id = fc.category_id
JOIN film f ON fc.film_id = f.film_id
GROUP BY c.name
ORDER BY c.name;
Conclusion
Dans le blog d'aujourd'hui, nous avons créé un agrégat PostgreSQL personnalisé dans Navicat Premium pour fonctionner avec la base de données de location de DVD qui concatène les titres de films par catégorie.
Vous souhaitez essayer Navicat Premium 17 ? Vous pouvez le télécharger pour un essai GRATUIT entièrement fonctionnel de 14 jours. Il est disponible pour les systèmes d'exploitation Windows, macOS et Linux.
