La documentation de PostGreSQL est parfois peu claire et souvent sibylline pour celui qui veut développer des fonctions dans PostGreSQL. C’est pourquoi j’ai voulu apporter mon expérience sur le codage des routines du langage PG/plSQL qui permet la création des procédures stockées et des UDF. Cet article constitue un tutorial sur la création des fonctions PostGreSQL en SQL et PL/pgSQL

0 – INTRODUCTION

Tout d’abord PostGreSQL ne fait pas de différence entre une fonction et une procédure car tout est fonction dans PostGreSQL, même, dans une certaines mesure, pour les déclencheurs.
Ensuite notons que nous pouvons créer de telles routines avec d’autres langages que SQL ou PL/pgSQL, mais ce n’est pas le but de cette série d’article.
Spécifions aussi que PL/pgSQL, signifie « Programming Language / postgreSQL » et sert justement à coder les routines (UDF, procédures et trigger) dans le langage interne à PostGreSQL au même titre que Transact SQL sert à MS SQL Server et PL/SQL à Oracle…
Enfin, notons que ce langage est aussi fortement emprunté à la norme SQL dans sa partie concernant les procédures stockées, connues sous le nom de SQL PSM (SQL Persistant Stored Module).

Dans cette première partie, nous allons nous intéresser aux fonctions scalaires, c’est à dire ne retournant qu’une seule valeur, étant entendue qu’il existe des fonctions qui renvoie des tables et d’autres attachées aux triggers.
Le découpage de cette série d’article étant le suivant :
1 – Fonctions scalaires
2 – Fonction manipulant des tables (fonction table, SQL dynamique, curseur, transactions, contraintes CHECK avec UDF…)
3 РFonctions pour d̩clencheurs

1 – CRÉATION D’UNE FONCTION SCALAIRE

Pour créer une fonction scalaire, la syntaxe basique est la suivant :

CREATE FUNCTION [<em>nom_schema</em>.]<em>nom_fonction</em> ( [ <liste_arguments> ] )  

RETURNS <type_sql>  

AS  

<corps>  

LANGUAGE <langage>

Avec :

<liste_arguments> ::=  

   <argument1> [, <argument2> [, ... ] ]  

 

<argument<em>N</em>> ::=  

   [ { <strong>IN</strong> | OUT | INOUT } ] nom_argument <type_sql> [ { DEFAULT | = } <expression_défaut> ]   

 

<langage> ::=  

   { SQL | PLPGSQL | C | internal | <em>udpl</em> }

NOTA :

• toute fonction créée sans précision de schéma SQL est créé dans le schéma SQL public;
• IN est la valeur par défaut du mode de passage des arguments;
• les passages d’argument en OUT et INOUT seront étudiés dans les chapitres suivants.
• le mot clef void en lieu et place du type de retour permet d’indiquer que la fonction ne renvoie rien.
• udpl est le nom d’un user-defined procedural language, c’est à dire un langage de code de fonction rajouté à l’aide de la commande CREATE LANGUAGE (ce qui nécessite préalablement la création d’un handler de langage).

Exemple 1 Рvoici une premi̬re fonction aussi simple que possible :

CREATE FUNCTION F_PLANCK_JS () 

RETURNS double precision 

AS 

$BODY$ 

BEGIN 

   RETURN 6.62606896E-34; 

END;    

$BODY$  

LANGUAGE PLPGSQL

Mettons à part le fait qu’elle renvoie la valeur de la constante de Planck exprimé en joule seconde et intéressons nous au corps de la fonction.
Nous avons délimité le code du corps avec une balise que nous avons choisit, dont le nom est BODY et qui doit être entourée de deux caractères dollar ($). Nous aurions pu utiliser n’importe quel mot (et même une chaîne de caractères vides), et il lui faut être présent deux fois, l’une au début du corps et l’autre à la fin.

Une autre manière de délimiter le corps est d’utiliser l’apostrophe, ce qui fait considérer que le corps est une chaine de caractères, mais dans ce cas il peut y avoir des confusions avec des chaines de caractères et des apostrophes internes au code exprimé.

Exemple 2 РVoici donc la m̻me fonction dont le corps est d̩limit̩ par des apostrophes :

CREATE FUNCTION F_PLANCK_JS () 

RETURNS double precision 

AS 

' 

BEGIN 

   RETURN 6.62606896E-34; 

END;    

' 

LANGUAGE PLPGSQL

En voici une autre qui vous fera comprendre que l’usage de l’apostrophe comme délimiteur du corps n’est peut être pas la meilleure solution :

Exemple 3 :

CREATE FUNCTION F_DATE_DU_JOUR () 

RETURNS CHAR(22) 

AS  

' 

BEGIN 

  RETURN ''Aujourd''''hui '' || CAST(CURRENT_DATE AS CHAR(10)); 

END; 

' 

LANGUAGE PLPGSQL

Pour modifier une fonction il faut utiliser l’ordre CREATE OR REPLACE.
Bien que l’ordre ALTER n’existe pas, la commande CREATE OR REPLACE agit de la même manière en préservant les éventuels privilèges accordés sur la fonction.

Pour supprimer une fonction il faut utiliser l’ordre DROP. Syntaxe :
 DROP FUNCTION [ IF EXISTS ] [nom_schéma.]nom_fonction ( [<liste_paramètres> ] ) [ CASCADE | RESTRICT ]

ATTENTION :

• vous pouvez supprimer une fonction, même si elle est utilisée dans une autre fonction, cette dernière partira en erreur lors de l’exécution.
• vous ne pouvez pas supprimer une fonction si elle est utilisée dans une vue, sauf à préciser CASCADE et dans ce cas tous les objets dépendant de cette fonction sont supprimés dans la foulée. RESTRICT (comportement par défaut) empêche la suppression si d’autres objets en dépendent.
• vous ne pouvez pas utiliser CREATE OR REPLACE si vous devez changer le type de retour de la fonction. Pour ce faire détruisez préalablement la fonction à l’aide de DROP.

Nous verrons au chapitre POLYMORPHISME pourquoi il est important de toujours spécifier la liste des paramètres pour le DROP.

IMPORTANT : comme dans tout code SQL, vous pouvez rajouter du commentaire dans vos fonctions avec les balises suivantes :

• – : les tirets et tous les caractères situés après jusqu’à la fin de ligne sont ignorés à la compilation.
• /* */ : les balises ouvrante /* et fermante */ ainsi que tout ce qui figure entre est ignoré à la compilation.

2 – UTILISATION DE LA FONCTION

Une fonction scalaire peut être utilisée partout ou il est possible de mettre une constante, c’est à dire aussi bien dans une requête que dans une autre fonction.

Exemples 4 :

SELECT F_DATE_DU_JOUR()

Appel directe dans la clause SELECT d’un ordre SELECT de la fonction F_DATE_DU_JOUR().

Exemple 5 :

Appel de la fonction F_DATE_DU_JOUR() dans le corps de la fonction F_DATE_HEURE_DU_JOUR ()

Il est aussi possible d’appeler une fonction scalaire dans la clause FROM d’un SELECT, mais ceci n’est pas recommandé.

3 – FONCTION AVEC LANGAGE SQL

Jusqu’ici nous avons utilisé le langage PL/pgSQL pour coder des fonctions procédurales sous forme de code itératif. Il est néanmoins possible d’utiliser directement le langage SQL pour des fonctions ensembliste renvoyant un jeu de données par exemple.
Voici une fonction scalaire renvoyant un jeu de données d’une seule ligne et d’une seule colonne… autrement dit, une constante !

Exemple 6 :

CREATE FUNCTION F_PI () 

RETURNS NUMERIC(37,36) 

AS 

$BODY$ 

   SELECT 3.14159265358979323846264338327950288 

$BODY$  

LANGUAGE SQL

On eut l’utiliser indifféremment en tant que fonction scalaire ou fonction table :

Exemple 7 :

SELECT F_PI()

ou :

Exemple 8 :

SELECT * 

FROM   F_PI()

Nous étudierons ce cas particulier de fonction plus en détail dans la seconde partie de notre série d’articles.

4 – QUELQUES MOTS CLEFS DE PL/pgSQL

Au passage nous venons de voir dans ces différents exemples, quelques mots clefs et signes importants :

• ; délimite la fin de la phrase de code. Chaque instruction (ou phrase de code) doit se terminer par le caractère point-virgule.
• := assignation de variable.
• BEGIN … END sont deux mots clef délimitant un bloc de code.
• RETURN permet de retourner une valeur et quitte le code de la fonction.
• DECLARE que nous allons voir un peu après, permet de déclarer des variables locales.

Les plus important mots clef, figurent au paragraphe 6, consacré aux structures de branchement…

5 – ARGUMENTS

Pour pouvoir être utilisés dans le code de la fonction, les arguments des fonctions peuvent être précisés par leur nom de paramètre (mais seulement depuis la version 8.1), ou par leur position ordinale relative à la déclaration de la liste des paramètres, précédés par un caractères dollar ($).

Exemple 9 :

CREATE FUNCTION F_ADDITION_MARTIENNE(n INT, m INT) 

RETURNS INTEGER 

AS 

$code$ 

BEGIN 

   RETURN (n * m) + n + m; 

END; 

$code$ 

LANGUAGE PLPGSQL

Exemple 10 – équivalent à l’exemple 9 :

CREATE FUNCTION F_ADDITION_MARTIENNE2(n INT, m INT) 

RETURNS INTEGER 

AS 

$code$ 

BEGIN 

   RETURN ($1 * $2) + $1 + $2; 

END; 

$code$ 

LANGUAGE PLPGSQL

Cette fonction renvoie l’addition martienne de deux nombres.

NOTA : au lieu de la notation ordinale avec $ vous pouvez aussi utiliser un alias de variable locale. Voir ci après.

SQL est un langage typé, même s’il existe dans les requêtes la possibilité de conversions implicites. Mais PL/pgSQL est encore plus exigent. Voici par exemple ce qui arrive lorsque le typage d’une fonction n’est pas suffisamment précis :

Exemple 11 :

CREATE FUNCTION F_ADDITION_MARTIENNE3(n SMALLINT, m SMALLINT) 

RETURNS INTEGER 

AS 

$code$ 

BEGIN 

   RETURN (n * m) + n + m; 

END; 

$code$ 

LANGUAGE PLPGSQL

Et un test qui passe mal :

Exemple 12 :

SELECT F_ADDITION_MARTIENNE3 (4, 5)

ERREUR:  la fonction f_addition_martienne3(integer, integer) n'existe pas 

LINE 1: SELECT F_ADDITION_MARTIENNE3 (4, 5) 

               ^ 

HINT:  Aucune fonction ne correspond au nom donné et aux types d'arguments. 

Vous devez ajouter des conversions explicites de type.

Il vous suffit alors de forcer le typage dans l’ordre SQL, ou bien de récrire la fonction…

Exemple 13 :

SELECT F_ADDITION_MARTIENNE3 (CAST(4 AS SMALLINT), CAST(5 AS SMALLINT))

NOTA : avant la version 9.0 de PostGreSQL, les arguments étant passé par défaut en IN, en principe seule la valeur de l’argument est disponible, pas la variable. Tant est si bien qu’il est impossible de modifier la valeur d’un argument. Pour ce faire, le plus simple est de rajouter une variable et de reprendre la valeur de l’argument dans cette variable.

Exemple de fonction syntaxiquement fausse (versions PG antérieures à la 9.0) :

Exemple 14 :

CREATE FUNCTION F_JOUR_EN_8(d DATE) 

RETURNS DATE 

AS 

$corps$ 

DECLARE semaine CONSTANT INTEGER  := 7; 

        date_retour DATE; 

BEGIN 

  d :=  d + semaine * INTERVAL '1 DAY'; 

  RETURN d; 

END; 

$corps$ 

LANGUAGE PLPGSQL

et message d’erreur précis :

ERREUR: « $1 » est déclaré CONSTANT
CONTEXT: compilation de la fonction PL/pgsql « f_jour_en_8 » près de la ligne 4

6 – VARIABLES ET CONSTANTES

Pour déclarer des variables ou constante locale, il faut ajouter un bloc DECLARE juste avant le BEGIN délimitant le début du code du corps de la fonction, puis lister les variables en les séparant par le caractères point-virgule.
Syntaxe :

DECLARE  

   <liste_variable>

avec :

<liste_variable> ::=  

   <variable1> [ ; <variable2> [ ; ... ] ] 

 

<variableN> ::= 

   <nom_variable> [ CONSTANT ] <type> [ { ALIAS FOR $n | := <value> } ]

Exemple 15 :

CREATE FUNCTION F_ECART_JOUR(d DATE) 

RETURNS INTEGER 

AS 

$corps$ 

DECLARE jour date := CURRENT_DATE; 

        nombre_jour INTEGER; 

BEGIN 

   nombre_jour := jour - d; 

   RETURN nombre_jour; 

END; 

$corps$ 

LANGUAGE PLPGSQL

Cette fonction renvoie l’écart en nombre de jours entre une date passée en argument et la date du jour.

Exemple 16, fonction avec constante :

CREATE FUNCTION F_JOUR_EN_8(d DATE) 

RETURNS DATE 

AS 

$corps$ 

DECLARE semaine CONSTANT INTEGER  := 7; 

        date_retour DATE; 

BEGIN 

   date_retour :=  d + semaine * INTERVAL '1 DAY'; 

   RETURN date_retour; 

END; 

$corps$ 

LANGUAGE PLPGSQL

Cette fonction retourne la date passée en argument sous « huitaine », c’est à dire le même jour la semaine suivante.

Exemple 17, fonction avec arguments aliassés :

CREATE FUNCTION F_JOUR_ADD(d DATE, j INTEGER)  

RETURNS DATE  

AS  

$corps$  

DECLARE ma_date     ALIAS FOR $1;  

        nombre_jour ALIAS FOR $2; 

BEGIN  

   RETURN ma_date + nombre_jour * INTERVAL '1 DAY';  

END;  

$corps$  

LANGUAGE PLPGSQL

Cette fonction renvoie la date d passée en argument ajouté du nombre de jours j voulu.

NOTA :

• la syntaxe acceptant directement le nom des paramètres dans le code procédural des fonctions est récente. Vérifiez si votre version de PostGreSQL les accepte, sinon utilisez un alias.
• le nombre d’arguments d’une fonction est limité à 16.

7 – POLYMORPHISME

Les fonctions de PostGreSQL admettent le polymorphisme (un même nom de fonction avec différents constructeur de paramètres) ce qui permet de rendre « générique » une même fonction.

Exemple 18 :

CREATE FUNCTION F_ADDITION_MARTIENNE(n DECIMAL, m DECIMAL) 

RETURNS DECIMAL 

AS 

$code$ 

BEGIN 

   RETURN (n * m) + n + m; 

END; 

$code$ 

LANGUAGE PLPGSQL

Exemple 19 :

CREATE FUNCTION F_ADDITION_MARTIENNE(n FLOAT, m FLOAT) 

RETURNS DECIMAL 

AS 

$code$ 

BEGIN 

   RETURN (n * m) + n + m; 

END; 

$code$ 

LANGUAGE PLPGSQL

Avec les exemples 9 ou 10 et 18, cela nous fait 3 fonctions pour les trois types différents d’opérandes : entier, réel et décimaux.
Un autre cas de polymorphisme est le fait qu’une fonction de même noms peut avoir un nombre différents d’arguments.

Voici maintenant deux fonctions de même nom, l’une ayant un argument de plus que l’autre :

Exemple 20 :

CREATE FUNCTION F_ADDITION_DATE(d DATE, an INTEGER) 

RETURNS DATE 

AS 

$code$ 

BEGIN 

   RETURN d + an * INTERVAL '1 YEAR'; 

END; 

$code$ 

LANGUAGE PLPGSQL

Cette fonction ajoute un nombre d’année spécifié par l’argument an à une date d.

Exemple 21 :

CREATE OR REPLACE FUNCTION F_ADDITION_DATE(d DATE, an INTEGER, mois INTEGER) 

RETURNS DATE 

AS 

$code$ 

BEGIN 

   RETURN d + an * INTERVAL '1 YEAR' + mois * INTERVAL '1 MONTH'; 

END; 

$code$ 

LANGUAGE PLPGSQL

Cette fonction ajoute un nombre d’année spécifié par l’argument an et de mois spécifié par l’argument mois, à une date d.

Exemple 22 :

CREATE FUNCTION F_DECODE_TITRE (code        INT DEFAULT 1,  

                                abreviation VARCHAR(4) DEFAULT 'M') 

RETURNS VARCHAR(12) 

AS 

$code$ 

BEGIN 

   RETURN CASE  

               WHEN code = 1 THEN 'Monsieur' 

               WHEN code = 2 THEN 'Madame' 

               WHEN code = 3 THEN 'Mademoiselle' 

               WHEN code = 4 THEN 'Maître' 

               WHEN code = 5 THEN 'Monseigneur' 

               WHEN code = 6 THEN 'Son altesse' 

               WHEN code = 7 THEN 'Sa majesté' 

               WHEN UPPER(abreviation) IN ('M', 'MR') THEN 'Monsieur' 

               WHEN UPPER(abreviation) = 'MME' THEN 'Madame' 

               WHEN UPPER(abreviation) = 'MLLE' THEN 'Mademoiselle' 

               WHEN UPPER(abreviation) = 'ME' THEN 'Maître' 

               WHEN UPPER(abreviation) = 'MS' THEN 'Monseigneur' 

               WHEN UPPER(abreviation) = 'SA' THEN 'Son altesse' 

               WHEN UPPER(abreviation) = 'SM' THEN 'Sa majesté' 

               ELSE '' 

          END; 

END 

$code$ 

LANGUAGE PLPGSQL

Ce dernier exemple va mettre en évidence la possibilité d’appels polymorphes :

Exemple 23 :

SELECT  F_DECODE_TITRE() AS TITRE 

UNION ALL 

SELECT  F_DECODE_TITRE(2) 

UNION ALL 

SELECT  F_DECODE_TITRE(NULL, 'Mlle'); 

 

TITRE 

--------------- 

Monsieur 

Madame 

Mademoiselle

Trois appels de forme différentes pour la même fonction : l’un sans argument, le second avec un argument et le troisième avec deux arguments

Vous comprenez maintenant pourquoi il est important de préciser dans le DROP le nom et les arguments de la fonction, sinon on ne peut savoir précisément quelle fonction vous voulez réellement supprimer.

8 – STRUCTURE DE TEST ET BRANCHEMENT

8.1 – Le branchement simple SI ALORS SINON

Il existe en différentes syntaxes :

Afin de ne pas entretenir de confusion, nous vous recommandons de n’utiliser toujours que la même syntaxe en, préconisant la plus classique :
IF ... THEN ... [ELSIF ... THEN ... [ELSIF ... THEN ... [ ... ] ] ] [ ELSE ... ] END IF;

Exemple 24 :

CREATE FUNCTION F_QUAND() 

RETURNS VARCHAR(9) 

AS 

$corps$ 

BEGIN 

   IF CURRENT_TIME >= '12:00:00' 

   THEN  

      RETURN 'après-midi'; 

   ELSE 

      RETURN 'matin'; 

   END IF; 

END; 

$corps$ 

LANGUAGE PLPGSQL

Cette fonction, appelé par exemple de la sorte SELECT F_QUAND(), renvoi « matin » si nous sommes le matin et « après-midi » si nous somme l’après midi.

Exemple 25 :

CREATE FUNCTION F_QUEL_JOUR() 

RETURNS VARCHAR(9) 

AS 

$corps$ 

BEGIN 

   IF    EXTRACT(DOW FROM CURRENT_DATE) = 1 

   THEN  RETURN 'Lundi'; 

   ELSIF EXTRACT(DOW FROM CURRENT_DATE) = 2 

   THEN  RETURN 'Mardi'; 

   ELSIF EXTRACT(DOW FROM CURRENT_DATE) = 3 

   THEN  RETURN 'Mercredi'; 

   ELSIF EXTRACT(DOW FROM CURRENT_DATE) = 4 

   THEN  RETURN 'Jeudi'; 

   ELSIF EXTRACT(DOW FROM CURRENT_DATE) = 5 

   THEN  RETURN 'Vendredi'; 

   ELSIF EXTRACT(DOW FROM CURRENT_DATE) = 6 

   THEN  RETURN 'Samedi'; 

   ELSIF EXTRACT(DOW FROM CURRENT_DATE) = 0 

   THEN  RETURN 'Dimanche'; 

   END IF; 

END; 

$corps$ 

LANGUAGE PLPGSQL

Cette fonction, appelé par exemple de la sorte SELECT F_QUEL_JOUR(), renvoi le nom de jour du jour actuel (Lundi, Mardi, Mercredi…).
La voici surchargée avec un paramètre date :

Exemple 26 :

CREATE FUNCTION F_QUEL_JOUR(dd DATE) 

RETURNS VARCHAR(9) 

AS 

$corps$ 

BEGIN 

   IF    EXTRACT(DOW FROM dd) = 1 

   THEN  RETURN 'Lundi'; 

   ELSIF EXTRACT(DOW FROM dd) = 2 

   THEN  RETURN 'Mardi'; 

   ELSIF EXTRACT(DOW FROM dd) = 3 

   THEN  RETURN 'Mercredi'; 

   ELSIF EXTRACT(DOW FROM dd) = 4 

   THEN  RETURN 'Jeudi'; 

   ELSIF EXTRACT(DOW FROM dd) = 5 

   THEN  RETURN 'Vendredi'; 

   ELSIF EXTRACT(DOW FROM dd) = 6 

   THEN  RETURN 'Samedi'; 

   ELSIF EXTRACT(DOW FROM d) = 0 

   THEN  RETURN 'Dimanche'; 

   END IF; 

END; 

$corps$ 

LANGUAGE PLPGSQL

Elle renvoie le nom du jour de la date passée en argument.

Exemple 27 :

CREATE FUNCTION F_NUM_JOUR_SEMAINE(d DATE) 

RETURNS INTEGER 

AS 

$code$ 

DECLARE retval INTEGER; 

BEGIN 

   retval = EXTRACT(DOW FROM d); 

   IF retval = 0  

   THEN 

      retval := 7; 

   END IF; 

   RETURN retval; 

END; 

$code$ 

LANGUAGE PLPGSQL

Cette fonction renvoie le n° du jour de semaine de 1 à 7, 1 étant le lundi et 7 le dimanche (la fonction EXTRACT avec l’argument DOW renvoie le n° du jour de semaine de 0 à 6 avec 0 pour dimanche et 6 pour samedi).

8.2 – La boucle LOOP

Sa syntaxe est la suivante :

[ <label> ]LOOP 

... 

END LOOP [ <label> ];

Comme il s’agit d’une boucle infinie et que dans ce cas elle dure une éternité (l’éternité c’est long, surtout vers la fin, nous dit Woody Allen), il est sage de la limitée à l’aide de la commande :

EXIT [ <label> ] [ WHEN <prédicat> ];

De la même manière il est possible de reboucler au début avec la commande :
CONTINUE [ <label> ] [ WHEN <prédicat> ];

L’identifiant label est un identifiant SQL qui est facultatif et permet de se repérer dans les différentes boucles, notamment lorsqu’elles sont imbriquées…

Exemple 28 :

CREATE FUNCTION F_LUNDI_PRECEDENT(d DATE)  

RETURNS DATE 

AS  

$corps$ 

DECLARE dd date;  

BEGIN  

   dd := d; 

   LOOP 

      EXIT WHEN F_QUEL_JOUR(dd) = 'Lundi';   

      dd = dd - INTERVAL '1 DAY'; 

   END LOOP; 

   RETURN dd; 

END;  

$corps$  

LANGUAGE PLPGSQL

Cette fonction renvoie le lundi précédent ou le jour même si c’est un lundi d’une date passée en argument. Elle utilise la fonction précédemment créée (F_QUEL_JOUR).

8.3 – La boucle WHILE

Syntaxe :

[ <label> ] 

WHILE <prédicat>  

LOOP 

   ... 

END LOOP [ <label> ];

Voici l’exemple 26 précédent repris avec une boucle WHILE :

Exemple 29 :

CREATE FUNCTION F_LUNDI_PRECEDENT(d DATE)   

RETURNS DATE  

AS   

$corps$  

DECLARE dd date;   

BEGIN   

   dd := d;  

   WHILE F_QUEL_JOUR(dd) <> 'Lundi' 

   LOOP  

      dd = dd - INTERVAL '1 DAY';  

   END LOOP;  

   RETURN dd;  

END;   

$corps$   

LANGUAGE PLPGSQL

Bien entendu on peut utiliser EXIT et CONTINUE, car il s’agit aussi d’une boucle LOOP !

8.4 – La boucle FOR

Syntaxe :

[ <label> ] 

FOR indice IN [ REVERSE ] borne1 .. borne2 [ BY pas ] LOOP 

    ... 

END LOOP [ <label> ];

Avec :

• indice : la variable de boucle, uniquement lisible dans la boucle (pas de modification possible) et qui ne doit pas être préalablement déclarée dans le bloc DECLARE.
• borne1, borne2 : les bornes de l’intervalle de bouclage. En cas d’inversion, la boucle n’est pas exécutée.
• pas : le pas d’incrément de la boucle qui doit être pris dans l’ensemble N* (entiers naturels, zéro excepté).

A nouveau s’agissant toujours d’une boucle LOOP vous pouvez utiliser EXIT et CONTINUE.

Exemple 30 :

CREATE FUNCTION F_JOURS_FIN_MOIS(d DATE) 

RETURNS INTEGER 

AS 

$code$ 

DECLARE dd DATE; 

        n  INTEGER; 

        m  INTEGER; 

BEGIN 

   dd := d; 

   m := EXTRACT(MONTH FROM d); 

   FOR i IN 1 .. 31 

   LOOP  

      n := i;          

      EXIT WHEN EXTRACT(MONTH FROM dd + i * INTERVAL '1 DAY') <> m; 

   END LOOP; 

   RETURN n-1;    

END; 

$code$ 

LANGUAGE PLPGSQL

Cette fonction renvoie le nombre de jours à courir à partir d’une date données pour aller à la fin du mois.

8.5 – La structure CASE

La dernière structure de branchement que nous allons étudier est la structure CASE qui diffère de celle utilisée par SQL dans le sens ou elle ne renvoie pas de valeur, mais permet des traitements comparable à une série de IF imbriqués. Comme pour SQL, elle possède deux syntaxes différentes :

8.5.1 РCASE valu̩ :

Syntaxe :

CASE <expression> 

   WHEN valeur11 [, valeur12 [ ... ]]  

   THEN 

      ... 

 [ WHEN valeur21 [, valeur22 [ ... ]]  

   THEN 

      ... ] 

 [ ... ] 

 [ ELSE 

      ... ] 

END CASE;

Exemple 31 :

CREATE FUNCTION F_TYPE_JOUR(d DATE) 

RETURNS VARCHAR(8) 

AS 

$code$ 

DECLARE retval CHAR(7); 

BEGIN 

   CASE EXTRACT(DOW FROM d) 

      WHEN 0, 6 THEN retval := 'Weekend'; 

      WHEN 1, 5 THEN retval := 'Semaine'; 

      ELSE retval := 'Inconnu'; 

   END CASE; 

   RETURN retval; 

END; 

$code$ 

LANGUAGE PLPGSQL

Cette fonction renvoie « Semaine » ou « Weekend » selon que le jour va du lundi au vendredi ou bien est un samedi ou dimanche.

8.5.2 РCASE g̩n̩ralis̩ :

CASE 

   WHEN <prédicat1>  

   THEN 

      ... 

 [ WHEN <prédicat2>  

   THEN 

      ... ] 

 [ ... ] 

 [ ELSE 

      ... ] 

END CASE;

Exemple 32 :

CREATE FUNCTION F_NOMBRE_JOUR_MOIS(d DATE) 

RETURNS INTEGER 

AS 

$code$ 

DECLARE retval INTEGER; 

        mois   INTEGER; 

        an     INTEGER; 

BEGIN 

   mois = EXTRACT(MONTH FROM d); 

   an   = EXTRACT(YEAR FROM d); 

   CASE 

      WHEN mois IN (1, 3, 5, 7, 8, 10, 12) THEN retval := 31; 

      WHEN mois IN (4, 6, 9, 11)           THEN retval := 30; 

      WHEN mois = 2 AND (an % 4) <> 0      THEN retval := 28; 

      WHEN mois = 2 AND (an % 4) = 0  

                    AND (an % 100) <> 0  

                    AND (an % 400) <> 0    THEN retval = 29; 

      WHEN mois = 2 AND (an % 4) = 0  

                    AND (an % 400) = 0     THEN retval = 29 ; 

      ELSE retval = 28; 

   END CASE; 

   RETURN retval;               

END; 

$code$ 

LANGUAGE PLPGSQL

Cette fonction renvoie le nombre de jours du mois de la date passée en argument en tenant compte des règles pour les années bissextiles.

9 – GESTION D’ERREUR

Nous allons maintenant étudier la façon de récupérer des erreurs, comme de les générer. Le gestionnaire d’exception (bloc de code EXCEPTION) permet de récupérer la main sur le code après une erreur. La fonction RAISE permet de lancer une erreur, mais aussi un avertissement, etc.

9.1 – bloc de gestion des erreurs

Les routines PLpgSQL permettent de placer un bloc de traitement global des erreurs introduit par le mot clef EXCEPTION. Ce bloc doit être situé en fin de corps avant le mot clef END. En voici la syntaxe :

EXCEPTION 

    WHEN <erreur11>  [ OR <erreur12>  [ OR ... ] ]  

    THEN 

        ... 

  [ WHEN <erreur21>  [ OR <erreur22>  [ OR ... ] ]  

    THEN 

        ...  ] 

  [ ... ]

Avec :

<erreurN> ::= 

   { erreur_SQL | SQLSTATE 'valeur' | OTHERS }

erreur_SQL : est le code alphabétique d’une erreur dans le tableau des erreurs figurant en annexe A de la documentation officielle.
SQLSTATE ‘valeur’ : permet d’indiquer une classe d’erreur (voir l’annexe A de la documentation officielle).
OTHERS : permet de trapper toutes les erreurs.

Exemple 33 :

CREATE FUNCTION F_NOM_JOUR(d DATE) 

RETURNS VARCHAR(8) 

AS 

$code$ 

DECLARE retval VARCHAR(8); 

BEGIN 

   CASE EXTRACT(DOW FROM d) 

      WHEN 1 THEN retval := 'Lundi'; 

      WHEN 2 THEN retval := 'Mardi'; 

      WHEN 3 THEN retval := 'Mercredi'; 

      WHEN 4 THEN retval := 'Jeudi'; 

      WHEN 5 THEN retval := 'Vendredi'; 

      WHEN 6 THEN retval := 'Samedi'; 

      WHEN 7 THEN retval := 'Dimanche'; 

   END CASE; 

   RETURN retval; 

EXCEPTION 

   WHEN case_not_found 

   THEN RETURN 'inconnu';  

END; 

$code$ 

LANGUAGE PLPGSQL

Dans cet exemple de fonction censée retournée le nom du jour en fonction de son numéro dans la semaine, le passage en argument d’une date comme le 19 juin 2011 passe en erreur dans le bloc EXCEPTION et renvoi « inconnu » car les dimanche sont numérotés 0 par la fonction EXTRACT (DOW…

Il est néanmoins possible de placer plusieurs gestionnaires d’exception, mais pour cela il faut autant de bloc d’instruction BEGIN /END que nécessaire.

9.2 – génération d’une erreur

Il est possible de générer une erreur à l’aide de la commande RAISE dont la syntaxe est la suivante :

RAISE [ { DEBUG | LOG | INFO | NOTICE | WARNING | EXCEPTION } ]  

      [ { 'message_erreur' [ <expressions> ] | erreur_SQL | SQLSTATE 'valeur' } ] 

[ USING { MESSAGE | DETAIL | HINT | ERRCODE } = 'message' ];

Avec :

<expressions> ::= 

   <expression1> [ , <expression2> [, ... ] ]

DEBUG, LOG, INFO : enregistre l’erreur dans le journal PostGreSQL. Le code n’est pas affecté et aucun gestionnaire d’erreur (bloc EXCEPTION) ne la prend en compte.
NOTICE, WARNING : enregistre l’erreur dans le journal PostGreSQL et l’envoie au client à titre d’avertissement. Le code n’est pas affecté et aucun gestionnaire d’erreur (bloc EXCEPTION) ne la prend en compte.
EXCEPTION : enregistre l’erreur dans le journal PostGreSQL, l’envoie au client à titre d’erreur, annule automatiquement la transaction, interrompt le code et passe le contrôle au gestionnaire d’erreur (bloc EXCEPTION) s’il y en a un.

DEBUG, LOG et INFO, de même que NOTICE et WARNING ne sont pas synonymes bien qu’il agissent de la même manière, car ils peuvent être combinés à la façon dont sont journalisées les erreurs. La priorité du plus faible au plus fort étant DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION.

EXCEPTION est la valeur par défaut dans la liste facultative suivant le mot clef RAISE.

La chaîne de caractères message_erreur peut contenir des caractères % qui seront remplacé par les expressions à la position ordinale considérée.

Les journaux PostGreSQL sont situé dans l’arborescence d’installation du serveur dans …\PostgreSQL\[N° de version)\data\pg_log\

Voici maintenant un peu plus de détails sur quelques unes des multiples syntaxes de la commande RAISE…

9.2.1 – RAISE EXCEPTION ‘message_erreur’ [ expressions ] [ USING HINT = ‘message’ ]

Permet de renvoyer un message d’erreur personnalisé avec incorporation de valeurs de variable dans le corps du message, suivi d’un éventuel message de conseil.

Exemple 34 :

CREATE FUNCTION F_CONSTRUCT_DATE(an INTEGER, mois INTEGER, jour INTEGER) 

RETURNS DATE 

AS 

$code$ 

DECLARE ds VARCHAR(10); 

        d  DATE; 

BEGIN 

   -- test des cnditions limites 

   IF an <= 0 

   THEN 

      RAISE EXCEPTION 'Année % en dehors de la plage de valeur 1..9999', an; 

   END IF;    

   IF mois NOT BETWEEN 1 AND 12 

   THEN 

      RAISE EXCEPTION 'Mois % en dehors de la plage de valeur 1..12', mois  

            USING HINT = 'Mois incorrect, mais année ' || CAST(an AS VARCHAR(4)) || ' correcte.'; 

   END IF; 

   IF jour NOT BETWEEN 1 AND 31 

   THEN 

      RAISE EXCEPTION 'Jour % en dehors de la plage de valeur 1..31', jour 

            USING HINT = 'Mois incorrect, mais année ' || CAST(an   AS VARCHAR(4)) || ' et mois ' 

                                                       || CAST(mois AS VARCHAR(4)) || ' correct.'; 

   END IF;                                                     

   IF jour = 31 AND mois IN (2, 4, 6, 9, 11)  

   THEN                                                     

      RAISE EXCEPTION 'Jour 31 impossible pour le mois %', mois 

            USING HINT = 'Mois ou jour incorrect, mais année ' || CAST(an AS VARCHAR(4)) || ' correcte.'; 

   END IF; 

   IF jour = 30 AND mois = 2  

   THEN                                                     

      RAISE EXCEPTION 'Jour 30 impossible pour le mois de février' 

            USING HINT = 'Rectifiez les paramètres de date.'; 

   END IF; 

   IF jour = 29 AND mois = 2 and an % 4 <> 0  

   THEN                                                     

      RAISE EXCEPTION 'Jour 29 impossible pour le mois de février de l''année %', an 

            USING HINT = 'Rectifiez les paramètres de date. Les années bissextiles sont divisibles par 4.'; 

   END IF; 

   IF jour = 29 AND mois = 2 and an % 4 = 0 AND an % 100 = 0 AND an % 400 <> 0  

   THEN                                                     

      RAISE EXCEPTION 'Jour 29 impossible pour le mois de février de l''année %', an 

            USING HINT = 'Rectifiez les paramètres de date. Toutes les années divisibles par 4 ne sont pas bissextiles : les siècles font exception sauf tous les 400 ans.'; 

   END IF;          

   -- nos paramètres sont bons, construction de la date 

   ds := CAST(an AS VARCHAR(4)); 

   WHILE CHARACTER_LENGTH(ds) < 4 

   LOOP 

       ds := '0' || ds; 

   END LOOP;     

   IF mois < 10 

   THEN 

      ds := ds || '-0' || CAST(mois AS VARCHAR(1)); 

   ELSE 

      ds := ds || '-'  || CAST(mois AS VARCHAR(2)); 

   END IF; 

   IF jour < 10 

   THEN 

      ds := ds || '-0' || CAST(jour AS VARCHAR(1)); 

   ELSE 

      ds := ds || '-'  || CAST(jour AS VARCHAR(2)); 

   END IF; 

   d := CAST(ds AS DATE);      

   RETURN d;    

END; 

$code$ 

LANGUAGE PLPGSQL

Cette fonction construit une date à partir des paramètres an, moi et jour qui lui sont fournit sous forme d’entiers.

Exemple 35 – L’appel suivant dans pgAdminIII … :
SELECT F_CONSTRUCT_DATE(1929, 2, 29)
… provoque l’apparition du texte suivant dans l’onglet « Message » de la fenêtre de l’éditeur SQL :

Le erreur générées par l’utilisateur ont toujours la valeur P0001 et peuvent être récupérées dans le gestionnaire d’exception par le code raise_exception correspondant à valeur ou par OTHERS.

Cette même erreur, provoque une entrée dans le journal d’événement en cours (les journaux ont un nom composé de la manière suivante : postgresql-%Y-%m-%d_%H%M%S.log) :

9.2.2 – RAISE EXCEPTION { erreur_SQL | SQLSTATE ‘valeur’ } [ USING HINT = ‘message’ ]

Permet de renvoyer un message d’erreur standard sur une erreur précise ou une classe d’erreur.

Exemple 36 :

CREATE FUNCTION F_ROOT_R(n NUMERIC, r INTEGER) 

RETURNS NUMERIC 

AS 

$$ 

DECLARE signe  NUMERIC; 

        nombre NUMERIC; 

BEGIN 

   signe := 1; 

   nombre := n; 

   IF r % 2 = 1 

   THEN  

      signe = -1; 

   END IF; 

   nombre := nombre * signe; 

   IF r % 2 = 0 

   THEN 

      RAISE EXCEPTION invalid_argument_for_power_function  

            USING HINT = 'L''extraction d''une racine d''un nombre négatif n''est possible que si la racine est impaire'; 

   END IF;             

   RETURN POWER(nombre, 1.0/r) * signe; 

END; 

$$ 

LANGUAGE PLPGSQL

Cette exemple extrait une racine R° d’un nombre N. Si la racine est impaire, le nombre peut être négatif.

Exemple 37 РEssayons avec une racine bicarr̩e :

SELECT F_ROOT_R(-81, 4)

Exemple 38 – Tandis qu’avec une racine cubique, cela marche :

10 – DÉBOGAGE

Il existe différents outils de débogage pour PostGreSQL :
Sous windows : PL/pgSQL Debugger. Nécessite au moins la version 8.2 de PostgreSQL.
Sous Linux/KDE : KPoGre PL/pgSQL function debugger.
Aucun débogueur ne permet de visualiser les requêtes SQL en cours d’exécution.

Mais avant d’utiliser ce genre d’outil il est possible d’utiliser des choses simples comme le RAISE en mode NOTICE ou DEBUG et en jouant sur les paramètres log_min_messages et client_min_messages du fichier postgreSQL.conf, les faire apparaître dans le client de développement ou bien les inscrire dans le journal d’événement.

Voici une fonction écrite juste pour tester RAISE NOTICE et RAISE DEBUG :

Exemple 39 :

CREATE FUNCTION F_TEST_RAISE(nature VARCHAR(6)) 

RETURNS VARCHAR(32) 

AS 

$$ 

BEGIN 

   CASE UPPER(nature)  

   WHEN 'NOTICE' 

   THEN 

      RAISE NOTICE 'Message NOTICE de test !'; 

   WHEN 'DEBUG' 

   THEN 

      RAISE DEBUG 'Message DEBUG de test !'; 

   ELSE 

      RAISE EXCEPTION 'Message EXCEPTION de test !';    

   END CASE;    

   RETURN NULL; 

END;       

$$ 

LANGUAGE PLPGSQL

10.1 РInformation du client en fonction du param̩trage de client_min_messages dans PostGreSQL.cong :

Avec :

X : aucune information

SC1 : Sortie cliente 1 :

SC2 : Sortie cliente 2 :

10.2 РJournalisation en fonction du param̩trage de log_min_messages dans PostGreSQL.cong :

Avec :

X : aucune information

JE1 : Journal Événement 1 :

JE2 : Journal Événement 2 :

11 – DIRECTIVES DIVERSES À LA CRÉATION DES FONCTIONS

Lors de la création d’une fonction, vous pouvez spécifier un certain nombre de directive qui aide le compilateur, l’optimiseur et le gestionnaire d’autorisation à faire les uns et les autres leur travail.
Voici les directives essentielles que l’on peut donner à une fonction PLpgSQL :

  WINDOW 

  | { IMMUTABLE | STABLE | VOLATILE }  

  | { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT } 

  | { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER } 

  | COST execution_cost 

  | ROWS result_rows 

  | SET configuration_parameter { TO value | = value | FROM CURRENT }

11.1 РParam̩trage pour la compilation :

WINDOW : signifie c’est une fonction de fenêtrage. Mais elle peuvent actuellement n’être écrite qu’en C.

CALLED ON NULL INPUT, RETURNS NULL ON NULL INPUT (ou STRICT) : pour les deux premiers (norme SQL) . Indique si l’on doit retourner immédiatement NULL si l’un des arguments est NULL (RETURNS NULL ON NULL INPUT ou STRICT) ou bien jouer le code même en cas de NULL (CALLED ON NULL INPUT).

11.2 – Paramétrage pour l’optimisation :

IMMUTABLE, STABLE, VOLATILE (correspond approximativement aux paramètres DETERMINISTIC … et CONTAINS, READ, MODIFIES … SQL de la norme) :

• IMMUTABLE signifie que la fonction renverra toujours le même résultat quelque soit le contenu de la base de données. Cela n’est possile que si la fonction ne lit pas de données dans la base ou bien qu’elle lit des données n’évoluant jamais au cours de la vie de la base. IMMUTABLE est l’équivalent de DETERMINISTIC + NO SQL dans la norme SQL.
• STABLE signifie que la fonction peut lire des données qui peuvent évoluer, mais ne modifie pas l’état de la base. STABLE est l’équivalent de DETERMINISTIC + READS SQL DATA dans la norme SQL.
• VOLATILE signifie que la fonction doit être systématiquement réévaluée par l’optimiseur à chaque ligne. Cela se conçoit si la fonction contient par exemple un éléments non déterministe (Woody Allen, à chaque fois qu’un lui demande l’heure, dit « je ne peut pas vous la donner, ça change tout le temps ! »). VOLATILE correspond à peu près à NOT DETERMINISTIC ou MODIFIES SQL DATA dans la norme SQL.

ATTENTION : VOLATILE est la paramétrage par défaut. Il est souhaitable pour toutes les fonctions scalaires ne faisant aucune lecture de données de tables ou vues d’être compilées avec l’option IMMUTABLE, sauf si elles contiennent une fonction non déterministe comme random ou CURRENT_DATE.

COST coût_exécution : indique le coût de la fonction pour l’optimiseur (à défaut c’est 100).

ROWS nombre_lignes : indique le nombre de ligne retournées pour l’optimiseur. Cette directive n’est possible que si la fonction est déclarée comme renvoyant des lignes de table. A défaut c’est 1000 lignes. (voir chapitre 2)

SET paramètre_de_configuration { TO value | = valeur | FROM CURRENT } : permet de modifier un paramètre de session ou de serveur le temps de l’exécution. Voir la commande SET et le chapitre de la documentation consacré à la configuration du serveur pour plus d’information.

Exemple 40 :

CREATE OR REPLACE FUNCTION f_planck_js() 

  RETURNS double precision AS 

$BODY$ 

BEGIN 

  RETURN 6.62606896E-34; 

END;    

$BODY$ 

LANGUAGE PLPGSQL  

IMMUTABLE 

COST 10;

11.3 РParam̩trage pour la s̩curit̩ :

[EXTERNAL] SECURITY INVOKER : la fonction sera exécutée dans le contexte de sécurité de l’utilisateur l’appelant (valeur à défaut).
[EXTERNAL] SECURITY DEFINER : la fonction sera exécutée dans le contexte de sécurité de l’utilisateur l’ayant créée.

12 – MÉTADONNÉES DES FONCTIONS

La norme SQL a prévue des vues d’information de schéma ou l’on trouve pèle-mêle toutes les routines, leurs paramètres et les privilèges y afférent.
SELECT * FROM INFORMATION_SCHEMA.ROUTINES –> donne la liste des fonctions
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS –> donne la liste des paramètres des fonctions
SELECT * FROM INFORMATION_SCHEMA.ROUTINE_PRIVILEGES –> donne la liste des privilèges données sur les fonctions

En sus des ces vues, il existe la table pg_proc du schéma pg_catalog qui fournit les détails spécifiques à l’encodage des fonctions de PostGreSQL.
SELECT * FROM pg_catalog.pg_proc

13 – CONSEILS DE CODAGE

Voici quelques conseil important car les développeurs ont généralement l’habitude de trop utiliser des fonctions, qui sont difficile voire impossible à optimiser, là ou du code ensembliste pourrait être bénéfique…

13.1 Рsch̩ma SQL :

Dans toutes vos requêtes, comme dans le code des routines, préfixez toujours votre fonction par son schéma, même si vous l’avez créée dans le schéma public, sinon PostGreSQL perdra du temps à chercher ou elle se trouve à chaque compilation car cela peut avoir évolué entre deux lancement d’une même requête ou fonction. Même les outils comme pgAdminIII ont tendance à oublier les schémas SQL, notamment quand vous dzmandez par l’IHM un script SELECT !

Exemple 41 :

SELECT public.f_quand();

13.2 – RETURN

Terminez toujours votre fonction scalaire par un RETURN… ça ne mange pas de pain et vous évitera quelques inconvennues lorsque les conditions ne sortent pas dans les branchements.

Exemple 42 :

CREATE FUNCTION F_MAX2(u INT, v INT) 

RETURNS INT 

AS 

$$ 

BEGIN 

   IF u > v 

   THEN 

      RETURN u; 

   END IF; 

   IF v > u 

   THEN 

      RETURN v; 

   END IF; 

END; 

$$ 

LANGUAGE PLPGSQL

Devinez pourquoi cette fonction ne fonctionne pas correctement…

13.3 – Effets de bord :

Tester les valeurs limites des arguments et si le cas est indéterminable, renvoyez NULL. Cela vous éviterez de dérouler du code inutile. Au pire, levez une exception.
De la même manière éliminez les cas triviaux (ceux qui demande le moins de code) en premier.

Exemple 43 – voici une fonction « pivot » qui fait une rotation des parties gauche et droite d’un mot autour d’un caractère pivot :

CREATE FUNCTION F_SPLIT(word VARCHAR(128), pivot INTEGER) 

RETURNS VARCHAR(128) 

AS 

$$ 

BEGIN 

-- mot vide 

   IF word = '' 

   THEN  

      RETURN ''; 

   END IF; 

-- pivot en dehors des limites du mot    

   IF pivot NOT BETWEEN 1 AND CHARACTER_LENGTH(word)  

   THEN 

      RETURN word; 

   END IF;    

-- pivot sur 1er caractère    

   IF pivot = 1  

   THEN 

      RETURN SUBSTRING(word FROM 2 FOR CHARACTER_LENGTH(word)-1)  

          || SUBSTRING(word FROM 1 FOR 1); 

   END IF;                 

-- pivot sur dernier caractère     

   IF pivot = CHARACTER_LENGTH(word)  

   THEN 

      RETURN SUBSTRING(word FROM CHARACTER_LENGTH(word) FOR 1)  

          || SUBSTRING(word FROM 1 FOR CHARACTER_LENGTH(word)-1); 

   END IF;        

-- cas général : 

   RETURN SUBSTRING(word FROM pivot + 1 FOR CHARACTER_LENGTH(word)-pivot)  

       || SUBSTRING(word FROM pivot FOR 1) 

       || SUBSTRING(word FROM 1 FOR pivot-1); 

END; 

$$ 

LANGUAGE PLPGSQL

13.4 – Fréquences d’hypothèses :

Si différentes hypothese de traitement sont possible, mettez toujours en premier le cas le plus fréquent.

Exemple 44 :

CREATE FUNCTION F_SANG_DETAIL(sang CHAR(3)) 

RETURNS VARCHAR(10) 

AS 

$$ 

DECLARE 

   groupe VARCHAR(2); 

   rhesus CHAR(1); 

BEGIN 

   rhesus := SUBSTRING(sang FROM 3 FOR 1); 

   IF rhesus = ' ' 

   THEN 

      rhesus := SUBSTRING(sang FROM 2 FOR 1); 

      groupe := SUBSTRING(sang FROM 1 FOR 1); 

   ELSE 

      groupe := SUBSTRING(sang FROM 1 FOR 2); 

   END IF;    

   CASE rhesus 

   WHEN '+'  

      THEN RETURN groupe || ' positif'; 

   WHEN '-'  

      THEN RETURN groupe || ' négatif'; 

   END CASE; 

   RETURN NULL;    

END; 

$$ 

LANGUAGE PLPGSQL

Le rhésus négatif étant présent dans 15 % de la population seulement.

13.5 РPr̩f̩rez SQL

Si vous avez le choix entre coder une fonction en langage SQL (donc ensembliste) ou bien en langage PL/pgSQL (itératif), n’hésitez jamais. Utilisez de préférence la version SQL qui est ensembliste et donc optimisable. Ce n’est pas toujours le cas d’une fonction itérative codée en PL/pgSQL.

Exemple 45 :

CREATE FUNCTION F_JOUR_EN_8(d date)  

RETURNS DATE 

AS 

$corps$ 

   SELECT CAST($1 + INTERVAL '7 DAYS' AS DATE); 

$corps$ 

LANGUAGE SQL

Au lieu de l’exemple 8

13.6 РPr̩f̩rez une requ̻te SQL

Si au lieu d’une fonction, et quelque soit la complexité, vous pouvez écrire une pure requête SQL, alors faîtes-le. Pour les mêmes raisons d’optimisation,

13.7 РUtilisez diff̩rents sch̩mas SQL

Placez vos fonctions dans différents schémas SQL. Par exemple les fonctions spécifiques à un pan fonctionnel de votre application gagneraient a être placées dans un schéma portant le nom fonctionnel (vente, production, compta…). Les fonctions génériques pouvant être laissées dans le schéma public.

13.8 – Gestion d’erreur

N’utilisez pas de bloc EXCEPTION si ce n’est pas absolument nécessaire. En effet le traitement des exception est couteux et cela n’a généralement pas de sens dans une fonction scalaire qui s’utilise la plupart du temps dans une requête SQL.

13.9 РJournalisation des ̩v̩nements

Vérifiez la façon dont le serveur journalise les erreurs. Ceci peut être un point de contention, en particulier si vous avez codé des routines faisant appel à la programmation par contraintes et lancé de nombreuses exceptions.
Le fichier de configuration du serveur est situé à …\PostGreSQL\[N° version]\data\postgresql.conf. Il s’agit d’un fichier texte. Les entrées à modifier sont log_min_messages, log_min_error_statement (le niveau « error » correspond aux EXCEPTION) et log_error_verbosity (qui peut être descendu à « terse »).

ANNEXE 1 : COMPARAISON MS SQL SERVER vs POSTGRESQL

Ce tableau comparatif ne porte que sur les fonctions scalaires. Il est destiné aux développeurs qui souhaitent passer de l’un à l’autre.

Les deux SGBDR sont assez proches en matière de possibilité de création d’UDF, comme en exploitation bien que SQL Server possède de légers avantages comme la possibilité de forcer la compilation ou le masquage du code. En revanche en matière de codage SQL Server s’avère plus simple avec une syntaxe dépouillé et souple, mais qui peut s’avérer piégeante pour des développeurs débutants.
Seule ombre au tableau de PostGreSQL, l’inaptitude du compilateur au transtypage à la volée (voir ci après).

(1) Exemple 46 :

CREATE FUNCTION F_SMALLINT(i SMALLINT) 

RETURNS INTEGER 

AS 

$$ 

BEGIN 

   RETURN i; 

END 

$$ 

LANGUAGE PLPGSQL

La fonction est déclaré pour un smallint en argument. L’exécution de :

Renvoie une erreur :

…parce que le compilateur SQL n’est pas capable de l’interpréter en tant que smallint et d’effectuer un transtypage à la volée, alors qu’il en est capable pour des expressions de pur SQL !

(2) il existe bien un paramétrage #option dump recompile pour PostGreSQL, mais elle ne semble fonctionner que pour des fonctions écrites en plpgpsm.

ANNEXE 2 : VOCABULAIRE

Argument : Valeur des paramètres de la fonction.

DDL : « Data Definition Language » (en français LDD pour Langage de Définition des Données), sous ensemble du langage SQL dédié à la création des objets manipulant les données de la base (essentiellement ordre CREATE, ALTER et DROP).

Entier naturel : ensemble mathématique noté habituellement N et désignant les nombre entiers positif y compris le zéro. N* note l’ensemble des entiers naturels excepté le zéro.

Polymorphisme : (du grec, « plusieurs formes« ) pour une fonction, le polymorphisme paramétrique (aussi appelé généricité), permet de définir plusieurs fonctions de même nom, possédant des paramètres différents (en nombre ou en type).

Position ordinale : rang d’un objet dans le sens de présentation. Généralement par rapport au sens de lecture.

Scalaire : information composée d’un seul élément de nature atomique.

Schéma SQL : objet de la base permettant de segmenter les objets de données en les plaçant dans différents « conteneurs » logiques. Pour une explication plus poussée, voir : De l’intérêt des SCHÉMA SQL…

Trigger : ou déclencheur (en français), routine dont le code s’exécute automatiquement au moment (avant, après ou à la place) d’un événement particulier. Dans PostGreSQL il n’existe que des déclencheurs sur DML (INSERT, UPDATE, DELETE)

UDF : « User Defined Function », fonctions définie par l’utilisateur, donc les fonctions scalaires que vous créez à l’aide de CREATE FUNCTION, par opposition aux fonctions natives du SQL de PostGreSQL, comme CURRENT_DATE par exemple.

ANNEXE 3 : QUELQUES RÉFÉRENCES

La documentation officielle :

http://www.postgresql.org/docs/current/static/sql-createfunction.html

Quelques bonnes pages :

http://www.commandprompt.com/ppbook/x19832

http://developer.postgresql.org/pgdocs/postgres/xfunc-sql.html

http://chestofbooks.com/computers/databases/postgresql/practical-postgresql/Exception-Handling.html

http://www.mt-soft.com.ar/2007/12/21/a-primer-on-postgres-exception-handling-for-the-php-developer-part-2/

Les forums :

http://www.developpez.net/forums/f89/bases-donnees/postgresql/

http://forums.postgresql.fr/

Les livres
PostgreSQL 9.0 Reference Manual – Volume 2: Programming Guide
Beginning Databases With Postgresql: From Novice To Professional
PostgreSQL

PS : quelques traductions imbéciles on fait donner au mot anglais « planner » le terme planificateur. C’est un non-sens absolu puisque ce terme désigne la partie de l’optimiseur de PostgreSQL destinée à construire un plan de requête le meilleur possible. Rappelons qu’un planificateur est quelqu’un ou quelque chose qui établi un planning… tandis que celui qui fabrique un plan pourrait être qualifié d’architecte ! Pour ma part je préfère le terme plus sain de gestionnaire de plan et pourquoi pas, tout simplement, d’optimiseur !

-------- 

Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ 

Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server 

www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation 

* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *