Cette section discute des détails d'implémentation les plus importants à connaître pour les utilisateurs de PL/pgSQL.
Les instructions et expressions SQL au sein d'une fonction PL/pgSQL peuvent faire appel aux variables et paramètres d'une fonction. En coulisses, PL/pgSQL remplace les paramètres de requêtes par des références. Les paramètres ne seront remplacés qu'aux endroits où un paramètre ou une référence de colonne sont autorisés par la syntaxe. Pour un cas extrême, considerez cet exemple de mauvaise programmation :
INSERT INTO foo (foo) VALUES (foo);
La première occurrence de foo
doit être un nom de table, d'après
la syntaxe et ne sera donc pas remplacée, même si la fonction a une variable
nommée foo
. La deuxième occurrence doit être le nom d'une
colonne de la table et ne sera donc pas remplacée non plus. Seule la
troisième occurrence peuvent être une référence à la variable de la
fonction.
Les versions de PostgreSQL avant la 9.0 remplaçaient la variable dans les trois cas, donnant lieu à des erreurs de syntaxe.
Les noms de variables n'étant pas différents des noms de colonnes, d'après la syntaxe, il peut y avoir ambigüité dans les instructions qui font référence aux deux : un nom donné fait-il référence à un nom de colonne ou à une variable ? Modifions l'exemple précédent.
INSERT INTO dest (col) SELECT foo + bar FROM src;
Ici, dest
et src
doivent être des noms de table et
col
doit être une colonne de dest
mais foo
et bar
peuvent être aussi bien des variables de la fonction
que des colonnes de src
.
Par défaut, PL/pgSQL signalera une erreur si un nom dans une requête SQL peut faire référence à la fois à une variable et à une colonne. Vous pouvez corriger ce problème en renommant la variable ou colonne, en qualifiant la référence ambigüe ou en précisant à PL/pgSQL quelle est l'interprétation à privilégier.
Le choix le plus simple est de renommer la variable ou colonne.
Une règle de codage récurrente est d'utiliser une
convention de nommage différente pour les variables de
PL/pgSQL que pour les noms de colonne. Par exemple,
si vous utilisez toujours des variables de fonctions en
v_
tout en vous
assurant qu'aucun nom de colonne ne commence par quelquechose
v_
,
aucun conflit ne sera possible.
Autrement, vous pouvez qualifier les références ambigües pour les rendre plus claires.
Dans l'exemple ci-dessus, src.foo
serait une référence sans ambigüité
à une colonne de table. Pour créer une référence sans ambigüité à une variable,
déclarez-la dans un bloc nommé et utilisez le nom du bloc
(voir Section 43.2). Par exemple,
<<bloc>> DECLARE foo int; BEGIN foo := ...; INSERT INTO dest (col) SELECT bloc.foo + bar FROM src;
Ici, bloc.foo
désigne la variable même s'il existe une colonne
foo
dans la base src
. Les paramètres de fonction, ainsi que
les variables spéciales tel que FOUND
, peuvent être qualifiés par le
nom de la fonction, parce qu'ils sont implicitement déclarés dans un bloc extérieur
portant le nom de la fonction.
Quelque fois, il n'est pas envisageable de lever toutes les ambigüités dans une grande quantité de code PL/pgSQL. Dans ces cas-ci, vous pouvez spécifier à PL/pgSQL qu'il doit traiter les références ambigües comme étant une variable (ce qui est compatible avec le comportement de PL/pgSQL avant PostgreSQL 9.0) ou comme étant la colonne d'une table (ce qui est compatible avec d'autres systèmes tels que Oracle).
Pour modifier ce comportement dans toute l'instance, mettez le paramètre de
configuration plpgsql.variable_conflict
à l'un de
error
, use_variable
ou
use_column
(où error
est la valeur par défaut).
Ce paramètre agit sur les compilations posterieures
d'instructions dans les fonctions PL/pgSQL mais pas les instructions
déjà compilées dans la session en cours. Cette modification pouvant
affecter de manière inattendue le comportement des fonctions PL/pgSQL,
elle ne peut être faite que par un administrateur.
Vous pouvez modifier ce comportement fonction par fonction, en insérant l'une de ces commandes spéciales au début de la fonction :
#variable_conflict error #variable_conflict use_variable #variable_conflict use_column
Ces commandes n'agissent que sur les fonctions qui les contient et surchargent
la valeur de plpgsql.variable_conflict
. Un exemple est
CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$ #variable_conflict use_variable DECLARE curtime timestamp := now(); BEGIN UPDATE users SET last_modified = curtime, comment = comment WHERE users.id = id; END; $$ LANGUAGE plpgsql;
Dans la commande UPDATE
, curtime
, comment
,
et id
font référence aux variables et paramètres de la fonction,
que la table users
ait ou non des colonnes portant ces noms. Notez
qu'il a fallu qualifier la référence à users.id
dans la
clause WHERE
pour qu'elle fasse référence à la colonne.
Mais nous ne qualifions pas la référence à comment
comme cible dans la liste UPDATE
car, d'après la syntaxe,
elle doit être une colonne de users
. Nous pourrions écrire la même
fonction sans dépendre de la valeur de variable_conflict
de cette manière :
CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$ <<fn>> DECLARE curtime timestamp := now(); BEGIN UPDATE users SET last_modified = fn.curtime, comment = stamp_user.comment WHERE users.id = stamp_user.id; END; $$ LANGUAGE plpgsql;
La substitution de variable n'arrive pas dans la chaîne de commande donnée
à EXECUTE
ou une de ces variantes. Si vous avez
besoin d'insérer une valeur dans une telle commande, faites-le lors de la
construction d'une valeur de chaîne, illustrée dans la
Section 43.5.4, ou utilisez USING
.
La substitution de variable fonctionne seulement dans les commandes
SELECT
, INSERT
, UPDATE
et DELETE
parce que le moteur SQL principal autorise
les paramètres de la requête seulement dans ces commandes. Pour utiliser un
nom variable ou une valeur dans les autres types d'instructions (généralement
appelées des instructions utilitaires), vous devez construire l'instruction
en question comme une chaîne et l'exécuter via EXECUTE
.
L'interpréteur PL/pgSQL analyse le source d'une fonction et produit un arbre binaire interne d'instructions la première fois que la fonction est appelée (à l'intérieur de chaque session). L'arbre des instructions se traduit complètement par la structure d'instructions PL/pgSQL mais les expressions et les commandes SQL individuelles utilisées dans la fonction ne sont pas traduites immédiatement.
Au moment où chaque expression et commande SQL est
exécutée en premier lieu dans la fonction, l'interpréteur
PL/pgSQL lit et analyse la commande pour
créer une instruction préparée en
utilisant la fonction SPI_prepare
du gestionnaire
SPI.
Les appels suivants à cette expression ou commande réutilisent le plan
préparé. Donc, une fonction avec des chemins de code conditionnel peu
fréquemment exécutés n'auront jamais la surcharge de l'analyse de ces
commandes qui ne sont jamais exécutées à l'intérieur de la session en
cours. Un inconvénient
est que les erreurs dans une expression ou commande spécifique ne peuvent
pas être détectées avant que la fonction a atteint son exécution. (Les
erreurs de syntaxe triviales seront détectées à la première passe d'analyse
mais quelque chose de plus complexe ne sera pas détecté avant son
exécution.)
PL/pgSQL (ou plus exactement le gestionnaire SPI) peut tenter de mettre en cache le plan d'exécution associé à toute requête préparée. Si un plan en cache n'est pas utilisé, alors un nouveau plan d'exécution est généré pour chaque appel de la requête, et les valeurs actuelles du paramètre (autrement dit les valeurs de la variable PL/pgSQL) peuvent être utilisées pour optimiser le plan sélectionné. Si la requête n'a pas de paramètres ou est exécuté plusieurs fois, le gestionnaire SPI considérera la création d'un plan générique qui n'est pas dépendant des valeurs du paramètre et placera ce plan en cache pour le réutiliser. Habituellement, ceci survient seulement si le plan d'exécution n'est pas très sensible aux valeurs des variables PL/pgSQL référencées. Si ce n'est pas le cas, générer un nouveau plan à chaque fois est un gain net. Voir PREPARE pour plus d'informations sur le comportement des requêtes préparées.
Comme PL/pgSQL sauvegarde des instructions
préparées et quelques fois des plans d'exécution
de cette façon, les commandes SQL qui apparaissent directement dans une
fonction PL/pgSQL doivent faire référence aux
même tables et aux mêmes colonnes à chaque exécution ; c'est-à-dire
que vous ne pouvez pas utiliser un paramètre comme le nom d'une table ou
d'une colonne dans une commande SQL. Pour contourner cette restriction,
vous pouvez construire des commandes dynamiques en utilisant l'instruction
EXECUTE
de PL/pgSQL
-- au prix d'une nouvelle analyse du plan et de la construction d'un
nouveau plan d'exécution sur chaque exécution.
La nature muable des variables de type record présente un autre problème
dans cette connexion. Quand les champs d'une variable record sont utilisés
dans les expressions ou instructions, les types de données des champs
ne doivent pas modifier d'un appel de la fonction à un autre car chaque
expression sera analysée en utilisant le type de données qui est présent
quand l'expression est atteinte en premier. EXECUTE
peut
être utilisé pour contourner ce problème si nécessaire.
Si la même fonction est utilisée comme trigger pour plus d'une table,
PL/pgSQL prépare et met en cache les instructions
indépendamment pour chacune de ses tables -- c'est-à-dire qu'il y a
un cache pour chaque combinaison fonction trigger/table, pas
uniquement pour chaque fonction. Ceci diminue certains des problèmes avec
les types de données variables ; par exemple, une fonction trigger
pourra fonctionner correctement avec une colonne nommée
cle
même si cette colonne a différents types dans
différentes tables.
De la même façon, les fonctions ayant des types polymorphiques pour les arguments ont un cache séparé des instructions pour chaque combinaison des types d'argument réels avec lesquels elles ont été appelées, donc les différences de type de données ne causent pas d'échecs inattendus.
La mise en cache des instructions peut parfois avoir des effets surprenants sur l'interprétation des valeurs sensibles à l'heure. Par exemple, il y a une différence entre ce que font ces deux fonctions :
CREATE FUNCTION logfunc1(logtxt text) RETURNS void AS $$ BEGIN INSERT INTO logtable VALUES (logtxt, 'now'); END; $$ LANGUAGE plpgsql;
et :
CREATE FUNCTION logfunc2(logtxt text) RETURNS void AS $$ DECLARE curtime timestamp; BEGIN curtime := 'now'; INSERT INTO logtable VALUES (logtxt, curtime); END; $$ LANGUAGE plpgsql;
Dans le cas de logfunc1
, l'analyseur principal de
PostgreSQL sait lors de l'analyser du
INSERT
que la chaîne 'now'
devrait être interprétée comme un timestamp
car la
colonne cible de logtable
est de ce type. Du coup,
'now'
sera converti en une constante
timestamp
quand INSERT
est analysé, puis
utilisé dans tous les appels de logfunc1
tout au
long de la vie de la session. Il est inutile de dire que ce n'est pas ce
que voulait le développeur. Une meilleure idée reviendrait à utiliser la
fonction now()
ou current_timestamp
.
Dans le cas de logfunc2
, l'analyseur principal de
PostgreSQL ne connaît pas le type que deviendra
'now'
et, du coup, il renvoie une valeur de type
text
contenant la chaîne now
. Lors de
l'affectation à la variable curtime
locale, l'interpréteur
PL/pgSQL convertie cette chaîne dans le type
timestamp
en appelant les fonctions textout
et timestamp_in
pour la conversion. Du coup, l'heure
calculée est mise à jour à chaque exécution comme le suppose le développeur.
Même s'il arrive que ça fonctionne ainsi, ce n'est pas très efficace, donc
l'utilisation de la fonction now()
sera encore une fois
une meilleur idée.