Les déclencheurs et les procédures stockées avec Postgresql

Résumé

Vous apprendrez ici à faire exécuter à Postgresql des traitements en réponse à des événements se produisant sur les tables d'une base de données.


Table des matières

Introduction
Mise en oeuvre
Découverte du premier déclencheur
Analyse du trigger
Autre trigger découverte de OLD et NEW
Les procédures stockées
Exercices
Notions minimales sur le langage PLPGSQL
Etude de cas
Gilles Thomassin
Julien Legrand

Introduction

Qu'est-ce qu'un trigger ?

  • Un déclencheur est un programme stocké dans une base de données.
  • Un trigger est associé à une table de la base de donnée.
  • Un trigger est associé à un événement qui se produit sur cette table.
  • Le trigger est exécuté automatiquement lorsque l'événement auquel il est attaché se produit sur la table

Exemple 1. trigger

Par exemple, si le trigger est attaché à l'événement "insertion" de la table "client", à chaque nouveau client, le trigger sera exécuté.

Notez que la mise à jour ou la suppression d'une ligne dans une table sont aussi des événements qui peuvent déclencher des triggers.

Le langage dans lequel est écrit le déclencheur(trigger) varie d'un SGBD à l'autre. Oracle utilise le PL/SQL ou le langage Java et Postgresql utilise plusieurs langages: python, tcl/tk, ruby etc... ainsi qu'une variante du PL/SQL appelée "plpgsql" qui sera celui que nous allons utiliser.

Mise en oeuvre

Pour pouvoir utiliser un langage d'écriture de fonctions dans une base de donnée Postgresql, il faut activer le support du langage pour la base. Cette opération est à répéter pour chaque base qui le nécessite.

La commande, à exécuter dans le shell, est la suivante: createlang plpgsql <nomdelabase> -U <nomdupropriétaire> pour plus de détail, se référer au manuel de createlang : man createlang

répétition de la saisie du mot de passe

Si le système d'authentification utilisé requiert un mot de passe, il faudra le taper plusieurs fois.

Découverte du premier déclencheur

Nous allons créer un déclencheur attaché à l'événement "insertion d'un nouveau client'. Ce trigger va générer une valeur pour le numéro du nouveau client : Il est tout d'abord nécessaire de créer une procédure stockée:
CREATE FUNCTION gen_cle_client () RETURNS TRIGGER AS 
'
  DECLARE
    nocli integer;
  BEGIN 
    select into nocli max(no_client) from client;
    IF nocli ISNULL THEN
      nocli:=0;
    END IF;
    NEW.no_client:=nocli+1;
    RETURN NEW;
  END; 
' 
LANGUAGE 'plpgsql'; 

Puis on va créer le trigger.
CREATE TRIGGER trig_bef_ins_client BEFORE INSERT ON client 
  FOR EACH ROW 
  EXECUTE PROCEDURE gen_cle_client();

A titre indicatif, voici l'équivalent sous Oracle:
Create trigger generation for client
  Before insert
  Declare variable plusGros integer;
As
Begin
  Select max(no_cli) from client into :plusGros;
  New.no_cli:=plusGros+1;
End;

Analyse du trigger

CREATE FUNCTION gen_cle_client () RETURNS TRIGGER AS
		
Le code qui suit est comme vous pouvez le constater délimité par des simples quotes, ce qui signifie que lorsqu'on a à placer des simples quotes à l'intérieur, il faudra prendre soin de les doubler.

'
DECLARE
  nocli integer;
Il s'agit ci-dessus de la zone de déclaration des variables locales. Chaque variable locale utilisée dans la fonction doit y être déclarée explicitement.

  BEGIN 
Le code est délimité entre BEGIN et END

    select into nocli max(no_client) from client;
Chaque instruction est terminée par un ";" On peut exécuter des requêtes qui renvoient des résultats dans des variables locales.

  IF nocli ISNULL THEN
Dans le cas où il n'y a pas de client "nocli" est égal à nul alors ...

  nocli:=0;
L'affectation se fait grâce au ":=". "nocli" reçoit zéro.

  END IF;
NEW.no_client:=nocli+1;
L'enregistrement qui va être inséré est stocké dans la variable NEW dont la valeur de chaque champ est accessible ainsi: "NEW.nomduchamp"

  RETURN NEW;
NEW est modifié et il faut donc le renvoyer.

  END; 
' 
LANGUAGE 'plpgsql'; 

Le langage utilisé pour l'écriture de la fonction doit être précisé.

Dans le code SQL ci-dessous on attache l'exécution de la fonction à l'événement et à la table auxquels il s'applique.
CREATE TRIGGER trig_bef_ins_client BEFORE INSERT ON client 
FOR EACH ROW 
EXECUTE PROCEDURE gen_cle_client();
Remarque: A propos de NEW et OLD Lorsque l'on fait un "update",NEW correspond à la ligne après changement et OLD correspond à la ligne avant changement. NEW et OLD sont des variables de type enregistrement comme nous le verrons par la suite. Le "FOR EACH ROW" signifie que la procédure sera exécutée pour chaque ligne insérée dans la table.

Autre trigger découverte de OLD et NEW

Il s'agit de conserver dans une table "histo_salaires" l'historique des salaires des salariés de l'entreprise.
CREATE FUNCTION gerehisto () RETURNS TRIGGER AS 
'
  DECLARE
    nocli integer;
  BEGIN 
    IF NEW.salaire<>OLD.salaire THEN
      INSERT into hist_sal values(NEW.nom,NEW.prenom,''NOW'',OLD.salaire,NEW.salaire);
    END IF;
    RETURN NEW;
  END; 
' 
LANGUAGE 'plpgsql'; 

CREATE TRIGGER trig_bef_update_employe BEFORE update ON salarie 
  FOR EACH ROW 
  EXECUTE PROCEDURE gerehisto();

Les procédures stockées

Une procédure stockée est une fonction stockée dans la base de données. Lorsqu'on l'utilise, om met en oeuvre le client serveur de traitements. Voici une super fonction qui renvoie le double d'un entier passé en paramètre:
CREATE FUNCTION double (integer) 
  RETURNS integer 
  AS 
'
  BEGIN 
    RETURN 2*$1;
  END; 
'
LANGUAGE 'plpgsql'; 

Voici maintenant comment vous pouvez l'employer
select double(2);
	

Voici un autre exemple de procédure contenant cette fois une requête:
CREATE FUNCTION get_pu_pdt (INTEGER)
RETURNS FLOAT
AS
'
  DECLARE 
    lePu FLOAT;
  BEGIN
    SELECT INTO lePu pu FROM pdt WHERE no_pdt= $1;
    RETURN lePu;
  END ;
'
LANGUAGE 'plpgsql';

Exercices

Schéma de la base à utiliser :

drop table if exists salarie;

create table salarie
(
	no_sal integer primary key,
	nom_sal varchar(50),
	prenom_sal varchar(50),
	salaire	real
);

drop table if exists hist_sal;

create table hist_sal
(
	num serial primary key,
	nom varchar(50),
	prenom varchar(50),
	date_changement date,
	ancienSalaire real,
	nouveauSalaire real
);

insert into salarie values (0,'sacquet','bilbon',1200);
insert into salarie values (1,'le gris','gandalf',1000);
insert into salarie values (2,'sacquet','frodon',800);
insert into salarie values (3,'gamegie','sam',600);
insert into salarie values (4,'etoile du soir','arwen',400);

Exercice 1 : Créer une fonction qui renvoie le nom d'un client dont le numéro est passé en paramètre.

Exercice 2 : Ecrire le trigger postgresql qui convertit le nom du nouveau client en majuscule sans espaces inutiles et le prénom du client avec initiale en majsucule sans espace inutile.

Notions minimales sur le langage PLPGSQL

Cette partie, est un essai de synthèse de la doc officielle. Se référer pour la documentation complète au site: www.postgresql.org

Conventions

  • Le langage n'est pas sensible à la casse.
  • Toute déclaration, bloc ou instruction se termine par un ";".
  • Les commentaires sont compris entre /* et */

Les variables

Toutes les variables doivent être déclarées dans la section prévue à cet effet
DECLARE
	>>>>>>>>>>c'est ici
BEGIN
END.
			
Les types des variables sont à choisir parmi les types sql disponibles: char varchar, integer, real, time, etc...

Exemple 2. déclaration des variables

no_pdt integer;
	pi CONSTANT real:=3.14;
	nomclient varchar:="Dupond";
				

Des alias aux paramètres

Lorsqu'une procédure stockée reçoit des paramètres en entrée, par exemple plusgrand(integer,integer) les paramètres sont accessibles dans l'ordre par respectivement $1 et $2. On pourra avantageusement les renommer comme ceci:
DECLARE
	nombre1 ALIAS FOR $1;
	nombre2 ALIAS FOR $2;
			
ceci permettra d'utiliser nombre1 et nombre2 en lieu et place de $1 et $2.

Les variables de type tuple (enregistrement)

On peut déclarer des variables composées correspondant à une ligne d'une table. Elles se comportent alors comme les structures du C.

Exemple 3. enregistrements

DECLARE
	monCli Client%rowtype;
BEGIN
	return monCli.age+10;
					
Moncli est un enregistrement de la table client. Les valeurs des champs de l'enregistrement sont alors accessibles grâce à la notation courante "nomdelavariableligne.nomduchamp".

Les paramètres reçus par une fonction peuvent être aussi des tuples. On pourra dans ce cas accéder à $1.nomclient etc.... à moins que l'on ai créé des alias sur ceux-ci auquel cas on utilisera "nomd'alias.nomduchamp".

Mémo

Exemple 4. affectation

identifiant:=expression ou valeur;
			

Exemple 5. Select into

Le résultat d'un select renvoyant 1 enregistrement de plusieurs champs peut être affecté à une variable de type tuple ou à une liste de variables simples séparées par des virgules
DECLARE monCli client%ROWTYPE;
	nocli integer;
	nomcli varchar;
BEGIN
	select into monCli * from client where nom_client='dupond';
		ou 
	select into nocli,nomcli * from client where nom_client=''dupond'';
			
Si le SELECT ne renvoie aucune ligne, les variables du INTO reçoivent la valeur NULL. Lorsque par contre le SELECT renvoie plusieurs lignes, c'est la première ligne reçue qui est prise en compte pour valoriser les variables. Il existe une variable spéciale nommée FOUND du type booléen qui permet de savoir si le select a renvoyé une ligne:
	SELECT INTO monEnregistrement * FROM EMP WHERE nomEmp = ''Dupond''; 
	IF NOT FOUND THEN RAISE EXCEPTION ''employé % non trouvé'', ''Dupond''; 
	END IF;
			
Mais vous pouvez aussi tester si les variables du INTO sont nulles avec IS NULL (ou ISNULL)
DECLARE users_rec RECORD; 
	full_name varchar; 
BEGIN 
	SELECT INTO users_rec * FROM users WHERE user_id=3; 
	IF users_rec.homepage IS NULL THEN 
		-- user entered no homepage, 
		return "http://" ;
	END IF;
END;
				

Structures de contrôle

if then endif; loop exit endloop for while curseurs

Etude de cas

Il s'agit de réaliser une base de donnée commerciale contenant les tables suivantes: "produit", "commande", et "ligne de commande". Tous les montants doivent se mettre à jour automatiquement. Ainsi que les quantités en stock. On ne pourra de plus pas enregistrer une ligne de commande que l'on ne pourrait satisfaire pour cause de stock insuffisant. Il s'agit d'une vue partielle de l'application. Notez bien que l'on ne gère pas ici les clients, ni les entrées en stock.
drop table ldc, commande, pdt;

Create Table pdt(
	no_pdt integer not null primary key, 
	design_pdt varchar(50) ,
	pu float not null, 
	stock integer default 0
);

Create Table commande(
	no_com integer not null primary key, 
	date_com date,
	montant float default 0
);

Create Table ldc (
	no_cde integer not null, 
	qte integer not null,
	no_pdt integer not null, 
	pu float, montant real,
	Primary key(no_pdt,no_cde),
	foreign key (no_cde) references commande(no_com),
	foreign key (no_pdt) references pdt(no_pdt)
);

insert into pdt values (1,'trousse cochon rose à trois pattes',25,8);
insert into pdt values (2,'ordi eeepc rose',329.99,17);
insert into pdt values (3,'bonnet blanc en laine',0.12,1);
insert into pdt values (4,'r11 d''occas',1257.22,2);
insert into pdt values (5,'belle wiwi',3542,1);

insert into commande (no_com, date_com) values (1,'2010-03-11');

Travail à faire :

  • Lorsqu'il y a nouvelle ligne de commande , le prix unitaire actuel du produit commandé est enregistré dans la ligne de commande. la quantité en stock du produit concerné est diminuée.Le montant de la commande est augmenté du montant de la ligne.
  • A chaque mise à jour d'une ligne de commande il faut mettre à jour le montant de la commande. et eventuellement les quantités en stock.
  • Idem en cas de suppression d'une ligne de commande.