35.4. Fonctions en langage de requêtes (SQL)

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

  nettoie_emp
  -----------

  (1 row)

INSERT INTO matable VALUES ($1);

INSERT INTO $1 VALUES (42);

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. La même chose peut se faire en une commande en utilisant la clause RETURNING :

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

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

INSERT INTO emp VALUES ('Bill', 4200, 45, '(2,1)');

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 :

Une 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 35.4.7, « Fonctions SQL comme sources de table ».

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

Une autre façon d'utiliser une fonction renvoyant un type composite est de l'appeler comme une fonction de table, comme décrit dans la Section 35.4.7, « Fonctions SQL comme sources de table ».

Il est possible d'attacher des noms aux paramètres d'une fonction, par exemple :

CREATE FUNCTION tf1 (acct_no integer, debit numeric) RETURNS numeric AS $$
    UPDATE bank
        SET balance = balance - $2
        WHERE no_compte = $1
    RETURNING balance;
$$ LANGUAGE SQL;

Dans cet exemple, le premier paramètre a comme nom acct_no, et le second paramètre debit. En ce qui concerne la fonction SQL, ces noms sont de la décoration. Vous devez toujours faire référence aux paramètres avec la notation $1, $2, etc dans le corps de la fonction. (Certains langages de procédure vous autorisent à utiliser les noms des paramètres à la place.) Néanmoins, les noms des paramètres peuvent être utiles dans un but de documentation. Quand une fonction a beaucoup de paramètres, il est aussi utile d'utiliser les noms lors de l'appel à la fonction, comme décrit dans Section 4.3, « Fonctions appelantes ».

Une autre façon de décrire les résultats d'une fonction est de la définir avec des paramètres en sortie comme dans cet exemple :

CREATE FUNCTION ajoute (IN x int, IN y int, OUT sum int)
AS 'SELECT $1 + $2'
LANGUAGE SQL;

SELECT ajoute(3,7);
 ajoute
--------
     10
(1 row)

Ceci n'est pas vraiment différent de la version d'ajoute montrée dans la Section 35.4.1, « Fonctions SQL sur les types de base ». La vraie valeur des paramètres en sortie est qu'ils fournissent une façon agréable de définir des fonctions qui renvoient plusieurs colonnes. Par exemple,

CREATE FUNCTION ajoute_n_produit (x int, 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
(1 row)

Ce qui est arrivé ici est que nous avons créé un type composite anonyme pour le résultat de la fonction. L'exemple ci-dessus a le même résultat final que

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;

mais ne pas avoir à s'embêter avec la définition séparée du type composite est souvent agréable. Notez que les noms attachés aux paramètres de sortie ne sont pas juste décoratif, mais déterminent le nom des colonnes du type composite anonyme. (Si vous omettez un nom pour un paramètre en sortie, le système choisira un nom lui-même.)

Notez que les paramètres en sortie ne sont pas inclus dans la liste d'arguments lors de l'appel d'une fonction de ce type en SQL. Ceci parce que PostgreSQL™ considère seulement les paramètres en entrée pour définir la signature d'appel de la fonction. Cela signifie aussi que seuls les paramètres en entrée sont importants lors de références de la fonction pour des buts comme sa suppression. Nous pouvons supprimer la fonction ci-dessus avec l'un des deux appels ci-dessous :

DROP FUNCTION ajoute_n_produit (x int, y int, OUT somme int, OUT produit int);
DROP FUNCTION ajoute_n_produit (int, int);

Les paramètres peuvent être marqués comme IN (par défaut), OUT ou INOUT ou VARIADIC. Un paramètre INOUT sert à la fois de paramètre en entrée (il fait partie de la liste d'arguments en appel) et comme paramètre de sortie (il fait partie du type d'enregistrement résultat). Les paramètres VARIADIC sont des paramètres en entrées, mais sont traités spécifiquement comme indiqué ci-dessous.

Les fonctions SQL peuvent accepter un nombre variable d'arguments à condition que tous les arguments « optionnels » sont du même type. Les arguments optionnels seront passés à la fonction sous forme d'un tableau. La fonction est déclarée en marquant le dernier paramètre comme VARIADIC ; ce paramètre doit être déclaré de type tableau. Par exemple :

CREATE FUNCTION mleast(VARIADIC arr numeric[]) RETURNS numeric AS $$
    SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;

SELECT mleast(10, -1, 5, 4.4);
 mleast 
--------
     -1
(1 row)

En fait, tous les arguments à la position ou après la position de l'argument VARIADIC sont emballés dans un tableau à une dimension, comme si vous aviez écrit

SELECT mleast(ARRAY[10, -1, 5, 4.4]);    -- doesn't work

Vous ne pouvez pas vraiment écrire cela, ou tout du moins cela ne correspondra pas à la définition de la fonction. Un paramètre marqué VARIADIC correspond à une ou plusieurs occurrences de son type d'élément, et non pas de son propre type.

Quelque fois, il est utile de pouvoir passer un tableau déjà construit à une fonction variadic ; ceci ets particulièrement intéressant quand une fonction variadic veut passer son paramètre tableau à une autre fonction. Vous pouvez faire cela en spécifiant VARIADIC dans l'appel :

SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);

Ceci empêche l'expansion du paramètre variadic de la fonction dans le type des éléments, ce qui permet à la valeur tableau de correspondre. VARIADIC peut seulement être attaché au dernier argument d'un appel de fonction.

Les paramètres de l'élément tableau générés à partir d'un paramètre variadic sont traités comme n'ayant pas de noms propres. Cela signifie qu'il n'est pas possible d'appeler une fonction variadic en utilisant des arguments nommés (Section 4.3, « Fonctions appelantes »), sauf quand vous spécifiez VARIADIC. Par exemple, ceci fonctionnera :

SELECT mleast(VARIADIC arr := ARRAY[10, -1, 5, 4.4]);

mais pas cela :

SELECT mleast(arr := 10);
SELECT mleast(arr := ARRAY[10, -1, 5, 4.4]);

Les fonctions peuvent être déclarées avec des valeurs par défaut pour certains des paramètres en entrée ou pour tous. Les valeurs par défaut sont insérées quand la fonction est appelée avec moins d'arguments que à priori nécessaires. Comme les arguments peuvent seulement être omis à partir de la fin de la liste des arguments, tous les paramètres après un paramètres disposant d'une valeur par défaut disposeront eux-aussi d'une valeur par défaut. (Bien que l'utilisation de la notation avec des arguments nommés pourrait autoriser une relâche de cette restriction, elle est toujours forcée pour que la notation des arguments de position fonctionne correctement.)

Par exemple :

CREATE FUNCTION foo(a int, b int DEFAULT 2, c int DEFAULT 3)
RETURNS int
LANGUAGE SQL
AS $$
    SELECT $1 + $2 + $3;
$$;

SELECT foo(10, 20, 30);
 foo 
-----
  60
(1 row)

SELECT foo(10, 20);
 foo 
-----
  33
(1 row)

SELECT foo(10);
 foo 
-----
  15
(1 row)

SELECT foo();  -- échec car il n'y a pas de valeur par défaut pour le premier argument
ERROR:  function foo() does not exist

Le signe = peut aussi être utilisé à la place du mot clé DEFAULT,

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(foonom) FROM recupfoo(1) AS t1;

 fooid | foosubid | foonom | upper
-------+----------+--------+-------
     1 |        1 | Joe    | JOE
(1 row)

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.

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.

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)

Il est aussi possible de renvoyer plusieurs lignes avec les colonnes définies par des paramètres en sortie, comme ceci :

CREATE TABLE tab (y int, z int);
INSERT INTO tab VALUES (1, 2), (3, 4), (5, 6), (7, 8);

CREATE FUNCTION sum_n_product_with_tab (x int, OUT sum int, OUT product int)
RETURNS SETOF record
AS $$
    SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

SELECT * FROM sum_n_product_with_tab(10);
 sum | product
-----+---------
  11 |      10
  13 |      30
  15 |      50
  17 |      70
(4 rows)

Le point clé ici est que vous devez écrire RETURNS SETOF record pour indiquer que la fonction renvoie plusieurs lignes et non pas une seule. S'il n'y a qu'un paramètre en sortie, indiquez le type de paramètre plutôt que record.

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.

Il existe une autre façon de déclarer une fonction comme renvoyant un ensemble de données. Cela passe par la syntaxe RETURNS TABLE(colonnes). C'est équivalent à utiliser un ou plusieurs paramètres OUT et à marquer la fonction comme renvoyant un SETOF record (ou SETOF d'un type simple en sortie, comme approprié). Cette notation est indiquée dans les versions récentes du standard SQL et, du coup, devrait être plus portable que SETOF.

L'exemple précédent, sum-and-product, peut se faire aussi de la façon suivante :

CREATE FUNCTION sum_n_product_with_tab (x int)
RETURNS TABLE(sum int, product int) AS $$
    SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

Il n'est pas autorisé d'utiliser explicitement des paramètres OUT ou INOUT avec la notation RETURNS TABLE -- vous devez indiquer toutes les colonnes en sortie dans la liste TABLE.

Les fonctions SQL peuvent être déclarées pour accepter et renvoyer les types « polymorphe » anyelement, anyarray, anynonarray et anyenum. Voir la Section 35.2.5, « Types et fonctions polymorphes » 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 polymorphic 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 a polymorphic type must have at least one
polymorphic argument.

Le polymorphisme peut être utilisé avec les fonctions qui ont des arguments en sortie. Par exemple :

CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray)
AS 'select $1, array[$1,$1]' LANGUAGE SQL;

SELECT * FROM dup(22);
 f2 |   f3
----+---------
 22 | {22,22}
(1 row)

Le polymorphisme peut aussi être utilisé avec des fonctions variadic. Par exemple :

CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$
    SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;

SELECT anyleast(10, -1, 5, 4);
 anyleast 
----------
       -1
(1 row)

SELECT anyleast('abc'::text, 'def');
 anyleast 
----------
 abc
(1 row)

CREATE FUNCTION concat(text, VARIADIC anyarray) RETURNS text AS $$
    SELECT array_to_string($2, $1);
$$ LANGUAGE SQL;

SELECT concat('|', 1, 4, 2);
 concat 
--------
 1|4|2
(1 row)