Fonctions

Fonctions et procédures

Les fonctions et procédures stockées sont des outils permettent d’implémenter des traitements fonctionnels dans la base de données, au plus proche des objets et des données. L’utilisation de cet outil est souvent motivée par la volonté de rapprocher le traitement des données, plutôt que de devoir extraire les données dans une application, d’y appliquer le traitement pour finalement écrire le résultat dans cette même base de données. Il s’agit donc d’un choix d’architecture logicielle qui dépend de ce que l’on souhaite faire des données, notamment leur destination : le choix d’utiliser ou non des fonctions et procédures stockées dépend en partie du besoin d’écrire les résultats ou de communiquer les données et les résultats à des systèmes externes à l’application.

Les fonctions et procédures stockées peuvent utiliser différents langages : intégrés à PostgreSQL comme SQL ou PL/pgSQL ou utilisant des processus externes, comme Python. De nombreuses extensions permettent d’intégrer d’autres langages comme Java, R ou PHP.

Si les fonctions stockées sont utilisables depuis longtemps dans PostgreSQL, la notion de procédure stockée apparaît avec la version 12 : elle apporte la possibilité de valider les modifications de données (COMMIT) pendant l’exécution de la procédure.

CREATE [ OR REPLACE ] FUNCTION 
   nom( [ [ mode_arg ] [ nom_arg ] type [ { DEFAULT | = }  
expr ] [, ...] ] ) 
   [ RETURNS type 
     | RETURNS TABLE ( nom_colonne type [, ...] ) ] 
 { LANGUAGE nom_langage 
   | IMMUTABLE | STABLE | VOLATILE 
   | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT 
   | SECURITY INVOKER | SECURITY DEFINER 
   | COST execution_cost 
   | ROWS result_rows 
 } ... 

Langage PL/pgSQL

Le langage PL/pgSQL est un module optionnel de PostgreSQL, livré avec PostgreSQL, et pré-installé en tant qu’extension dans les bases de données. Il s’agit d’un langage interprété, utilisé dans une fonction ou une procédure, permettant de manipuler les données à travers le langage SQL. En effet, ce dernier est directement utilisable dans le code PL/pgSQL.

L’exemple suivant réécrit l’exemple de la fonction SQL précédente, avec le langage PL/pgSQL :

CREATE OR REPLACE FUNCTION tickets.concat_artiste 
 ( art_nom1 text, art_nom2 text 
  , sep text default ' / ' ) 
 RETURNS text 
 LANGUAGE plpgsql 
 STRICT IMMUTABLE 
AS $$ 
DECLARE 
 v_ret text ; 
BEGIN 
 v_ret := art_nom1 || sep || art_nom2 ; 
 
 return v_ret ; 
END 
$$ ; 

Le code est composé de deux blocs :

Le code de la fonction affecte le résultat de la concaténation des chaînes de caractères dans la variable v_ret, puis cette variable est utilisée par l’instruction return à la fin de la fonction.

Langage PL/Python

Outre les langages SQL et PL/pgSQL, il est possible de créer des fonctions en utilisant de nombreux langages dans une instance PostgreSQL : Perl, Python, Tcl, Java, R, JavaScript, Lua, Shell. PostgreSQL s’appuie sur un interpréteur de langage externe. Ces langages sont embarqués dans PostgreSQL à travers un gestionnaire de langage. Avec PostgreSQL sont distribués les gestionnaires pour Tcl, Perl et Python. Par défaut, le gestionnaire PL/Python est untrusted, car il est possible d’exécuter du code en dehors du contexte de l’instance PostgreSQL, par exemple en lisant ou en écrivant des fichiers, ou en exécutant d’autres processus.

En ce qui concerne le module PL/Python, il n’est pas présent ni actif par défaut, mais l’installation est possible très simplement par le biais des systèmes de paquetages des distributions. Il existe des variantes pour Python 2 et Python 3, et dans les exemples suivants, la variante Python 3 est utilisée.

Les commandes suivantes permettent d’installer le module additionnel dans les systèmes d’exploitation Debian/Ubuntu :

apt install postgresql-plpython3-12 

ou Redhat/Centos :

yum install postgresql12-plpython3 

Ensuite, dans la base de données dans laquelle on souhaite utiliser le langage, la commande suivante active l’extension :

CREATE EXTENSION plpython3u ; 

Déclencheurs

Il est possible de déclencher l’exécution de fonctions sur des actions ayant lieu sur les tables, et ainsi automatiser des traitements. Les actions ou événements sont les ordres INSERT, UPDATE, DELETE ou TRUNCATE appelés sur une table ou une vue. N’importe quel langage de procédures stockées permet d’écrire les fonctions et elles peuvent être mises en place par la commande CREATE TRIGGER, dont le synopsis est le suivant :

CREATE TRIGGER nomdeclencheur 
{ BEFORE | AFTER | INSTEAD OF } 
{ INSERT | UPDATE [ OF colonne [, ...]] 
  | DELETE | TRUNCATE [ OR ... ] } 
  ON nomtable 
 [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_nom } [ ... ] ] 
 [ FOR EACH { ROW | STATEMENT } ] 
 WHEN ( condition ) 
 EXECUTE PROCEDURE nomfonction ( arguments ) 

Un déclencheur s’utilise avant, après ou à la place d’un événement grâce aux clauses BEFORE, AFTER et INSTEAD OF et peut être utilisé sur chaque ligne concernée par les requêtes INSERT, UPDATE ou DELETE, avec la clause FOR EACH ROW ou une seule fois par ordre SQL avec l’option FOR EACH STATEMENT. Il est possible qu’un même déclencheur fasse référence à plusieurs ordres SQL, en les combinant avec la clause OR.

Il est possible de créer plusieurs déclencheurs sur une même table, et dans ce cas, ils sont appelés en suivant l’ordre alphabétique du nom du déclencheur.

La clause INSTEAD OF ne peut être utilisée que sur une vue, et un déclencheur sur un ordre TRUNCATE ne peut être appelé qu’avec la clause FOR EACH STATEMENT.

En ce qui concerne l’ordre UPDATE, il est aussi possible de cibler une colonne particulière, la fonction est alors appelée uniquement...