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

33.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;

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.

Une quelconque collection de commandes en langage SQL peut �tre rassembl�e et d�finie comme une fonction. � c�t� des requ�tes SELECT, les commandes peuvent inclure la modification de donn�es (c'est-�-dire INSERT, UPDATE, et DELETE). Toutefois, la commande finale doit �tre un SELECT qui renvoie un r�sultat du type sp�cifi� par la fonction. Une autre solution, si vous voulez d�finir une fonction qui r�alise une action mais n'a pas de valeur utile � renvoyer, est de la d�finir pour renvoyer un type void. Dans ce cas, le corps de la fonction ne doit pas finir par un SELECT. Par exemple :

CREATE FUNCTION nettoie() RETURNS void AS '
    DELETE FROM emp
        WHERE salaire <= 0;
' LANGUAGE SQL;

SELECT nettoie();

 clean_emp
-----------

(1 row)

33.4.2. Fonctions SQL sur les types composites

Quand nous sp�cifions une fonction avec des arguments de types composite, nous devons non seulement sp�cifier quel argument utiliser (comme nous l'avons fait pr�c�demment avec $1 et $2), mais aussi sp�cifier l'attribut de cet argument. 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     integer,
    age         integer,
    cubicle     point
);

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

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

 nom  | reve
------+-------
 Sam  |  2400

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 un nom de table pour d�finir la ligne courante de la table comme une valeur composite (emp). La ligne de la table peut aussi �tre r�f�renc�e ainsi :

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

qui accentue sa nature 'ligne'.

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 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 :

• L'ordre de la liste du select doit �tre exactement le m�me que celui dans lequel les colonnes apparaissent dans la table associ�e au type composite (donner des noms aux colonnes dans le corps de la fonction, comme nous l'avons fait dans l'exemple, n'a aucune interaction avec le syst�me).

• Vous devez transtyper les expressions pour concorder avec la d�finition du type composite ou bien vous aurez l'erreur suivante :

  ERROR:  function declared to return emp returns varchar instead of text at
  column 1

Une fonction qui renvoie une ligne (type composite) peut �tre utilis�e comme une fonction table, telle que celle d�crite ci-dessous. Elle peut aussi �tre appel�e dans le contexte d'une expression SQL, mais seulement si vous extrayez un seul attribut de la ligne ou si vous passez la ligne enti�re � une autre fonction qui accepte le m�me type composite.

Voici un exemple d'extraction d'un attribut d'un type ligne :

SELECT (nouveau_emp()).nom;

 nom
------
 None

Nous avons besoin des parenth�ses suppl�mentaires pour �viter une erreur de l'analyseur :

    SELECT 
nouveau_emp().nom;

ERROR:  syntax error at or near "." at character 17

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

SELECT nom(nouveau_emp());

 nom
------
 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

L'autre fa�on d'utiliser une fonction renvoyant un r�sultat ligne est de d�clarer une deuxi�me fonction acceptant un argument de type ligne et de lui passer le r�sultat de la premi�re fonction :

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

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

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

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

33.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 33.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 d�fini, 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.