PL/pgSQL est un langage de procédures 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 de procédures chargeable qui
est utilisé pour créer des fonctions standards et triggers,
ajoute des structures de contrôle au langage SQL,
permet d'effectuer des traitements complexes,
hérite de tous les types, fonctions et opérateurs définis par les utilisateurs,
est défini comme digne de confiance par le serveur,
est facile à utiliser.
Exception faites des conversions d'entrées/sorties et des fonctions de traitement pour les types définis par l'utilisateur, tout ce qui peut être défini dans les fonctions en 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, de les utiliser pour définir des opérateurs ou de les utiliser dans des expressions d'index.
Le gestionnaire d'appel PL/pgSQL découpe le texte source de la fonction et produit un arbre d'instructions binaires internes au premier appel de la fonction (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 réutilisent 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 de vie de la connexion à la base de données. 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ée 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
Langages de procédures
CREATE FUNCTION remplit() RETURNS integer AS $$
DECLARE
-- declarations
BEGIN
PERFORM ma_fonction();
END;
$$ LANGUAGE plpgsql;
Si vous exécutez la fonction ci-dessus, l'OID de ma_fonction() sera référencé dans le plan d'exécution produit pour l'expression PERFORM. Par la suite, si vous détruisez et recréez ma_fonction(), remplit() ne sera plus en mesure de trouver ma_fonction(). Vous auriez alors à recréer remplit(), 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 problème est d'utiliser CREATE OR REPLACE FUNCTION lors de la mise à jour de la définition de ma_fonction (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.
![[Note]](images/note.png)
L'expression PL/pgSQL EXECUTE n'a pas de rapport avec l'instruction SQL 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).
SQL est le langage que PostgreSQL™ et la plupart des autres bases de données relationnelles utilisent 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 et traiter les résultats, faire quelques calculs, et enfin envoyer d'autres requêtes au serveur. Tout ceci induit des communications interprocessus et induit aussi 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 de procédures, mais avec de gros gains car il n'y a plus la surcharge nécessaire à la communication client/serveur.
Élimination des allers/retours entre le client et le serveur
Il n'est pas nécessaire de traiter ou transférer entre le client et le serveur les résultats intermédiaires dont le client n'a pas besoin
Il n'est pas nécessaire de s'occuper du va-et-vient des analyses de requêtes
Ceci peut permettre une augmentation considérable des performances en comparaison à une application qui n'utilise pas les procédures stockées.
Ainsi, avec PL/pgSQL vous pouvez utiliser tous les types de données, opérateurs et fonctions du SQL.
Les fonctions écrites en PL/pgSQL peuvent accepter en 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 (voir la Section 7.2.1.4, « Fonctions de table »).
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 (voir la Section 33.2.5, « Types et fonctions polymorphes »). Voir l'exemple de la Section 37.4.1, « Alias de paramètres de fonctions ».
Les fonctions PL/pgSQL peuvent aussi être déclarées comme devant renvoyer un « ensemble » 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.
Les fonctions PL/pgSQL peuvent aussi être déclarées avec des paramètres en sortie à la place de la spécification explicite du code de retour. Ceci n'ajoute pas de fonctionnalité fondamentale au langage mais c'est un moyen agréable principalement pour renvoyer plusieurs valeurs.
Des exemples spécifiques apparaissent dans la Section 37.4.1, « Alias de paramètres de fonctions » et la Section 37.7.1, « Retour d'une fonction ».