Blog ENI : Toute la veille numérique !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
💥 1 livre papier acheté 
= la version en ligne automatiquement offerte. Cliquez ici
  1. Livres et vidéos
  2. PostgreSQL
  3. Fonctions
Extrait - PostgreSQL Principes de base de l'utilisation de la base de données
Extraits du livre
PostgreSQL Principes de base de l'utilisation de la base de données Revenir à la page d'achat du livre

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.

1. Fonctions

Les fonctions sont créées avec la commande CREATE FUNCTION, dont le synopsis est le suivant :

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 
 } ... 

Le nom de la fonction peut être qualifié avec un schéma, comme pour...

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 bloc DECLARE permet de déclarer des variables internes à la fonction,

  • le bloc BEGIN débute le code exécutable, qui se termine par l’instruction END.

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.

1. Syntaxe du langage

Le langage PL/pgSQL est très proche d’autres langages procéduraux, ce qui permet à de nombreux développeurs d’appréhender rapidement ce langage. La première différence visible est la possibilité d’intégrer des requêtes SQL directement dans le corps du code de la fonction, et la deuxième différence, un peu moins visible, est que, par défaut, chaque appel de la fonction est une transaction : tout est valide et sans erreur, sans quoi les modifications apportées à la base de données sont annulées.

a. Déclaration de variables

Les variables internes d’une fonction utilisent les types de données de PostgreSQL, et peuvent faire référence aux types de données utilisés dans les tables de la base de données. D’autres types de données sont utilisables dans le contexte des fonctions PL/pgSQL. Le type...

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 ; 

1. Requête SQL

Le gestionnaire PL/Python dispose de quelques fonctions permettant d’exécuter des requêtes SQL et de produire des messages. Les fonctions suivantes permettent l’exécution de requêtes SQL :

  • plpy.execute(requete [, lignes-max]) : exécute la requête SQL passée en paramètre, et retourne un objet contenant les résultats. Le second paramètre, optionnel, permet de limiter le nombre d’enregistrements dans le résultat.

  • plpy.prepare(requete [, argtypes]) : prépare le plan d’exécution de la requête passée en paramètre et retourne un objet représentant le plan d’exécution. Le second paramètre est un tableau contenant les types de données des arguments de la requête.

  • plpy.execute(plan [, arguments [, lignes-max]]) : exécute la requête préalablement préparée...

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