35.6. Instructions de base

35.6.1. Assignation

L'assignation d'une valeur à une variable ou à un champ row/record est écrite ainsi :

identifiant := expression;

Comme expliqué plus haut, l'expression dans une telle instruction est évaluée au moyen de la commande SQL SELECT envoyée au moteur principal de bases de données. L'expression ne doit manier qu'une seule valeur.

Si le type de données du résultat de l'expression ne correspond pas au type de donnée de la variable, ou que la variable a une taille ou une précision (comme char(20)), la valeur résultat sera implicitement convertie par l'interpréteur PL/pgSQL en utilisant la fonction d'écriture (output-function) du type du résultat, et la fonction d'entrée (input-function) du type de la variable. Notez que cela pourrait potentiellement conduire à des erreurs d'exécution générées par la fonction d'entrée si la forme de la chaîne de la valeur résultat n'est pas acceptable par la fonction d'entrée.

Exemples :

id_utilisateur := 20;
tax := sous_total * 0.06;

35.6.2. SELECT INTO

Le résultat d'une commande SELECT manipulant plusieurs colonnes (mais une seule ligne) peut être assignée à une variable de type record ou ligne ou une liste de valeurs scalaires. Ceci est fait via :

SELECT INTO cible expressions FROM ...;

où cible peut être une variable record, une variable ligne, ou une liste, séparées de virgules, de simples variables de champs record/ligne. L'expression select_expressions et le reste de la commande sont identiques à du SQL standard.

Notez que cela est assez différent de l'interprétation normale par PostgreSQL de SELECT INTO, où la cible de INTO est une table nouvellement créée. Si vous voulez créer une table à partir du résultat d'un SELECT d'une fonction PL/pgSQL, utilisez la syntaxe CREATE TABLE ... AS SELECT.

Si une ligne ou une liste de variable est utilisée comme cible, les valeurs sélectionnées doivent correspondre exactement à la structure de la cible, sinon une erreur d'exécution se produira. Quand une variable record est la cible, elle se configure seule automatiquement au type ligne formé par les colonnes résultant de la requête.

À l'exception de la clause INTO, l'instruction SELECT est identique à la commande SQL SELECT normale et peut en utiliser toute la puissance.

La clause INTO peut apparaître pratiquement partout dans l'instruction SELECT. De façon personnalisé, il est écrit soit juste après SELECT comme indiqué ci-dessus soit juste avant FROM — c'est-à-dire soit juste avant soit juste après la liste de select_expressions.

Si la requête ne renvoie aucune ligne, des valeurs NULL sont assignées au(x) cibles(s). Si la requête renvoie plusieurs lignes, la première ligne est assignées au(x) cible(s) et le reste est rejeté (notez que << la première ligne >> n'est pas correctement définie à moins d'utiliser ORDER BY).

Actuellement, la clause INTO peut apparaître presque n'importe où dans l'instruction SELECT mais il est recommandé de la placer immédiatement après le mot clé SELECT comme décrit plus haut. Les versions futures de PL/pgSQL pourront être moins laxistes sur le placement de la clause INTO.

Vous pouvez vérifier la variable spéciale FOUND (voir la Section 35.6.6) après une instruction SELECT INTO pour déterminer si l'affectation est réussie, c'est-à-dire si au moins une ligne a été renvoyée par la requête. Par exemple :

SELECT INTO mon_enreg * FROM emp WHERE nomemp = mon_nom;
IF NOT FOUND THEN
    RAISE EXCEPTION 'employé % non trouvé', mon_nom;
END IF;

Pour tester si un résultat record/ligne est NULL, vous pouvez utiliser la conditionnelle IS NULL. Il n'y a cependant aucun moyen de dire si une ou plusieurs lignes additionnelles ont été rejetées. Voici un exemple qui traite le cas où aucune ligne n'a été renvoyée.

DECLARE
    enreg_utilisateurs RECORD;
BEGIN
    SELECT INTO enreg_utilisateurs * FROM utilisateurs WHERE id_utilisateur=3;

    IF enreg_utilisateurs.accueil IS NULL THEN
        -- l'utilisateur n'a entré aucune page, renvoyer "http://"
        RETURN 'http://';
    END IF;
END;

35.6.3. Exécuter une expression ou requête sans résultat

Quelque fois, on souhaite évaluer une expression ou une requête mais rejeter le résultat (généralement parce qu'on appelle une fonction qui a des effets de bords utiles mais pas de résultat utile). Pour ce faire dans PL/pgSQL, utilisez l'instruction PERFORM :

PERFORM requête;

Ceci exécute requête et annule le résultat. Écrivez requête de la même façon que vous le feriez dans une commande SQL SELECT mais remplacez le mot clé initial SELECT par PERFORM. Les variables PL/pgSQL seront substituées dans la requête comme d'habitude. Par ailleurs, la variable spéciale FOUND est positionnée à true si la requête produit au moins une ligne ou false si elle n'en produit aucune.

Un exemple :

PERFORM create_mv('cs_session_page_requests_mv', my_query);

35.6.4. Ne rien faire du tout

Quelque fois, une instruction qui ne fait rien est utile. Par exemple, il indique qu'une partie de la chaîne if/then/else est délibérément vide. Pour cela, utilisez l'instruction :

         NULL;
       

Par exemple, les deux fragments de code suivants sont équivalents :

         BEGIN
         y := x / 0;
         EXCEPTION
         WHEN division_by_zero THEN
         NULL;  -- ignore l'erreur
         END;
       

         BEGIN
         y := x / 0;
         EXCEPTION
	 WHEN division_by_zero THEN  -- ignore l'erreur
         END;
       

Ce qui est préférable est une histoire de goût.

35.6.5. Exécuter des Commandes Dynamiques

Souvent vous voudrez générer des commandes dynamiques dans vos fonctions PL/pgSQL, c'est-à-dire des commandes qui impliquent différentes tables ou différents types de données à chaque fois qu'elles sont exécutées. Les tentatives normales de PL/pgSQL pour garder en cache les planifications des commandes ne fonctionneront pas dans de tels scénarios. Pour gérer ce type de problème, l'instruction EXECUTE est fournie :

EXECUTE chaîne-commande;

où chaîne-commande est une expression manipulant une chaîne (de type text) contenant la commande à être exécutée. Cette chaîne est littéralement donnée à manger au moteur SQL.

Notez en particulier qu'aucune substitution de variable PL/pgSQL n'est faite sur la chaîne-commande. Les valeurs des variables doivent être insérées dans la chaîne de commande lors de sa construction.

A la différence de toutes les autres commandes dans PL/pgSQL, une commande lancée par une instruction EXECUTE n'est pas préparée ni sauvée une seule fois pendant la durée de la session. À la place, la commande est préparée à chaque fois que l'instruction est lancée. La chaîne commande peut être dynamiquement créée à l'intérieur de la fonction pour agir sur des tables ou colonnes différentes.

Les résultats des commandes SELECT sont rejetés par EXECUTE, et SELECT INTO n'est pas actuellement géré à l'intérieur d'une instruction EXECUTE. Donc, il n'existe aucune façon d'extraire un résultat à partir d'un SELECT créé dynamiquement en utilisant la commande EXECUTE standard. Néanmoins, il existe deux autres façons de le faire : la première est d'utiliser FOR-IN-EXECUTE décrite dans Section 35.7.4, et la deuxième est d'utiliser un curseur avec OPEN-FOR-EXECUTE, comme décrit dans Section 35.8.2.

En travaillant avec des commandes dynamiques, vous aurez souvent à gérer des échappements de guillemets simples. La méthode recommandée pour mettre entre guillemets un texte fixe dans le corps de votre fonction est d'utiliser les guillemets dollar (si votre code n'utilise pas les guillemets dollar, référez-vous à l'aperçu dans la Section 35.2.1, ce qui peut vous faire gagner des efforts lors du passage de ce code à un schéma plus raisonnable).

Les valeurs dynamiques qui sont à insérer dans la requête construite requièrent une gestion spéciale car elles pourraient elles-même contenir des guillemets. Un exemple (ceci suppose que vous utilisez les guillemets dollar pour la fonction dans sa globalité, du coup les guillemets n'ont pas besoin d'être doublés) :

EXECUTE 'UPDATE tbl SET '
    || quote_ident(colname)
    || ' = '
    || quote_literal(newvalue)
    || ' WHERE key = '
    || quote_literal(keyvalue);
    

Cet exemple montre l'utilisation des fonctions quote_ident(text) et quote_literal(text). Pour plus de sûreté, les variables contenant les identifiants des colonnes et des tables doivent être passés à la fonction quote_ident. Les variables contenant les valeurs devant être des chaînes dans la commande construite devraient être passées à quote_literal. Les deux font les étapes appropriées pour renvoyer le texte en entrée entouré par des guillemets doubles ou simples respectivement, avec tout caractère intégré spécial proprement échappé.

Notez que les guillemets dollar sont souvent utiles pour placer un texte fixe entre guillemets. Il serait une très mauvaise idée d'essayer de faire l'exemple ci-dessus de cette façon

    EXECUTE 'UPDATE tbl SET '
    || quote_ident(colname)
    || ' = $$'
    || newvalue
    || '$$ WHERE key = '
    || quote_literal(keyvalue);
    

car cela casserait si le contenu de newvalue pouvait contenir $$. La même objection s'appliquerait à tout délimiteur dollar que vous pourriez choisir. Donc, pour mettre un texte inconnu entre guillemets de façon sûr, vous devez utiliser quote_literal.

Un exemple bien plus important d'une commande dynamique et d'EXECUTE est disponible dans l'Exemple 35-6, qui construit et exécute une commande CREATE FUNCTION pour définir une nouvelle fonction.

35.6.6. Obtention du Statut du Résultat

Il y a plusieurs moyen de déterminer l'effet d'une commande. La première méthode est d'utiliser GET DIAGNOSTICS, qui a la forme suivante :

GET DIAGNOSTICS variable = élément [ , ... ] ;

Cette commande permet la récupération des indicateurs de l'état du système. Chaque élément est un mot clé identifiant une valeur d'état devant être assignée à la variable indiquée (qui devrait être du bon type de donnée pour pouvoir la recevoir.) Les items d'état actuellement disponibles sont ROW_COUNT, le nombre de lignes traitées par la dernière commande SQL envoyée au moteur SQL, et RESULT_OID, l'OID de la dernière ligne insérée par la commande SQL la plus récente. Notez que RESULT_OID n'est utile qu'après une commande INSERT.

Exemple :

GET DIAGNOSTICS var_integer = ROW_COUNT;

La seconde méthode pour déterminer les effets d'une commande est la variable spéciale nommée FOUND de type boolean. FOUND commence par être false dans chaque fonction PL/pgSQL. Elle est positionnée par chacune des types d'instructions suivants.

• Une instruction SELECT INTO positionne FOUND à true si elle renvoie une ligne, false si aucune ligne n'est renvoyée.

• Une instruction PERFORM positionne FOUND à true si elle produit (rejette) une ligne, faux si aucune ligne n'est produite.

• Les instructions UPDATE, INSERT, et DELETE positionnent FOUND à true si au moins une ligne est affectée, false si aucune ligne n'est affectée.

• Une instruction FETCH positionne FOUND à true si elle renvoie une ligne, false si aucune ligne n'est renvoyée.

• La commande FOR positionne FOUND à true si elle effectue une itération une ou plusieurs fois, sinon elle renvoie false. Ceci s'applique aux trois variantes de l'instruction FOR (boucles FOR integer, FOR record-set, et FOR record-set dynamique). FOUND n'est positionné de cette façon que quand la boucle FOR s'achève ; dans l'exécution de la chaîne, FOUND n'est pas modifiée par l'instruction FOR, bien qu'il puisse être modifié par l'exécution d'autres instructions situées dans le corps de la boucle.

FOUND est une variable locale à l'intérieur de chaque fonction PL/pgSQL ; chaque changement qui y est fait n'affecte que la fonction courante.