🔝 Retour au Sommaire
PL/Python est un langage procédural pour PostgreSQL qui permet d'écrire des fonctions et des procédures stockées en Python. C'est une extension puissante qui combine la flexibilité et la richesse de l'écosystème Python avec la robustesse de PostgreSQL.
- Familiarité : Si vous connaissez déjà Python, vous pouvez réutiliser vos connaissances
- Écosystème riche : Accès aux bibliothèques Python (NumPy, pandas, requests, etc.)
- Logique complexe : Facilite l'implémentation d'algorithmes complexes
- Traitement de données : Idéal pour les transformations, analyses et calculs avancés
- Intégration : Permet d'appeler des APIs externes, manipuler des fichiers JSON, etc.
| Aspect | PL/pgSQL | PL/Python |
|---|---|---|
| Syntaxe | Spécifique PostgreSQL | Python standard |
| Performance | Très rapide | Moins rapide (interprété) |
| Bibliothèques | Limitées | Écosystème Python complet |
| Courbe d'apprentissage | Moyenne | Facile si Python connu |
| Cas d'usage | Logique SQL pure | Logique complexe, APIs, ML |
PostgreSQL propose deux variantes de PL/Python :
-
plpython3u (Untrusted) : Version "non sécurisée"
- Accès complet au système de fichiers
- Peut importer n'importe quelle bibliothèque Python
- Nécessite les privilèges superutilisateur pour créer des fonctions
- Recommandé pour la plupart des usages modernes
-
plpythonu (legacy, Python 2) : Supprimé depuis PostgreSQL 16. N'existe plus dans PG 18
-- Nécessite les privilèges superutilisateur
CREATE EXTENSION plpython3u;Note importante : PL/Python utilise la version de Python installée sur le système. Sur la plupart des distributions modernes, il s'agit de Python 3.x.
CREATE OR REPLACE FUNCTION python_version()
RETURNS text AS $$
import sys
return sys.version
$$ LANGUAGE plpython3u;
SELECT python_version();
-- Résultat : "3.11.5 (main, Sep 11 2023, ...) ..."CREATE OR REPLACE FUNCTION nom_fonction(param1 type1, param2 type2)
RETURNS type_retour AS $$
# Code Python ici
# Les paramètres sont directement accessibles
result = param1 + param2
return result
$$ LANGUAGE plpython3u;- Délimiteurs
$$: Encadrent le code Python (alternative aux guillemets simples) - Indentation Python : Respecter l'indentation Python standard
- Variables automatiques : Les paramètres de la fonction sont des variables Python
- Instruction
return: Retourne la valeur à PostgreSQL - Clause
LANGUAGE plpython3u: Indique le langage utilisé
PostgreSQL convertit automatiquement les types entre SQL et Python :
| Type PostgreSQL | Type Python | Exemple |
|---|---|---|
INTEGER, BIGINT |
int |
42 |
NUMERIC, DECIMAL |
Decimal |
Decimal('123.45') |
REAL, DOUBLE PRECISION |
float |
3.14 |
TEXT, VARCHAR |
str |
"Hello" |
BOOLEAN |
bool |
True, False |
DATE |
datetime.date |
date(2025, 11, 22) |
TIMESTAMP |
datetime.datetime |
datetime(2025, 11, 22, 14, 30) |
ARRAY |
list |
[1, 2, 3] |
JSON, JSONB |
dict ou list |
{"key": "value"} |
NULL |
None |
None |
CREATE OR REPLACE FUNCTION demo_types(
un_entier INTEGER,
un_texte TEXT,
une_date DATE,
un_tableau INTEGER[]
)
RETURNS TEXT AS $$
# Les types sont automatiquement convertis
import datetime
# un_entier est un int Python
double = un_entier * 2
# un_texte est un str Python
majuscules = un_texte.upper()
# une_date est un datetime.date
dans_un_an = une_date + datetime.timedelta(days=365)
# un_tableau est une list Python
somme = sum(un_tableau)
return f"Double: {double}, Texte: {majuscules}, Date+1an: {dans_un_an}, Somme: {somme}"
$$ LANGUAGE plpython3u;
-- Utilisation
SELECT demo_types(10, 'postgresql', '2025-11-22', ARRAY[1,2,3,4,5]);
-- Résultat : "Double: 20, Texte: POSTGRESQL, Date+1an: 2026-11-22, Somme: 15"CREATE OR REPLACE FUNCTION calculer_tva(prix_ht NUMERIC, taux_tva NUMERIC)
RETURNS NUMERIC AS $$
# Calcul simple
tva = prix_ht * (taux_tva / 100)
prix_ttc = prix_ht + tva
return prix_ttc
$$ LANGUAGE plpython3u;
SELECT calculer_tva(100.00, 20.0);
-- Résultat : 120.00CREATE OR REPLACE FUNCTION formater_nom_complet(prenom TEXT, nom TEXT)
RETURNS TEXT AS $$
# Utilisation des méthodes Python sur les chaînes
prenom_format = prenom.strip().capitalize()
nom_format = nom.strip().upper()
return f"{prenom_format} {nom_format}"
$$ LANGUAGE plpython3u;
SELECT formater_nom_complet(' jean ', ' dupont ');
-- Résultat : "Jean DUPONT"CREATE OR REPLACE FUNCTION valider_email(email TEXT)
RETURNS BOOLEAN AS $$
import re
# Expression régulière pour valider un email (simplifiée)
pattern = r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
if email is None:
return False
return bool(re.match(pattern, email))
$$ LANGUAGE plpython3u;
SELECT valider_email('user@example.com'); -- TRUE
SELECT valider_email('invalid.email'); -- FALSE L'un des cas d'usage les plus puissants de PL/Python est la manipulation de données JSON/JSONB.
CREATE OR REPLACE FUNCTION extraire_infos_json(data JSONB)
RETURNS TABLE(nom TEXT, age INTEGER, ville TEXT) AS $$
# data est automatiquement converti en dict Python
# Extraction simple
nom = data.get('nom', 'Inconnu')
age = data.get('age', 0)
# Navigation dans des structures imbriquées
adresse = data.get('adresse', {})
ville = adresse.get('ville', 'Non spécifiée')
return [(nom, age, ville)]
$$ LANGUAGE plpython3u;
-- Utilisation
SELECT * FROM extraire_infos_json('
{
"nom": "Alice Dubois",
"age": 30,
"adresse": {
"ville": "Paris",
"code_postal": "75001"
}
}'::JSONB);
-- Résultat :
-- nom | age | ville
-- Alice Dubois | 30 | ParisCREATE OR REPLACE FUNCTION creer_profil_json(
nom TEXT,
prenom TEXT,
age INTEGER
)
RETURNS JSONB AS $$
import json
from datetime import datetime
# Création d'un dictionnaire Python
profil = {
"nom_complet": f"{prenom} {nom}",
"age": age,
"categorie": "majeur" if age >= 18 else "mineur",
"timestamp": datetime.now().isoformat(),
"metadata": {
"version": "1.0",
"source": "plpython"
}
}
# Retour automatiquement converti en JSONB
return profil
$$ LANGUAGE plpython3u;
SELECT creer_profil_json('Durand', 'Sophie', 25);
-- Résultat : JSON structuré avec toutes les informationsL'un des avantages majeurs de PL/Python est l'accès à l'écosystème Python.
CREATE OR REPLACE FUNCTION analyser_url(url TEXT)
RETURNS TABLE(schema TEXT, domaine TEXT, chemin TEXT) AS $$
from urllib.parse import urlparse
# Analyse de l'URL
parsed = urlparse(url)
return [(parsed.scheme, parsed.netloc, parsed.path)]
$$ LANGUAGE plpython3u;
SELECT * FROM analyser_url('https://www.postgresql.org/docs/current/plpython.html');
-- Résultat :
-- schema | domaine | chemin
-- https | www.postgresql.org | /docs/current/plpython.htmlCREATE OR REPLACE FUNCTION calculer_hash_sha256(texte TEXT)
RETURNS TEXT AS $$
import hashlib
# Calcul du hash SHA-256
hash_obj = hashlib.sha256(texte.encode('utf-8'))
return hash_obj.hexdigest()
$$ LANGUAGE plpython3u;
SELECT calculer_hash_sha256('PostgreSQL');
-- Résultat : "8f3f8e8f8e8f..." (hash SHA-256)Attention : Les bibliothèques tierces (NumPy, pandas, requests, etc.) doivent être installées sur le système où PostgreSQL s'exécute :
# Sur le serveur PostgreSQL
pip3 install numpy pandas requestsPL/Python peut retourner des ensembles de résultats (SETOF, TABLE).
CREATE OR REPLACE FUNCTION generer_serie(debut INTEGER, fin INTEGER)
RETURNS SETOF INTEGER AS $$
# Utilisation d'un générateur Python
for i in range(debut, fin + 1):
yield i
$$ LANGUAGE plpython3u;
SELECT * FROM generer_serie(1, 5);
-- Résultat :
-- 1
-- 2
-- 3
-- 4
-- 5CREATE OR REPLACE FUNCTION decomposer_texte(texte TEXT)
RETURNS TABLE(mot TEXT, longueur INTEGER) AS $$
# Split du texte en mots
mots = texte.split()
# Création d'une liste de tuples
resultats = [(mot, len(mot)) for mot in mots]
return resultats
$$ LANGUAGE plpython3u;
SELECT * FROM decomposer_texte('PostgreSQL est une base de données relationnelle');
-- Résultat :
-- mot | longueur
-- PostgreSQL | 10
-- est | 3
-- une | 3
-- base | 4
-- de | 2
-- données | 7
-- relationnelle| 13CREATE OR REPLACE FUNCTION diviser(a NUMERIC, b NUMERIC)
RETURNS NUMERIC AS $$
if b == 0:
# Lever une exception PostgreSQL
plpy.error("Division par zéro interdite")
return a / b
$$ LANGUAGE plpython3u;
SELECT diviser(10, 0);
-- ERROR: plpy.Error: Division par zéro interditePL/Python fournit plusieurs fonctions pour communiquer avec PostgreSQL :
CREATE OR REPLACE FUNCTION demo_messages(valeur INTEGER)
RETURNS TEXT AS $$
# DEBUG : Message de débogage (non affiché par défaut)
plpy.debug(f"Valeur reçue: {valeur}")
# INFO : Message informatif
plpy.info("Traitement en cours...")
# NOTICE : Notification (affiché par défaut)
plpy.notice("Valeur validée")
# WARNING : Avertissement
if valeur > 100:
plpy.warning("Valeur élevée détectée")
# ERROR : Erreur (arrête l'exécution)
if valeur < 0:
plpy.error("Valeur négative non autorisée")
return "Traitement réussi"
$$ LANGUAGE plpython3u;Le module plpy permet d'exécuter des requêtes SQL depuis le code Python.
CREATE OR REPLACE FUNCTION compter_utilisateurs()
RETURNS INTEGER AS $$
# Exécution d'une requête SQL
result = plpy.execute("SELECT COUNT(*) as total FROM utilisateurs")
# Accès au résultat (liste de dictionnaires)
return result[0]["total"]
$$ LANGUAGE plpython3u;CREATE OR REPLACE FUNCTION obtenir_utilisateur_par_email(email_recherche TEXT)
RETURNS TABLE(id INTEGER, nom TEXT, email TEXT) AS $$
# Utilisation de requêtes préparées (protection contre SQL injection)
plan = plpy.prepare(
"SELECT id, nom, email FROM utilisateurs WHERE email = $1",
["TEXT"]
)
# Exécution avec paramètre
result = plpy.execute(plan, [email_recherche])
# Retour des résultats
return [(row["id"], row["nom"], row["email"]) for row in result]
$$ LANGUAGE plpython3u;CREATE OR REPLACE FUNCTION archiver_anciens_logs(jours INTEGER)
RETURNS INTEGER AS $$
from datetime import datetime, timedelta
# Calcul de la date limite
date_limite = datetime.now() - timedelta(days=jours)
# Insertion dans table d'archive
plan_insert = plpy.prepare(
"INSERT INTO logs_archive SELECT * FROM logs WHERE created_at < $1",
["TIMESTAMP"]
)
result_insert = plpy.execute(plan_insert, [date_limite])
# Suppression des logs archivés
plan_delete = plpy.prepare(
"DELETE FROM logs WHERE created_at < $1",
["TIMESTAMP"]
)
result_delete = plpy.execute(plan_delete, [date_limite])
# Retour du nombre d'enregistrements archivés
return result_delete.nrows()
$$ LANGUAGE plpython3u;PL/Python maintient un dictionnaire global GD qui persiste entre les appels de fonction dans la même session.
CREATE OR REPLACE FUNCTION obtenir_config(cle TEXT)
RETURNS TEXT AS $$
# GD persiste entre les appels
if "cache" not in GD:
GD["cache"] = {}
plpy.notice("Cache initialisé")
# Vérifier le cache
if cle in GD["cache"]:
plpy.notice(f"Valeur trouvée en cache pour: {cle}")
return GD["cache"][cle]
# Sinon, requête en base
plan = plpy.prepare("SELECT valeur FROM config WHERE cle = $1", ["TEXT"])
result = plpy.execute(plan, [cle])
if result:
valeur = result[0]["valeur"]
GD["cache"][cle] = valeur # Mise en cache
return valeur
return None
$$ LANGUAGE plpython3u;Attention : GD est spécifique à chaque session/connexion PostgreSQL.
CREATE OR REPLACE FUNCTION obtenir_meteo(ville TEXT)
RETURNS JSONB AS $$
import urllib.request
import json
# Attention : ceci est un exemple simplifié
# En production, gérer les erreurs, timeouts, etc.
try:
url = f"https://api.meteo.fr/v1/{ville}"
with urllib.request.urlopen(url, timeout=5) as response:
data = json.loads(response.read().decode())
return data
except Exception as e:
plpy.warning(f"Erreur API: {str(e)}")
return {"error": str(e)}
$$ LANGUAGE plpython3u;CREATE OR REPLACE FUNCTION calculer_statistiques(valeurs NUMERIC[])
RETURNS TABLE(
moyenne NUMERIC,
mediane NUMERIC,
ecart_type NUMERIC,
min_val NUMERIC,
max_val NUMERIC
) AS $$
if not valeurs:
return [(None, None, None, None, None)]
# Conversion en liste Python
data = list(valeurs)
data_sorted = sorted(data)
# Calculs statistiques
n = len(data)
moyenne = sum(data) / n
# Médiane
if n % 2 == 0:
mediane = (data_sorted[n//2 - 1] + data_sorted[n//2]) / 2
else:
mediane = data_sorted[n//2]
# Écart-type
variance = sum((x - moyenne) ** 2 for x in data) / n
ecart_type = variance ** 0.5
min_val = min(data)
max_val = max(data)
return [(moyenne, mediane, ecart_type, min_val, max_val)]
$$ LANGUAGE plpython3u;
-- Utilisation
SELECT * FROM calculer_statistiques(ARRAY[10, 20, 30, 40, 50]::NUMERIC[]);CREATE OR REPLACE FUNCTION generer_donnees_test(nb_lignes INTEGER)
RETURNS TABLE(
id INTEGER,
nom TEXT,
email TEXT,
age INTEGER,
date_inscription DATE
) AS $$
import random
import string
from datetime import date, timedelta
prenoms = ['Alice', 'Bob', 'Charlie', 'Diana', 'Ethan', 'Fiona']
noms = ['Martin', 'Bernard', 'Dubois', 'Thomas', 'Robert', 'Petit']
for i in range(1, nb_lignes + 1):
prenom = random.choice(prenoms)
nom_famille = random.choice(noms)
nom_complet = f"{prenom} {nom_famille}"
# Email généré
email = f"{prenom.lower()}.{nom_famille.lower()}@example.com"
# Âge aléatoire
age = random.randint(18, 65)
# Date d'inscription aléatoire (dernière année)
jours_arriere = random.randint(0, 365)
date_inscr = date.today() - timedelta(days=jours_arriere)
yield (i, nom_complet, email, age, date_inscr)
$$ LANGUAGE plpython3u;
-- Génération de 10 enregistrements de test
SELECT * FROM generer_donnees_test(10);Toujours utiliser des requêtes préparées pour éviter les injections SQL :
-- ❌ DANGEREUX - Vulnérable aux injections SQL
CREATE OR REPLACE FUNCTION recherche_danger(nom TEXT)
RETURNS INTEGER AS $$
query = f"SELECT COUNT(*) FROM users WHERE nom = '{nom}'"
result = plpy.execute(query)
return result[0]["count"]
$$ LANGUAGE plpython3u;
-- ✅ SÉCURISÉ - Requête préparée
CREATE OR REPLACE FUNCTION recherche_securisee(nom TEXT)
RETURNS INTEGER AS $$
plan = plpy.prepare("SELECT COUNT(*) FROM users WHERE nom = $1", ["TEXT"])
result = plpy.execute(plan, [nom])
return result[0]["count"]
$$ LANGUAGE plpython3u;- Limiter l'utilisation : PL/Python est plus lent que PL/pgSQL pour la logique SQL pure
- Privilégier PL/pgSQL pour les opérations intensives en SQL
- Utiliser PL/Python pour :
- Logique métier complexe
- Traitement de données non relationnelles
- Intégration avec des bibliothèques externes
- Appels d'API
CREATE OR REPLACE FUNCTION operation_transactionnelle()
RETURNS TEXT AS $$
# PL/Python s'exécute dans le contexte transactionnel de PostgreSQL
# Pas de BEGIN/COMMIT explicite nécessaire
try:
plpy.execute("INSERT INTO logs (message) VALUES ('Début opération')")
# Opération principale
plpy.execute("UPDATE comptes SET solde = solde + 100 WHERE id = 1")
plpy.execute("INSERT INTO logs (message) VALUES ('Opération réussie')")
return "Succès"
except Exception as e:
# En cas d'erreur, PostgreSQL rollback automatiquement
plpy.error(f"Erreur: {str(e)}")
$$ LANGUAGE plpython3u;Toujours documenter vos fonctions :
CREATE OR REPLACE FUNCTION fonction_documentee(param TEXT)
RETURNS TEXT AS $$
"""
Description : Cette fonction fait XYZ
Arguments :
param (TEXT) : Description du paramètre
Retourne :
TEXT : Description du retour
Exemple :
SELECT fonction_documentee('test');
Notes :
- Point important 1
- Point important 2
"""
# Code ici
return param.upper()
$$ LANGUAGE plpython3u;
-- Ajouter un commentaire PostgreSQL
COMMENT ON FUNCTION fonction_documentee(TEXT) IS
'Convertit le texte en majuscules avec validation';- Performance : Plus lent que PL/pgSQL pour les opérations SQL simples
- Pas de parallélisation native : Les fonctions PL/Python ne sont pas parallélisables
- Interpréteur unique : Un seul interpréteur Python par backend PostgreSQL
- Mémoire : Charge mémoire plus importante que PL/pgSQL
- plpython3u est "untrusted" : Accès complet au système
- Réserver aux superutilisateurs : Seuls les superusers peuvent créer des fonctions
- Isolation limitée : Pas de sandbox pour le code Python
- Bibliothèques : Attention aux bibliothèques tierces et dépendances
- Dépendances système : Version Python et bibliothèques installées
- Portabilité : Code dépendant de l'environnement système
- Tests : Plus difficile à tester que du SQL pur
- Débogage : Moins d'outils natifs que pour PL/pgSQL
-
Traitement de données complexe
- Parsing de formats non standards
- Algorithmes mathématiques avancés
- Machine Learning / IA (avec scikit-learn, TensorFlow)
-
Intégrations externes
- Appels d'APIs REST
- Connexion à des services tiers
- Envoi d'emails, SMS
-
Manipulation de données non relationnelles
- JSON/XML complexe
- Fichiers binaires
- Formats propriétaires
-
Prototypage rapide
- Tests de concepts
- Scripts de migration
- Transformations de données ponctuelles
-
Opérations SQL intensives
- Privilégier PL/pgSQL
- Meilleure performance native
-
Haute fréquence d'appel
- Overhead d'interprétation Python
- Latence accrue
-
Opérations transactionnelles critiques
- Préférer la logique SQL pure
- Meilleure intégration ACID
Voici un exemple complet illustrant plusieurs concepts :
-- Table de produits
CREATE TABLE produits (
id SERIAL PRIMARY KEY,
nom TEXT NOT NULL,
categorie TEXT,
tags TEXT[]
);
-- Table d'achats
CREATE TABLE achats (
id SERIAL PRIMARY KEY,
utilisateur_id INTEGER,
produit_id INTEGER REFERENCES produits(id),
date_achat TIMESTAMP DEFAULT NOW()
);
-- Fonction de recommandation
CREATE OR REPLACE FUNCTION recommander_produits(
user_id INTEGER,
limite INTEGER DEFAULT 5
)
RETURNS TABLE(
produit_id INTEGER,
nom TEXT,
score NUMERIC,
raison TEXT
) AS $$
from collections import Counter
# 1. Récupérer l'historique d'achat de l'utilisateur
plan_historique = plpy.prepare("""
SELECT p.categorie, p.tags
FROM achats a
JOIN produits p ON p.id = a.produit_id
WHERE a.utilisateur_id = $1
""", ["INTEGER"])
historique = plpy.execute(plan_historique, [user_id])
if not historique:
plpy.notice("Aucun historique d'achat trouvé")
return []
# 2. Analyser les préférences
categories = [row["categorie"] for row in historique if row["categorie"]]
tous_tags = []
for row in historique:
if row["tags"]:
tous_tags.extend(row["tags"])
# Compter les occurrences
categories_freq = Counter(categories)
tags_freq = Counter(tous_tags)
# Catégorie préférée
categorie_pref = categories_freq.most_common(1)[0][0] if categories_freq else None
tags_prefs = [tag for tag, _ in tags_freq.most_common(3)]
plpy.info(f"Préférences: catégorie={categorie_pref}, tags={tags_prefs}")
# 3. Trouver des produits similaires non encore achetés
plan_reco = plpy.prepare("""
SELECT DISTINCT p.id, p.nom, p.categorie, p.tags
FROM produits p
WHERE p.id NOT IN (
SELECT produit_id FROM achats WHERE utilisateur_id = $1
)
AND (p.categorie = $2 OR p.tags && $3)
LIMIT $4
""", ["INTEGER", "TEXT", "TEXT[]", "INTEGER"])
recommandations = plpy.execute(
plan_reco,
[user_id, categorie_pref, tags_prefs, limite * 2]
)
# 4. Calculer un score de pertinence
resultats = []
for prod in recommandations:
score = 0.0
raisons = []
# Bonus pour la catégorie
if prod["categorie"] == categorie_pref:
score += 10
raisons.append(f"Catégorie '{categorie_pref}'")
# Bonus pour les tags
if prod["tags"]:
tags_communs = set(prod["tags"]) & set(tags_prefs)
score += len(tags_communs) * 5
if tags_communs:
raisons.append(f"Tags: {', '.join(tags_communs)}")
if score > 0:
raison_complete = " | ".join(raisons)
resultats.append((prod["id"], prod["nom"], score, raison_complete))
# 5. Trier par score et limiter
resultats.sort(key=lambda x: x[2], reverse=True)
return resultats[:limite]
$$ LANGUAGE plpython3u;
-- Utilisation
SELECT * FROM recommander_produits(123, 5);- PostgreSQL PL/Python : https://www.postgresql.org/docs/current/plpython.html
- Python 3 Documentation : https://docs.python.org/3/
- Maîtriser Python : Plus vous connaissez Python, plus PL/Python est puissant
- Comprendre l'écosystème PostgreSQL : PL/Python complète PL/pgSQL, ne le remplace pas
- Expérimenter : Tester différents cas d'usage pour identifier les meilleurs patterns
- Benchmarker : Toujours comparer les performances avec PL/pgSQL
- Sécuriser : Attention aux privilèges et à l'exposition des fonctions
PL/Python (plpython3u) est un outil puissant qui :
✅ Avantages :
- Syntaxe Python familière et accessible
- Accès à l'écosystème Python complet
- Idéal pour la logique complexe et les intégrations
- Manipulation facilitée de JSON, APIs, algorithmes
❌ Limitations :
- Performance inférieure à PL/pgSQL pour le SQL pur
- Nécessite des privilèges superutilisateur
- Dépendances système et maintenance
- Moins bien intégré dans les transactions PostgreSQL
Recommandation : Utilisez PL/Python quand vous avez besoin de la puissance de Python, mais privilégiez PL/pgSQL pour les opérations SQL standards. Les deux langages sont complémentaires dans l'écosystème PostgreSQL.
Prochaines étapes :
- 15.7.2. PL/Perl (plperlu)
- 15.7.3. PL/v8 (JavaScript)