Fonctions stockées

SQL / PLpgSQL
(PostgreSQL)
Travaux Pratiques

30 JANVIER 2025

Parcours : LP – Génie Informatique / Semestre 3

UE : 2INF1328, Bases de Données
Encadré par : M. DJIRAIKINAN TITEMBAYE Donald
SGBD de référence : MySQL / PostgreSQL
EDOH-BEDI Komi Godwin
Rédigé par : APPIAH Samuel
EGBESSA Magnim Florence
KPAKPAO Pyabalo
TABLE DES MATIERES

Introduction......................... 2
Partie I : Fondamentaux des Fonctions Stockées 2
1. Qu'est-ce qu'une fonction stockée ? 2
2. Structure générale d'une fonction 3
3. Types de fonctions .......... 3
4. Langages disponibles ...... 4
5. Variables et structures de contrôle 5
Partie II : Travaux Pratiques . 5
Préparation de l'environnement 5
Pratique 1 : Fonction scalaire simple 7
Pratique 2 : Fonction retournant une table 8
Pratique 3 : Fonction avec calcul 10
Pratique 4 : Fonction d'agrégation personnalisée 11
Recapitulation ................... 13
Commandes utiles ............. 13
Points Clés à Retenir .......... 14
Approfodissement ............. 14
Conclusion ......................... 15
Les compétences acquises....................................... 15

1
Introduction
Les fonctions stockées en PostgreSQL, via PL/pgSQL, permettent
d’intégrer la logique métier directement dans la base. Ce guide, basé sur
un cas académique (étudiants et notes), montre pas à pas la création de
fonctions scalaires, de fonctions retournant des tables et d’agrégats
personnalisés, afin d’améliorer performance, maintenabilité et sécurité
des applications.

Objectif
Maîtriser la création de fonctions stockées et de fonctions d'agrégation
personnalisées dans PostgreSQL à travers des cas pratiques sur la gestion
des étudiants et leurs notes.

Prérequis
• Connaissance de base en SQL

• PostgreSQL installé

• Accès à un client PostgreSQL

Partie I : Fondamentaux des Fonctions Stockées

1. Qu'est-ce qu'une fonction stockée ?
Une fonction stockée est un bloc de code SQL réutilisable, stocké dans la
base de données. Elle permet d'encapsuler une logique métier complexe
et de la réutiliser facilement.
Avantages :
• Réutilisabilité du code

• Performance améliorée (code précompilé)

• Maintenance simplifiée

• Sécurité renforcée (encapsulation de la logique)

2
Structure générale d'une fonction

CREATE OR REPLACE FUNCTION nom_fonction(parametre1 TYPE,

parametre2 TYPE)
RETURNS type_retour
LANGUAGE plpgsql
AS $$
DECLARE
-- Variables locales (optionnel)
variable1 TYPE;
BEGIN
-- Corps de la fonction
-- Logique et traitement
RETURN resultat;
END;
$$;

Composants essentiels
• CREATE OR REPLACE FUNCTION : Crée ou remplace la fonction

• Paramètres : Données en entrée (optionnel)

• RETURNS : Type de données retourné

• LANGUAGE plpgsql : Langage procédural PostgreSQL

• DECLARE : Déclaration des variables locales

• BEGIN...END : Corps de la fonction

• RETURN : Valeur retournée

2. Types de fonctions
✓ Fonctions scalaires
Retournent une valeur unique (nombre, texte, booléen, etc.)

3
CREATE FUNCTION calculer_age(date_naissance DATE)
RETURNS INTEGER
LANGUAGE sql
AS $$
SELECT EXTRACT(YEAR FROM AGE(date_naissance))::INTEGER;
$$;

✓ Fonctions retournant des tables

Retournent un ensemble de lignes (comme une requête SELECT).

CREATE FUNCTION etudiants_admis()

RETURNS TABLE(nom VARCHAR, moyenne NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
SELECT [Link], AVG([Link]) as moyenne
FROM etudiants e
JOIN notes n ON [Link] = n.id_etudiant
GROUP BY [Link]
HAVING AVG([Link]) >= 10;
END;
$$;

✓ Fonctions d'agrégation
Effectuent des calculs sur un ensemble de valeurs (comme SUM, AVG,
COUNT).

3. Langages disponibles
• SQL : Pour fonctions simples basées sur des requêtes
• PL/pgSQL : Langage procédural pour logique complexe (conditions, boucles)
• PL/Python, PL/Perl, etc. : Autres langages selon extensions installées

4
 4. Variables et structures de contrôle
Variables
DECLARE
v_moyenne NUMERIC;
v_nom VARCHAR(50);
v_compteur INTEGER := 0; -- Initialisation

Conditions
IF condition THEN
-- instructions
ELSIF autre_condition THEN
-- instructions
ELSE
-- instructions
END IF;

Boucles
FOR variable IN requete LOOP
-- instructions
END LOOP;

WHILE condition LOOP

-- instructions
END LOOP;

Partie II : Travaux Pratiques

Préparation de l'environnement

Création de la base de données et des tables

✓ Création de la base de données

CREATE DATABASE tp_fonctions_stockees;

5
 ✓ Connexion à la base
\c tp_fonctions_stockees

Création des tables

✓ Table ETUDIANTS
CREATE TABLE ETUDIANTS (
id_etudiant SERIAL PRIMARY KEY,
nom VARCHAR(50),
prenom VARCHAR(50),
Email VARCHAR(100)
);

✓ Table NOTES

CREATE TABLE NOTES (

Id_notes SERIAL PRIMARY KEY,
id_etudiant INT NOT NULL,
matiere VARCHAR(50),
note NUMERIC(4,2) CHECK (note >= 0 AND note <= 20),
CONSTRAINT fk_etudiant FOREIGN KEY (id_etudiant)
REFERENCES ETUDIANTS(id_etudiant)
);

Insertion des données

✓ Insertion de 4 étudiants

INSERT INTO etudiants (nom, prenom, email) VALUES

('AFI', 'Marie', 'marie@[Link]'),
('KOFFI', 'Lucas', 'lucas@[Link]'),
('ABALO', 'Sophie', 'sophie@[Link]'),
('KOSSI', 'Thomas', 'thomas@[Link]');

6
 ✓ Insertion de quelques notes

INSERT INTO notes (id_etudiant, matiere, note) VALUES

(1, 'Mathématiques', 15.5),
(1, 'Informatique', 17.0),
(2, 'Mathématiques', 12.0),
(2, 'Informatique', 14.5),
(3, 'Mathématiques', 8.5),
(3, 'Informatique', 11.0),
(4, 'Mathématiques', 16.0),
(4, 'Informatique', 15.5);

Pratique 1 : Fonction scalaire simple

Objectif
Créer une fonction qui détermine la mention selon une note.
Consigne
Créez une fonction obtenir_mention(note NUMERIC) qui retourne :
• "Très Bien" si note >= 16
• "Bien" si note >= 14
• "Assez Bien" si note >= 12
• "Passable" si note >= 10
• "Insuffisant" sinon
Code
CREATE OR REPLACE FUNCTION obtenir_mention(p_note NUMERIC)
RETURNS VARCHAR
LANGUAGE plpgsql
AS $$
BEGIN
IF p_note >= 16 THEN RETURN 'Très Bien';
ELSIF p_note >= 14 THEN RETURN 'Bien';
ELSIF p_note >= 12 THEN RETURN 'Assez Bien';

7
 ELSIF p_note >= 10 THEN RETURN 'Passable';
ELSE RETURN 'Insuffisant';
END IF;
END;
$$;

Explication
• Fonction scalaire : Retourne une seule valeur (VARCHAR)
• Structure IF/ELSIF : Évalue les conditions dans l'ordre
• Paramètre préfixé : p_note évite les conflits de noms
Tests
-- Test simple
SELECT note, obtenir_mention(note) as mention FROM notes;

-- Avec identification de l'étudiant

SELECT note, id_etudiant, obtenir_mention(note) as mention
FROM notes;

-- Jointure avec les noms

SELECT [Link], [Link], obtenir_mention([Link]) as mention
FROM notes n
JOIN etudiants e ON n.id_etudiant = e.id_etudiant;

Pratique 2 : Fonction retournant une table

Objectif
Créer une fonction qui retourne le bulletin d'un étudiant.
Consigne
Créez une fonction bulletin_etudiant(id_etudiant INTEGER) qui retourne une table avec :
• matiere
• note
• mention (utiliser la fonction de la pratique 1)
Code
8
 CREATE OR REPLACE FUNCTION bulletin_etudiant(p_id_etudiant
INTEGER)
RETURNS TABLE(matiere VARCHAR, note NUMERIC, mention
VARCHAR)
LANGUeAGE plpgsql
AS $$
BEGIN
RETURN QUERY
SELECT
[Link],
[Link],
obtenir_mention([Link])::VARCHAR
FROM notes n
WHERE n.id_etudiant = p_id_etudiant
ORDER BY [Link];
END;
$$;

Explication
• RETURNS TABLE : Définit la structure de la table retournée
• RETURN QUERY : Retourne le résultat d'une requête SELECT
• Réutilisation de fonction : Appelle obtenir_mention() pour chaque note
• Cast explicite : ::VARCHAR pour correspondre au type déclaré
Tests
-- Bulletin d'un étudiant spécifique
SELECT * FROM bulletin_etudiant(1);

-- Bulletin pour tous les étudiants

SELECT [Link], [Link], b.*
FROM etudiants e
JOIN LATERAL bulletin_etudiant(e.id_etudiant) b ON TRUE;

9
Note : JOIN LATERAL permet d'utiliser la valeur de la table de gauche (e.id_etudiant) dans la
fonction.

Pratique 3 : Fonction avec calcul

Objectif
Créer une fonction qui calcule la moyenne d'un étudiant et indique s'il est admis.
Consigne
Créez une fonction resultats_etudiant(id_etudiant INTEGER) qui retourne :
• nom complet (nom + prénom)
• moyenne
• mention
• admis (BOOLEAN : true si moyenne >= 10)
Code
CREATE OR REPLACE FUNCTION resultats_etudiant(p_id_etudiant
INTEGER)
RETURNS TABLE(
nom_complet VARCHAR,
moyenne NUMERIC,
mention VARCHAR,
admis BOOLEAN
)
LANGUAGE plpgsql
AS $$
DECLARE
v_moyenne NUMERIC;
BEGIN
-- Calcul de la moyenne
SELECT ROUND(AVG(note), 2) INTO v_moyenne
FROM notes
WHERE id_etudiant = p_id_etudiant;

-- Retour des résultats

RETURN QUERY
SELECT
([Link] || ' ' || [Link])::VARCHAR as nom_complet,

10
 v_moyenne as moyenne,
obtenir_mention(v_moyenne)::VARCHAR as mention,
(v_moyenne >= 10)::BOOLEAN as admis
FROM etudiants e
WHERE e.id_etudiant = p_id_etudiant;
END;
$$;

Explication
• DECLARE : Variable locale pour stocker la moyenne
• SELECT...INTO : Stocke le résultat dans une variable
• Concaténation : || pour assembler nom et prénom
• Expression booléenne : (v_moyenne >= 10) évalue directement en BOOLEAN
Tests
-- Résultat d'un étudiant
SELECT * FROM resultats_etudiant(1);

-- Résultats de tous les étudiants

SELECT * FROM resultats_etudiant(1)
UNION ALL
SELECT * FROM resultats_etudiant(2)
UNION ALL
SELECT * FROM resultats_etudiant(3)
UNION ALL
SELECT * FROM resultats_etudiant(4)
ORDER BY moyenne DESC;

Pratique 4 : Fonction d'agrégation

personnalisée
Objectif
Créer une fonction d'agrégation qui compte le nombre de notes supérieures ou égales à 10.
Consigne
Créez un agrégat compte_notes_admises() qui compte les notes >= 10.
Rappel : Un agrégat nécessite :
• Une fonction de transition (SFUNC) : traitée pour chaque ligne
• Un type d'état (STYPE) : stocke le résultat intermédiaire

11
 • Une condition initiale (INITCOND) : valeur de départ
Code
Fonction de transition
CREATE OR REPLACE FUNCTION compter_admis_transition(
compteur INTEGER,
note NUMERIC
)
RETURNS INTEGER
LANGUAGE sql
AS $$
SELECT CASE
WHEN note IS NULL THEN compteur
WHEN note >= 10 THEN compteur + 1
ELSE compteur
END;
$$;

Création de l'agrégat
CREATE AGGREGATE compte_notes_admises(NUMERIC) (
SFUNC = compter_admis_transition,
STYPE = INTEGER,
INITCOND = '0'
);
Explication
• Fonction de transition : Appelée pour chaque valeur, incrémente le compteur si note >=
10
• STYPE = INTEGER : Le compteur est un entier
• INITCOND = '0' : Initialise le compteur à zéro
• Gestion des NULL : Important pour éviter les erreurs
Fonctionnement pas à pas
note 1: 15.5 → compteur: 0 + 1 = 1
note 2: 17.0 → compteur: 1 + 1 = 2
note 3: 8.5 → compteur: 2 + 0 = 2
note 4: 11.0 → compteur: 2 + 1 = 3
Résultat final: 3 notes admises
Tests
Test 1 : Par matière

12
 SELECT
matiere,
COUNT(*) as total_notes,
compte_notes_admises(note) as notes_admises,
ROUND(AVG(note), 2) as moyenne
FROM notes
GROUP BY matiere;

Test 2 : Par étudiant

SELECT
[Link],
[Link],
COUNT(n.*) as total_notes,
compte_notes_admises([Link]) as notes_admises
FROM etudiants e
LEFT JOIN notes n ON e.id_etudiant = n.id_etudiant
GROUP BY e.id_etudiant, [Link], [Link];

Test 3 : Global
SELECT
COUNT(*) as total_notes,
compte_notes_admises(note) as notes_admises,
ROUND(100.0 * compte_notes_admises(note) / COUNT(*), 1)
as taux_reussite
FROM notes;

Recapitulation
Commandes utiles
Voir toutes vos fonctions

13
\df

Voir les détails d'une fonction

\df+ nom_fonction

Supprimer une fonction

DROP FUNCTION IF EXISTS nom_fonction;

Supprimer un agrégat
DROP AGGREGATE IF EXISTS nom_agregat(NUMERIC);

Modifier une fonction existante

CREATE OR REPLACE FUNCTION nom_fonction...

Points Clés à Retenir

1. Fonctions scalaires : Une valeur en sortie, idéales pour calculs simples
2. Fonctions table : Ensemble de lignes, utilisables avec JOIN LATERAL
3. Variables DECLARE : Stockent des résultats intermédiaires
4. Réutilisabilité : Les fonctions peuvent s'appeler entre elles
5. Agrégats personnalisés : Créent de nouvelles opérations GROUP BY
6. Typage strict : Attention aux casts (::TYPE) pour éviter les erreurs
7. LANGUAGE : sql pour requêtes simples, plpgsql pour logique procédurale

Approfodissement
• Fonctions avec paramètres par défaut
• Fonctions avec VARIADIC (nombre variable de paramètres)
• Triggers utilisant des fonctions
• Fonctions retournant SETOF pour flux de données
• Gestion des exceptions avec BEGIN...EXCEPTION...END

14
 • Optimisation des performances avec STABLE/IMMUTABLE

Conclusion
Les fonctions stockées PostgreSQL représentent bien plus qu'un simple mécanisme de
réutilisation de code. Comme nous l'avons vu à travers ces travaux pratiques, elles permettent
d'encapsuler la logique métier, d'améliorer les performances par la réduction des allers-retours
réseau, et de garantir la cohérence des traitements au niveau de la base de données.
De la simple fonction scalaire obtenir_mention() aux agrégats personnalisés comme
compte_notes_admises(),en passant par les fonctions retournant des tables complexes, vous
disposez maintenant des fondamentaux pour développer des solutions robustes et maintenables.

Les compétences acquises

• Maîtrise de la syntaxe PL/pgSQL et de la structure des fonctions
• Création de fonctions scalaires pour calculs et transformations
• Développement de fonctions retournant des ensembles de données
• Construction d'agrégats personnalisés pour analyses avancées
• Utilisation pratique dans des contextes métier réels

15