Le langage de fonction principal de PostgreSQL est plpgsql, langage qui permet beaucoup d’interaction au sein de la base de données. Cependant, lorsque l’on veut automatiser un grand nombre de procédures au niveau de la base de données, il faut parfois avoir accès aux commandes shell, chose qui n’est pas possible dans plpgsql.

Pour avoir accès au shell, il nous faut changer le langage de fonctions. Nous allons donc choisir un langage créé pour creer des fonctions entièrement en shell !

PL/sh Procedural Language Handler

Comme nous l’apprend le titre, PL/sh est un langage de procédure créé pour PostgreSQL par Peter Eisentraut en 2001. La dernière version est actuellement la version 1.3 datant de fin décembre 2007, adaptée pour les versions 8.2 et 8.3 de PostgreSQL.

PL/sh permet donc, de crééer des fonctions du type :

CREATE FUNCTION alerte (text) RETURNS text AS $addition$
#!/bin/sh
echo “Alerte !!!” | sendmail $1
$addition$ LANGUAGE plsh;

Nous allons donc installer ce nouveau langage dans notre cluster de base de données.

Téléchargement des sources

Pour bien faire, nous allons télécharger les sources de pgplsh à partir du serveur de sources de Mes-Stats par exemple :

mkdir ~/pgplsh
cd ~/pgplsh
wget http://sources.mes-stats.fr/postgresql/plsh/pgplsh-1.3.tar.gz

Puis, nous allons extraire les fichiers :

tar -xzvf pgplsh-1.3.tar.gz
cd pgplsh-1.3

Determiner le path de pg_config

Avant de commencer la compilation et l’installation du langage de procédure, nous allons devoir trouver le path de l’outil qui donne la configuration de PostgreSQL, pg_config. Pour celà, rien de plus simple, tapez :

locate pg_config | grep “pg_config$”

Un résultat de ce type devrait arriver :

/usr/local/pgsql/bin/pg_config

C’est le path par défaut de PostgreSQL. Nous allons l’utiliser pour le reste de l’installation.

Compilation et installation

Maintenant, nous allons compiler puis installer ce nouveau langage sur PostgreSQL. On fait cela en 3 commandes (vous devez toujours être dans le répertoire dans lequel nous avons extrait les fichiers de l’archive téléchargée) :
Note: Exécutez ces opération en tant que super-utilisateur.

./configure PG_CONFIG=/usr/local/pgsql/bin/pg_config
make
make install

Vous devriez avoir quelque chose comme ça :

[...]
———————————————————————-
Libraries have been installed in:
/usr/local/lib/pgplsh
If you ever happen to want to link against installed libraries
in a given directory, LIBDIR, you must either use libtool, and
specify the full pathname of the library, or use the `-LLIBDIR’
flag during linking and do at least one of the following:
- add LIBDIR to the `LD_LIBRARY_PATH’ environment variable
during execution
- add LIBDIR to the `LD_RUN_PATH’ environment variable
during linking
- use the `-Wl,–rpath -Wl,LIBDIR’ linker flag
- have your system administrator add LIBDIR to `/etc/ld.so.conf’

See any operating system documentation about shared libraries for
more information, such as the ld(1) and ld.so(8) manual pages.
———————————————————————-
[...]

C’est que le nouveau langage est bien installé !

Installer le langage sur une base de données

Une fois le langage installer sur le cluster (ou serveur) de base de données PostgreSQL, il faut “l’activer” en quelques sortes sur la/les base(s) de données sur lesquelles nous voulons utiliser ce langage.

Pour celà, il faut d’abors localiser le fichier SQL permettant d’installer ce langage dans un base :

updatedb
locate pgplsh | grep “pgplsh.sql$”

Note: Exécutez au moins la première commande en tant que super-utilisateur.

La commande devrait vous retourner un résultat du type :

/home/samuel/pgplsh/pgplsh-1.3/createlang_pgplsh.sql
/usr/local/share/pgplsh/createlang_pgplsh.sql

Grâce au résultat (la dernière ligne), nous allons pouvoir initialiser le langage de procédure PL/sh sur notre base de données “test” par exemple, en faisant :

psql -d test -f /usr/local/share/pgplsh/createlang_pgplsh.sql

Note: Exécuter cette commande en tant que “postgres” (cet utilisateur système étant l’administrateur du serveur de base de données si vous avez la même base que dans l’article Installer & Configurer PostgreSQL sur Debian)

Ce qui devrait vous retourner strictement :

CREATE FUNCTION
CREATE FUNCTION
CREATE LANGUAGE

Le langage de procédure PL/sh est maintenant installé sur la base “test”.

Créer une fonction shell

Maintenant, nous allons créer un trigger très simple, qui nous envoi un mail lors de l’insertion de données dans la table “matable” du schéma “monschema” de la base “test” :

CREATE TABLE public.test (
col1 text,
col2 text,
);

CREATE FUNCTION public.alerte_update () RETURNS trigger AS $alerte$#!/bin/sh
echo “Ajout d’un enregistrement dans monschema.matable…” | sendmail monmail@monfai.fr
$alerte$ LANGUAGE plsh;

CREATE TRIGGER trigger_test
AFTER INSERT ON public.test
FOR EACH ROW EXECUTE PROCEDURE public.alerte_update();

Ce code envoi un mail à “monmail@monfai.fr” lors de l’insertion d’une ligne dans la table !