Fonction PLSQL¶

nous allons utiliser les tables suivantes

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 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 tous 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 commencent à ê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);