Fonctions PL/pgSQL, triggers
Objectifs
Beaucoup de requêtes SQL sont destinées à être exécutées plusieurs fois, avec des arguments différents. Exemple :
SELECT post_id,post_content,post_date,post_by,user_name
FROM posts
JOIN users
ON user_id = post_by
WHERE post_topic = ${topic_id}
ORDER BY post_date DESC
LIMIT ${MAX_NB_POSTS}
OFFSET ${offset};
Seulement trois variables, donc on aimerait remplacer par
get_posts(${topic_id}, ${MAX_NB_POSTS}, ${offset});
C'est l'objet de la première partie.
Dans un second temps, nous verrons comment rendre des requêtes SQL plus intuitives et plus simples via une modélisation objet.
Les fonctions dans PostGreSQL
Introduction
La définition de fonctions utilisateur dans PostGreSQL permet d'ajouter du dynamisme aux tables. On distingue deux types de fonctions :
- les procédures stockées (stored procedure), que l'on peut appeler à tout moment et qui utilisent les données de la table ;
- les déclencheurs (triggers) associés à une opération précise sur la table, et qui sont déclenchés à chaque fois que celle-ci a lieu.
Ces fonctions viennent compléter le schéma d'une entité :
Motivations
Jusqu'à présent : l'application client doit envoyer chaque requête au serveur de bases de données, attendre que celui-ci la traite, recevoir et traiter les résultats, et recommencer etc.
£\Rightarrow£ Beaucoup de communications interprocessus.
PL/pgSQL permet d'exécuter de traiter les résultats directement côté serveur (comme vous l'auriez fait localement).
£\Rightarrow£ Inutile de récupérer les résultats intermédiaires.
£\Rightarrow£ Diminution du nombre de communications.
Fonctions SQL
Syntaxe générale
CREATE [ OR REPLACE ] FUNCTION
name ( [ [ argmode ] [ argname ]
argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
[ RETURNS rettype
| RETURNS TABLE ( column_name column_type [, ...] ) ]
{ LANGUAGE lang_name
| WINDOW
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [ EXTERNAL ] SECURITY INVOKER
| [ EXTERNAL ] SECURITY DEFINER
| COST execution_cost
| ROWS result_rows
| SET configuration_parameter
{ TO value | = value | FROM CURRENT }
| AS 'definition' -- (presque) tout se passera dans ce bloc
| AS 'obj_file', 'link_symbol'
} ...
[ WITH ( attribute [, ...] ) ]
Voir l'aide en ligne.
Définition - part 1
Fonction SQL
- Suite d'instructions SQL, sans éléments du langage PL/pgSQL (pas de IF THEN ELSE, pas de boucles ...etc).
- À privilégier (performances) lorsqu'elles se révèlent suffisantes.
Les fonctions SQL exécutent une liste arbitraire d'instructions SQL et renvoient le résultat de la dernière requête de cette liste. Dans le cas d'un résultat simple (pas d'ensemble), la première ligne du résultat de la dernière requête sera renvoyée.
Si la dernière requête de la liste ne renvoie aucune ligne, la valeur NULL est renvoyée.
Une fonction SQL peut être déclarée de façon à renvoyer un ensemble (set) en spécifiant le type renvoyé par la fonction comme SETOF un_type, ou de façon équivalente en la déclarant comme RETURNS TABLE(colonnes). Dans ce cas, toutes les lignes de la dernière requête sont renvoyées.
Définition - part 2
Le corps d'une fonction SQL doit être constitué d'une liste d'une ou de plusieurs instructions SQL séparées par des points-virgule. Sauf si la fonction déclare renvoyer void, la dernière instruction doit être un SELECT ou un INSERT | UPDATE | DELETE ayant une clause RETURNING.
Toute collection de commandes dans le langage SQL peut être assemblée et définie comme une fonction. En plus des requêtes SELECT, les commandes peuvent inclure des requêtes de modification des données (INSERT, UPDATE et DELETE) ainsi que d'autres commandes SQL.
(sans toutefois pouvoir utiliser les commandes de contrôle de transaction, telles que COMMIT, SAVEPOINT, et certaines commandes utilitaires, comme VACUUM, dans les fonctions SQL).
Pour tous les détails, voir la doc.
Premiers exemples
CREATE FUNCTION ajoute(integer, integer) RETURNS integer AS $$
SELECT $1 + $2;
$$ LANGUAGE SQL;
SELECT ajoute(1, 2) AS reponse;
reponse
---------
3
CREATE FUNCTION debit_compte(int, real) RETURNS real AS $$
UPDATE banque
SET solde = solde - $2
WHERE no_compte = $1;
SELECT solde FROM banque WHERE no_compte = $1;
$$ LANGUAGE SQL;
SELECT * FROM banque;
no_compte | solde
-----------+-------
1 | 50.0
SELECT debit_compte(1, 32.0);
debit_compte
--------------
18.0
Arguments OUT
Les arguments "OUT" prennent les valeurs retournées par la fonction.
CREATE FUNCTION ajoute_n_produit (IN x int, IN y int,
OUT sum int, OUT product int) AS $$
SELECT $1 + $2, $1 * $2
$$ LANGUAGE SQL;
SELECT * FROM sum_n_product(11,42);
sum | product
-----+---------
53 | 462
Cela crée un type anonyme, et est équivalent à :
CREATE TYPE produit_ajoute AS (somme int, produit int);
CREATE FUNCTION ajoute_n_produit (int, int)
RETURNS produit_ajoute AS '
SELECT $1 + $2, $1 * $2
' LANGUAGE SQL;
Les types utilisateur
-- Type composite : construit sur des types existants
CREATE TYPE name AS ( [ attribute_name
data_type [ COLLATE collation ] [, ... ]
] )
-- Type enumere : par exemple {rouge, bleu, vert}
CREATE TYPE name AS ENUM
( [ 'label' [, ... ] ] )
CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');
CREATE TABLE bug (
id serial,
description text,
status bug_status
);
Remarque : quand une table est créée un type composite du même nom est automatiquement enregistré (et utilisable).
Retourner un ensemble
Quand une fonction SQL est déclarée renvoyer un SETOF un_type, la requête finale de la fonction est complètement exécutée et chaque ligne extraite est renvoyée en tant qu'élément de l'ensemble résultat.
CREATE TABLE foo (fooid int, foosousid int, foonom text);
INSERT INTO foo VALUES (1, 1, 'Joe');
INSERT INTO foo VALUES (1, 2, 'Ed');
INSERT INTO foo VALUES (2, 1, 'Mary');
CREATE FUNCTION recupfoo(int) RETURNS SETOF foo AS $$
SELECT *
FROM foo
WHERE fooid = $1;
$$ LANGUAGE SQL;
SELECT * FROM recupfoo(1);
fooid | foosousid | foonom
-------+-----------+--------
1 | 1 | Joe
1 | 2 | Ed
Fonctions PL/pgSQL
Introduction
Structure (du corps) d'une fonction PL/pgSQL
[ <<label>> ]
[ DECLARE
declarations ]
BEGIN
instructions
END [ label ];
Un label sert à référencer un bloc, et n'est utile que lorsque l'on se retrouve dans un sous-(sous-...)bloc et que l'on veut sortir d'un bloc supérieur, ou y continuer l'exécution, ou utiliser ses variables.
[ <<label>> ]
LOOP
instructions
END LOOP [ label ];
Référencer un bloc externe
CREATE FUNCTION somefunc() RETURNS integer AS $$
<< outerblock >>
DECLARE
quantity integer := 30;
BEGIN
RAISE NOTICE 'Quantity here is %', quantity; -- 30
quantity := 50;
--
-- Create a subblock
--
DECLARE
quantity integer := 80;
BEGIN
RAISE NOTICE 'Quantity here is %', quantity; -- 80
RAISE NOTICE 'Outer quantity here is %',
outerblock.quantity; -- 50
END;
RAISE NOTICE 'Quantity here is %', quantity; -- 50
RETURN quantity;
END;
$$ LANGUAGE plpgsql;
Référencer une boucle externe
CREATE FUNCTION somefunc() RETURNS void AS $$
DECLARE
i int := 0;
j int := 2;
BEGIN
<< boucle >>
LOOP
i := i+2;
LOOP
IF i <> j THEN CONTINUE boucle;
ELSIF i+j > 5 THEN EXIT; -- ou EXIT WHEN i+j > 5;
END IF;
j := j+1;
RAISE NOTICE 'inner loop %, %', i, j;
END LOOP;
IF i^3 < 0 THEN RETURN; END IF;
RAISE NOTICE 'outer loop %, %', i, j;
END LOOP;
END;
$$ LANGUAGE plpgsql;
... boucle infinie (affiche i2,3 puis plus rien, car i != j)
Déclarations de variables
-- Syntaxe
nom [ CONSTANT ] type [ COLLATE nom_collationnement ]
[ NOT NULL ] [ { DEFAULT | := } expression ];
-- Exemples
quantite CONSTANT integer := 32;
url varchar := 'http://mysite.com';
id_utilisateur quantite%type := 10; -- type de quantite
ma_ligne nom_table%ROWTYPE; -- type d'une rangee dans ma_table
mon_champ nom_table.nom_colonne%TYPE;
une_ligne RECORD; -- type pour une rangee de table quelconque
-- Exemple d'utilisation
CREATE OR REPLACE FUNCTION foo(varchar, OUT b boolean,
a int DEFAULT 32) AS $$
DECLARE
v_string ALIAS FOR $1;
c integer := a % 7;
BEGIN
b := FALSE;
IF v_string LIKE '%.txt' THEN b := TRUE; END IF;
END;
$$ LANGUAGE plpgsql;
Requêtes SQL
Pour toute commande SQL qui ne renvoie pas de lignes, par exemple INSERT sans clause RETURNING, vous pouvez exécuter normalement la commande à l'intérieur d'une fonction PL/pgSQL.
Pour évaluer une expression ou une requête SELECT sans récupérer le résultat il faut utiliser l'instruction PERFORM :
CREATE FUNCTION foo() RETURNS void AS $$
BEGIN
PERFORM *
FROM produits p
WHERE p.id_produit = 42;
IF NOT FOUND THEN RAISE NOTICE 'Pas trouve...'; END IF;
END;
$$ LANGUAGE plpgsql;
Récupérer un résultat
Exécuter une requête avec une seule ligne de résultats.
SELECT expressions_select INTO [STRICT] cible FROM ...;
INSERT ... RETURNING expressions INTO [STRICT] cible;
UPDATE ... RETURNING expressions INTO [STRICT] cible;
DELETE ... RETURNING expressions INTO [STRICT] cible;
-- Exemple (dans le corps d'une fonction PL/pgSQL...)
SELECT * FROM emp WHERE nom = un_nom INTO rec;
RAISE NOTICE 'prenom-nom = % %', rec.prenom, un_nom ;
Note : différent du SELECT INTO qui crée une nouvelle table.
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ [ AS ] output_name ] [, ...]
INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ ...etc ]
Exécuter des commandes dynamiques
Si une requête est appelée avec des arguments variant beaucoup, l'optimiseur de requêtes est inutile. Dans ce cas on peut utiliser :
EXECUTE command-string [ INTO [STRICT] target ]
[ USING expression [, ...] ];
EXECUTE 'SELECT count(*)
FROM matable
WHERE insere_par = $1 AND insere <= $2'
INTO c
USING utilisateur_verifie, date_verifiee;
EXECUTE 'SELECT count(*) FROM '
|| tabname::regclass
|| ' WHERE insere_par = $1 AND insere <= $2'
INTO c
USING utilisateur_verifie, date_verifiee;
RETURN NEXT
Utile pour retourner plusieurs lignes (un SETOF mon_type).
CREATE TABLE truc (id_truc INT, sousid_truc INT, nom_truc TEXT);
INSERT INTO truc VALUES (1, 2, 'trois');
INSERT INTO truc VALUES (4, 5, 'six');
CREATE FUNCTION obtenirTousLesTrucs() RETURNS SETOF truc AS $$
DECLARE
r truc%rowtype;
BEGIN
FOR r IN SELECT * FROM truc
WHERE id_truc > 0
LOOP
-- quelques traitements
RETURN NEXT r; -- renvoie la ligne courante du SELECT
END LOOP;
RETURN;
END $$ LANGUAGE plpgsql;
SELECT * FROM obtenirTousLesTrucs();
id_truc | sousid_truc | nom_truc
---------+-------------+----------
1 | 2 | trois
4 | 5 | six
CASE ... WHEN ...
Équivalent du switch (PHP, MATLAB, R ...)
CASE expression_recherche
WHEN expression [, expression [ ... ]] THEN
instructions
[ WHEN expression [, expression [ ... ]] THEN
instructions
... ]
[ ELSE
instructions ]
END CASE;
CASE x
WHEN 1, 2 THEN
msg := 'un ou deux';
ELSE
msg := 'autre valeur que un ou deux';
END CASE;
Boucle sur (les rangées d') une table
[<<label>>]
FOR cible IN requete LOOP
instructions
END LOOP [ label ];
CREATE FUNCTION fusionne() RETURNS SETOF table1 AS $$
DECLARE
ligne RECORD;
BEGIN
FOR ligne IN SELECT * FROM table1 ORDER BY id
LOOP
-- ligne contient un enregistrement de table1
EXECUTE 'UPDATE table2 SET champ_texte = champ_texte || '
|| quote_ident(ligne.champ_texte)
|| ' WHERE table2.id = ' table1.id;
IF table1.id < 10 THEN RETURN NEXT ligne; END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;
Exemple - Question sur stackoverflow
I have a database with columns looking like:
session | order | atype | amt
--------+-------+-------+-----
1 | 0 | ADD | 10
1 | 1 | ADD | 20
1 | 2 | SET | 35
1 | 3 | ADD | 10
2 | 0 | SET | 30
2 | 1 | ADD | 20
2 | 2 | SET | 55
It represents actions happening. Each session starts at 0. ADD adds an amount, while SET sets it. I want a function to return the end value of a session, e.g.
SELECT session_val(1); --returns 45
SELECT session_val(2); --returns 55
Is it possible to write such a function/query?
Fonctions de fenêtrage
Principe : appliquer fonction d'agrégat (MIN, MAX, AVG, ...), mais faire apparaître toutes les lignes dans le résultat.
-- Syntaxe
function_name ([expression [, expression ... ]])
OVER window_name
function_name ([expression [, expression ... ]])
OVER ( window_definition )
function_name ( * ) OVER window_name
function_name ( * ) OVER ( window_definition )
-- avec pour window_definition :
[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ]
[ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]
-- Exemple
SELECT sum(salaire) OVER w, avg(salaire) OVER w
FROM salaireemp
WINDOW w AS (PARTITION BY nomdep ORDER BY salaire DESC);
Fonctions de fenêtrage - exemple 1
Moyennes des salaires par catégories professionnelles.
SELECT nomdep, noemp, salaire,
avg(salaire) OVER (PARTITION BY nomdep)
FROM salaireemp;
nomdep | noemp | salaire | avg
-----------+-------+---------+-----------------------
develop | 11 | 5200 | 5020.0000000000000000
develop | 7 | 4200 | 5020.0000000000000000
develop | 9 | 4500 | 5020.0000000000000000
develop | 8 | 6000 | 5020.0000000000000000
develop | 10 | 5200 | 5020.0000000000000000
personnel | 5 | 3500 | 3700.0000000000000000
personnel | 2 | 3900 | 3700.0000000000000000
ventes | 3 | 4800 | 4866.6666666666666667
ventes | 1 | 5000 | 4866.6666666666666667
ventes | 4 | 4800 | 4866.6666666666666667
Fonctions de fenêtrage - exemple 2
Somme cumulée des salaires rangés par ordre croissant.
SELECT salaire, sum(salaire)
OVER (ORDER BY salaire)
FROM salaireemp;
salaire| sum
--------+-------
3500 | 3500
3900 | 7400
4200 | 11600
4500 | 16100
4800 | 25700
4800 | 25700
5000 | 30700
5200 | 41100
5200 | 41100
6000 | 47100
Réponse 1 : SQL
CREATE FUNCTION getEndVal(session INT) RETURNS BIGINT AS $$
SELECT SUM(amt) AS session_val
FROM (
SELECT segment,
MAX(segment) OVER() AS max_segment,
amt
FROM (
SELECT SUM(CASE WHEN atype = 'SET' THEN 1 ELSE 0 END)
OVER(ORDER BY "order") AS segment,
amt
FROM command
WHERE session = getEndVal.session -- ou $1
) x
) x
WHERE segment = max_segment;
$$ language SQL;
select getEndValue(1) AS v1,getEndValue(2) AS v2;
v1 | v2
----+----
45 | 55
Décryptage
SELECT SUM(CASE WHEN atype = 'SET' THEN 1 ELSE 0 END)
OVER(ORDER BY "order") AS segment,
amt
FROM command WHERE session = 1;
segment | amt
---------+-----
0 | 10
0 | 20
1 | 35
1 | 10
SELECT segment,
MAX(segment) OVER() AS max_segment,
amt
FROM << resultat de la requete precedente >>
segment | max_segment | amt
---------+-------------+-----
0 | 1 | 10
0 | 1 | 20
1 | 1 | 35
1 | 1 | 10
Réponse 2 : PL/pgSQL
CREATE FUNCTION getEndVal(session INT) RETURNS BIGINT AS $$
DECLARE
value BIGINT := 0;
rec command%ROWTYPE;
BEGIN
FOR rec IN
SELECT *
FROM command
WHERE command.session = getEndVal.session
LOOP
IF rec.atype = 'SET' THEN
value := rec.amt;
ELSIF rec.atype = 'ADD' THEN
value := value + rec.amt;
END IF;
END LOOP;
RETURN value;
END $$ language plpgsql;
... Beaucoup plus lisible !
Les triggers
Syntaxe (simplifiée, usuelle)
CREATE FUNCTION nom_fonction() RETURNS trigger AS $$
<< corps de la fonction >>
$$ language plpgsql;
- Pas d'arguments : ils sont passés par un autre mécanisme.
- Le type de retour est toujours trigger.
CREATE TRIGGER name { BEFORE | AFTER | INSTEAD OF }
{ event [ OR ... ] }
ON table
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE PROCEDURE function_name ( arguments )
-- where event can be one of:
INSERT
UPDATE [ OF column_name [, ... ] ]
DELETE
TRUNCATE
CREATE TABLE emp (
nom_employe text,
salaire integer,
date_dermodif timestamp,
utilisateur_dermodif text
);
CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
IF NEW.nom_employe IS NULL THEN
RAISE EXCEPTION 'nom_employe ne peut pas etre NULL';
END IF;
IF NEW.salaire IS NULL THEN
RAISE EXCEPTION '% doit avoir un salaire', NEW.nom_employe;
END IF;
IF NEW.salaire < 0 THEN
RAISE EXCEPTION 'Le salaire ne peut pas etre negatif';
END IF;
NEW.date_dermodif := current_timestamp;
NEW.utilisateur_dermodif := current_user;
RETURN NEW;
END;
$emp_stamp$ LANGUAGE plpgsql;
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
"Variables trigger"
Plusieurs variables sont accessibles dans une fonction trigger.
- NEW (RECORD) : la ligne modifiée ou insérée.
- OLD (RECORD) : la ligne avant modification/suppression.
- TG_OP (VARCHAR) : indique pour quelle opération le trigger a été lancé, 'INSERT', 'UPDATE', 'DELETE' ou 'TRUNCATE'.
- TG_ARGV[] (TEXT ARRAY) : les arguments de l'instruction CREATE TRIGGER. L'index débute à 0.
-- Exemple d'utilisation
CREATE FUNCTION ins_function() RETURNS trigger AS $$
BEGIN
IF tg_op = 'DELETE' THEN
INSERT INTO backup_tbl(empid, empname, salary, operation)
VALUES (old.empid, old.empname, old.salary, tg_op);
RETURN old;
ELSIF tg_op = 'UPDATE' THEN
INSERT INTO backup_tbl(empid, empname, salary, operation)
VALUES (old.empid, old.empname, old.salary, tg_op);
RETURN new;
END IF;
END $$ LANGUAGE plpgsql;
Conseils PL/pgSQL (cf. paragraphe dans la doc)
Utiliser l'éditeur de texte de votre choix pour créer les fonctions, et psql dans une autre fenêtre pour charger et tester ces fonctions.
Écrivez les fonctions en utilisant CREATE OR REPLACE : ainsi il suffit de recharger le fichier pour mettre à jour la fonction.
Par exemple :-- dans mon_fichier.sql
CREATE OR REPLACE FUNCTION fonction_test(integer)
RETURNS integer AS $$
[...]
$$ LANGUAGE plpgsql;
\i nom_fichier.sql
Puis soumettre des commandes SQL pour tester la fonction (éventuellement aussi via un fichier).
Remarques
Encapsuler toutes les opérations effectuées au sein du SGBD dans des fonctions permet à une application cliente d'utiliser la base sans en connaître ses mécanismes internes.
- SGBD serveur : postgresql (sur 192.168.31.236) ;
- application client : psql, ou des scripts PHP (via l'interface web)
INSERT INTO users (...,...)
VALUES($name,$password,$email,NOW(),...) RETURNING user_id;
-- deviendrait
insertUserGetId($name,$password,$email,NOW(),...);
UPDATE posts SET post_content = ...
WHERE post_id = $post_id RETURNING post_topic;
-- deviendrait
updatePostGetTopic($postId,...);
Alternatives à PL/pgSQL
PL/Tcl
- Langage à typage dynamique, multi-plateformes, extensible, facile à apprendre.
- Extension Tk permettant de développer rapidement des interfaces graphiques.
- Tk s'interface avec divers langages dont Perl, Python, Ruby, Lua, Common Lisp, Ada, Haskell, Prolog, OCaml, R.
CREATE FUNCTION tcl_max(integer, integer) RETURNS integer AS $$
if {[argisnull 1]} {
if {[argisnull 2]} { return_null }
return $2
}
if {[argisnull 2]} { return $1 }
if {$1 > $2} {return $1}
return $2
$$ LANGUAGE pltcl;
PL/Perl
Langage interprété, polyvalent et adapté au traitement et à la manipulation de fichiers texte, notamment du fait de l'intégration des expressions régulières dans la syntaxe même du langage.
CREATE TABLE test (
i int,
v varchar
);
CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$
if (($_TD->{new}{i} >= 100) || ($_TD->{new}{i} <= 0)) {
return "SKIP"; # passe la commande INSERT/UPDATE
} elsif ($_TD->{new}{v} ne "immortal") {
$_TD->{new}{v} .= "(modified by trigger)";
return "MODIFY"; # modifie la ligne et la renvoit
} else {
return; # execute la commande INSERT/UPDATE
}
$$ LANGUAGE plperl;
CREATE TRIGGER test_valid_id_trig
BEFORE INSERT OR UPDATE ON test
FOR EACH ROW EXECUTE PROCEDURE valid_id();
PL/Python
[Wikipedia] Python est un langage de programmation objet, multi-paradigme et multi-plateformes. Il favorise la programmation impérative structurée et orientée objet. Il est doté d'un typage dynamique fort, d'une gestion automatique de la mémoire par ramasse-miettes et d'un système de gestion d'exceptions.
CREATE FUNCTION insere_fraction(numerateur int,
denominateur int) RETURNS text AS $$
from plpy import spiexceptions
try:
plan = plpy.prepare("INSERT INTO fractions (frac)
VALUES ($1 / $2)", ["int", "int"])
plpy.execute(plan, [numerateur, denominateur])
except spiexceptions.DivisionByZero:
return "denominateur doit etre different de zero"
except spiexceptions.UniqueViolation:
return "a deja cette fraction"
except plpy.SPIError, e:
return "autre erreur, SQLSTATE %s" % e.sqlstate
else:
return "fraction inseree"
$$ LANGUAGE plpythonu;
D'autres langages sont utilisables
Par exemple :- java
- Lua
- R
- shell (bash, csh, etc.)
- scheme
Les extensions correspondantes sont téléchargeables ici.
Il existe également des librairies facilitant l'écriture de tests unitaires pour PL/pgSQL : pgtap (+ google...).
... Et en C, aussi
#include "postgres.h"
#include "executor/spi.h"
#include "utils/builtins.h"
int execq(text *sql, int cnt) {
// Convertit l'objet texte PostGreSQL en chaine C
char* command = text_to_cstring(sql);
SPI_connect();
int ret = SPI_exec(command, cnt);
int proc = SPI_processed; // nombre de lignes retournees
// ...etc, SPI_tuptable contient la table resultat
SPI_finish();
pfree(command);
return proc;
}
Puis compiler en une shared library 'mylib.x', puis :
CREATE FUNCTION execq(text, integer) RETURNS integer
AS 'mylib.x','execq'
language C;
Bilan
Les fonctions dans PostgreSQL- permettent d'effectuer plusieurs requêtes SQL en traitant leurs résultats intermédiaires, directement sur le serveur ;
- permettent d'automatiser côté serveur des opérations que l'on aurait dû faire côté client (exemple : déclencheur qui empêche la mise à jour d'un pseudo) ;
- peuvent s'écrire dans différent langages.
... À suivre : faciliter l'utilisation d'une base de données relationnelle par une application client orientée objet.
/