TABLE
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 », ce qui signifie un ensemble de lignes)
en spécifiant le type renvoyé par la fonction comme SETOF
, ou de façon équivalente en
la déclarant comme un_type
RETURNS
TABLE(
. 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.
colonnes
)
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
ou un INSERT
,
UPDATE
ou un DELETE
qui a 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
, DELETE
et MERGE
) 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).
Néanmoins, la commande finale doit être un
SELECT
ou doit avoir une clause RETURNING
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
. 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)
Vous pouvez aussi l'écrire comme une procédure, ce qui évite d'avoir à renseigner un paramètre de sortie. Par exemple:
CREATE PROCEDURE clean_emp() AS ' DELETE FROM emp WHERE salary < 0; ' LANGUAGE SQL; CALL clean_emp();
Dans les cas simples comme celui-ci, la différence entre une fonction retournant
un void
et une procédure est purement esthétique. Cependant,
les procédures offrent des fonctionnalités supplémentaires comme le contrôle
des transactions qui n'est pas disponible dans une fonction. De plus, les
procédures sont conformes à la norme SQL alors que renvoyer void
est une fonctionnalité spécifique à PostgreSQL.
Le corps entier d'une fonction SQL est analysé avant d'être exécuté. Bien
qu'une fonction SQL puisse contenir des commandes qui modifient les
catalogues systèmes (par exemple CREATE TABLE
), les
effets de telles commandes ne seront pas visibles lors de l'analyse des
commandes suivantes dans la fonction. De ce fait, par exemple,
CREATE TABLE foo (...); INSERT INTO foo VALUES(...);
ne fonctionnera pas si c'est intégré dans une seule fonction SQL car
foo
n'existera pas encore quand la commande SQL
INSERT
sera analysée. Il est recommandé d'utiliser
PL/PgSQL à la place de SQL dans ce genre de
situations.
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.4) pour cette constante.
Si vous choisissez d'utiliser la syntaxe habituelle avec des guillemets
simples, vous devez doubler les marques de guillemet simple
('
) et les antislashs (\
), en supposant
que vous utilisez la syntaxe d'échappement de chaînes, utilisés dans le corps
de la fonction (voir la Section 4.1.2.1).
Les arguments d'une fonction SQL peuvent être référencés dans le corps de la fonction en utilisant soit les noms soit les numéros. Des exemples de chaque méthode se trouvent ci-dessous.
Pour utiliser un nom, déclarez l'argument de la fonction comme ayant un
nom, puis écrivez le nom dans le corps de la fonction. Si le nom de
l'argument est le même que celui d'une colonne dans la commande SQL en
cours, le nom de la colonne est prioritaire. Pour contourner ce comportement,
qualifiez le nom de l'argument avec le nom de la fonction, autrement dit
.
(Si cela entre en conflit avec un nom de colonne qualifié, cette fois
encore, la colonne l'emporte. Vous pouvez éviter toute ambiguïté en
choisissant un alias différent pour la table à l'intérieur de la commande
SQL.)
nom_fonction
.nom_argument
Dans l'ancienne approche numérique, les arguments sont référencés en
utilisant la syntaxe
$
:
n
$1
fait référence au premier argument,
$2
au second, et ainsi de suite. Ceci fonctionnera
que l'argument ait été déclaré avec un nom ou pas.
Si un argument est de type composite, la notation à point,
ou nom_argument
.nom_champ
$1.
peut
être utilisé pour accéder aux attributs de l'argument. Encore une fois,
vous pourriez avoir besoin de qualifier le nom de l'argument avec le nom de
la fonction pour qu'il n'y ait pas d'ambiguité.
nom_champ
Les arguments de fonctions SQL peuvent seulement être utilisés comme valeurs de données, et non pas comme identifiants. Du coup, par exemple, ceci est accepté :
INSERT INTO mytable VALUES ($1);
mais ceci ne fonctionnera pas :
INSERT INTO $1 VALUES (42);
La possibilité d'utiliser des noms pour référencer les arguments d'une
fonction SQL a été ajoutée à PostgreSQL 9.2.
Les fonctions devant être utilisées sur des versions antérieures doivent
utiliser la notation $
.
n
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 :
CREATE FUNCTION add_em(x integer, y integer) RETURNS integer AS $$ SELECT x + y; $$ LANGUAGE SQL; SELECT add_em(1, 2) AS answer; answer -------- 3
Autrement, nous pourrions nous passer des noms pour les arguments et utiliser à la place des numéros :
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 (no_compte integer, debit numeric) RETURNS numeric AS $$ UPDATE bank SET balance = balance - debit WHERE no_compte = tf1.no_compte; 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 cet exemple, nous choisissons le nom no_compte
comme premier argument mais ce nom est identique au nom d'une colonne
dans la table banque
. Dans la commande
UPDATE
, no_compte
fait référence
à la colonne banque.no_compte
, donc
tf1.no_compte
doit être utilisé pour faire référence
à l'argument. Nous pouvons bien sûr éviter cela en utilisant un nom
différent pour l'argument.
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 (no_compte integer, debit numeric) RETURNS numeric AS $$ UPDATE bank SET balance = balance - debit WHERE no_compte = tf1.no_compte; SELECT balance FROM banque WHERE no_compte = tf1.no_compte; $$ 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 (no_compte integer, debit numeric) RETURNS numeric AS $$ UPDATE bank SET balance = balance - debit WHERE no_compte = tf1.no_compte RETURNING balance; $$ LANGUAGE SQL;
Si le SELECT
final ou la clause
RETURNING
dans une fonction SQL ne
renvoie pas exactement le type de résultat déclaré pour la fonction,
PostgreSQL convertira automatiquement la
valeur dans le type requis, si c'est possible avec une conversion
implicite ou d'affectation. Sinon vous devrez écrire une conversion
explicite. Par exemple, supposons que nous voulions que la précédente
fonction add_em
retourne un type float8
à la place. Il suffit d'écrire :
CREATE FUNCTION add_em(integer, integer) RETURNS float8 AS $$ SELECT $1 + $2; $$ LANGUAGE SQL;
car la somme de type integer
peut être convertie
implicitement en float8
(voir Chapitre 10 or
CREATE CAST pour plus d'informations sur les
conversions).
Quand nous écrivons une fonction avec des arguments de type composite,
nous devons non seulement spécifier l'argument utilisé,
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 nom_table
.*
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. (Voir Section 8.16.5 pour des détails sur ces deux syntaxes pour la valeur composite d'une ligne de table.)
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 :
L'ordre de la liste du SELECT doit être exactement le même que celui dans lequel les colonnes apparaissent dans le 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).
Il faut s'assurer que le type de chaque expression peut être converti dans le type de la colonne correspondante du type composite. Sinon, une erreur telle que :
+ERROR: return type mismatch in function declared to return emp
DETAIL: Final statement returns text instead of point at column 4.
sera renvoyée. Comme c'est le cas ppour les types de base, le système ne fera pas de conversion explicite automatiquement, mais seulement des conversions implicites ou d'affectation.
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.
Un autre exemple est que si nous essayons d'écrire une fonction qui
retourne un domaine sur un type composite, plutôt qu'un simple type
composite, il est toujours nécessaire de l'écrire comme retournant une
seule colonne, puisqu'il n'y a aucune autre manière de produire une
valeur qui soit exactement du type de ce domaine.
Nous pouvons appeler cette fonction soit en l'utilisant dans une expression de valeur :
SELECT nouveau_emp(); nouveau_emp -------------------------- (None,1000.0,25,"(2,2)")
soit en l'utilisant comme une fonction table :
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 38.5.8.
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 :
SELECT nom(nouveau_emp()); name ------ None
Comme expliqué dans Section 8.16.5, la notation avec des champs et la notation fonctionnelle sont équivalentes.
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 x + y' 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 38.5.2. 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 x + y, x * y' 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 paramètres de sortie sont aussi utilisables dans les procédures mais
leur fonctionnement est légèrement différent par rapport aux paramètres
de sortie des fonctions. Dans les commandes CALL
,
les paramètres en sortie doivent être incluses dans la liste des
arguments. Par exemple, la procédure précédente permettant de débiter un
compte bancaire pourrait être écrite ainsi :
CREATE PROCEDURE tp1 (accountno integer, debit numeric, OUT new_balance numeric) AS $$ UPDATE bank SET balance = balance - debit WHERE accountno = tp1.accountno RETURNING balance; $$ LANGUAGE SQL;
Pour appeler cette procédure, an argument matching the OUT
parameter must be included. Il est habituel d'écriture NULL
.
CALL tp1(17, 100.0, NULL);
Si vous écrivez autre chose, cela doit être une expression qui est convertible implicitement vers le type déclaré du paramètre, comme pour les paramètres en entrée. Notez néanmoins qu'une telle expression ne sera pas évaluée.
Lors de l'appel d'une procédure à partir de
PL/pgSQL, au lieu d'écrire
NULL
, vous devez écrire une variable qui recevra la
sortie de la procédure. Voir Section 43.6.3 pour plus
d'informations.
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 est particulièrement intéressant quand
une fonction variadic veut passer son paramètre tableau à une autre
fonction. En outre, il s'agit de la seule méthode sûre
pour appeler une fonction VARIADIC
trouvée dans un
schéma qui autorise les utilisateurs qui ne sont pas de confiance à créer
des objets ; voir Section 10.3. 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.
Spécifier VARIADIC
dans l'appel est aussi la seule
façon de passer un tableau vide à une fonction variadique. Par
exemple :
SELECT mleast(VARIADIC ARRAY[]::numeric[]);
Écrire simplement SELECT mleast()
ne fonctionne pas
car un paramètre variadique doit correspondre à au moins un argument
réel. (Vous pouvez définir une deuxième fonction aussi nommée
mleast
, sans paramètres, si vous voulez permettre ce
type d'appels.)
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), 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.) Que vous l'utilisez ou non, cette possibilité implique la nécessite de prendre des précautions lors de l'appel de fonctions dans les bases de données où certains utilisateurs ne font pas confiance à d'autres utilisateurs ; voir Section 10.3.
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
, 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.
un_type
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
.
Il est souvent utile de construire le résultat d'une requête par l'appel
répété d'une fonction retournant un ensemble, dont les paramètres varient à
chaque appel avec les valeurs des lignes d'une table ou d'une sous-requête.
La manière idéale de le réaliser est d'utiliser le mot clé
LATERAL
, qui est décrit dans Section 7.2.1.5. Voici un exemple de fonction retournant un
ensemble permettant d'énumérer les éléments d'une structure en arbre :
SELECT * FROM noeuds; nom | parent --------------+-------- Haut | Enfant1 | Haut Enfant2 | Haut Enfant3 | Haut Sous-Enfant1 | Enfant1 Sous-Enfant2 | Enfant1 (6 rows) CREATE FUNCTION listeenfant(text) RETURNS SETOF text AS $$ SELECT nom FROM noeuds WHERE parent = $1 $$ LANGUAGE SQL STABLE; SELECT * FROM listeenfant('Haut'); listeenfant -------------- Enfant1 Enfant2 Enfant3 (3 rows) SELECT nom, enfant FROM noeuds, LATERAL listeenfant(nom) AS enfant; name | child ---------+------------- Haut | Enfant1 Haut | Enfant2 Haut | Enfant3 Enfant1 | Sous-Enfant1 Enfant1 | Sous-Enfant2 (5 rows)
Cet exemple ne fait rien de plus que ce qui aurait été possible avec une simple jointure mais, dans des cas plus complexes, l'alternative consistant à reporter du travail dans une fonction peut se révéler assez pratique.
Les fonctions retournant des ensembles peuvent aussi être appelées dans la clause select d'une requête. Pour chaque ligne que cette requête génère par elle-même, la fonction retournant un ensemble est appelée, et une ligne résultat est générée pour chaque élément de l'ensemble retourné par la fonction. L'exemple précédent peut aussi être implémenté avec des requêtes telles que :
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.
Ceci est dû au fait que la fonction listeenfant
renvoie un ensemble vide pour ces arguments et ainsi aucune ligne n'est
générée. Ce comportement est identique à celui attendu par une requête de
jointure interne join avec le résultat de la fonction utilisant la syntaxe
LATERAL
.
Le comportement de PostgreSQL pour une
fonction renvoyant des lignes (SETOF) dans la liste SELECT d'une requête
est pratiquement identique à celui d'une fonction SETOF écrite dans une
clause LATERAL FROM
. Par exemple :
SELECT x, generate_series(1,5) AS g FROM tab;
est pratiquement équivalente à :
SELECT x, g FROM tab, LATERAL generate_series(1,5) AS g;
Ce serait exactement la même chose, sauf que dans cet exemple spécifique,
le planificateur pourrait choisir de placer g
à
l'extérieur de la jointure de boucle imbriquée puisque
g
n'a pas de réelle dépendance latérale sur
tab
. Cela résulterait en un ordre différent des
lignes en sortie. Les fonctions SETOF dans la liste SELECT sont toujours
évaluées comme si elles étaient à l'intérieur d'une jointure de boucle
imbriquée avec le reste de la clause FROM
, pour que
les fonctions soient exécutées complètement avant de considérer la
prochaine ligne provenant de la clause FROM
.
S'il y a plus d'une fonction SETOF dans la liste du SELECT de la requête,
le comportement est similaire à ce que vous obtiendriez en plaçant les
fonctions dans une seule clause FROM
de
LATERAL ROWS FROM( ... )
. Pour chaque ligne de la
requête sous-jacente, il existe une ligne en sortie utilisant le premier
résultat de chaque fonction, ensuite une ligne en sortie utilisant le
deuxième résultat, et ainsi de suite. Si certaines des fonctions SETOF
produisent moins de résultats que les autres, des valeurs NULL sont
ajoutées pour les données manquantes, pour que le nombre total de lignes
émises pour une ligne sous-jacente soit la même que pour la fonction
SETOF qui a produit le plus de lignes. De ce fait, les fonctions SETOF
s'exécutent complètement jusqu'à ce qu'elles aient terminé, puis
l'exécution continue avec la prochaine ligne sous-jacente.
Les fonctions SETOF peuvent être imbriquées dans une liste SELECT, bien
que cela ne soit pas autorisées dans les éléments d'une clause
FROM
. Dans de tels cas, chaque niveau d'imbrication
est traité séparément, comme s'il s'agissait d'un élément
LATERAL ROWS FROM( ... )
séparé. Par exemple, dans
SELECT srf1(srf2(x), srf3(y)), srf4(srf5(z)) FROM tab;
les fonctions SETOF srf2
, srf3
,
et srf5
seront exécutées ligne par ligne pour chaque
ligne de tab
, puis srf1
et
srf4
seront appliquées ligne par ligne pour chaque
ligne produite par les fonctions inférieures.
Les fonctions SETOF ne peuvent pas être utilisées à l'intérieur de
constructions d'évaluations conditionnelles, telles que
CASE
ou COALESCE
. Ce comportement
signifie aussi que des fonctions SETOF seront évaluées même quand il
pourrait apparaître qu'elles devraient être ignorées grâce à une
construction d'évaluation conditionnelle, telle que
CASE
ou COALESCE
. Par exemple,
considérez :
SELECT x, CASE WHEN x > 0 THEN generate_series(1, 5) ELSE 0 END FROM tab;
Il pourrait sembler que cela produit cinq répétitions des lignes en
entrée qui ont x > 0
, et une seule répétition des
autres parce que generate_series(1, 5)
serait
exécuté dans un élément LATERAL FROM
implicite,
l'expression CASE
est toujours évaluée, elle
produirait cinq répétitions de chaque ligne en entrée. Pour diminuer la
confusion, ce genre de cas renvoie une erreur au moment de l'analyse.
Si la dernière commande d'une fonction est INSERT
,
UPDATE
ou DELETE
avec une clause
RETURNING
, cette commande sera toujours exécutée
jusqu'à sa fin, même si la fonction n'est pas déclarée avec
SETOF
ou que la requête appelante ne renvoie pas toutes
les lignes résultats. Toutes les lignes supplémentaires produites par la
clause RETURNING
sont silencieusement abandonnées mais
les modifications de table sont pris en compte (et sont toutes terminées
avant que la fonction ne se termine).
Avant PostgreSQL 10, placer plus d'une
fonction renvoyant des lignes dans la même clause SELECT n'avait pas un
comportement très simple, sauf si elles produisaient le même nombre de
lignes. Dans le cas contraire, on obtenait un nombre de lignes en sortie
égale au plus petit multiple commun du nombre de lignes produit par les
différentes fonctions. De plus, les fonctions SETOF imbriquées ne
fonctionnaient comme ce qui est décrit ci-dessus. À la place, une
fonction EOF pouvait avoir tout au plus un argument SETOF, et chaque
imbrication de fonctions SETOF était exécutée séparément. De plus, une
exécution conditionnelle (fonctions SETOF à l'intérieur d'un
CASE
, etc) était auparavant autorisée, ce qui
compliquait encore plus les choses. L'utilisation de la syntaxe
LATERAL
est recommandée lors de l'écriture de
requêtes devant fonctionner avec les versions plus anciennes de
PostgreSQL pour produire des résultats
cohérents sur différentes versions. Si vous avez une requête qui se base
sur une exécution conditionnelle d'une fonction SETOF, vous pourriez la
corriger en déplaçant le test conditionnel dans une fonction SETOF
personnalisée. Par exemple :
SELECT x, CASE WHEN y > 0 THEN generate_series(1, z) ELSE 5 END FROM tab;
pourrait devenir
CREATE FUNCTION case_generate_series(cond bool, start int, fin int, els int) RETURNS SETOF int AS $$ BEGIN IF cond THEN RETURN QUERY SELECT generate_series(start, fin); ELSE RETURN QUERY SELECT els; END IF; END$$ LANGUAGE plpgsql; SELECT x, case_generate_series(y > 0, 1, z, 5) FROM tab;
Cette formulation fonctionnera de la même façon sur toutes les versions de PostgreSQL.
TABLE
#
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(
. C'est équivalent
à utiliser un ou plusieurs paramètres colonnes
)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 décrits dans Section 38.2.5. 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 has type unknown
Avec make_array
déclaré comme ci-dessus, vous devez
fournir deux arguments qui sont exactement du même type de données ;
le système ne tentera pas de résoudre les différences de type. De ce
fait, par exemple, ceci ne fonctionnera pas :
SELECT make_array(1, 2.5) AS numericarray; ERROR: function make_array(integer, numeric) does not exist
Une approche alternative est d'utiliser la famille « commune » de types polymorphiques, qui autorise le système à identifier le type commun convenable :
CREATE FUNCTION make_array2(anycompatible, anycompatible) RETURNS anycompatiblearray AS $$ SELECT ARRAY[$1, $2]; $$ LANGUAGE SQL; SELECT make_array2(1, 2.5) AS numericarray; numericarray -------------- {1,2.5} (1 row)
Comme les règles pour la résolution de type commun reviennent par défaut
à choisir le type text
quand toutes les entrées sont de type
inconnu, ceci fonctionne aussi :
SELECT make_array2('a', 'b') AS textarray; textarray ----------- {a,b} (1 row)
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 result of type anyelement requires at least one input of type anyelement, anyarray, anynonarray, anyenum, or anyrange.
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_values(text, VARIADIC anyarray) RETURNS text AS $$ SELECT array_to_string($2, $1); $$ LANGUAGE SQL; SELECT concat_values('|', 1, 4, 2); concat_values --------------- 1|4|2 (1 row)
Lorsqu'une fonction SQL dispose d'un ou plusieurs paramètres d'un
type de données collationnable, le collationnement applicable
est déterminé pour chacun des appels à la fonction afin de
correspondre au collationnement assigné aux arguments, tel que
décrit à la section Section 24.2. Si un
collationnement peut être correctement identifié (c'est-à-dire
qu'il ne subsiste aucun conflit entre les collationnements
implicites des arguments), alors l'ensemble des paramètres
collationnables sera traité en fonction de ce collationnement. Ce
comportement peut donc avoir une incidence sur les opérations
sensibles aux collationnements se trouvant dans le corps de la
fonction. Par exemple, en utilisant la fonction
anyleast
décrite ci-dessus, le résultat de
SELECT anyleast('abc'::text, 'ABC');
dépendra du collationnement par défaut de l'instance. Ainsi, pour
la locale C
, le résultat sera ABC
,
alors que pour de nombreuses autres locales, la fonction retournera
abc
. L'utilisation d'un collationnement
particulier peut être forcé lors de l'appel de la fonction en
spécifiant la clause COLLATE
pour chacun des
arguments, par exemple
SELECT anyleast('abc'::text, 'ABC' COLLATE "C");
Par ailleurs, si vous souhaitez qu'une fonction opère avec un
collationnement particulier, sans tenir compte du collationnement
des paramètres qui lui seront fournis, il faudra alors spécifier la
clause COLLATE
souhaitée lors de la définition
de la fonction. Cette version de la fonction
anyleast
utilisera systématiquement la locale
fr_FR
pour la comparaison des chaines de
caractères :
CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$ SELECT min($1[i] COLLATE "fr_FR") FROM generate_subscripts($1, 1) g(i); $$ LANGUAGE SQL;
Mais il convient de bien noter que cette modification risque d'entraîner une erreur si des données d'un type non sensible au collationnement lui sont fournies.
Si aucun collationnement commun ne peut être déterminé entre les arguments fournis, la fonction SQL appliquera aux paramètres le collationnement par défaut de leur type de donnée (qui correspond généralement au collationnement par défaut de l'instance, mais qui peut différer entre des domaines différents).
Le comportement des paramètres collationnables peut donc être assimilé à une forme limitée de polymorphisme, uniquement applicable aux types de données textuels.