Programmation

Introduction

La programmation côté serveur consiste principalement à créer des procédures stockées dans le serveur, en utilisant un gestionnaire de procédures. PostgreSQL ne gère pas lui-même les langages de procédures mais délègue cette tâche à un gestionnaire dédié, ce qui permet d’implémenter de nombreux langages de programmation comme langage de procédure stockée. C’est le cas par exemple de PL/Perl ou PL/Python, qui s’appuient sur un interpréteur externe à PostgreSQL, alors que le langage PL/pgSQL est livré avec PostgreSQL.

Procédures stockées

Les langages de procédures stockées ne sont pas disponibles par défaut dans une base de données. Ils doivent être installés, dans chaque base de données, avec la commande CREATE LANGUAGE ou avec la commande système équivalente createlang. Il est possible d’installer un langage dans une base de données modèle comme template1, pour le rendre automatiquement disponible dans les bases de données créées à partir de ce modèle.

1. SQL

Une fonction du type SQL peut exécuter une liste de requête SQL.

Il n’est pas possible d’utiliser dans ce type de fonction des commandes de contrôles de transactions comme SAVEPOINT ou COMMIT, ni des commandes utilitaires, comme VACUUM, qui ne peuvent pas être exécutées au sein d’une transaction.

Les ordres DML comme SELECT, INSERT, UPDATE, DELETE et les ordres DDL comme CREATE, ALTER ou DROP peuvent être exécutés. Le corps de la fonction peut être encadré soit par de simples guillemets, soit par des doubles dollars, particulièrement utiles lorsque les requêtes elles-mêmes contiennent de simples guillemets.

Seules les données du dernier ordre SQL peuvent être retournées. Par défaut, une fonction ne retournant qu’un seul tuple, seule la première ligne du dernier enregistrement sera retournée, quel que soit le tri appliqué dans cette requête. Il est aussi possible pour une fonction de retourner un ensemble de lignes.

Les arguments utilisés en entrée de la fonction peuvent être utilisés de deux façons. Lorsqu’ils ne sont pas nommés, leur indice permet de les utiliser directement, comme dans l’exemple suivant :


CREATE FUNCTION addition(integer, integer)  
RETURNS integer  
LANGUAGE SQL 
AS $$ 
    SELECT $1 + $2; 
$$;

Cette fonction fait simplement l’addition des deux nombres entiers passés en paramètre. Cette même fonction peut être écrite en utilisant des paramètres nommés, comme dans l’exemple suivant :


CREATE FUNCTION addition( m integer, n integer)  
RETURNS integer  
LANGUAGE SQL 
AS $$ 
    SELECT m + n; 
$$;

Le choix...

Déclencheurs

Un déclencheur est une fonction spéciale qui est appelée sur un événement INSERT, UPDATE ou DELETE sur une table. N’importe quel langage de procédures stockées permet d’écrire ces fonctions et elles peuvent être mises en place par la commande CREATE TRIGGER :


CREATE TRIGGER nom  
 { BEFORE | AFTER | INSTEAD OF }  
{ INSERT | UPDATE [ OF colonne [, ...]]  
  | DELETE | TRUNCATE [ OR ... ] }  
    ON table  
    [ FOR [ EACH ] { ROW | STATEMENT } ]  
    WHEN ( condition )  
    EXECUTE PROCEDURE nomfonc ( arguments )

Un déclencheur s’utilise avant, après ou à la place d’un événement et peut être utilisé sur chaque ligne concernée ou une seule fois par opération avec l’option FOR EACH STATEMENT.

Une fonction de déclencheur n’a généralement pas de paramètres en entrée et retourne un type de données trigger.

1. Code PL/pgSQL

Lorsque une fonction trigger est écrite en PL/pgSQL, quelques variables sont implicitement définies, afin de connaître le contexte d’utilisation de la fonction. Les variables implicites sont les suivantes :

TG_WHEN : moment du déclenchement : AFTER ou BEFORE, soit avant ou après l’ordre SQL déclenchant l’appel de la fonction.
TG_OP : opération INSERT, UPDATE ou DELETE.
TG_TABLE_NAME : nom de la table sur laquelle la fonction est déclenchée.
TG_SCHEMA_NAME : nom du schéma de la table sur lequel la fonction est déclenchée.
TG_NARGS, TG_ARGV : nombre de paramètres et tableau des paramètres d’entrée de la fonction.

Lors de l’utilisation d’un...

Contrôle de fonctions

L’extension plpgsql_check permet de contrôler le code Pl/PgSQL des fonctions. Le profileur permet d’évaluer le détail de l’exécution des fonctions.

1. Profileur de fonctions

À partir de la version 9.6 de PostgreSQL, il existe une extension proposant de profiler l’exécution de fonctions PL/pgSQL afin d’en étudier le comportement et donc d’optimiser leur fonctionnement et leurs performances. Cette extension n’est pas disponible sous forme de paquet binaire dans les distributions RedHat ou Debian, il est donc nécessaire de compiler le module. De plus, il n’est pas recommandé de réaliser les profilages de fonctions dans un système de production, mais plutôt dans un environnement de test.

Le profileur est constitué de deux parties : un module chargé dans l’instance PostgreSQL collectant les informations et une commande cliente récupérant les informations de l’instance et produisant le rapport de profilage.

2. Intallation

Le code source du profileur de fonctions est disponible à l’adresse suivante : https://bitbucket.org/openscg/plprofiler/overview. La page de téléchargement est la suivante : https://bitbucket.org/openscg/plprofiler/downloads/?tab=tags. Au moment de l’écriture de cet ouvrage, la version la plus récente est la 3.2, et l’archive des sources correspondante est la suivante : openscg-plprofiler-380a6e19d5a7.zip.

L’installation du profileur nécessite quelques outils et bibliothèques, ainsi que les fichiers d’en-tête de PostgreSQL. Ces dépendances peuvent être installées en utilisant le système de paquetages de la distribution. Dans un système RedHat :


sudo yum install postgresql10-devel gcc python-setuptools 
python-devel perl

Dans un système Debian :


sudo apt-get install postgresql-server-dev-10 gcc python-setuptools 
python-dev perl

Une fois les dépendances installées, le module est compilé...