L'automatisation des traitements avec PostgreSQL

Une première solution pour automatiser des traitements consiste à utiliser une fonctionnalité spécifique à l'outil d'administration pgAdmin : pgScript. Lorsque vous lancez l'éditeur de requêtes de pgAdmin, vous pouvez noter l'existence d'un menu Requêtes / Exécuter pgScript ou celle d'un bouton Exécuter pgScript.

pgScript est une extension du langage SQL qui offre des fonctionnalités de programmation : variables, structures de test et de boucle. Curieux de le découvrir, vous vous précipitez dans la documentation de PostgreSQL pour découvrir avec perplexité qu'il n'en est pas fait mention. Que se passe-t-il ? C'est tout à fait normal : pgScript est spécifique à pgAdmin, donc vous trouverez sa documentation dans la documentation de pgAdmin.

Si vous utilisez pgAdmin et avez besoin d'automatiser certaines tâches, pgScript peut vous être utile. Nous n'en dirons pas plus ici, car ce type d'automatisation est limité : si vous avez besoin d'écrire une application cliente, où vous utilisez d'autres outils de requêtes, il faut disposer d'une solution disponible sur le serveur et non dans un outil client.

La solution disponible sur le serveur consiste à définir dans la base de données de nouveaux objets : des fonctions. Puisque ce sont des objets de la base de données, nous allons à nouveau utiliser les instructions DDL de SQL : CREATE, ALTER et DROP pour effectuer les opérations de gestion de ces objets.

Une fonction est définie par un nom, des paramètres optionnels et une valeur de retour également optionnelle. PostgreSQL propose plusieurs langages pour écrire les instructions internes de la fonction et le langage employé doit être indiqué lors de la définition. Nous emploierons SQL dans ce chapitre et nous verrons au prochain chapitre l'utilisation de PL/pgSQL qui permet de réaliser des traitements algorithmiques complexes.

La création d'une fonction s'effectue avec l'instruction CREATE FUNCTION dont la syntaxe est :

CREATE FUNCTION nomFonction(par1, par2, par3) RETURNS typeDonnées AS $$
Instructions SQL;
$$ LANGUAGE SQL;

La structure de la déclaration est un peu surprenante, avec les deux mots $$ qui entourent une série d'instructions SQL. En fait, les instructions de la fonction sont indiquées dans une chaîne de caractères constante, qui peut être définie à l'aide des apostrophes. Mais si les instructions comportent des apostrophes, il faut les doubler pour qu'elles soient considérées comme du texte et la définition peut devenir difficile à écrire et à lire. PostgreSQL propose donc une notation pour définir une constante chaîne avec des dollars, appelée guillemets dollars. Les expressions 'texte', $$texte$$ ou $delim$texte$delim$ sont équivalentes pour définir une chaîne de caractères. On peut choisir ce que l'on veut comme balise delim et, lorsque c'est nécessaire, imbriquer des définitions de chaînes avec des balises différentes. L'intérêt des guillemets dollars est qu'il n'est pas utile de doubler les apostrophes ou les antislashs, ce qui facilite l'écriture.

Le langage des instructions est indiqué par l'instruction LANGUAGE après la définition des instructions. On peut utiliser n'importe quelle instruction SQL dans le corps de la fonction à l'exception des instructions de gestion des transactions (BEGIN, COMMIT, ROLLBACK, SAVEPOINT) et on peut exécuter plusieurs instructions.

Les résultats de la dernière instruction SQL sont renvoyés par la fonction et leur type doit être défini par la clause RETURNS typeDonnées.

Si la fonction ne renvoie rien, il faut utiliser une clause RETURNS void.

Une fonction peut avoir des paramètres pour lesquels il est obligatoire de définir le type. Par défaut ces paramètres sont des paramètres d'entrée. Le nom des paramètres est optionnel : seule la position des paramètres est importante et ils sont référencés par les variables $1, $2, etc. dans les instructions de la procédure.

Par exemple, une fonction qui calcule le nombre de villes ayant le même code postal peut s'écrire ainsi :

CREATE OR REPLACE FUNCTION compte(cp codepostal)
RETURNS bigint AS
$BODY$ select count(*) from ville where ville.cp = $1 $BODY$
LANGUAGE 'sql'

La variable cp ne sert à rien et on pourrait l'omettre, mais la définition de la fonction serait moins lisible. On peut utiliser n'importe quel type, y compris les types définis dans la base, ce que nous avons fait en employant le type codepostal. Le paramètre est employé avec l'expression $1.

La fonction peut s'appeler comme n'importe quelle autre fonction, de cette façon :

select compte(69000::codepostal);

Nous avons utilisé ici une conversion avec la notation ::typeDeDonnées pour indiquer à PostgreSQL le type de la constante. Une telle conversion peut être employée dans n'importe quelle expression SQL où une conversion est nécessaire. Si nous n'avions pas effectué la conversion, PostgreSQL aurait considéré que 69 000 est un entier et aurait envoyé un message d'erreur, car il n'existe pas de fonction compte(integer). L'appel de fonction s'effectue avec un typage fort et il n'y a pas de conversion automatique.

Puisque le typage des paramètres est fort, PostgreSQL accepte plusieurs définitions d'une même fonction, à condition que les signatures de ces définitions soient différentes, c'est-à-dire que le nombre ou les types des paramètres doivent être différents. On peut par exemple créer une fonction compte qui prend comme paramètre le nom d'une ville, avec cette définition :

CREATE OR REPLACE FUNCTION compte(nville character varying)
RETURNS bigint AS
$BODY$ select count(*) from ville where ville.nville = $1 $BODY$
LANGUAGE 'sql'

Sans aucune ambiguïté, PostgreSQL utilisera cette définition de fonction pour l'instruction select compte('Lyon'); puisque le paramètre est une chaîne de caractères.

Si nous souhaitons retourner plus d'informations, il faut utiliser un type table ou un type composite. Par exemple, pour obtenir tous les champs de la table ville, on peut utiliser la définition suivante :

CREATE OR REPLACE FUNCTION listeville1(cp codepostal)
RETURNS ville AS
$BODY$ select * from ville where ville.cp = $1 $BODY$ 
LANGUAGE 'sql'

L'ennui de cette approche est qu'il peut être nécessaire de créer un type composite uniquement pour cette fonction, par exemple pour n'obtenir que le code postal et la ville :

CREATE TYPE cp_ville AS (cp codepostal, nville character varying)
CREATE OR REPLACE FUNCTION listeville2(cp codepostal)
RETURNS cp_ville AS
$BODY$ select cp,nville from ville where ville.cp = $1 $BODY$ 
LANGUAGE 'sql'

Si ce type n'est pas utile par ailleurs, on peut utiliser des paramètres de sortie, avec la syntaxe suivante, exactement équivalente :

CREATE OR REPLACE FUNCTION listeville3(IN codepostal, OUT codepostal, OUT character varying)
AS $BODY$ select cp,nville from ville where ville.cp = $1 $BODY$ 
LANGUAGE 'sql'

Les fonctions listeville1, listeville2 ou listeville3 peuvent être utilisées partout où une table est attendue, notamment dans une clause FROM. Mais ces trois fonctions ne fournissent qu'un seul enregistrement, même si plusieurs villes ont le même code postal. Pour obtenir un jeu d'enregistrement en retour d'une fonction, il faut utiliser la clause SETOF. Par exemple, pour connaître toutes les villes correspondant au même code postal, on peut écrire :

CREATE OR REPLACE FUNCTION listeville4(IN codepostal, OUT codepostal, OUT character varying)
RETURNS SETOF RECORD
AS $BODY$ select cp,nville from ville where ville.cp = $1 $BODY$ 
LANGUAGE 'sql'

Une façon plus simple d'écrire la même fonction consiste à utiliser un type de retour TABLE. La fonction peut s'écrire finalement

CREATE OR REPLACE FUNCTION listeville(codepostal)
RETURNS TABLE(cp codepostal, nville character varying)
AS $BODY$ select cp,nville from ville where ville.cp = $1 $BODY$ 
LANGUAGE 'sql'

Il est possible de définir une valeur par défaut des paramètres avec l'option DEFAULT ainsi que d'avoir un nombre variable de paramètres avec le type VARIADIC. Je vous invite à parcourir la documentation pour approfondir ces notions, ainsi que celles concernant les attributs VOLATILE, STABLE et IMMUTABLE, qui permettent d'indiquer à PostgreSQL si la fonction peut modifier ou non la base de données, afin d'optimiser la façon dont sont exécutées les requêtes.