Chapitre 37. PL/pgSQL - SQL Procedural Language

Table des matières
37.1. Survol
37.1.1. Avantages de l'Utilisation de PL/pgSQL
37.1.2. Arguments supportés et types de données résultats
37.2. Astuces pour Développer en PL/pgSQL
37.3. Structure de PL/pgSQL
37.4. Déclarations
37.4.1. Alias de Paramètres de Fonctions
37.4.2. Copie de types
37.4.3. Types ligne
37.4.4. Types Record
37.4.5. RENAME
37.5. Expressions
37.6. Instructions de base
37.6.1. Assignation
37.6.2. SELECT INTO
37.6.3. Exécuter une expression ou requête sans résultat
37.6.4. Exécuter des Commandes Dynamiques
37.6.5. Obtention du Statut du Résultat
37.7. Structures de contrôle
37.7.1. Retour d'une fonction
37.7.2. Contrôles Conditionnels
37.7.3. Boucles simples
37.7.4. Boucler dans les résultats de requêtes
37.8. Curseurs
37.8.1. Déclaration de variables curseur
37.8.2. Ouverture De Curseurs
37.8.3. Utilisation des Curseurs
37.9. Erreurs et Messages
37.10. Procédures Déclencheur
37.11. Portage d'Oracle PL/SQL
37.11.1. Exemples de Portages
37.11.2. Autres choses à surveiller
37.11.3. Annexe

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

37.1. 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 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

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() 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 my_function(), populate() ne sera plus en mesure de trouver my_function(). Vous auriez alors à recréer 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 problè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.

Note : 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'utilisateur, 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.

37.1.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 de procédures, 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.

37.1.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 (voir la Section 7.2.1.4).

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.1). Voir l'exemple de la Section 37.4.1.

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.