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

Chapitre 55  PL/pgSQL : instructions de base

Dans cette section ainsi que les suivantes, nous décrirons tous les types d'instructions explicitement compris par PL/pgSQL. Tout ce qui n'est pas reconnu comme l'un de ces types d'instruction est présumé être une commande SQL et est envoyé au moteur principal de bases de données pour être exécutée (après substitution de chaque variable PL/pgSQL utilisée dans l'instruction). Ainsi, par exemple, les commandes SQL INSERT, UPDATE, et DELETE peuvent être considérées comme des instructions de PL/pgSQL, mais ne sont pas spécifiquement listées ici.

55.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 un 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ésulat 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 :
user_id := 20;
tax := subtotal * 0.06;

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

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, ou 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.

Si la requête ne renvoit aucune ligne, des valeurs null sont assignées au(x) cibles(s). Si la requête renvoit 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 apres le mot clé SELECT comme décrit plus haut. Les version futures de PL/pgSQL pourront être moins laxistes sur le placement de la clause INTO.

Vous pouvez utiliser FOUND immédiatement après une instruction SELECT INTO pour déterminer si l'assignation a réussi (c'est à dire qu'au moins une ligne a été renvoyée par la requête). Par exemple :
SELECT INTO myrec * FROM emp WHERE empname = myname;
IF NOT FOUND THEN
    RAISE EXCEPTION ''employee % not found'', myname;
END IF;
Pour tester si un résultat record/ligne est null, vous pouvez utiliser la conditionelle 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
    users_rec RECORD;
    full_name varchar;
BEGIN
    SELECT INTO users_rec * FROM users WHERE user_id=3;

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

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

Quelquefois l'on souhaite évaluer une expression ou une requête mais rejeter le résultat (généralement parce que l'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, qui doit être une instruction SELECT, et rejette le résultat. Les variables PL/pgSQL sont normalement remplacées dans la requête. 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.

Remarque.
On pourrait s'attendre à ce qu'un SELECT sans clause INTO aboutisse à ce résultat, mais en réalité la seule façon acceptée de faire cela est PERFORM.
Un exemple :
PERFORM create_mv(''cs_session_page_requests_mv'', my_query);

55.4  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 planification des commandes ne marcheront 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.

Lorsque vous travaillez avec des commandes dynamiques vous avez à faire face à l'échappement des guillemets simples dans PL/pgSQL. Référez vous au survol dans 51.3, qui peut vous épargner quelque effort.

À 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 variables tables ou colonnes.

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. Ainsi, la seule façon d'extraire le résultat d'un SELECT créé dynamiquement est d'utiliser la forme FOR-IN-EXECUTE décrite plus loin.

Exemple :
EXECUTE ''UPDATE tbl SET ''
        || quote_ident(colname)
        || '' = ''
        || quote_literal(newvalue)
        || '' WHERE ...'';
Cet exemple montre l'usage des fonctions quote_ident(text) et quote_literal(text). Les variables contenant les identifiants de colonne et de table devraient être passées à la fonction quote_ident. Les variables contenant les valeurs qui devraient être des chaînes de caractères dans la commande construite devraient être passées à quote_literal. Ces deux fonctions effectuent les traitements appropriés pour renvoyer le texte entré enfermé entre doubles ou simples guillemets respectivement, chaque caractère spécial correctement échappé.

Voici un exemple beaucoup plus important de commande dynamique et d'utilisation d'EXECUTE :
CREATE FUNCTION cs_update_referrer_type_proc() RETURNS integer AS '
DECLARE
    referrer_keys RECORD;  --déclare un record générique à utiliser dans un FOR
    a_output varchar(4000);
BEGIN
   a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar,varchar,varchar)
                  RETURNS varchar AS ''''
                     DECLARE
                         v_host ALIAS FOR $1;
                         v_domain ALIAS FOR $2;
                         v_url ALIAS FOR $3;
                     BEGIN '';

   -- Notez comment nous scannons les résultats d'une requête dans une boucle
   -- FOR en utilisant la construction FOR <record>.

   FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP
       a_output := a_output || '' IF v_'' || referrer_keys.kind
               || '' LIKE '''''''''' || referrer_keys.key_string
               || '''''''''' THEN RETURN ''''''
               || referrer_keys.referrer_type || ''''''; END IF;'';
   END LOOP;

   a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE plpgsql;'';

   EXECUTE a_output;
END;
' LANGUAGE plpgsql;

55.5  Obtention du statut du résultat

Il y a plusieurs moyens 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 = item [ , ... ] ;
Cette commande permet la récupération des indicateurs de l'état du système. Chaque item 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ées 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, false 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é 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 ; chaque changement qui y est fait n'affecte que la fonction PL/pgSQL courante.

D.Gonzalez (gonzalez@univ-lille3.fr) en date du th , 

Previous Up Next