Fiche de révision : Fonctions PL/pgSQL et contrats

📋 Plan du Cours

1. Pourquoi encapsuler une question SQL
2. Contrat, signature et bloc PL/pgSQL
3. Paramètres, variables et requêtes
4. Décompte du stock disponible
5. Retourner une existence ou des lignes
6. Prédire et corriger une fonction
7. Arbitrer le contrat et le tester

📖 1. Pourquoi encapsuler une question SQL

🔑 Notions clés & Définitions

• Fonction PostgreSQL : reçoit des paramètres, exécute un traitement et retourne un résultat conforme à un type annoncé.

★ À maîtriser

• 🔄 La transformation suit quatre étapes:
  1. Définir les entrées
  2. Filtrer les données selon la question métier
  3. Calculer le résultat
  4. Retourner le résultat sous un type explicite

Compléments

📌 Avant d’écrire une fonction, il faut annoncer ses entrées, son résultat et le comportement attendu lorsqu’aucune donnée ne répond à la question.

💡 Astuce mémo

Question répétée → point d’entrée réutilisable → moins d’oublis de filtre ou de jointure

📖 2. Contrat, signature et bloc PL/pgSQL

🔑 Notions clés & Définitions

• Contrat appelable : comprend des valeurs d’entrée nommées et typées, un traitement SQL organisé dans un bloc et une valeur de sortie compatible avec RETURNS.

📝 Points essentiels

• Dans une signature PL/pgSQL, RETURNS integer annonce un décompte entier et non une liste de lignes ou un message d’alerte.

• Dans le squelette d’une fonction PL/pgSQL, DECLARE prépare les variables, BEGIN et END délimitent le traitement, puis RETURN fournit le résultat promis au contrat.

💡 Astuce mémo

Entrer → calculer → retourner

📖 3. Paramètres, variables et requêtes

📝 Points essentiels

📌 Un paramètre est une valeur fournie par l’appelant, une variable stocke un résultat intermédiaire dans le bloc et une colonne représente une donnée provenant d’une ligne SQL.

📌 Dans une fonction, un SELECT qui produit des lignes doit avoir une destination explicite, généralement INTO pour une variable ou RETURN QUERY pour un retour tabulaire.

• Le transfert des données s’effectue en deux passages : INTO transfère le résultat SQL vers une variable PL/pgSQL, puis RETURN transfère cette variable vers l’appelant.

💡 Astuce mémo

Paramètre fourni par l’appelant, variable stockée dans le bloc, colonne issue d’une ligne SQL

📖 4. Décompte du stock disponible

★ À maîtriser

• La fonction don.stock_disponible(p_groupe_code text, p_type_produit text) retourne le nombre entier de poches disponibles pour un groupe et un type de produit après avoir recherché l’identifiant du groupe puis compté les poches filtrées.

📌 Dans don.stock_disponible, un groupe inconnu provoque RETURN NULL, tandis qu’un groupe connu sans poche disponible retourne 0, car COUNT(*) produit alors zéro.

Compléments

• L’appel don.stock_disponible('O-', 'CGR') retourne 16 lorsque le groupe est connu et possède du stock disponible.

📌 COALESCE(v_stock, 0) transforme NULL en zéro, mais ce choix de contrat peut faire passer un groupe mal orthographié pour un groupe sans stock.

💡 Astuce mémo

Groupe connu sans stock = 0 ; groupe inconnu = NULL

📖 5. Retourner une existence ou des lignes

🔑 Notions clés & Définitions

• EXISTS : répond par un booléen à la question de savoir si au moins une ligne satisfait les conditions de la requête.

📝 Points essentiels

• La fonction don.site_a_collecte(p_site_id integer, p_jour date) retourne un booléen indiquant si une collecte a eu lieu pour un site et une date donnés.

📌 Le type de retour doit répondre au besoin métier : COUNT convient à un décompte, EXISTS à une existence et RETURN QUERY à une liste de poches.

💡 Astuce mémo

COUNT répond combien, EXISTS répond si, RETURN QUERY répond quoi

📖 6. Prédire et corriger une fonction

★ À maîtriser

• Pour prédire le résultat d’une fonction, il faut suivre les paramètres reçus, les lignes retenues par FROM, JOIN et WHERE, l’expression calculée et sa variable cible, puis la valeur compatible avec RETURNS.

📌 Une requête SELECT INTO doit respecter le grain métier : un agrégat peut produire un total unique, tandis qu’une sélection de plusieurs lignes ne devient pas automatiquement un seul résultat.

Compléments

• Pour obtenir le dernier don d’un donneur, la requête sélectionne le volume, ordonne les dons par horodatage décroissant, limite le résultat à une ligne, puis vérifie FOUND avant de retourner la valeur.

💡 Astuce mémo

Filtrer → agréger → déposer → retourner

📖 7. Arbitrer le contrat et le tester

🔑 Notions clés & Définitions

• Appel de test : doit couvrir un cas normal et au moins un cas limite qui oblige à expliciter le contrat.

★ À maîtriser

📌 Une fonction robuste rend visibles ses entrées, son grain, la destination du résultat, son comportement dans le cas vide et les tests qui vérifient son contrat.

• 🔄 La méthode comporte quatre actions:
  1. Paramétrer la question métier
  2. Typer les entrées, les variables et la sortie
  3. Calculer avec une destination explicite
  4. Retourner un résultat compatible dans le cas vide

Compléments

📌 Une fonction est appelée dans SELECT et retourne un résultat, tandis qu’une procédure est appelée avec CALL et organise plutôt une action ou un effet de bord.

💡 Astuce mémo

Entrées → grain → destination → cas vide → test

📊 Tableaux de synthèse

Types de contrats de fonction