31.4. Fonctions en langage de requ�tes (SQL)

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 (gardez � l'esprit que <<�la premi�re ligne�>> d'un r�sultat multiligne n'est pas bien d�finie � moins d'utiliser ORDER BY). 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 untype. Dans ce cas, toutes les lignes de la derni�re requ�te sont renvoy�es. Des d�tails suppl�mentaires sont donn�s plus loin dans ce chapitre.

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. Un point-virgule apr�s la derni�re instruction est optionnel. Sauf si la fonction d�clare renvoyer void, la derni�re instruction doit �tre un SELECT.

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 (la seule exception est que vous ne pouvez pas placer de commandes BEGIN, COMMIT, ROLLBACK ou SAVEPOINT dans une fonction SQL). N�anmoins, la commande finale doit �tre un SELECT qui renvoie ce qui a �t� sp�cifi� comme type de retour de la fonction. Autrement, si vous voulez d�finir une fonction SQL qui r�alise des actions mais n'a pas de valeur utile � renvoyer, vous pouvez la d�finir comme renvoyant void. Dans ce cas, le corps de la fonction ne doit pas finir avec un SELECT. Par exemple, cette fonction supprime les lignes avec des salaires n�gatifs depuis la table emp :

  CREATE FUNCTION nettoie_emp() RETURNS void AS '
  DELETE FROM emp
  WHERE salaire < 0;
  ' LANGUAGE SQL;
  
  SELECT nettoie_emp();
  
  nettoie_emp
  -----------
  
  (1 row)

La syntaxe de la commande CREATE FUNCTION requiert que le corps de la fonction soit �crit comme une constante de type cha�ne. Il est habituellement plus agr�able d'utiliser les guillemets dollar (voir la Section 4.1.2.2) pour cette constante. Si vous choisissez d'utiliser la syntaxe habituelle avec des guillemets simples, vous devez �chapper les marques de guillemet simple (') et les antislashs (\) utilis�s dans le corps de la fonction, typiquement en les doublant (voir la Section 4.1.2.1).

Les arguments de la fonction SQL sont r�f�renc�s dans le corps de la fonction en utilisant la syntaxe suivante. $n:$1 se r�f�re au premier argument, $2 au second et ainsi de suite. Si un argument est de type composite, on utilisera la notation par point, par exemple $1.name, pour acc�der aux attributs de l'argument. Les arguments peuvent seulement �tre utilis�s comme valeurs des donn�es, pas comme des identifieurs. Du coup, par exemple, ceci est correct :

INSERT INTO matable VALUES ($1);

mais ceci ne fonctionnera pas :

INSERT INTO $1 VALUES (42);

31.4.1. Fonctions SQL sur les types de base

La fonction SQL la plus simple possible n'a pas d'argument et retourne un type de base tel que integer :

CREATE FUNCTION un() RETURNS integer AS $$
    SELECT 1 AS resultat;
$$ LANGUAGE SQL;

-- Autre syntaxe pour les cha�nes litt�rales :
CREATE FUNCTION un() RETURNS integer AS '
    SELECT 1 AS resultat;
' LANGUAGE SQL;

SELECT un();

 un
----
  1

Notez que nous avons d�fini un alias de colonne avec le nom resultat dans le corps de la fonction pour se r�f�rer au r�sultat de la fonction mais cet alias n'est pas visible hors de la fonction. En effet, le r�sultat est nomm� un au lieu de resultat.

Il est presque aussi facile de d�finir des fonctions SQL acceptant des types de base comme arguments. Dans l'exemple suivant, remarquez comment nous faisons r�f�rence aux arguments dans le corps de la fonction avec $1 et $2.

CREATE FUNCTION ajoute(integer, integer) RETURNS integer AS $$
    SELECT $1 + $2;
$$ LANGUAGE SQL;

SELECT ajoute(1, 2) AS reponse;

 reponse
---------
      3

Voici une fonction plus utile, qui pourrait �tre utilis�e pour d�biter un compte bancaire :

CREATE FUNCTION tf1 (integer, numeric) RETURNS integer AS $$
    UPDATE banque
        SET balance = balance - $2
        WHERE no_compte = $1;
    SELECT 1;
$$ LANGUAGE SQL;

Un utilisateur pourrait ex�cuter cette fonction pour d�biter le compte 17 de 100 000 euros ainsi :

SELECT tf1(17, 100.000);

Dans la pratique, on pr�f�rera vraisemblablement un r�sultat plus utile que la constante 1. Une d�finition plus probable est :

CREATE FUNCTION tf1 (integer, numeric) RETURNS numeric AS $$
    UPDATE banque
        SET balance = balance - $2
        WHERE no_compte = $1;
    SELECT balance FROM banque WHERE no_compte = $1;
$$ LANGUAGE SQL;

qui ajuste le solde et renvoie sa nouvelle valeur.

31.4.2. Fonctions SQL sur les types composites

Quand nous �crivons une fonction avec des arguments de type composite, nous devons non seulement sp�cifier l'argument utilis� (comme nous l'avons fait pr�c�demment avec $1 et $2), mais aussi sp�cifier l'attribut d�sir� de cet argument (champ). Par exemple, supposons que emp soit le nom d'une table contenant des donn�es sur les employ�s et donc �galement le nom du type composite correspondant � chaque ligne de la table. Voici une fonction double_salaire qui calcule ce que serait le salaire de quelqu'un s'il �tait doubl� :

CREATE TABLE emp (
    nom         text,
    salaire     numeric,
    age         integer,
    cubicle     point
);

CREATE FUNCTION double_salaire(emp) RETURNS numeric AS $$
    SELECT $1.salaire * 2 AS salaire;
$$ LANGUAGE SQL;

SELECT nom, double_salaire(emp.*) AS reve
    FROM emp
    WHERE emp.cubicle ~= point '(2,1)';

 name | reve
------+-------
 Bill |  8400

Notez l'utilisation de la syntaxe $1.salaire pour s�lectionner un champ dans la valeur de la ligne argument. Notez �galement comment la commande SELECT utilise * pour s�lectionner la ligne courante enti�re de la table comme une valeur composite (emp). La ligne de la table peut aussi �tre r�f�renc�e en utilisant seulement le nom de la table ainsi :

SELECT nom, double_salaire(emp) AS reve
    FROM emp
    WHERE emp.cubicle ~= point '(2,1)';

mais cette utilisation est obsol�te car elle est facilement obscure.

Quelque fois, il est pratique de construire une valeur d'argument composite en direct. Ceci peut se faire avec la construction ROW. Par exemple, nous pouvons ajuster les donn�es pass�es � la fonction :

SELECT nom, double_salaire(ROW(nom, salaire*1.1, age, cubicle)) AS reve
	FROM emp;

Il est aussi possible de construire une fonction qui renvoie un type composite. Voici un exemple de fonction renvoyant une seule ligne de type emp :

CREATE FUNCTION nouvel_emp() RETURNS emp AS $$
    SELECT text 'Aucun' AS nom,
        1000.0 AS salaire,
        25 AS age,
        point '(2,2)' AS cubicle;
$$ LANGUAGE SQL;

Dans cet exemple, nous avons sp�cifi� chacun des attributs avec une valeur constante, mais un quelconque calcul aurait pu �tre substitu� � ces valeurs.

Notez deux aspects importants � propos de la d�finition de fonction :

Un autre fa�on de d�finir la m�me fonction est :

        CREATE FUNCTION nouveau_emp() RETURNS emp AS $$
        SELECT ROW('Aucun', 1000.0, 25, '(2,2)')::emp;
        $$ LANGUAGE SQL;
      

Ici, nous �crivons un SELECT qui renvoie seulement une colonne du bon type composite. Ceci n'est pas vraiment meilleur dans cette situation mais c'est une alternative pratique dans certains cas — par exemple, si nous avons besoin de calculer le r�sultat en appelant une autre fonction qui renvoie la valeur composite d�sir�e.

Nous pourrions appeler cette fonction directement de deux fa�ons :

    SELECT nouveau_emp();
    
    nouveau_emp
    --------------------------
    (None,1000.0,25,"(2,2)")
    
    SELECT * FROM nouveau_emp();
    
    nom   | salaire | age | cubicle
    ------+---------+-----+---------
    Aucun |  1000.0 |  25 | (2,2)
  

La deuxi�me fa�on est d�crite plus compl�tement dans la Section 31.4.3.

Quand vous utilisez une fonction qui renvoie un type composite, vous pourriez vouloir seulement un champ (attribut) depuis ce r�sultat. Vous pouvez le faire avec cette syntaxe :

SELECT (nouveau_emp()).nom;

 nom
------
 None

Les parenth�ses suppl�mentaires sont n�cessaires pour �viter une erreur de l'analyseur. Si vous essayez de le faire sans, vous obtiendrez quelque chose comme ceci :

SELECT nouveau_emp().nom;
ERROR:  syntax error at or near "." at character 17
LINE 1: SELECT nouveau_emp().nom;
                            ^

Une autre option est d'utiliser la notation fonctionnelle pour extraire un attribut. Une mani�re simple d'expliquer cela est de dire que nous pouvons �changer les notations attribut(table) et table.attribut.

SELECT nom(nouveau_emp());

 name
------
 None

-- C'est la m�me chose que
-- SELECT emp.nom AS leplusjeune FROM emp WHERE emp.age < 30;

SELECT nom(emp) AS leplusjeune FROM emp WHERE age(emp) < 30;

 leplusjeune
-------------
 Sam
 Andy

Astuce�: L'�quivalence entre la notation fonctionnelle et la notation d'attribut rend possible l'utilisation de fonctions sur des types composites pour �muler les <<�champs calcul�s�>>. Par exemple, en utilisant la d�finition pr�c�dente pour double_salaire(emp), nous pouvons �crire

SELECT emp.nom, emp.double_salaire FROM emp;

Une application utilisant ceci n'aurait pas besoin d'�tre consciente directement que double_salaire n'est pas une vraie colonne de la table. (Vous pouvez aussi �muler les champs calcul�s avec des vues.)

Une autre fa�on d'utiliser une fonction renvoyant un r�sultat ligne est de passer le r�sultat � une autre fonction qui accepte le bon type ligne en entr�e :

CREATE FUNCTION recupnom(emp) RETURNS text AS $$
    SELECT $1.nom;
$$ LANGUAGE SQL;

SELECT recupnom(nouveau_emp());
 recupnom
----------
 Aucun
(1 row)

Une autre fa�on d'utiliser une fonction qui renvoie une type composite est de l'appeler comme une fonction de table (d�crite ci-dessous).

31.4.3. Fonctions SQL comme sources de table

Toutes les fonctions SQL peuvent �tre utilis�es dans la clause FROM d'une requ�te mais ceci est particuli�rement utile pour les fonctions renvoyant des types composite. Si la fonction est d�finie pour renvoyer un type de base, la fonction table produit une table d'une seule colonne. Si la fonction est d�finie pour renvoyer un type composite, la fonction table produit une colonne pour chaque attribut du type composite.

Voici un exemple :

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 foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT *, upper(fooname) FROM getfoo(1) AS t1;

 fooid | foosubid | fooname | upper
-------+----------+---------+-------
     1 |        1 | Joe     | JOE
(2 rows)

Comme le montre cet exemple, nous pouvons travailler avec les colonnes du r�sultat de la fonction comme s'il s'agissait des colonnes d'une table normale.

Notez que nous n'obtenons qu'une ligne comme r�sultat de la fonction. Ceci parce que nous n'avons pas utilis� l'instruction SETOF. Cette instruction est d�crite dans la prochaine section.

31.4.4. Fonctions SQL renvoyant un ensemble

Quand une fonction SQL est d�clar�e renvoyer un SETOF un_type, la requ�te finale SELECT 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.

Cette caract�ristique est normalement utilis�e lors de l'appel d'une fonction dans une clause FROM. Dans ce cas, chaque ligne renvoy�e par la fonction devient une ligne de la table vue par la requ�te. Par exemple, supposons que la table foo ait le m�me contenu que pr�c�demment et �crivons :

CREATE FUNCTION recupfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM recupfoo(1) AS t1;

Alors nous obtenons :

 fooid | foosousid | foonom
-------+-----------+--------
     1 |         1 | Joe
     1 |         2 | Ed
(2 rows)

Actuellement, les fonctions renvoyant des ensembles peuvent aussi �tre appel�es dans la liste du select d'une requ�te. Pour chaque ligne g�n�r�e par la requ�te, la fonction renvoyant un ensemble est appel�e et une ligne est g�n�r�e pour chaque �l�ment de l'ensemble r�sultat. Notez cependant que cette fonctionnalit� est d�conseill�e et pourra �tre supprim�e dans une version future. Voici un exemple de fonction renvoyant un ensemble � partir de la liste d'un SELECT :

CREATE FUNCTION listeenfant(text) RETURNS SETOF text AS $$
    SELECT nom FROM noeuds WHERE parent = $1
$$ LANGUAGE SQL;

SELECT * FROM noeuds;
   nom        | parent
--------------+--------
 Haut         |
 Enfant1      | Haut
 Enfant2      | Haut
 Enfant3      | Haut
 Sous-Enfant1 | Enfant1
 Sous-Enfant2 | Enfant1
(6 rows)

SELECT listeenfant('Haut');
 listeenfant
--------------
 Enfant1
 Enfant2
 Enfant3
(3 rows)

SELECT nom, listeenfant(nom) FROM noeuds;
  nom    | listeenfant
---------+--------------
 Haut    | Enfant1
 Haut    | Enfant2
 Haut    | Enfant3
 Enfant1 | Sous-Enfant1
 Enfant1 | Sous-Enfant2
(5 rows)

Notez, dans le dernier SELECT, qu'aucune ligne n'est renvoy�e pour Enfant2, Enfant3, etc. C'est parce que la fonction listeenfant renvoie un ensemble vide pour ces arguments et ainsi aucune ligne n'est g�n�r�e.

31.4.5. Fonctions SQL polymorphes

Les fonctions SQL peuvent �tre d�clar�es pour accepter et renvoyer les types <<�polymorphe�>> anyelement et anyarray. Voir la Section 31.2.1 pour une explication plus approfondie. Voici une fonction polymorphe cree_tableau qui construit un tableau � partir de deux �l�ments de type arbitraire :

CREATE FUNCTION cree_tableau(anyelement, anyelement) RETURNS anyarray AS $$
    SELECT ARRAY[$1, $2];
$$ LANGUAGE SQL;
SELECT cree_tableau(1, 2) AS tableau_entier, cree_tableau('a'::text, 'b') AS
tableau_texte;

 tableau_entier | tableau_texte
----------------+---------------
 {1,2}          | {a,b}
(1 row)

Notez l'utilisation du transtypage 'a'::text pour sp�cifier le type text de l'argument. Ceci est n�cessaire si l'argument est une cha�ne de caract�res car, autrement, il serait trait� comme un type unknown, et un tableau de type unknown n'est pas un type valide. Sans le transtypage, vous obtiendrez ce genre d'erreur :

ERROR:  could not determine ANYARRAY/ANYELEMENT type because input is UNKNOWN

Il est permis d'avoir des arguments polymorphes avec un type de renvoi fixe, mais non l'inverse. Par exemple :

CREATE FUNCTION est_plus_grand(anyelement, anyelement) RETURNS bool AS $$
    SELECT $1 > $2;
$$ LANGUAGE SQL;

SELECT est_plus_grand(1, 2);
 est_plus_grand
----------------
 f
(1 row)

CREATE FUNCTION fonction_invalide() RETURNS anyelement AS $$
    SELECT 1;
$$ LANGUAGE SQL;
ERROR:  cannot determine result datatype
DETAIL:  A function returning ANYARRAY or ANYELEMENT must have at least one
argument of either type.