Pl/Python et les curseurs en Pl/Pgsql pour PostgreSQL

1. Objectif

Le but de cet article est de familiariser le public avec certaines nouvelles techniques de programmation avec des langages procéduraux dans PostgreSQL 7.2. J'apprécie beaucoup les langages procéduraux et, par dessus tout, PostgreSQL rocks quand on en arrive aux procédures stockées. Vous pouvez créer de nombreux types de fonctions différents comme : SQL, Pl/PgSQL, Pl/Perl et Pl/Tcl. Depuis peu, dans PostgreSQL 7.2, vous avez aussi Pl/Perlu et Pl/Python — Perl et Python sans restriction. La capacité d'utiliser d'autres langages de programmation à l'intérieur d'une base de données facilite considérablement la vie d'un programmeur (même s'il y a une certaine inefficacité avec Perl et Python). Depuis quelques années, j'étais agacé par le fait que les langages procéduraux ne pouvaient renvoyer plus d'un élément. Ce problème a donc été pris en compte, mais de façon imparfaite. Un des derniers points pour me faciliter la vie considérablement est de faire renvoyer aux langages procéduraux plus d'un élément. Nous verrons que nous pouvons en faire le tri, mais j'ai des suggestions pour l'améliorer.

J'utilise Red Hat 7.2 comme base pour cet article. Les choses seront différentes avec votre version de Linux.

2. Compiler PostgreSQL

Il y a trois points que vous devez savoir quand vous compilez PostgreSQL :

Vous pouvez également ajouter odbc, tcl et d'autres langages, mais je m'en tiens à Perl et Python à titre d'exemples pour le moment. Après avoir exécuté make et make install, suivez ces commandes en tant qu'utilisateur postgres.

3. Exécuter Pl/Python et Pl/PgSQL avec des curseurs

Voici une liste de commandes que vous pouvez exécuter à l'aide de la commande psql template1. Nous partons du principe que la base de données postgres a été créée.

Exécutez ces commandes :

\c postgres

drop table contact;
create table contact (
first text, last text, phone text, address text,
city text,state text, zip text
);

drop function replace_e_to_a(text);
CREATE FUNCTION replace_e_to_a(text) RETURNS text AS
'
import re
Text1 = re.sub(''e'', ''a'',args[0])
return Text1
'
LANGUAGE 'plpython';

drop function replace_numbers_to_z(text);
CREATE FUNCTION replace_numbers_to_z(text) RETURNS text AS
'
import re
Text1 = re.sub(''[0-9]'', ''z'',args[0])
return Text1
'
LANGUAGE 'plpython';

insert into contact values ('Mark','Nielsen','123-123-1234',
  '1234 Somewhere St.', 'Some City 123', 'TX','12345-1234');
insert into contact values ('Mark','Nielsen2','123-123-1234',
  '3456 Somewhere St.', 'Some City 444', 'MI','12345-1234');
insert into contact values ('Mark','Nielsen3','123-123-1234',
  '5678 Somewhere St.', 'Some City 654', 'AX','12345-1234');

select first, last, address  from contact;

drop function select_contact();
CREATE FUNCTION select_contact () RETURNS refcursor AS '
DECLARE
    cursor1 CURSOR FOR select replace_e_to_a(first)as first, 
                        replace_e_to_a(last) as last,
                        replace_numbers_to_z(address) as address
            from contact;
BEGIN
 open cursor1;
 return (cursor1);
END;
' LANGUAGE 'plpgsql';

begin;
select select_contact();
FETCH cursor1; FETCH cursor1;FETCH cursor1;
end;

et voici à quoi la sortie devrait ressembler :

DROP
CREATE
DROP
CREATE
DROP
CREATE
INSERT 255188 1
INSERT 255189 1
INSERT 255190 1
 first |   last   |      address       
-------+----------+--------------------
 Mark  | Nielsen  | 1234 Somewhere St.
 Mark  | Nielsen2 | 3456 Somewhere St.
 Mark  | Nielsen3 | 5678 Somewhere St.
(3 rows)

DROP
CREATE
BEGIN
 select_contact 
----------------
 cursor1
(1 row)

 first |  last   |      address       
-------+---------+--------------------
 Mark  | Nialsan | zzzz Somewhere St.
(1 row)

 first |   last   |      address       
-------+----------+--------------------
 Mark  | Nialsan2 | zzzz Somewhere St.
(1 row)

 first |   last   |      address       
-------+----------+--------------------
 Mark  | Nialsan3 | zzzz Somewhere St.
(1 row)

COMMIT

À partir de cet exemple, vous pouvez voir comment Pl/pgSQL exécute les procédures Python (les procédures Pl/pgSQL). Il n'est pas nécessaire que Pl/pgSQL exécute des procédures Pl/Python, je l'ai juste fait de cette manière. N'utilisez Perl et Python que pour manipuler les données, mais non pour changer les données dans les tables. Perl et Python ne font qu'entrer et sortir des données, ils n'influent pas sur la base de données directement. de plus, pl/perlu est installé quand vous compilez Perl dans PostgreSQL, ce qui est très sympathique.

4. Suggestions pour le futur

J'ai encore trois grandes requêtes/suggestions :

1. Les procédures semblent être capables de ne renvoyer qu'un « élémént ». Elles ne peuvent en renvoyer deux. Pour ma part, j'aimerais être en mesure de renvoyer au moins deux éléments ‐ un curseur et un drapeau indiquant si le curseur est bon ou non. Les curseurs NE FONT PAS renvoyer aux procédures plus d'une valeur, elles renvoient le curseur que vous pouvez considérer comme étant une référence à de nombreux éléments, non les éléments eux-mêmes. La manière dont je compile mes procédures est qu'elles peuvent admettre jusqu'à 512 variables d'entrée, mais elles ne peuvent renvoyer qu'un seul « élémént ». Je suis sidéré de la raison à cela. J'ai essayé de suivre les discussions sur les listes de diffusion et les documentations, mais je m'y perds.

2. Il ne semble pas y avoir énormément de choses que vous puissiez faire avec les curseurs, comme vérifier combien il y a d'entrées ou s'il en reste, s'il y a même une entrée, si elle s'est exécutée correctement, etc. Les curseurs doivent être un peu plus avancés. Peut-être n'ai-je simplement pas assez d'expérience avec les curseurs pour les commenter.

3. Les procédures ne peuvent renvoyer des lignes de données. En dehors du fait qu'elles ne peuvent revoyer qu'un élément, elles ne peuvent renvoyer des lignes d'un seul élément ou des lignes qui contiennent des éléments multiples. Être capable de renvoyer plusieurs lignes avec plusieurs champs devrait être sympathique. Même si nous ne voulons renvoyer qu'une ligne de données, il devrait être agréable de renvoyer un curseur à raison d'un seul élément, puis son état (bon, mauvais, combien, etc.) également. Si vous pouvez renvoyer plus d'un élément, vous pourriez aussi bien le rendre illimité ou limité par un nombre à la compilation (compile-time number) (comme le sont les entrées).

J'aime la progression des langages procéduraux dans PostgreSQL. Ce me semble être la voie à suivre de beaucoup des autres serveurs de bases de données libres, voire même de la plupart des commerciaux. En tenir compte à l'étape suivante serait vraiment un bon coup de pied à l'étrier des serveurs de base de données. J'essaie d'aider en rédigeant des articles, peut-être pouvez-vous essayer d'aider aussi en ajoutant des fonctionnalités ?

Ce n'est pas véritablement une requête à propos des curseurs, mais de DBD:Pg pour Perl et éventuellement, d'autres interfaces à PostgreSQL — les curseurs ne sont pas pris en charge en fait, pour autant que je puisse l'affirmer. Donc, si Pl/PgSQL pouvait renvoyer plusieurs lignes de plusieurs éléments, cela ce problème serait pris en considération.

Le seul autre moyen que je connais pour stocker des données à partir d'une procédure est dans une table temporaire accessible une fois la procédure terminée. Pour moi, c'est un peu extrême pour 99 % des données que je veux obtenir. Extrême parce que d'ordinaire, je ne veux qu'une ligne de données et que créer une table juste pour une ligne de données ne vaut pas la peine.

5. Conclusions

Pl/Python me fera finalement renoncer à Perl une fois pour toutes (je me suis converti à Python). Pl/PgSQL a des qualités qui me facilitent la programmation et la création de procédures compliquées — je souhaite juste qu'il puisse réellement renvoyer plusieurs éléments et non simplement une référence à un curseur ou à tout autre élément unique.

L'inconvénient est que ma version de DBD::Pg pour Perl et mon interface Python ne prennent pas en charge les curseurs, ce qui m'est donc inutile le plus souvent, bien que les choses s'améliorent cependant. J'ai trouvé quelques pistes sur http://developer.postgresql.org/todo.php qui semblent prometteuses. Du fait que les curseurs ne sont pas du tout pris en charge dans les langages de programmation que j'utilise, si je dois vraiment stocker de grandes quantités de données, je devrai probablement avoir recours à des tables temporaires. Je ne comprends toujours pas pourquoi un langage procédural ne peut pas renvoyer des données, comme vous pouvez le faire dans une commande sqlnormale. Quelle que soit la limitation, il serait bon de la surmonter.

6. Références

1. Un article plus ancien : Mark's Web/Database Installation Zope, Apache, Mysql, PostgreSQL, Perl, Python, PHP.

2. Les articles de Branden Williams sur PostgreSQL.

3. http://techdocs.postgresql.org/oresources.php.

4. http://techdocs.postgresql.org/.

Voici d'autres liens, qui n'ont rien à voir avec cet article, mais que je garde sous la main pour d'autres dans le futur.

• http://authpg.sourceforge.net/.

• http://www.digitalstratum.com/pglogd/.

• http://www.giuseppetanzilli.it/mod_auth_pgsql/.

Mark travaille chez AudioBoomerang.com [1], qui crée, distribue et suit des campagnes multimédia personnalisées sur le courrier électronique, le Web et les lettres d'information. Consultant, il distribue les produits finaux aux clients de AudioBoomerang.com, tels que des rapports statistiques personnalisés avancé utilisés pour les profils démographiques ou psychologiques pour des campagnes futures. Pendant ses loisirs, il écrit des articles sur le Logiciel Libre (GPL) ou la documentation du Libre (FDL) et il est impliqué dans le centre d'apprentissage à but non lucratif eastmont.net.