Documentation PostgreSQL 7.4.29 | ||||
---|---|---|---|---|
Précédent | Arrière rapide | Chapitre 37. PL/pgSQL - SQL Procedural Language | Avance rapide | Suivant |
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.
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;
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;
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);
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;
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é 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.
Précédent | Sommaire | Suivant |
Expressions | Niveau supérieur | Structures de contrôle |