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éeIMMUTABLEouSTABLE— 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 parCALL. Contrairement à une fonction, elle peut contenirCOMMITetROLLBACK— utile pour un traitement par lots.- RETURNS TABLE
Une fonction qui renvoie un ensemble de lignes, utilisable dans un
FROMcomme 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_pathexplicite, sinon un objet malveillant peut être appelé à la place de celui attendu.
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.