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.