Fonction PLSQL
**************

nous allons utiliser les tables suivantes

.. code-block:: sql
    
    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 :

.. code-block:: sql
    
    -- 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 :

.. code-block:: sql

	-- 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 :

.. code-block:: sql

	SELECT * FROM calcul_total(1);