Documentation PostgreSQL 7.4.29
Précédent	Arrière rapide	Chapitre 37. PL/pgSQL - SQL Procedural Language	Avance rapide	Suivant

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

37.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;

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

A 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 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 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 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
    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;

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

Note : 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);

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

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 Section 37.2.1, qui peut vous épargner quelque effort.

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. A 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. Il existe deux façons d'extraire un résultat d'un SELECT créé dynamiquement : la première est d'utiliser la boucle FOR-IN-EXECUTE décrite dans Section 37.7.4, et la deuxième est d'utiliser un curseur avec OPEN-FOR-EXECUTE, comme décrit dans Section 37.8.2.

Exemple :

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

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

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;

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