Partant d'un besoin de calculs statistiques sur des données stockées dans postgresql j'ai été ammené à intégrer le langage R dans notre SI, travaillant avec PostgreSQL sous Debian j'ai cherché le paquet idoine pour me faciliter l'intégration, celui-ci n'existant que dans Squeeze j'ai backporté le paquet postgresql-8.3-plr pour Lenny que je mets à disposition ici. Le paquet original est basé sur le travail Joseph E Conway à l'origine de PL/R

Je profite de ce billet pour décrire l'intégration complète de PL/R dans PostgreSQL.

On y va, première étape récupérer le paquet pour Lenny

wget http://rodolphe.quiedeville.org/backports/lenny/i386/postgresql-8.3-plr_8.3.0.8-1_i386.deb

On procède à l'installation en ayant au préalable installé les dépendances du paquet qui sont r-base-core, postgresql-8.3, et libc6 (>= 2.7-1), toutes satisfaites dans Lenny à ce jour. J'ose imaginer que vous avez déjà postgresql-8.3 et la libc6 d'installée ;-)

heke:/usr/src# dpkg -i postgresql-8.3-plr_8.3.0.8-1_i386.deb
Selecting previously deselected package postgresql-8.3-plr.                             
(Reading database ... 42279 files and directories currently installed.)                 
Unpacking postgresql-8.3-plr (from postgresql-8.3-plr_8.3.0.8-1_i386.deb) ...           
Setting up postgresql-8.3-plr (1:8.3.0.8-1) ...

L'installation du paquet étant fait nous allons maintenant intégrer ce nouveau langage dans notre base et créer une première fonction d'aggrégat pour calculer la médiane. Pour cela nous allons avoir besoin de deux nouvelles fonctions que sont plr_array_accum et median, nous travaillerons en double precision. Vous pouvez télécharger le fichier de commande SQL plr.sql qui crée les fonctions pour tous les types numériques courants.

CREATE FUNCTION plr_array_accum (_float8, float8)
       RETURNS float8[]
       AS '$libdir/plr','plr_array_accum'
       LANGUAGE 'C';

CREATE FUNCTION r_median(_float8)
       RETURNS float
       AS 'median(arg1)'
       LANGUAGE 'plr';

CREATE AGGREGATE median (
       sfunc = plr_array_accum,
       basetype = float8,
       stype = _float8,
       finalfunc = r_median
);

Ceci étant fait nous allons maintenant créer une table avec un jeu de test.

CREATE TABLE foo (
    bar double precision
);   

et le jeu de test

INSERT INTO foo (bar) VALUES (5);
INSERT INTO foo (bar) VALUES (48);
INSERT INTO foo (bar) VALUES (110);
INSERT INTO foo (bar) VALUES (36);

Nous allons maintenant pouvoir calculer la médiane de bar aussi simplement que son maximum ou la somme en utilisant la nouvelle fonction d'agrégat median

test=# SELECT median(bar) from foo ;
 median
--------
     42
(1 row)

Un tel résultat ne peut-être qu'exact, non ?