Previous Up Next
Université Lille 3-Charles de GaulleUFR de Mathématiques Sciences Économiques et Sociales

Chapitre 50  PL/pgSQL : présentation

50.1  Origine

Les pages qui concernent PL/pgSQL sont fortement inspirées1 du chapitre qui lui est consacré dans une traduction de la documentation officielle qu'on peut trouver à cette adresse :
http://pgsql-fr.tuxfamily.org/pgsql-fr/plpgsql.html
Je n'ai fait qu'en modifier la présentation.

50.2  PL/pgSQL ?

PL/pgSQL est un langage procédural chargeable pour le système de bases de données PostgreSQL. Les objectifs de la conception de PL/pgSQL ont été de créer un langage procédural chargeable qui

50.3  Installer PL/pgSQL

Pour être utilisé, PL/pgSQL doit être installé. Cela peut se faire au niveau du serveur (en fait dans la base template1), et il sera automatiquement utilisable dans les tables créées ensuite.

Si ce n'est pas le cas :
CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
      '$libdir/plpgsql' LANGUAGE C;
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
      HANDLER plpgsql_call_handler;
La première commande dit au serveur de bases de données où trouver les informations nécessaires pour manipuler les appels de fonctions pour PL/pgSQL.

La deuxième commande définit l'objet précedemment déclaré comme devant manipuler des objets dont l'attribut langage sera plpgsql.

50.4  Survol

Le gestionnaire d'appel PL/pgSQL découpe le texte source de la fonction et produit un arbre d'instructions binaires internes la première fois que la fonction est appelée (au sein de chaque session). L'arbre d'instructions traduit complètement la structure de l'expression PL/pgSQL, mais les expressions SQL individuelles et les commandes SQL utilisées dans la fonction ne sont pas traduites immédiatement.

Chaque expression et commande SQL étant d'abord utilisée dans la fonction, l'interpréteur PL/pgSQL crée un plan d'exécution élaboré (en utilisant les fonctions SPI_prepare et SPI_saveplan du gestionnaire SPI). Les visites suivantes à cette expression ou commande reutilisent le plan élaboré. Ainsi, une fonction avec du code conditionnel qui contient de nombreuses expressions pour lesquelles des plans d'exécution pourraient être nécessaires ne feront que préparer et sauvegarder ces plans, qui ne sont réellement utilisés que durant le temps que la connexion à la base de données vivra. Ceci peut réduire substantiellement le temps total nécessaire à l'analyse syntaxique, et générer des plans d'exécution pour les expressions d'une fonction PL/pgSQL. Un inconvénient est que les erreurs d'une expression ou commande particulière peuvent ne pas être détectées jusqu'à ce que cette partie de la fonction soit atteinte au cours de l'exécution.

Une fois que PL/pgSQL a créé un plan d'exécution pour une commande de fonction particulière, il réutilisera ce plan pour le temps que durera la connexion à la base de données. C'est généralement un gain de performances, mais cela peut causer quelques problèmes si vous modifiez dynamiquement votre schéma de base de données. Par exemple :
CREATE FUNCTION populate() RETURNS integer AS '
DECLARE
    -- declarations
BEGIN
    PERFORM my_function();
END;
' LANGUAGE plpgsql;
Si vous exécutez la fonction ci-dessus, l'OID de my_function() référencé dans le plan d'exécution est produit pour l'expression PERFORM. Par la suite, si vous détruisez et recréez my_function(), populate() ne sera plus en mesure de trouver my_function(). Vous auriez alors à recréer la fonction populate(), ou au moins à lancer une nouvelle connexion à la base de donnée pour faire en sorte de la compiler à nouveau. Un autre moyen d'éviter ce probème est d'utiliser CREATE OR REPLACE FUNCTION lors de la mise à jour de la définition de my_function (quand une fonction est "remplacée", son OID n'est pas changé).

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 se référer aux mêmes tables et colonnes pour chaque exception ; en fait, vous ne pouvez pas utiliser un paramètre tel que 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'expression PL/pgSQL EXECUTE (au prix de la construction d'un nouveau plan d'exécution pour chaque exécution).

Remarque.
L'expression PL/pgSQL EXECUTE n'a pas de rapport avec l'expression EXECUTE supportée par le serveur PostgreSQL. L'expression EXECUTE du serveur ne peut pas être utilisée au sein des fonctions PL/pgSQL (et n'est pas nécessaire).
Exception faites des conversions d'entrées/sorties et des fonctions de traitement pour des types définis par l'utlisateur, tout ce qui peut être défini dans les fonctions du langage C peut aussi être fait avec PL/pgSQL. Par exemple il est possible de créer des fonctions de traitement conditionnel complexes et par la suite les utiliser pour définir des opérateurs ou les utiliser dans des expressions d'index.

50.4.1  Avantages de l'utilisation de PL/pgSQL

SQL est le langage que PostgreSQL (et la plupart des autres bases de données relationnelles) utilise comme langage de requête. Il est portable et facile à apprendre. Mais chaque expression SQL doit être exécutée individuellement par le serveur de bases de données.

Cela signifie que votre application client doit envoyer chaque requête au serveur de bases de données, attendre que celui-ci la traite, recevoir les résultats, faire quelques traitements, et enfin envoyer d'autres requêtes au serveur. Tout ceci induit des communications interprocessus et peut aussi induire une surcharge du réseau si votre client est sur une machine différente du serveur de bases de données.

Grâce à PL/pgSQL vous pouvez grouper un bloc de traitement et une série de requêtes au sein du serveur de bases de données, et bénéficier ainsi de la puissance d'un langage procédural, tout en gagnant du temps puisque vous évitez toute la charge de la communication client/serveur. Ceci peut permettre un gain de performances considérable.

Ainsi, avec PL/pgSQL vous pouvez utiliser tous les types de données, opérateurs et fonctions du SQL.

50.4.2  Arguments supportés et types de données résultats

Les fonctions écrites en PL/pgSQL peuvent accepter comme argument n'importe quel type de données supporté par le serveur, et peuvent renvoyer un résultat de n'importe lequel de ces types. Elles peuvent aussi accepter ou renvoyer n'importe quel type composite (type ligne) spécifié par nom. Il est aussi possible de déclarer une fonction PL/pgSQL renvoyant un type record, signifiant que le résultat est un type ligne dont les colonnes sont déterminées par spécification dans la requête appelante.

Les fonctions PL/pgSQL peuvent aussi être déclarées comme acceptant et renvoyant les types « polymorphes », anyelement et anyarray. Le type de données réel géré par une fonction polymorphe peut varier d'appel en appel.

Les fonctions PL/pgSQL peuvent aussi être déclarées comme devant renvoyer un « set » ou une table de n'importe lequel des type de données dont elles peuvent renvoyer une instance unique. De telles fonctions génèrent leur sortie en exécutant RETURN NEXT pour chaque élément désiré de l'ensemble résultat.

Enfin, une fonction PL/pgSQL peut être déclarée comme renvoyant void si elle n'a pas de valeur de retour utile.


1
C'est une manière hypocrite de ne pas dire « intégralement recopiées »...
D.Gonzalez (gonzalez@univ-lille3.fr) en date du th , 

Previous Up Next