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

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

[ <<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;

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.

-- dans mon_fichier.sql
CREATE OR REPLACE FUNCTION fonction_test(integer) 
        RETURNS integer AS $$
    [...]
$$ LANGUAGE plpgsql;

\i nom_fichier.sql

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.

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

• 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

• 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.