Bases de données

CREATE FUNCTION hello() RETURNS void AS $$
BEGIN
    -- RAISE est l'instruction pour afficher quelque chose ;
    -- le mot-clé suivant indique le type de message : warning, erreur, ...
    -- Ici il s'agit d'une simple notification "NOTICE"
    RAISE NOTICE 'Hello world !';
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION hello(varchar) RETURNS varchar AS $$
    -- || est l'opérateur de concaténation de chaînes.
    -- $1 prend la valeur de l'argument (que l'on n'a pas nommé, 
    -- donc c'est la seule façon de l'utiliser ici)
    SELECT 'Hello ' || $1 || ' !';
$$ LANGUAGE SQL;

CREATE FUNCTION fibo(n integer) RETURNS integer AS $$
BEGIN
    IF n = 0 THEN
        RETURN 0;
    -- Erreur assez fréquente : c'est "ELSIF" et pas "ELSEIF" ; 
    -- mais on peut aussi écrire "ELSE IF ..."
    ELSIF n = 1 THEN
        RETURN 1;
    ELSE
        RETURN fibo(n-1) + fibo(n-2);
    -- Erreur assez fréquente aussi : c'est "END IF" et pas "ENDIF" ou "FI" ...etc
    END IF;
END;
$$ LANGUAGE plpgsql;
-- Note : en terme de performance, c'est la pire façon de l'écrire. 
-- Pourquoi ? ...Mais c'est aussi l'écriture la plus simple. La version itérative 
-- nécessite de déclarer quelques variables (mais reste facile à écrire). Enfin, 
-- vous pouvez aussi obtenir le résultat en une seul ligne de calcul. Comment ?

CREATE FUNCTION syracuse(n integer) RETURNS integer ARRAY AS $$
DECLARE
    curVal integer; -- la valeur courante de la suite, £u_n£
    resultat varchar := ''; -- j'ai choisi de tout stocker dans une chaîne de caractères, 
                            -- puis de transformer cette chaîne. On peut aussi déclarer un tableau,
                            -- le remplir et le retourner. Il faut alors un indice supplémentaire.
    -- [ Version utilisant un tableau : ]
    --resultat int[];
    --index integer := 0;
BEGIN
    curVal := n;
    WHILE curVal > 1 LOOP
        -- L'opérateur CAST force une conversion de type ; ici, integer en varchar.
        -- On concatène donc la chaîne courante avec la valeur de £u_n£
        resultat := resultat || CAST(curVal AS varchar) || ',';
        -- [ Version utilisant un tableau : ]
        --resultat[index] := curVal;
        --index := index + 1;
        -- Puis on calcule £u_{n+1}£
        IF curVal % 2 = 0 THEN
            curVal := curVal / 2;
        ELSE
            curVal := 3*curVal + 1;
        END IF;
    END LOOP;
    -- N'oublions pas d'ajouter 1 ; cela évite aussi de terminer par une virgule
    resultat := resultat || CAST(1 AS varchar);
    -- "regex_split_to_array" a exactement le comportement suggéré par son nom ;-)
    RETURN regexp_split_to_array(resultat, ',');
    -- [ Version utilisant un tableau : ]
    --resultat[index] := 1;
    --RETURN resultat;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION resume(phrase varchar) RETURNS varchar AS $$
    WITH phraseTable AS (
        -- La ligne suivante est correcte avec PostGreSQL 9.3+
        --SELECT regexp_split_to_table(phrase, E'[,;.:?!"\'\\s]+') AS mots
        -- Cependant, PostGreSQL 9.1, installée sur la machine du TP, n'autorise 
        -- pas l'utilisation de l'argument nommé ; il faut remplacer par $1 :
        SELECT regexp_split_to_table($1, E'[,;.:?!"\'\\s]+') AS mots
    )
    SELECT string_agg( left(mots, 1), '' ) AS resume 
    FROM phraseTable;
$$ LANGUAGE SQL;

-- Auxiliaire : extrait un numéro de téléphone
CREATE FUNCTION formate_tel(tel varchar) RETURNS varchar AS $$
DECLARE
    str varchar;
BEGIN
    -- \D désigne "tout sauf un chiffre". On remplace donc toute suite de caractères 
    -- n'étant pas des chiffres par la chaîne vide. 'g' est un flag indiquant "global" ;
    -- si on l'omet, seule la première occurrence est remplacée. Concernant les expressions régulières 
    -- vous pouvez consulter cette page : http://www.postgresql.org/docs/9.1/static/functions-matching.html
    str := regexp_replace(tel, E'\\D+', '', 'g');
    IF length(str) <> 10 THEN
        RETURN NULL;
    END IF;
    -- left() et right() permettent de récupérer une sous-chaîne au début (resp. à la fin) de str.
    -- On peut aussi utiliser substr() partout. (Ou décider de ne pas utiliser de délimiteurs...)
    str := left(str,2) || '-' || substr(str,3,2) || '-' || substr(str,5,2) || '-' 
        || substr(str,7,2) || '-' || right(str,2);
    RETURN str;
END;
$$ LANGUAGE plpgsql;

-- Fonction principale : met à jour la table en formatant les numéros
CREATE FUNCTION formate_table(nomTable varchar) RETURNS void AS $$
BEGIN
    -- EXECUTE envoie une chaîne de caractères qui sera interprétée et exécutée en temps que 
    -- commande SQL par psql. On est obligé de procéder ainsi car les fonctions ne peuvent pas 
    -- prendre de tables en arguments (on passe donc juste le nom de la table). Il y a quelques 
    -- questions sur les forums à ce sujet, ici par exemple : 
    -- http://postgresql.1045698.n5.nabble.com/Passing-a-table-as-parameter-td4235966.html
    EXECUTE 'UPDATE ' || nomTable || ' SET tel = formate_tel(tel)';
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION connexe(n integer) RETURNS varchar ARRAY AS $$
    WITH RECURSIVE composante(id) AS (
            -- On initialise la composante connexe avec l'identifiant passé en paramètre
            SELECT id 
            FROM noeuds
            WHERE id = n
        UNION
            -- À chaque étape, l'idée est de chercher les voisins de l'ensemble des noeuds de
            -- la dernière table "récursive" stockée (composante). Le mot-clé UNION permet 
            -- d'éliminer les doublons dans les résultats de requêtes, et donc évite une boucle infinie.
            -- Enfin, on refait un UNION plus bas car il faut tester les deux extrémités des arêtes
            -- (celles-ci ne sont listées qu'une seule fois ; on a pas précisé (1,5) par exemple, 
            -- mais comme le graphe est non orientée elle existe).
            (WITH c AS (SELECT id FROM composante)
            SELECT v.id2
            FROM voisins v JOIN c ON v.id1 = c.id
                UNION
            SELECT v.id1
            FROM voisins v JOIN c ON v.id2 = c.id)
    ),
    -- À ce stade tout le boulot a été fait, il suffit de récupérer les informations associées 
    -- à chaque identifiant retourné par le "WITH RECURSIVE"
    infosVoisins AS (
        SELECT n.info
        FROM noeuds n
        WHERE n.id IN (SELECT * FROM composante)
    )
    -- array_agg() permet une lecture horizontale plus agréable ; si on voulait réutiliser les 
    -- résultats il serait préférable de les garder sous forme de table.
    SELECT array_agg(info) FROM infosVoisins;
$$ LANGUAGE SQL;

CREATE FUNCTION range_voisins() RETURNS void AS $$
    UPDATE voisins
    -- Rien de compliqué ici : il suffit de réordonner les identifiants sur chaque arête.
    SET (id1,id2) = (id2,id1)
    WHERE id1 > id2;
$$ LANGUAGE SQL;

-- Fonction auxiliaire : calcule la similarité entre un nom donné et un nom de console
CREATE FUNCTION get_str_simil(nom varchar, ref varchar) RETURNS varchar AS $$
DECLARE
    matchRatio REAL; -- le degré de similarité
    -- Les deux variables "canoniques" suivantes correspondant aux arguments nom et ref après 
    -- avoir supprimé tout ce qui n'était pas alphanumérique (virgules parasites par exemple).
    nomCanonic varchar := lower(regexp_replace(nom, '[^[:alnum:]]', '', 'g'));
    refCanonic varchar := lower(regexp_replace(ref, '[^[:alnum:]]', '', 'g'));
    -- Enfin dans le cas où les longueurs des chaînes diffèrent, évitons de déborder :
    minLength INTEGER := least(length(nomCanonic), length(refCanonic));
BEGIN
    matchRatio := 0.0;
    FOR i IN 1..minLength LOOP
        IF substr(nomCanonic,i,1) = substr(refCanonic,i,1) THEN
            matchRatio := matchRatio + 1.0;
        END IF;
    END LOOP;
    -- Formule : pourcentage de sous-chaîne commune (sur la longueur minLength), modulée par 
    -- la taille relative de minLength par rapport à la longueur de chaîne maximale.
    -- Cette formule n'a aucune raison d'être la "meilleure" - mais semble raisonnable
    RETURN (minLength::REAL / greatest(length(nomCanonic), length(refCanonic))) * (matchRatio / minLength);
END;
$$ LANGUAGE plpgsql;

-- Fonction trigger principale : remplace le nouveau nom (ou le nom mis à jour) 
-- par le nom de console le plus similaire
CREATE FUNCTION corrige_nom_console() RETURNS trigger AS $$
DECLARE
    rangee consoles%rowtype; -- type ligne de la table consoles (plus spécifique que "RECORD")
    matchRatio REAL; -- degré de similarité (entre deux chaînes de caractères)
    bestMatchRatio REAL := 0.0; -- plus haut degré de similarité trouvé
    bestMatch varchar := NULL; -- meilleure correspondance (parmi les noms de consoles)
    threshold_ratio REAL := 0.7; -- un seuil (arbitraire) sur les degrés de similarité
BEGIN
    FOR rangee IN (SELECT * FROM consoles) LOOP
        -- Pour chaque ligne de la table consoles, on applique la fonction auxiliaire
        matchRatio := get_str_simil(NEW.console, rangee.nom);
        IF matchRatio > bestMatchRatio THEN
            -- Si le ratio (degré) de similarité bat le précédent record, on l'enregistre
            bestMatchRatio := matchRatio;
            bestMatch := rangee.nom;
        END IF;
    END LOOP;
    -- Le test suivant est nécessaire pour s'assurer d'une bonne probabilité de succès.
    -- En effet si la meilleure similarité vaut par exemple 0.3, 
    -- l'utilisateur a certainement rentré n'importe quoi !
    IF bestMatchRatio > threshold_ratio THEN
        NEW.console := bestMatch;
		RETURN NEW;
    ELSE
		RAISE EXCEPTION 'Warning : no match found in consoles.nom ; transaction aborted';
		RETURN NULL;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Enfin, le trigger lui-même
CREATE TRIGGER t_corrige_nom_console BEFORE INSERT OR UPDATE
    ON jeux
    -- Note : "FOR EACH ROW" indique que la fonction trigger sera appelée 
    -- pour chaque ligne *concernée par l'ajout/modification/suppression* ;
    -- les autres lignes ne sont (heureusement) pas affectées.
    FOR EACH ROW
    EXECUTE PROCEDURE corrige_nom_console();

-- Ajout d'un attribut dans la table consoles : on l'initialise à FALSE.
-- En réalité il faudrait le calculer, mais ce n'est pas très grave pour l'exercice
ALTER TABLE consoles ADD obsolete BOOLEAN NOT NULL DEFAULT FALSE;

-- Fonction principale : change l'attribut "obsolete" si nécessaire
CREATE or replace FUNCTION corrige_obs_console() RETURNS trigger AS $$
BEGIN
    PERFORM 1 -- on utilise PERFORM au lieu de SELECT car on veut ignorer les lignes sélectionnées
              -- Tout ce qui nous intéresse est de savoir si la requête renvoie des lignes.
    FROM jeux
    WHERE console = OLD.console AND etat = 'neuf'
    LIMIT 1;
    IF NOT FOUND THEN
        -- Plus aucun jeu neuf n'est disponible pour la console
        UPDATE consoles
        SET obsolete = TRUE
        WHERE nom = OLD.console;
    END IF;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

-- Un trigger "AFTER" est suffisant : on n'est pas pressé.
CREATE TRIGGER t_corrige_obs_console AFTER DELETE
    ON jeux
    FOR EACH ROW
    EXECUTE PROCEDURE corrige_obs_console();

-- La version suivante recalcule tout à chaque modification :
-- c'est plus coûteux, mais beaucoup plus facile à écrire et relire.
-- Compte-tenu de l'application visée, cela peut être préférable 
-- (et comme on me l'a suggéré, peut aussi éviter certaines erreurs d'arrondis)
CREATE FUNCTION etudiants_maj_stats() RETURNS trigger AS $$
    UPDATE statistiques 
    SET (moyenne_generale, proportion_recus, nb_presents) = 
        ((SELECT AVG(note) FROM etudiants),
        (CAST((SELECT COUNT(*) FROM etudiants WHERE note >= 10) AS real) 
            / (SELECT COUNT(*) FROM etudiants)),
        -- le dernier calcul COUNT(*) est redondant, mais même en l'évitant on a dû effectuer 
        -- deux parcours complets de la table étudiants, et un comptage après sélection.
        (SELECT COUNT(*) FROM etudiants));
$$ LANGUAGE SQL;
-- Note : la fonction suppose que la table statistiques est initialement non vide
INSERT INTO statistiques VALUES (0.0, 0.0, 0);

-- J'avais initialement écrit cette version, effectuant les calculs incrémentalement.
-- C'est nettement plus laborieux, mais aussi plus efficace (O(1) au lieu de O(n)).
CREATE FUNCTION etudiants_maj_stats() RETURNS trigger AS $$
DECLARE
    mg REAL; -- moyenne_generale
    pr REAL; -- proportion_recus
    np INTEGER; -- nb_presents
    noteMin REAL := 10.0;
    deltaRecus INTEGER;

    -- deltaRecus est assez délicate à calculer car il y a beaucoup de cas :
    -- si l'étudiant était reçu mais ne l'est plus, s'il ne l'était pas et le devient, 
    -- s'il est inséré et l'est/ne l'est pas, ou s'il est supprimé et l'était (ou ne l'était pas).
    -- C'est pourquoi j'ai déclaré cette variable à part et isolé son calcul. 
    --
    -- Ensuite, nbPresents est très simple à mettre à jour, et moyenneGenerale s'obtient par 
    -- une formule obtenue assez facilement aussi.

BEGIN
    -- Commençons par récupérer les statistiques actuelles ;
    -- si la table est vide elles seront initialisées à NULL et inutilisées
    SELECT moyenne_generale FROM statistiques INTO mg;
    SELECT proportion_recus FROM statistiques INTO pr;
    SELECT nb_presents FROM statistiques INTO np;
    -- On a décidé de regrouper les événement INSERT, UPDATE et DELETE dans le même trigger ;
    -- il faut donc trier selon la valeur de TG_OP :
    CASE TG_OP
    WHEN 'INSERT' THEN
        IF NEW.note >= noteMin THEN
            deltaRecus := 1;
        ELSE
            deltaRecus := 0;
        END IF;
        PERFORM 1 FROM statistiques LIMIT 1;
        IF NOT FOUND THEN
            -- Cas où la table statistiques est vide (elle l'est au départ).
            INSERT INTO statistiques VALUES (NEW.note,deltaRecus,1);
        ELSE
            UPDATE statistiques 
            SET (moyenne_generale,proportion_recus,nb_presents) = 
                ((np*mg+NEW.note)/(np+1),(pr*np+deltaRecus)/(np+1),np+1);
        END IF;
        RETURN NEW;
    WHEN 'UPDATE' THEN
        deltaRecus := 0;
        IF NEW.note >= noteMin AND OLD.note < noteMin THEN
            deltaRecus := 1;
        ELSIF NEW.note < noteMin AND OLD.note >= noteMin THEN
            deltaRecus := -1;
        END IF;
        UPDATE statistiques 
        SET (moyenne_generale,proportion_recus,nb_presents) = 
            ((np*mg+NEW.note-OLD.note)/np,(pr*np+deltaRecus)/np,np);
        RETURN NEW;
    WHEN 'DELETE' THEN
        IF OLD.note >= noteMin THEN
            deltaRecus := -1;
        ELSE
            deltaRecus := 0;
        END IF;
        UPDATE statistiques 
        SET (moyenne_generale,proportion_recus,nb_presents) = 
            ((np*mg-OLD.note)/(np-1),(pr*np+deltaRecus)/(np-1),np-1);
        RETURN OLD;
    END CASE;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER t_maj_stats AFTER INSERT OR UPDATE OR DELETE
    ON etudiants
    FOR EACH ROW
    EXECUTE PROCEDURE etudiants_maj_stats();

-- Exemple de test :
INSERT INTO etudiants (note) VALUES (12);
SELECT * FROM statistiques;
INSERT INTO etudiants (note) VALUES (7),(18),(5);
SELECT * FROM statistiques;

-- Commençons par ajoute une colonne "matière" à la table statistiques (qui contiendra donc autant 
-- de lignes que de matières).
TRUNCATE statistiques; -- pour éviter une erreur à la ligne suivante (pourquoi ? laquelle ?)
ALTER TABLE statistiques ADD COLUMN matiere VARCHAR(20) PRIMARY KEY;

-- On crée la table "notes", pour éviter d'avoir à manipuler un tableau (associatif) de notes pour 
-- chaque étudiant. C'est plus pratique, et permet aussi de rester en première forme normale.
CREATE TABLE notes (
    id_etudiant INTEGER NOT NULL,
    matiere VARCHAR(20) NOT NULL,
    note REAL,
    PRIMARY KEY (id_etudiant, matiere)
);
-- Pour compléter la mise à jour, on supprime l'attribut note dans etudiants :
ALTER TABLE etudiants DROP COLUMN note;

-- L'attribut note de la table etudiants ayant glissé dans la table notes, la table etudiants ne nous 
-- est plus utile (il ne reste que le champ info). Le déclencheur modifie donc la table 
-- statistiques lors d'une action sur notes. Le code ressemble beaucoup à ce que l'on a déjà écrit.
-- Différences :
--     définition du trigger : CREATE TRIGGER ... ON notes ...
--     corps de la fonction : SELECT/UPDATE ... statistiques ... WHERE matiere = OLD.matiere 
--                                                              (ou NEW.matiere suivant le cas)

-- Version "simple mais coûteuse" :
CREATE FUNCTION notes_maj_stats() RETURNS trigger AS $$
DECLARE
    m VARCHAR(20); -- matiere
BEGIN
    -- Il faut retrouver la matière pour savoir quelle ligne mettre à jour
    IF TG_OP = 'INSERT' THEN
        m := NEW.matiere;
    ELSE
        m := OLD.matiere;
    END IF;
    -- On peut ensuite faire la mise à jour sur le même schéma que précédemment
    UPDATE statistiques 
    SET (moyenne_generale, proportion_recus, nb_presents) = 
        ((SELECT AVG(note) FROM notes WHERE matiere = m),
        (CAST((SELECT COUNT(*) FROM notes WHERE note >= 10 AND matiere = m) AS real) 
            / (SELECT COUNT(*) FROM notes WHERE matiere = m)),
        -- le dernier calcul COUNT(*) est redondant, comme précédemment
        (SELECT COUNT(*) FROM notes WHERE matiere = m))
    WHERE matiere = m;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- Note : la fonction suppose que chaque matière a une ligne dans la table statistiques ;
-- on peut le faire manuellement ou utiliser un autre trigger, ou ajouter un test...
INSERT INTO statistiques VALUES (0.0, 0.0, 0, 'maths');
INSERT INTO statistiques VALUES (0.0, 0.0, 0, 'sport');

CREATE TRIGGER t_maj_stats2 AFTER INSERT OR UPDATE OR DELETE
    ON notes
    FOR EACH ROW
    EXECUTE PROCEDURE notes_maj_stats();

-- La version plus efficace est similaire à etudiants_maj_stats(). 
-- Je ne l'écris pas ici pour ne pas surcharger la correction (déjà bien remplie).
-- Vous la trouverez dans les commentaires du fichier HTML.

-- Exemple de test :
INSERT INTO notes VALUES (1,'maths',6);
SELECT * FROM statistiques;
INSERT INTO notes VALUES (1,'sport',15);
SELECT * FROM statistiques;
INSERT INTO notes VALUES (2,'maths',16),(3,'maths',10);
INSERT INTO notes VALUES (2,'sport',8),(3,'sport',5);
SELECT * FROM statistiques;
-- Affichage final attendu :
 moyenne_generale | proportion_recus | nb_presents | matiere 
------------------+------------------+-------------+---------
          10.6667 |         0.666667 |           3 | maths
          9.33333 |         0.333333 |           3 | sport

CREATE FUNCTION check_lock_tuple() RETURNS trigger AS $$
DECLARE
    name_lock_attribute varchar := TG_ARGV[0];
    is_locked BOOLEAN;
BEGIN
    -- "USING" permet d'utiliser la ligne spéciale OLD ; c'est le seul moyen de l'injecter dans une requête 
    -- de type EXECUTE '...' (nécessaire car on ne dispose que de la chaîne de caractère "nom de l'attribut")
    EXECUTE 'SELECT $1.' || name_lock_attribute INTO is_locked USING OLD;
    IF is_locked THEN
        RAISE WARNING 'Error: tuple is locked!';
        RETURN OLD;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER t_lock_tuple BEFORE UPDATE
    ON nom_table
    FOR EACH ROW
    -- Dans un cas concret on remplaçerait name_lock_attribute par le vrai nom de l'attribut de blocage,
    -- et nom_table par le vrai nom de la table.
    EXECUTE PROCEDURE check_lock_tuple('name_lock_attribute');

ALTER TABLE ma_table DISABLE TRIGGER trigger;
-- puis lorsque la modification a été effectuée :
ALTER TABLE ma_table ENABLE TRIGGER trigger;

DELETE FROM ma_table WHERE id = ...;
INSERT INTO ma_table (...,...) VALUES (...,...);

-- J'ai séparé les fonctions associées à INSERT/UPDATE/DELETE, pour plus de lisibilité. 
-- On pourrait aussi les regrouper (cela permettrait d'avoir un seul trigger)
CREATE FUNCTION commerce_maj_tables_i() RETURNS trigger AS $$
DECLARE
    stockProduit INTEGER;
BEGIN
    SELECT stock FROM produits WHERE id = NEW.id_produit INTO stockProduit;
    -- On vérifie d'abord si les stocks sont suffisants
    IF stockProduit < NEW.quantite THEN
        RAISE EXCEPTION 'Transaction impossible : pas assez de produits en stock';
        RETURN NULL;
    END IF;
    -- On récupère le prix unitaire
    SELECT prix_unitaire FROM produits WHERE id = NEW.id_produit INTO NEW.prix_unitaire;
    -- Finalement, mise à jour des tables produits et entetes_commandes
    UPDATE produits SET stock = stockProduit - NEW.quantite WHERE id = NEW.id_produit;
    UPDATE entetes_commandes SET montant = montant + NEW.quantite * NEW.prix_unitaire WHERE id = NEW.id_entete;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION commerce_maj_tables_u() RETURNS trigger AS $$
DECLARE
    stockProduit INTEGER;
    deltaMontant REAL;
    deltaStock INTEGER;
BEGIN
    SELECT stock FROM produits WHERE id = NEW.id_produit INTO stockProduit;
    -- On vérifie d'abord si les stocks sont suffisants
    IF (NEW.id_produit = OLD.id_produit AND stockProduit < (NEW.quantite - OLD.quantite)) OR
       (NEW.id_produit <> OLD.id_produit AND stockProduit < NEW.quantite) 
    THEN
        RAISE EXCEPTION 'Transaction impossible : pas assez de produits en stock';
        RETURN OLD;
    END IF;
    deltaMontant := NEW.prix_unitaire * NEW.quantite - OLD.prix_unitaire * OLD.quantite;
    deltaStock := OLD.quantite - NEW.quantite;
    -- Les attributs peuvent tous être mis à jour. On les traite séquentiellement
    IF NEW.id_entete <> OLD.id_entete THEN
        -- Transfert du détail de commande (p.ex. si on s'était trompé de commande)
        UPDATE entetes_commandes SET montant = montant - OLD.prix_unitaire * OLD.quantite WHERE id = OLD.id_entete;
        deltaMontant := deltaMontant + OLD.prix_unitaire * OLD.quantite;
    END IF;
    IF NEW.id_produit <> OLD.id_produit THEN
        -- Changement d'identifiant (p.ex. si on s'était trompé de produit) ;
        -- Dans ce cas il faut recalculer le prix unitaire, et le stock
        deltaMontant := deltaMontant - NEW.prix_unitaire * NEW.quantite;
        SELECT prix_unitaire FROM produits WHERE id = NEW.id_produit INTO NEW.prix_unitaire;
        deltaMontant := deltaMontant + NEW.prix_unitaire * NEW.quantite;
        UPDATE produits SET stock = stock + OLD.quantite WHERE id = OLD.id_produit;
        deltaStock := deltaStock - OLD.quantite;
    ELSIF NEW.prix_unitaire <> OLD.prix_unitaire THEN
        -- Dans le cas contraire, interdiction de changer le prix unitaire
        RAISE NOTICE 'Attention : tentative de modifier le prix unitaire (invariable). Ce dernier n''a pas été mis à jour';
        NEW.prix_unitaire := OLD.prix_unitaire;
        deltaMontant := OLD.prix_unitaire * (NEW.quantite - OLD.quantite);
    END IF;
    UPDATE entetes_commandes SET montant = montant + deltaMontant WHERE id = NEW.id_entete;
    UPDATE produits SET stock = stock + deltaStock WHERE id = NEW.id_produit;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION commerce_maj_tables_d() RETURNS trigger AS $$
BEGIN
    -- Mise à jour du stock du produit et du montant de la commande
    UPDATE produits SET stock = stock + OLD.quantite WHERE id = OLD.id_produit;
    UPDATE entetes_commandes SET montant = montant - OLD.quantite * OLD.prix_unitaire WHERE id = OLD.id_entete;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER t_commerce_maj_tables_i BEFORE INSERT
    ON details_commandes
    FOR EACH ROW
    EXECUTE PROCEDURE commerce_maj_tables_i();

CREATE TRIGGER t_commerce_maj_tables_u BEFORE UPDATE
    ON details_commandes
    FOR EACH ROW
    EXECUTE PROCEDURE commerce_maj_tables_u();

CREATE TRIGGER t_commerce_maj_tables_d AFTER DELETE
    ON details_commandes
    FOR EACH ROW
    EXECUTE PROCEDURE commerce_maj_tables_d();

-- Note : les tables et fonctions systèmes pg_* sont dans le schéma pg_catalog, 
-- mais on a pas besoin de le spécifier (même après un set search_path ...) ;
-- sauf bien sûr si on a créé quelque chose du même nom dans le schéma public.
CREATE FUNCTION nettoie_funcs() RETURNS void AS $$
DECLARE
    i RECORD; -- une ligne générique
BEGIN
    FOR i IN
        -- La sous-requête suivante récupère les noms et arguments des fonctions ;
        -- c'est nécessaire (et suffisant) pour les supprimer via DROP FUNCTION
        (SELECT p.proname AS nomFonction, 
                pg_get_function_identity_arguments(p.oid) AS arguments
        FROM pg_proc p
        JOIN pg_namespace n
            ON p.pronamespace = n.oid
        -- On se restreint au namespace 'public' (à remplacer par le nom du schéma 
        -- si vous avez travaillé dans un autre schéma). Si on n'indique pas cette clause, 
        -- beaucoup d'erreurs s'afficheront : "impossible de supprimer telle fonction système" 
        -- (heureusement !) car on n'en est pas le/la propriétaire.
        WHERE n.nspname = 'public')
    LOOP
        EXECUTE 'DROP FUNCTION ' || i.nomFonction || '(' || i.arguments || ')';
    END LOOP;
END;
-- "SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that created it"
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Note : solution tentante mais impossible car on n'est pas propriétaire de pg_proc.
CREATE FUNCTION nettoie_funcs() RETURNS void AS $$
    DELETE FROM pg_proc WHERE oid IN
        (SELECT p.oid
        FROM pg_proc p
        JOIN pg_namespace n
            ON p.pronamespace = n.oid
        WHERE n.nspname = 'public')
$$ LANGUAGE SQL;

CREATE FUNCTION nettoie_trigs() RETURNS void AS $$
DECLARE
    triggNameRecord RECORD;
    triggTableRecord RECORD;
BEGIN
    FOR triggNameRecord IN 
        -- Récupération des noms de triggers dans le schéma public (ou autre suivant votre cas)
        (SELECT DISTINCT trigger_name 
         FROM information_schema.triggers 
         WHERE trigger_schema = 'public') 
    LOOP
        FOR triggTableRecord IN 
            -- Il faut supprimer toutes les variantes du trigger : en effet 
            -- un trigger de même nom peut être associé à plusieurs tables.
            (SELECT DISTINCT event_object_table 
             FROM information_schema.triggers 
             WHERE trigger_name = triggNameRecord.trigger_name) 
        LOOP
            RAISE NOTICE 'Dropping trigger: % on table: %', 
                triggNameRecord.trigger_name, 
                triggTableRecord.event_object_table;
            EXECUTE 'DROP TRIGGER ' || triggNameRecord.trigger_name 
                || ' ON ' || triggTableRecord.event_object_table;
        END LOOP;
    END LOOP;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

SELECT nettoie_trigs();
SELECT * FROM information_schema.triggers;
-- Devrait afficher "0 rows"

SELECT nettoie_funcs();
SELECT p.proname
FROM pg_proc p
JOIN pg_namespace n
    ON p.pronamespace = n.oid
WHERE n.nspname = 'public';
-- Devrait afficher "0 rows" ; nettoie_funcs() a elle-même été supprimée.