Formations — 1 037 leçons en français

Accueil › SQL & bases de données › Fonctions et procédures stockées

Fonctions et procédures stockées

Vues, fonctions et triggersintermediaire

PostgreSQL permet de stocker de la logique dans la base. La question n'est pas « peut-on ? » mais « faut-il ? ».

Fonction SQL — la plus simple, et souvent intégrée à la requête appelante :

CREATE FUNCTION total_commande(p_commande_id integer)
RETURNS numeric
LANGUAGE sql STABLE AS $$
    SELECT COALESCE(sum(quantite * prix_unitaire), 0)
    FROM ligne_commande WHERE commande_id = p_commande_id;
$$;

SELECT reference, total_commande(id) FROM commande;

Fonction PL/pgSQL — dès qu'il faut des variables, des boucles ou des exceptions :

CREATE FUNCTION valider_commande(p_id integer)
RETURNS void
LANGUAGE plpgsql AS $$
DECLARE v_lignes integer;
BEGIN
    SELECT count(*) INTO v_lignes FROM ligne_commande WHERE commande_id = p_id;
    IF v_lignes = 0 THEN
        RAISE EXCEPTION 'Commande % sans ligne', p_id USING ERRCODE = 'check_violation';
    END IF;
    UPDATE commande SET statut = 'validee' WHERE id = p_id;
END;
$$;

La volatilité compte : IMMUTABLE, STABLE ou VOLATILE (défaut). Une fonction VOLATILE est réévaluée à chaque ligne et ne peut pas être indexée. Déclarez juste — mais ne mentez pas, la base met en cache en conséquence.

Procédure (CREATE PROCEDURE, appelée par CALL) : la seule à pouvoir faire COMMIT en cours de route, donc à traiter par lots.

Les termes à connaître

Fonction SQL

Écrite en SQL pur (LANGUAGE sql). Simple, et surtout intégrable (inlinable) dans la requête appelante quand elle est déclarée IMMUTABLE ou STABLE — donc sans surcoût.

PL/pgSQL

Le langage procédural de PostgreSQL : variables, boucles, conditions, gestion d'exceptions. Nécessaire dès qu'il y a de la logique, mais jamais intégré dans la requête appelante.

IMMUTABLE / STABLE / VOLATILE

La volatilité déclarée. IMMUTABLE : même entrée, même sortie, toujours (indexable). STABLE : constante pendant une instruction (now()). VOLATILE (défaut) : peut varier à chaque appel (random()).

Procédure

Créée avec CREATE PROCEDURE, appelée par CALL. Contrairement à une fonction, elle peut contenir COMMIT et ROLLBACK — utile pour un traitement par lots.

RETURNS TABLE

Une fonction qui renvoie un ensemble de lignes, utilisable dans un FROM comme une table paramétrée.

SECURITY DEFINER

Exécute la fonction avec les droits de son créateur. Puissant et risqué : toujours fixer un search_path explicite, sinon un objet malveillant peut être appelé à la place de celui attendu.

Lire l'explication détaillée et faire le quiz →

Cette page présente la partie essentielle de la leçon. Les exemples commentés, les pièges courants et les trois questions de quiz sont dans l'application.