Procédures et fonctions

Obtenir une valeur, ou agir

En PostgreSQL, une fonction (CREATE FUNCTION) renvoie une valeur et s'utilise à l'intérieur d'une requête. Une procédure (CREATE PROCEDURE, depuis PostgreSQL 11) n'a pas de valeur de retour, s'appelle seule avec CALL, et peut valider ou annuler des transactions pendant son exécution.¹

RETURNS dans l'en-tête, RETURN dans le corps

Une fonction qui calcule un prix TTC, avec par défaut le taux normal de la TVA suisse, 8,1 %.⁵

CREATE OR REPLACE FUNCTION calculer_ttc(prix NUMERIC, tva NUMERIC DEFAULT 0.081)
RETURNS NUMERIC
AS $$
BEGIN
    RETURN round(prix * (1 + tva), 2);   -- arrondi au centime
END;
$$ LANGUAGE plpgsql;

SELECT calculer_ttc(100);                 -- 108.10
SELECT calculer_ttc(100, 0.026);          -- 102.60 (taux réduit)
SELECT calculer_ttc(tva => 0.026, prix => 20);   -- 20.52, paramètres nommés
SELECT nom, calculer_ttc(prix) AS prix_ttc FROM produits;

-- dans un bloc PL/pgSQL : affectation à une variable
DO $$
DECLARE v_total NUMERIC;
BEGIN
    v_total := calculer_ttc(100, 0.026);
    RAISE NOTICE 'v_total = %', v_total;    -- v_total = 102.60
END $$;

CALL, et des valeurs qui entrent ou sortent

CREATE OR REPLACE PROCEDURE augmenter_salaire(
    p_employe_id INTEGER,
    p_pourcentage NUMERIC
)
AS $$
BEGIN
    UPDATE employes
    SET salaire = round(salaire * (1 + p_pourcentage / 100), 2)
    WHERE id = p_employe_id;

    RAISE NOTICE 'Salaire augmenté de %', p_pourcentage || '%';
END;
$$ LANGUAGE plpgsql;

CALL augmenter_salaire(42, 10);                                 -- Léa : 2000.00 → 2200.00
CALL augmenter_salaire(p_employe_id => 42, p_pourcentage => 10); -- paramètres nommés

Les trois modes de paramètres

BEGIN … EXCEPTION WHEN … THEN

Un bloc PL/pgSQL peut avoir une section EXCEPTION : si une instruction du bloc lève une erreur, PostgreSQL abandonne le reste du bloc, annule ce que le bloc a modifié et cherche une clause WHEN correspondante.² Fais défiler : la flèche suit l'exécution.

Une fonction qui se lit comme une table

RETURNS TABLE (…) (ou RETURNS SETOF type) déclare un résultat de plusieurs lignes. Dans le corps, RETURN QUERY et RETURN NEXT ne quittent pas la fonction : ils ajoutent des lignes au résultat, et c'est la fin de la fonction, ou un RETURN sans argument, qui le renvoie.²

CREATE OR REPLACE FUNCTION get_top_salaries(n INTEGER)
RETURNS TABLE(nom VARCHAR, salaire NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT e.nom, e.salaire      -- alias e : évite le conflit avec nom et salaire
    FROM employes e
    ORDER BY e.salaire DESC
    LIMIT n;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_top_salaries(3);

  nom  | salaire
-------+---------
 Zoé   |    6100
 Noah  |    5200
 Lina  |    4800

COMMIT dans une procédure, jamais dans une fonction

Une procédure appelée par CALL (comme un bloc DO) peut terminer la transaction en cours avec COMMIT ou ROLLBACK ; une nouvelle commence aussitôt.² Trois limites : jamais dans une fonction ; jamais dans un bloc qui a une section EXCEPTION, qui forme une sous-transaction ; jamais si le CALL est lui-même dans une transaction ouverte par BEGIN.¹ Dans ces trois cas, PostgreSQL 18 répond invalid transaction termination.

-- comptes(id, solde) avec la contrainte CHECK (solde >= 0)
CREATE OR REPLACE PROCEDURE transferer_fonds(
    p_from INTEGER,
    p_to INTEGER,
    p_montant NUMERIC
)
AS $$
BEGIN
    UPDATE comptes SET solde = solde - p_montant WHERE id = p_from;
    UPDATE comptes SET solde = solde + p_montant WHERE id = p_to;
    COMMIT;
    RAISE NOTICE 'Transfert de % effectué', p_montant;
END;
$$ LANGUAGE plpgsql;

Six bonnes pratiques

• préfixer les paramètres (p_) et les variables (v_) : ils ne se confondent plus avec les colonnes ;
• déclarer les variables avec %TYPE : v_sal employes.salaire%TYPE suit le type de la colonne s'il change un jour³ ;
• ne pas réaffecter un paramètre IN : c'est trompeur en PL/pgSQL et refusé en Oracle ;
• pour une requête dont on ignore le résultat, écrire PERFORM : un SELECT sans INTO provoque l'erreur query has no destination for result data ;
• n'intercepter que les erreurs qu'on sait traiter, et nommer la condition (division_by_zero) plutôt que OTHERS ;
• arrondir les montants (round(…, 2)) : les calculs en NUMERIC allongent vite le nombre de décimales.

Trois exercices corrigés

Tables utilisées : eleves(id, nom, classe) et notes(eleve_id, note), notes de 1 à 6. Tous les corrigés ont été exécutés dans PostgreSQL 18.

CREATE OR REPLACE FUNCTION moyenne_classe(p_classe TEXT)
RETURNS NUMERIC AS $$
DECLARE
    v_moyenne NUMERIC;
BEGIN
    SELECT round(avg(n.note), 2) INTO v_moyenne
    FROM notes n JOIN eleves e ON e.id = n.eleve_id
    WHERE e.classe = p_classe;
    RETURN v_moyenne;
END;
$$ LANGUAGE plpgsql;

SELECT moyenne_classe('2M3');   -- 4.75 avec les notes 5.5, 4.5, 4 et 5

CREATE OR REPLACE PROCEDURE ajouter_note(p_eleve INTEGER, p_note NUMERIC)
AS $$
BEGIN
    IF p_note < 1 OR p_note > 6 THEN
        RAISE EXCEPTION 'Note % hors de l''échelle 1 à 6', p_note;
    END IF;
    INSERT INTO notes (eleve_id, note) VALUES (p_eleve, p_note);
END;
$$ LANGUAGE plpgsql;

CALL ajouter_note(3, 5.5);   -- enregistrée
CALL ajouter_note(3, 7);     -- ERREUR : Note 7 hors de l'échelle 1 à 6

CREATE OR REPLACE FUNCTION nb_eleves(p_classe TEXT)
RETURNS INTEGER AS $$
BEGIN
    SELECT count(*) FROM eleves WHERE classe = p_classe;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION nb_eleves(p_classe TEXT)
RETURNS INTEGER AS $$
DECLARE
    v_nb INTEGER;
BEGIN
    SELECT count(*) INTO v_nb FROM eleves WHERE classe = p_classe;
    RETURN v_nb;
END;
$$ LANGUAGE plpgsql;

La fiche en huit lignes

Teste-toi