Calcul
Colonnes calculées
Note
Une colonne calculée (ou dérivée) est une colonne dont la valeur n’est pas directement saisie par l’utilisateur, mais obtenue à partir d’une expression basée sur d’autres colonnes de la même table.
Colonnes générées
Il est possible de déclarer une colonne comme générée automatiquement et stockée. La valeur est calculée à l’insertion ou mise à jour et enregistrée physiquement dans la table.
CREATE TABLE commande (
id SERIAL PRIMARY KEY,
prix_unitaire NUMERIC(10,2),
quantite INTEGER,
total NUMERIC(10,2) GENERATED ALWAYS AS (prix_unitaire * quantite) STORED
);
- total est calculé automatiquement.
- On ne peut pas insérer ou modifier total directement, PostgreSQL s’en occupe.
- La valeur est stockée → plus rapide à lire, mais prend de la place.
Colonnes calculées via de vues
Si l’on ne veut pas stocker la valeur (pour éviter la redondance), on peut utiliser une vue.
CREATE VIEW commande_calculee AS
SELECT id,
prix_unitaire,
quantite,
prix_unitaire * quantite AS total
FROM commande;
ci total n’est pas stocké → il est recalculé à chaque requête. Moins d’espace disque, mais peut être plus lent si beaucoup de calculs.
Dans d’autres SGBD (MySQL, Oracle), il existe des colonnes générées virtuelles (calculées à la volée sans stockage).
Vérification
Une contrainte CHECK permet d’imposer une condition (une règle logique) sur les valeurs d’une colonne ou d’un ensemble de colonnes.
On peut définir une contrainte CHECK :
au niveau colonne
CREATE TABLE employe (
id SERIAL PRIMARY KEY,
nom TEXT NOT NULL,
age INT CHECK (age >= 18) -- contrainte sur une seule colonne
);
au niveau table (plusieurs colonnes possibles)
CREATE TABLE commande (
id SERIAL PRIMARY KEY,
prix_unitaire NUMERIC(10,2),
quantite INT,
CHECK (prix_unitaire >= 0 AND quantite > 0) -- règle sur plusieurs colonnes
);
Déclencheurs
Un trigger est un mécanisme qui permet d’exécuter automatiquement une fonction lorsqu’un événement se produit sur une table ou une vue.
Cela permet d’automatiser certaines règles métier, de contrôler l’intégrité des données ou de maintenir des historiques.
Un trigger repose sur deux éléments :
- Une fonction (en PL/pgSQL ou un autre langage supporté) qui contient le code à exécuter.
- Le trigger lui-même, qui associe cette fonction à un événement (INSERT, UPDATE, DELETE…).
Volatilité
Note
La volatilité indique au moteur postgreSQL à quel point le résultat de la fonction dépend de l’état de la base ou du contexte. Il existe 3 niveaux.
IMMUTABLE
Le résultat ne change jamais pour les mêmes arguments.
Exemple : abs(-5) donne toujours 5, peu importe quand ou où la fonction est exécutée.
Utilisable dans : index, colonnes calculées, contraintes CHECK, etc.
STABLE
Le résultat ne change pas pendant une requête donnée, mais peut changer entre deux requêtes.
La fonction ST_Length(geom) est STABLE car pour une même géométrie, la longueur est toujours la même, MAIS cela dépend de paramètres de la session (SRID de référence).
VOLATILE
Le résultat peut changer même dans une seule requête. Par exemple : random() ou now() donnent des résultats différents à chaque appel.