Introduction à PostgreSQL

Ce document parcours les différentes fonctionnalités du puissant gestionnaire de base de données PostgreSQL. Le but étant de vous donner un aperçu rapide des avantages que son utilisation pourrait vous apporter. Pour comprendre les exemples qui suivent il faut être familier avec le langage SQL.

PostgreSQL est un système de gestion de base de données relationnelle et objet (SGBDOR). Ces fonctionnalités sont comparables à celles des ces concurrents propriétaires comme Oracle.

PostgreSQL offre :

• La possibilité d’imposer des contraintes à l’insertion des données
• Les contraintes d’intégrités (garantie d’intégrité des données)
• La possibilité de créer des vues
• La possibilité d’hériter des tables et des types de données
• Les procédures stockées (programmation coté serveur)
• Les transactions (plusieurs étapes en une seule opération)
• Les triggers (gestion des événements)

Pour aborder ces notions rien ne vaut le fameux exemple des clients, des produits et des commandes. Ce schéma sera utilisé dans ce document afin de mettre en pratique les possibilités de PostgreSQL.

• Fichier SQL avec tout les exemples format HTML.
• Fichier SQL avec tout les exemples format texte ;

Structure de la base données

La structure de la base de donnée sera la suivante :

• Clients
• Commandes » Clients
• Produits
• Produit « table de liaison » Commande

Une fois traduit en SQL on pourrait obtenir cela :

CREATE TABLE  clients
 (
     pkey SERIAL PRIMARY KEY ,
     nom VARCHAR (50 ) NOT NULL ,
     prenom VARCHAR (50 ) NOT NULL
 );
CREATE TABLE  commandes
 (
     pkey SERIAL PRIMARY KEY ,
     -- la commande pointe sur un client
     client_pkey INTEGER references  clients NOT NULL ,
     total NUMERIC (10 ,2 )
 );
CREATE TABLE  produits
 (
     pkey SERIAL PRIMARY KEY ,
     -- la contrainte "prix_positif" impose  un prix strictement positif
     prix NUMERIC (10 ,2 ) CONSTRAINT  prix_positif CHECK  (prix > 0 ) NOT NULL ,
     -- l’intitulé du produit doit être unique
     label VARCHAR (50 ) UNIQUE NOT NULL
 );
-- cette table indique la quantité d’un produit pour une commande
CREATE TABLE  produit_commande
 (
     -- "ON DELETE CASCADE" indique que si la ligne de données
     -- référencée est supprimée celle-ci doit l'être également
     commande_pkey INTEGER references  commandes ON DELETE CASCADE ,
     produit_pkey INTEGER references  produits ON DELETE CASCADE ,
     -- la quantité doit être strictement positive
     quantite INTEGER CONSTRAINT  quantite_positive CHECK  (quantite > 0 ) NOT NULL ,
     -- la clé primaire est formée de la clé du produit et de la commande
     PRIMARY KEY (produit_pkey, commande_pkey)
 );

PostgreSQL gère automatiquement les contraintes d’intégrités lié aux cléfs étrangères. Par exemple il est encore impossible d’insérer une commande. Il faut avant cela insérer un client puis ensuite seulement on pourra insérer une commande qui référence ce client.

-- l’insértion de la commande provoque une erreur
INSERT INTO  commandes (client_pkey,total) VALUES  (1 ,0 );
INSERT INTO  clients (nom,prenom) VALUES  ('Dupont' , 'André' );

-- mais l’inverse est correct
INSERT INTO  clients (nom,prenom) VALUES  ('Dupont' , 'André' );
INSERT INTO  commandes (client_pkey,total) VALUES  (1 ,0 );

Documentation en français sur la création de base de données avec PostgreSQL et sur le langage SQL.

Les vues

Les vues permettent de créer une abstraction sur des requètes fréquentes. Ces requètes ne prennent pas de paramètres. On leur donne un nom. On peut ensuite les utiliser dans toute l’applications.

Cette vue permet de sélectionner les informations du produit ainsi que la quantité vendu à ce jour :

CREATE VIEW  vente_produits AS 
SELECT  produits.*, 
(SELECT SUM (produit_commande.quantite) 
        FROM  produit_commande WHERE  produit_commande.produit_pkey=produits.pkey) 
AS  quantite FROM  produits;

Documentation sur les vues en français.

Les procédures stockées

PostgreSQL permet de créer des programmes dans un langage de procédure. Ces programmes sont stockés et executés sur le serveur. Les avantages sont multiples :

• Cela permet dans certains cas d’améliorer l’efficacité des traitements en évitant des aller et retour entre le PostgreSQL et le client de la base.
• Cela offre une grande souplesse d’utilisation et une plus grande abstraction par rapport à la base de données.

Comme exemple de procédure, réalisons une fonction qui retourne tout les produits d’une commandes et les quantités associées :

-- créer un type de donné correspondant à ligne de réponse
-- est nécessaire pour ce qui va suivre
CREATE TYPE  produit_de_la_commande 
AS  (pkey INTEGER , prix NUMERIC (10 ,2 ), label VARCHAR (50 ), quantite INTEGER );

-- la fonction "produits_de_la_commande" retourne tout les 
-- produits d’une commande choisie avec les quantités
CREATE OR REPLACE FUNCTION  produits_de_la_commande(INTEGER ) 
RETURNS SETOF produit_de_la_commande AS
 $BODY$
DECLARE
-- on déclare une variable de travail type "produit_de_la_commande"
 produit produit_de_la_commande;
BEGIN
-- la clé de la commande est passé comme paramètre. Si ce paramètre n’est
-- nommmé il prend comme valeur $n. N étant la position du paramètre dans le prototype.
FOR  produit IN SELECT  produits.*, produit_commande.quantite FROM  produits, produit_commande 
WHERE  commande_pkey=$1 and  produit_pkey=produits.pkey
     LOOP
         -- cette syntaxe est nécessaire pour retourner plusieurs lignes
         RETURN NEXT  produit;
     END  LOOP;
RETURN ;
END ;
 $BODY$
 LANGUAGE 'plpgsql' ;

L’intérêt d’une telle fonction est assez faible, mais elle permet d’obtenir une abstraction intéressante que nous allons utiliser par la suite :

-- cette fonction calcul le total d’une commande, le mets à jour et le renvoi
CREATE OR REPLACE FUNCTION  calcul_total(INTEGER , OUT  commande_total NUMERIC ) AS
 $BODY$
DECLARE
 produit produit_de_la_commande;
BEGIN
 commande_total=0.0 ;
FOR  produit IN SELECT  * FROM  produits_de_la_commande($1)
     LOOP
         commande_total=commande_total+(produit.prix*produit.quantite);
     END  LOOP;
     -- mise à jour
     UPDATE  commandes SET  total=commande_total WHERE  pkey=$1;
END ;
 $BODY$
 LANGUAGE 'plpgsql' ;

Les choses commence à être intéressantes. Nous avons calculé le total de la commande à l’aide de produits_de_la_commande puis nous avons mis à jour la ligne de la table commande correspondante, enfin nous renvoyons le total de la commande via le protoype de la fonction avec le mots clé OUT.

Nous pouvons utiliser cette fonction de la manière suivante :

SELECT * FROM calcul_total(1);

Documentation en français sur les procédures.

Les Triggers

Les triggers (événements) permettent d'éxécuter des procédures quand certaines actions se déclenchent. Par exemple, lors de la modification d’un champ ou de sa suppression. On peut choisir déclencher le trigger avant ou après (BEFORE, AFTER) l'événement.

Cela peut-être très util. Dans ce cas, nous pourrions utiliser cette fonctionnalité pour mettre à jour le total d’une commande lors de la modification d’une ligne dans la table "produit_commande" :

-- un trigger a besoin d’une fonction spécial qui retourne un trigger
CREATE OR REPLACE FUNCTION  calcul_total_trigger() RETURNS TRIGGER AS
 $BODY$
BEGIN
     -- NEW correspond à la ligne qui vient d'être créé/modifiée
     -- PERFORM est nécessaire car la valeur de retour ne nous interesse pas
     PERFORM calcul_total(NEW.commande_pkey);
     RETURN NEW ;
END ;
 $BODY$
 LANGUAGE 'plpgsql' ;

-- le trigger qui réagis sur INSERT ou UPDATE
CREATE TRIGGER  recalcul_total
     AFTER INSERT OR UPDATE ON  produit_commande FOR EACH ROW
     EXECUTE PROCEDURE  calcul_total_trigger();

Documentation en français sur les Triggers.

Autres points importants non abordés

• Héritage de table
• Les transactions