39.10. Les dessous 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 noms de variables n'étant pas différents des noms de colonnes, d'après la syntaxe, il peut y avoir ambuiguité 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éfait, 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'interpretation à 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_quelquechose tout en vous assurant qu'aucun nom de colonne ne commence par 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 amigüité à une colonne de table. Pour créer une référence sans amigüité à une variable, déclarez-la dans un bloc nommé et utilisez le nom du bloc (voir Section 39.2, « Structure de PL/pgSQL »). 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 exterieur 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. Pour fixer ce paramètre avant le chargement de PL/pgSQL, vous devez ajouter « plpgsql » à la liste custom_variable_classes dans postgresql.conf. Cette modification pouvant affecter de manière inattendue le comportement des fonctions PL/pgSQL, elle ne peut être faïte 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 39.5.4, « Exécuter des commandes dynamiques », 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 crée un plan d'exécution préparée (en utilisant les fonctions SPI_prepare et SPI_saveplan du gestionnaire SPI ). Les appels suivants à cette expression ou commande réutilisent le plan préparé. Du coup, une fonction avec du code conditionnel contenant de nombreuses instructions pour lesquels les plans d'exécutions pourraient être requis vont seulement préparer et sauvegarder ces plans qui sont utilisés tout au long de la durée de vie de la connexion à la base de données. Ceci peut réduire substantiellement le temps total requis pour analyser et générer les plans d'exécution pour les instructions d'une fonction PL/pgSQL. 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.)

Un plan sauvegardé sera regénéré automatiquement s'il y a des changements dans le schéma de n'importe quelle table utilisée dans la requête ou si une fonction utilisée dans la requête et définie par un utilisateur est redefinie Ceci rend la re-utilisation d'un plan préparé transparent dans la plupart des cas mais il existe des cas particuliers ou un plan obsolète peut être re-utilisé. Par exemple, la suppression et la re-création d'un opérateur defini par un utilisateur n'affectera pas les plans déjà en cache; ils continueront d'appeler la fonction sous-jacente de l'opérateur d'origine, si celle-ci n'a pas été modifiée. Lorsque c'est nécessaure, le cache peut être vidé en commençant une nouvelle session.

Comme PL/pgSQL sauvegarde les 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 de la construction d'un nouveau plan d'exécution à chaque exécution.

Un autre point important est que les plans préparés utilisent des paramètres pour permettre le changement des valeurs des variables PL/pgSQL entre chaque exécution, comme indiqué en détail ci-dessus. Quelque fois, cela signifie qu'un plan est moins efficace que s'il avait été généré avec une valeur spécifique. Comme exemple, regardez :

SELECT * INTO mon_enregistrement FROM dictionnaire WHERE mot LIKE terme_recherche;

où terme_recherche est une variable PL/pgSQL. Le plan caché pour cette requête n'utilisera jamais un index sur une variable. Le plan caché pour cette requête n'utilisera jamais un index sur un mot car le planificateur ne peut pas savoir si le modèle LIKE comprendra une ancre à gauche à l'exécution. Pour utiliser un index, la requête doit être planifiée avec un modèle spécifique pour LIKE. C'est un autre cas où EXECUTE peut être utilisé pour forcer la génération d'un nouveau plan à 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 planifié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 plans indépendament 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 plans pour chaque combinaison des types d'argument réél 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 du plan 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 que, au moment de la préparation du plan pour INSERT, la chaîne 'now' devra être interprétée comme une valeur de type timestamp car la colonne cible de logtable est de ce type. Du coup, 'now' sera converti en une constante quand INSERT est planifié, puis utilisé dans tous les appels à logfunc1 lors de la durée de vie de la session. Il n'est pas nécessaire de préciser que ce n'est pas le souhait du développeur.

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 text_out et timestamp_in pour la conversion. Du coup, l'heure calculée est mise à jour à chaque exécution comme le suppose le développeur.