Dans cette section ainsi que les suivantes, nous décrirons tous les types d'instructions explicitement compris par PL/pgSQL. Tout ce qui n'est pas reconnu comme l'un de ces types d'instruction est présumé être une commande SQL et est envoyé au moteur principal de bases de données pour être exécutée comme décrit dans la Section 42.5.2 et dans la Section 42.5.3.
L'affectation d'une valeur à une variable PL/pgSQL s'écrit ainsi :
variable
{ := | = }expression
;
Comme expliqué précédemment, l'expression dans cette instruction est évaluée au
moyen de la commande SQL SELECT
envoyée au moteur principal de
bases de données. L'expression ne doit manier qu'une seule valeur (éventuellement
une valeur de rangée, si cette variable est une variable de rangée ou
d'enrengistrement). La variable cible peut être une simple variable
(éventuellement qualifiée avec un nom de bloc), un champ d'une rangée
ou variable d'enrengistrement ou un élément de tableau qui se trouve
être une simple variable ou champ. Le signe d'égalité (=
)
peut être utilisé à la place de :=
, qui lui est conforme
au PL/SQL.
Si le type de données du résultat de l'expression ne correspond pas au type de donnée de la variable, la valeur sera convertie via une conversion d'affectation (cf Section 10.4. Si aucune conversion d'affectation n'est connue pour les deux types de données concernées, l'interpréteur PL/pgSQL tentera de convertir le résultat textuellement, c'est-à-dire en appliquant successivement la fonction de sortie du type résultat puis la fonction d'entrée du type de la variable. Notez que la fonction d'entrée peut générer des erreurs à l'exécution si la chaîne passée en paramètre n'est pas acceptable pour le type de la variable.
Exemples :
taxe := sous_total * 0.06; mon_enregistrement.id_utilisateur := 20;
Pour toute commande SQL qui ne renvoie pas de lignes, par exemple
INSERT
sans clause RETURNING
, vous
pouvez exécuter la commande à l'intérieur d'une fonction
PL/pgSQL rien qu'en écrivant la commande.
Tout nom de variable PL/pgSQL apparaissant dans le texte de la commande est traité comme un paramètre, puis la valeur actuelle de la variable est fournie comme valeur du paramètre à l'exécution. C'est le traitement exact décrit précédemment pour les expressions. Pour les détails, voir la Section 42.11.1.
Lors de l'exécution d'une commande SQL de cette façon, PL/pgSQL peut placer le plan en cache et le réutiliser plus tard, comme indiqué dans Section 42.11.2.
Parfois, il est utile d'évaluer une expression ou une requête
SELECT
mais sans récupérer le résultat, par
exemple lors de l'appel d'une fonction qui a des effets de bord
mais dont la valeur du résultat n'est pas utile. Pour faire cela
en PL/pgSQL, utilisez l'instruction
PERFORM
:
PERFORM requête
;
Ceci exécute la requête
et ne tient pas compte
du résultat. Écrivez la requête
de la même
façon que vous écririez une commande SELECT
mais
remplacez le mot clé initial SELECT
avec
PERFORM
.
Pour les requêtes WITH
, utilisez
PERFORM
puis placez la requête entre parenthèses.
(De cette façon, la requête peut seulement renvoyer une ligne.)
Les variables PL/pgSQL seront substituées dans
la requête comme pour les commandes qui ne renvoient pas de résultat. Le
plan est mis en cache de la même façon. La variable spéciale
FOUND
est configurée à true si la requête a produit
au moins une ligne, false dans le cas contraire (voir la
Section 42.5.5).
Vous pourriez vous attendre à ce que l'utilisation directe de
SELECT
aboutisse au même résultat mais, actuellement,
la seule façon acceptée de le faire est d'utiliser
PERFORM
. Une commande SQL qui peut renvoyer des lignes
comme SELECT
sera rejetée comme une erreur si elle
n'a pas de clause INTO
, ce qui est discuté dans la
section suivante.
Un exemple :
PERFORM creer_vuemat('cs_session_page_requests_mv', ma_requete);
Le résultat d'une commande SQL ne ramenant qu'une seule ligne (mais avec une
ou plusieurs colonnes) peut être affecté à une variable de type record, row ou
à une liste de variables scalaires. Ceci se fait en écrivant la commande SQL
de base et en ajoutant une clause INTO
. Par exemple,
SELECTexpressions_select
INTO [STRICT]cible
FROM ...; INSERT ... RETURNINGexpressions
INTO [STRICT]cible
; UPDATE ... RETURNINGexpressions
INTO [STRICT]cible
; DELETE ... RETURNINGexpressions
INTO [STRICT]cible
;
où cible
peut être une variable de type record,
row ou une liste de variables ou de champs record/row séparées par
des virgules. Les variables PL/pgSQL seront
substituées dans le reste de la requête, et le plan est mis en cache
comme décrit ci-dessus pour les commandes qui ne renvoient pas de lignes.
Ceci fonctionne pour SELECT
,
INSERT
/UPDATE
/DELETE
avec RETURNING
, et les commandes utilitaires qui renvoient
des résultats de type rowset (comme EXPLAIN
).
Sauf pour la clause INTO
, la commande SQL est identique
à celle qui aurait été écrite en dehors de PL/pgSQL.
Notez que cette interprétation de SELECT
avec INTO
est assez différente de la commande habituelle SELECT INTO
où la
cible INTO
est une table nouvellement créée. Si vous
voulez créer une table à partir du résultat d'un
SELECT
à l'intérieur d'une fonction
PL/pgSQL, utilisez la syntaxe
CREATE TABLE ... AS SELECT
.
Si une ligne ou une liste de variables est utilisée comme cible, les colonnes du résultat de la requête doivent correspondre exactement à la structure de la cible (nombre de champs et types de données). Dans le cas contraire, une erreur sera rapportée à l'exécution. Quand une variable record est la cible, elle se configure automatiquement avec le type row des colonnes du résultat de la requête.
La clause INTO
peut apparaître pratiquement partout
dans la commande SQL. Elle est écrite soit juste avant soit juste après
la liste d'expressions_select
dans une commande
SELECT
, ou à la fin de la commande pour d'autres types
de commande. Il est recommandé de suivre cette convention au cas où
l'analyseur PL/pgSQL devient plus strict dans
les versions futures.
Si STRICT
n'est pas spécifié dans la clause
INTO
, alors
cible
sera configuré avec la première ligne
renvoyée par la requête ou à NULL si la requête n'a renvoyé aucune ligne.
(Notez que « la première ligne » n'est bien définie que
si vous avez utilisé ORDER BY
.) Toute ligne résultat
après la première ligne est annulée. Vous pouvez vérifier la valeur de la
variable spéciale FOUND
(voir la
Section 42.5.5) pour déterminer si une
ligne a été renvoyée :
SELECT * INTO monrec FROM emp WHERE nom = mon_nom; IF NOT FOUND THEN RAISE EXCEPTION 'employé % introuvable', mon_nom; END IF;
Si l'option STRICT
est indiquée, la requête doit
renvoyer exactement une ligne. Dans le cas contraire, une erreur sera rapportée à
l'exécution, soit NO_DATA_FOUND
(aucune ligne) soit
TOO_MANY_ROWS
(plus d'une ligne). Vous pouvez utiliser
un bloc d'exception si vous souhaitez récupérer l'erreur, par exemple :
BEGIN SELECT * INTO STRICT monrec FROM emp WHERE nom = mon_nom; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE EXCEPTION 'employé % introuvable', mon_nom; WHEN TOO_MANY_ROWS THEN RAISE EXCEPTION 'employé % non unique', mon_nom; END;
Une exécution réussie de la commande avec STRICT
renvoie
toujours true pour FOUND
.
Pour les commandes INSERT
/
UPDATE
/ DELETE
utilisées avec la
clause RETURNING
, PL/pgSQL
renvoie une erreur si plus d'une ligne est renvoyée, même si la clause
STRICT
n'est pas indiquée. Ceci est dû au fait qu'il
n'existe pas d'option ORDER BY
qui permettrait de
déterminer la ligne affectée à renvoyer.
Si print_strict_params
est activé pour cette fonction,
alors, quand une erreur est renvoyée parce que les conditions de
STRICT
ne sont pas rencontrées, la partie
DETAIL
du message d'erreur incluera les informations
sur les paramètres passés à la requête. Vous pouvez modifier la
configuration de print_strict_params
pour toutes les
fonctions en configurant plpgsql.print_strict_params
,
bien que seules les compilations suivantes des fonctions seront affectées.
Vous pouvez aussi l'activer fonction par fonction en utilisant une option
du compilateur, par exemple :
CREATE FUNCTION get_userid(username text) RETURNS int AS $$ #print_strict_params on DECLARE userid int; BEGIN SELECT users.userid INTO STRICT userid FROM users WHERE users.username = get_userid.username; RETURN userid; END; $$ LANGUAGE plpgsql;
En cas d'échec, cette fonction pourrait renvoyer un message d'erreur tel que :
ERROR: query returned no rows DETAIL: parameters: $1 = 'nosuchuser' CONTEXT: PL/pgSQL function get_userid(text) line 6 at SQL statement
L'option STRICT
correspond au comportement du
SELECT INTO
d'Oracle PL/SQL et des instructions
relatives.
Pour gérer les cas où vous avez besoin de traiter plusieurs lignes de résultat à partir d'une requête SQL, voir la Section 42.6.6.
Créer dynamique des requêtes SQL est un besoin habituel dans les fonctions
PL/pgSQL, par exemple des requêtes
qui impliquent différentes tables ou différents types de données à chaque
fois qu'elles sont exécutées. Les tentatives normales de
PL/pgSQL pour garder en cache les planifications
des commandes (voir la Section 42.11.2) ne
fonctionneront pas dans de tels scénarios. Pour gérer ce type
de problème, l'instruction EXECUTE
est proposée :
EXECUTEcommand-string
[ INTO [STRICT]target
] [ USINGexpression
[, ...] ];
où chaîne-commande
est une expression manipulant
une chaîne (de type text
) contenant la commande à exécuter.
La cible
optionnelle est une variable record ou ligne ou
même une liste de variables simples ou de champs de lignes/enregistrements
séparées par des virgules, dans lesquels les résultats de la commande seront
enregistrés. Les expressions USING
optionnelles fournissent
des valeurs à insérer dans la commande.
Aucune substitution des variables PL/pgSQL ne se fait dans la chaîne de commande calculée. Toutes les valeurs des variables requises doivent être insérées dans la chaîne de commande au moment de sa construction ; ou vous pouvez utiliser des paramètres comme décrits ci-dessous.
De plus, il n'y a pas mise en cache des commandes exécutées via
EXECUTE
. À la
place, la commande est planifiée à chaque fois que l'instruction est lancée.
La chaîne commande peut être créée dynamiquement à l'intérieur de la
fonction pour agir sur des tables ou colonnes différentes.
La clause INTO
spécifie où devraient être affectés les
résultats d'une commande SQL renvoyant des lignes. Si une ligne ou une
liste de variable est fournie, elle doit correspondre exactement à la
structure des résultats de la requête (quand
une variable de type record est utilisée, elle sera automatiquement typée
pour correspondre à la structure du résultat). Si plusieurs lignes sont
renvoyées, alors seule la première sera assignée à la variable
INTO
. Si aucune ligne n'est renvoyée, NULL est affectée
à la variable INTO
. Si aucune clause
INTO
n'est spécifiée, les résultats de la requête sont
ignorés.
Si l'option STRICT
est indiquée, une erreur est
rapportée sauf si la requête produit exactement une ligne.
La chaîne de commande peut utiliser des valeurs de paramètres, référencées
dans la commande avec $1
, $2
, etc.
Ces symboles font référence aux valeurs fournies dans la clause
USING
. Cette méthode est souvent préférable à
l'insertion des valeurs en texte dans une chaîne de commande : cela
évite la surcharge à l'exécution pour la conversion des valeurs en texte
et vice-versa. C'est aussi moins sensible aux attaques par injection SQL
car il n'est pas nécessaire de mettre entre guillemets ou d'échapper les
valeurs. Voici un exemple :
EXECUTE 'SELECT count(*) FROM matable WHERE insere_par = $1 AND insere <= $2' INTO c USING utilisateur_verifie, date_verifiee;
Notez que les symboles de paramètres peuvent seulement être utilisés pour des valeurs de données -- si vous voulez utiliser des noms de tables et/ou colonnes déterminés dynamiquement, vous devez les insérer dans la chaîne de commande en texte. Par exemple, si la requête précédente devait se faire avec une table sélectionnée dynamiquement, vous devriez faire ceci :
EXECUTE 'SELECT count(*) FROM ' || quote_ident(tabname) || ' WHERE insere_par = $1 AND insere <= $2' INTO c USING utilisateur_verifie, date_verifiee;
Une meilleure solution est d'utiliser la spécification de formatage
%I
de la fonction format()
pour les noms de table ou de colonne (les chaînes de caractères
séparées par un retour à la ligne sont concaténées):
EXECUTE format('SELECT count(*) FROM %I ' 'WHERE insere_par = $1 AND insere <= $2', matable) INTO c USING utilisateur_verifie, date_verifiee;
Une autre restriction sur les symboles de paramètres est qu'ils ne
fonctionnent que dans les commandes SELECT
,
INSERT
, UPDATE
et
DELETE
. Dans les autres types d'instructions
(appelés de manière générique commandes utilitaires), vous devez
insérer les valeurs sous forme de texte même si ce ne sont que des
données.
Un EXECUTE
avec une chaîne de commande constante et des
paramètres USING
, comme dans le premier exemple
ci-dessus, est équivalent fonctionnellement à l'écriture simple d'une
commande directement dans PL/pgSQL et permet
le remplacement automatique des variables PL/pgSQL.
La différence importante est que EXECUTE
va planifier de
nouveau la commande pour chaque exécution, générant un plan qui est
spécifique aux valeurs actuelles des paramètres ; alors que
PL/pgSQL pourrait sinon créer un plan générique
et le stocke pour le réutiliser. Dans des situations où le meilleur plan
dépend fortement des valeurs des paramètres, cela peut être utile d'utiliser
EXECUTE
pour s'assurer qu'un plan générique n'est pas
sélectionné.
SELECT INTO
n'est actuellement pas supporté à
l'intérieur de EXECUTE
; à la place, exécutez une
commande SELECT
et spécifiez INTO
comme faisant parti lui-même d'EXECUTE
.
L'instruction EXECUTE
de
PL/pgSQL n'a pas de relation avec l'instruction
SQL EXECUTE supportée
par le serveur PostgreSQL. L'instruction
EXECUTE
du serveur ne peut pas être utilisée directement
dans les fonctions PL/pgSQL. En fait, elle n'est pas
nécessaire.
Exemple 42.1. Mettre entre guillemets des valeurs dans des requêtes dynamiques
En travaillant avec des commandes dynamiques, vous aurez souvent à gérer des échappements de guillemets simples. La méthode recommandée pour mettre entre guillemets un texte fixe dans le corps de votre fonction est d'utiliser les guillemets dollar (si votre code n'utilise pas les guillemets dollar, référez-vous à l'aperçu dans la Section 42.12.1, ce qui peut vous faire gagner des efforts lors du passage de ce code à un schéma plus raisonnable).
Les valeurs dynamiques à insérer dans la requête
construite requièrent une attention spéciale car elles pourraient
elles-même contenir des guillemets. Voici un exemple utilisant la fonction
format()
(cet exemple suppose que vous utilisiez les
guillemets dollar pour la fonction dans sa globalité pour que
les guillemets n'aient pas besoin d'être doublés) :
EXECUTE format('UPDATE table SET %I = $1 ' 'WHERE clef = $2', nom_colonne) USING nouvelle_valeur, valeur_clef;
Il est également possible d'appeler explicitement les fonctions d'échappement:
EXECUTE 'UPDATE tbl SET ' || quote_ident(nom_colonne) || ' = ' || quote_literal(nouvelle_valeur) || ' WHERE cle = ' || quote_literal(valeur_cle);
Cet exemple démontre l'utilisation des fonctions
quote_ident
et quote_literal
(voir Section 9.4).
Pour plus de sûreté, les expressions contenant
les identifiants des colonnes et des tables doivent être passées à la
fonction quote_ident
avant l'insertion dans une requête
dynamique. Les expressions contenant des
valeurs de type chaîne de caractères doivent être
passées à quote_literal
. Ce sont les étapes
appropriées pour renvoyer le texte en entrée entouré par des guillemets
doubles ou simples respectivement, en échappant tout caractère spécial.
Comme quote_literal
est labelisé
STRICT
, elle renverra toujours NULL lorsqu'elle est
appelée avec un argument NULL. Dans l'exemple ci-dessus, si
nouvelle_valeur
ou valeur_clé
étaient NULL, la requête dynamique entière deviendrait NULL, amenant une
erreur à partir du EXECUTE
. Vous pouvez éviter ce
problème en utilisant la fonction quote_nullable
qui
fonctionne de façon identique à quote_literal
sauf
si elle est appelée avec un argument NULL, elle renvoie la chaîne
NULL
.
Par exemple,
EXECUTE 'UPDATE tbl SET ' || quote_ident(nom_colonne) || ' = ' || quote_nullable(nouvelle_valeur) || ' WHERE key = ' || quote_nullable(valeur_clé);
Si vous travaillez avez des valeurs qui peuvent être NULL, vous devez
utiliser quote_nullable
à la place de
quote_literal
.
Comme toujours, il faut s'assurer que les valeurs NULL d'une requête ne
ramènent pas des valeurs inattendues. Par exemple, la clause
WHERE
'WHERE key = ' || quote_nullable(valeur_clé)
ne sera jamais vrai si valeur_clé
est NULL car le résultat
de l'opérateur d'égalité, =
, avec au moins un des opérandes
NULL est toujours NULL. Si vous souhaitez que NULL fonctionne comme toute
autre valeur de clé ordinaire, vous devez ré-écrire la clause ci-dessus de
cette façon :
'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)
(Actuellement, IS NOT DISTINCT FROM
est géré moins
efficacement que =
, donc ne l'utilisez pas sauf en cas
d'extrême nécessité. Voir Section 9.2 pour plus
d'informations sur les NULL et IS DISTINCT
.)
Notez que les guillemets dollar sont souvent utiles pour placer un texte fixe entre guillemets. Ce serait une très mauvaise idée d'écrire l'exemple ci-dessus de cette façon :
EXECUTE 'UPDATE tbl SET ' || quote_ident(nom_colonne) || ' = $$' || nouvelle_valeur || '$$ WHERE cle = ' || quote_literal(valeur_cle);
car cela casserait si le contenu de nouvelle_valeur
pouvait contenir
$$
. La même objection s'applique à tout délimiteur dollar
que vous pourriez choisir. Donc, pour mettre un texte inconnu entre
guillemets de façon sûr, vous devez utiliser
quote_literal
,
quote_nullable
ou quote_ident
,
comme approprié.
Les requêtes SQL dynamiques peuvent aussi être construites en toute
sécurité en utilisant la fonction format
(voir
Section 9.4.1). Par exemple :
EXECUTE format('UPDATE matable SET %I = %L ' 'WHERE clef = %L', nom_colonne, nouvelle_valeur, valeur_clef);
%I
est équivalent à quote_ident
, et
%L
est équivalent à quote_nullable
.
La fonction format
peut être utilisée avec la
clause USING
:
EXECUTE format('UPDATE tbl SET %I = $1 WHERE cle = $2', nom_colonne) USING nouvellevaleur, clevaleur;
Cette forme est meilleure car les variables sont traitées
dans le format natif à leur type plutôt que de les convertir
inconditionnellement en texte et de les échapper via le spécifieur
de format %L
. C'est également plus performant.
Un exemple bien plus important d'une commande dynamique et
d'EXECUTE
est disponible dans l'Exemple 42.10, qui construit et exécute une commande
CREATE FUNCTION
pour définir une nouvelle fonction.
Il y a plusieurs moyens pour déterminer l'effet d'une commande. La première méthode
est d'utiliser GET DIAGNOSTICS
:
GET [ CURRENT ] DIAGNOSTICSvariable
{ = | := }élément
[ , ... ];
Cette commande récupère les indicateurs de statut du système.
CURRENT
est un mot optionnel (mais voir aussi
GET STACKED DIAGNOSTICS
dans Section 42.6.8.1). Chaque
élément
est un mot clé identifiant une valeur de
statut à affecter à la variable
indiquée (qui
doit être du bon type de données pour la recevoir). Les éléments de statut
actuellement disponibles sont affichés dans Tableau 42.1. L'opérateur deux-points-
égal (:=
) peut être utilisé à la place de l'opérateur
=
qui lui est compatible avec le standard SQL.
Exemple :
GET DIAGNOSTICS var_entier = ROW_COUNT;
Tableau 42.1. Éléments de diagnostique disponibles
Nom | Type | Description |
---|---|---|
ROW_COUNT | bigint | le nombre de lignes traitées par la commande SQL la plus récente |
PG_CONTEXT | text | ligne(s) de texte décrivant la pile d'appels actuelle (voir Section 42.6.9) |
La seconde méthode permettant de déterminer les effets d'une commande est la variable
spéciale nommée FOUND
de type boolean
.
La variable FOUND
est initialisée à false au début de chaque fonction
PL/pgSQL. Elle est positionnée par chacun des types
d'instructions suivants :
Une instruction SELECT INTO
positionne
FOUND
à true si une ligne est affectée, false
si aucune ligne n'est renvoyée.
Une instruction PERFORM
positionne FOUND
à true si elle renvoie une ou plusieurs lignes, false si aucune ligne n'est
produite.
Les instructions UPDATE
, INSERT
, et
DELETE
positionnent FOUND
à true
si au moins une ligne est affectée, false si aucune ligne n'est affectée.
Une instruction FETCH
positionne FOUND
à true si elle renvoie une ligne, false si aucune ligne n'est renvoyée.
Une instruction MOVE
initialise
FOUND
à true si elle repositionne le curseur
avec succès. Dans le cas contraire, elle le positionne à false.
Une instruction FOR
ou
FOREACH
initialise
FOUND
à la valeur true s'il itère une ou
plusieurs fois, et à false dans les autres cas.
FOUND
est initialisé de cette façon quand
la boucle se termine : pendant l'exécution de la
boucle, FOUND
n'est pas modifié par la
boucle, bien qu'il pourrait être modifié par l'exécution
d'autres requêtes dans le corps de la boucle.
Les instructions RETURN QUERY
et RETURN
QUERY EXECUTE
mettent à jour la variable FOUND
à true si la requête renvoie au moins une ligne, et false si aucune
ligne n'est renvoyée.
Les autres instructions PL/pgSQL ne changent
pas l'état de FOUND
. Notez que la commande
EXECUTE
modifie la sortie de
GET DIAGNOSTICS
mais ne change pas
FOUND
.
FOUND
est une variable locale à l'intérieur de chaque
fonction PL/pgSQL ; chaque changement qui
y est fait n'affecte que la fonction en cours.
Quelque fois, une instruction qui ne fait rien est utile. Par exemple,
elle indique qu'une partie de la chaîne IF
/THEN
/ELSE
est délibérément
vide. Pour cela, utilisez l'instruction :
NULL;
Par exemple, les deux fragments de code suivants sont équivalents :
BEGIN y := x / 0; EXCEPTION WHEN division_by_zero THEN NULL; -- ignore l'erreur END;
BEGIN y := x / 0; EXCEPTION WHEN division_by_zero THEN -- ignore l'erreur END;
Ce qui est préférable est une question de goût.
Dans le PL/SQL d'Oracle, les listes d'instructions vides ne sont pas
autorisées et, du coup, les instructions NULL
sont
requises dans les situations telles que celles-ci.
PL/pgSQL vous permet d'écrire simplement
rien.