07 - Fonctions et stored procedures en PL/pgSQL

Ce que tu vas apprendre

• La différence entre une fonction et une procedure
• PL/pgSQL : variables, contrôle de flux, exceptions
• RETURN TABLE, RETURN QUERY, SETOF
• Quand mettre de la logique dans la base vs dans l'application

Prerequisites

Avoir lu les articles sur les transactions et les bases du SQL.

Fonction vs procedure

Depuis PostgreSQL 11, il existe deux concepts distincts :

• Fonction : retourne une valeur (scalaire, table, SETOF). Utilisable dans un SELECT. Ne peut pas gerer ses propres transactions (pas de COMMIT/ROLLBACK a l'intérieur).
• Procedure : ne retourne rien (ou des paramètres INOUT). Appelable avec CALL. Peut gerer ses propres transactions.

En pratique, 90% du temps tu ecris des fonctions. Les procedures sont utiles quand tu as besoin de commiter au milieu d'une opération (un batch de traitement par exemple).

Une fonction simple

sqlCREATE OR REPLACE FUNCTION calculate_total(p_order_id INT)
RETURNS NUMERIC AS $
DECLARE
  total NUMERIC := 0;
BEGIN
  SELECT SUM(unit_price * quantity) INTO total
  FROM order_items
  WHERE order_id = p_order_id;

  RETURN COALESCE(total, 0);
END;
$ LANGUAGE plpgsql;

Utilisation :

sqlSELECT calculate_total(1);
-- Resultat : 149.98

SELECT o.id, o.status, calculate_total(o.id) AS total
FROM orders o;

Le $ est un delimiteur de bloc. Ca évité de devoir echapper les apostrophes. DECLARE pour déclarer les variables locales. INTO pour stocker le résultat d'une requête dans une variable.

Le prefixe p_ sur les paramètres (p_order_id) est une convention pour éviter les conflits de noms avec les colonnes. Sans ca, order_id = order_id dans le WHERE serait toujours vrai (la colonne comparee a elle-meme). Piege classique.

Variables et types

sqlCREATE OR REPLACE FUNCTION demo_variables()
RETURNS TEXT AS $
DECLARE
  counter INT := 0;
  user_name TEXT;
  now_ts TIMESTAMP := NOW();
  user_row users%ROWTYPE;  -- une ligne complete de la table users
  price products.price%TYPE;  -- le type de la colonne products.price
BEGIN
  -- Affecter une valeur
  counter := counter + 1;

  -- Recuperer une ligne entiere
  SELECT * INTO user_row FROM users WHERE id = 1;

  RETURN user_row.name || ' - counter: ' || counter;
END;
$ LANGUAGE plpgsql;

%ROWTYPE prend la structure d'une table. %TYPE prend le type d'une colonne. C'est plus maintenable que de coder le type en dur, parce que si la colonne change de type, la fonction suit.

Contrôle de flux

IF / ELSIF / ELSE

sqlCREATE OR REPLACE FUNCTION get_price_tier(p_price NUMERIC)
RETURNS TEXT AS $
BEGIN
  IF p_price < 25 THEN
    RETURN 'budget';
  ELSIF p_price < 100 THEN
    RETURN 'standard';
  ELSIF p_price < 500 THEN
    RETURN 'premium';
  ELSE
    RETURN 'luxe';
  END IF;
END;
$ LANGUAGE plpgsql;

Boucles

sqlCREATE OR REPLACE FUNCTION sum_first_n(n INT)
RETURNS INT AS $
DECLARE
  total INT := 0;
  i INT;
BEGIN
  FOR i IN 1..n LOOP
    total := total + i;
  END LOOP;
  RETURN total;
END;
$ LANGUAGE plpgsql;

-- Boucle sur un resultat de requete
CREATE OR REPLACE FUNCTION list_expensive_products(min_price NUMERIC)
RETURNS TEXT AS $
DECLARE
  prod RECORD;
  result TEXT := '';
BEGIN
  FOR prod IN SELECT name, price FROM products WHERE price > min_price LOOP
    result := result || prod.name || ': ' || prod.price || E'\n';
  END LOOP;
  RETURN result;
END;
$ LANGUAGE plpgsql;

RECORD est un type générique qui prend la forme du résultat de la requête. Utile quand tu ne connais pas la structure a l'avance.

RETURN TABLE et RETURN QUERY

Pour retourner plusieurs lignes :

sqlCREATE OR REPLACE FUNCTION get_customer_orders(p_customer_id INT)
RETURNS TABLE (
  order_id INT,
  total NUMERIC,
  status TEXT,
  created_at TIMESTAMP
) AS $
BEGIN
  RETURN QUERY
  SELECT o.id, o.total, o.status, o.created_at
  FROM orders o
  WHERE o.customer_id = p_customer_id
  ORDER BY o.created_at DESC;
END;
$ LANGUAGE plpgsql;

Utilisation :

sqlSELECT * FROM get_customer_orders(1);

-- Ou avec un filtre supplementaire
SELECT * FROM get_customer_orders(1) WHERE status = 'completed';

La fonction se comporte comme une table. Tu peux la filtrer, la joindre, l'utiliser dans un FROM. C'est tres puissant pour encapsuler des requêtes complexes avec de la logique conditionnelle.

Les procedures

sqlCREATE OR REPLACE PROCEDURE transfer_funds(
  p_sender INT,
  p_receiver INT,
  p_amount NUMERIC
) AS $
DECLARE
  sender_balance NUMERIC;
BEGIN
  -- Verifier le solde
  SELECT balance INTO sender_balance FROM accounts WHERE id = p_sender FOR UPDATE;

  IF sender_balance IS NULL THEN
    RAISE EXCEPTION 'Compte expediteur % introuvable', p_sender;
  END IF;

  IF sender_balance < p_amount THEN
    RAISE EXCEPTION 'Solde insuffisant: % disponible, % demande',
      sender_balance, p_amount;
  END IF;

  -- Effectuer le transfert
  UPDATE accounts SET balance = balance - p_amount WHERE id = p_sender;
  UPDATE accounts SET balance = balance + p_amount WHERE id = p_receiver;

  -- Log
  RAISE NOTICE 'Transfert de % de compte % vers compte % effectue',
    p_amount, p_sender, p_receiver;
END;
$ LANGUAGE plpgsql;

-- Appel
CALL transfer_funds(1, 2, 100.00);

Note le FOR UPDATE dans le SELECT : ca verrouille la ligne du compte expediteur pendant la transaction (voir l'article 06 sur les locks).

RAISE : logs et exceptions

sql-- Niveaux de log
RAISE DEBUG 'Message debug (pas visible par defaut)';
RAISE NOTICE 'Info visible dans psql';
RAISE WARNING 'Attention, quelque chose de suspect';
RAISE EXCEPTION 'Erreur fatale, annule la transaction';

-- Avec des parametres
RAISE NOTICE 'Utilisateur % a effectue % operations', user_id, op_count;

-- Code d'erreur personnalise
RAISE EXCEPTION 'Fonds insuffisants'
  USING ERRCODE = 'insufficient_funds',
        HINT = 'Verifiez le solde du compte';

Gestion des exceptions

sqlCREATE OR REPLACE FUNCTION safe_insert_user(p_email TEXT, p_name TEXT)
RETURNS TEXT AS $
BEGIN
  INSERT INTO users (email, name) VALUES (p_email, p_name);
  RETURN 'OK';
EXCEPTION
  WHEN unique_violation THEN
    RETURN 'Email deja utilise: ' || p_email;
  WHEN check_violation THEN
    RETURN 'Donnees invalides';
  WHEN OTHERS THEN
    RETURN 'Erreur inattendue: ' || SQLERRM;
END;
$ LANGUAGE plpgsql;

sqlSELECT safe_insert_user('alice@mail.com', 'Alice');
-- 'OK'
SELECT safe_insert_user('alice@mail.com', 'Alice 2');
-- 'Email deja utilise: alice@mail.com'

Le bloc EXCEPTION attrape les erreurs et empeche la transaction de planter. SQLERRM contient le message d'erreur. La doc PostgreSQL sur les codes d'erreur liste tous les codes possibles.

Fonctions SQL pures (sans PL/pgSQL)

Pour les cas simples, une fonction SQL sans PL/pgSQL est plus legere :

sqlCREATE OR REPLACE FUNCTION price_with_tax(p_price NUMERIC, p_rate NUMERIC DEFAULT 0.20)
RETURNS NUMERIC AS $
  SELECT ROUND(p_price * (1 + p_rate), 2);
$ LANGUAGE sql;

SELECT price_with_tax(100);     -- 120.00
SELECT price_with_tax(100, 0.055); -- 105.50

Les fonctions SQL pures sont inlinables par le planificateur (il peut les intégrer directement dans la requête appelante). Les fonctions PL/pgSQL ne le sont pas. Donc si ta fonction est un simple calcul, préféré LANGUAGE sql.

Quand mettre la logique dans la base

Mon opinion, et c'est un sujet de debat : la logique métier appartient a l'application, pas a la base. Les fonctions PostgreSQL sont ideales pour :

• Les calculs proches des donnees (aggregations sur des millions de lignes)
• Les contraintes de coherence qu'on ne peut pas garantir autrement
• Les triggers (timestamps, audit, voir l'article 08)
• Les opérations batch qui doivent etre atomiques

Mais les regles métier (est-ce qu'un utilisateur peut passer une commande, quel est le prix apres les promotions, etc.) doivent rester dans le code TypeScript/Python/Go. Parce que le code applicatif est version contrôle avec Git, testable avec des tests unitaires, et déployé avec un process CI/CD. Les fonctions SQL, c'est plus complique a versionner et tester.

Si tu suis la serie sur l'architecture hexagonale, les fonctions SQL sont des détails d'implementation de tes adaptateurs, pas de la logique domaine.

Sur paltemps.fr, on limite les fonctions PL/pgSQL aux triggers et aux requêtes d'analyse trop complexes pour un ORM.

Résumé

• Les fonctions retournent une valeur, les procedures non (mais gerent les transactions)
• PL/pgSQL : DECLARE, IF/ELSIF, FOR, LOOP, RAISE, EXCEPTION
• RETURN TABLE + RETURN QUERY pour retourner des résultats tabulaires
• Les fonctions LANGUAGE sql sont inlinables et plus performantes pour les cas simples
• Logique métier dans l'app, logique donnees dans la base

Article précédent : 06 - Transactions et ACID Article suivant : 08 - Les triggers

Sources

• PostgreSQL Documentation - PL/pgSQL
• PostgreSQL Documentation - CREATE FUNCTION
• PostgreSQL Documentation - Error Codes