36.6. Instructions de base

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.

À 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 36.6.6, « Obtention du statut du résultat ») 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;

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 creer_vuemat('cs_session_page_requests_mv', ma_requete);

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.

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 [ INTO cible ];

où chaîne-commande est une expression manipulant une chaîne (de type text) contenant la commande à être exécutée et cible est une variable record ou ligne ou une liste de variables simples ou de champs de lignes/enregistrements séparées par des virgules.

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.

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

La clause INTO spécifie où les résultats d'une commande SELECT devraient être affectés. Si une ligne ou une liste de variable est fournie, elle doit correspondre exactement à la structure des résultats produits par le SELECT (quand une variable de type record est utilisée, elle se configurera toute seule pour correspondre à la structure du résultat). Si plusieurs lignes sont renvoyées, alors seule la première sera assignée à la variable INTO. Si aucune ligne n'est renvoyée, NULL est affectée à la variable INTO. Si aucune clause INTO n'est spécifiée, les résultats d'une commande SELECT sont annulées.

SELECT INTO n'est actuellement pas supporté à l'intérieur de EXECUTE.

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 36.2.1, « Utilisation des guillemets simples (quotes) », 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(nom_colonne)
    || ' = '
    || quote_literal(nouvelle_valeur)
    || ' WHERE cle = '
    || quote_literal(valeur_cle);

Cet exemple démontre l'utilisation des fonctions quote_ident et quote_literal. Pour plus de sûreté, les expressions contenant les identifiants des colonnes et des tables doivent être passés à la fonction quote_ident. Les expressions 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 36.6, « Portage d'une fonction qui crée une autre fonction de PL/SQL vers PL/pgSQL », qui construit et exécute une commande CREATE FUNCTION pour définir une nouvelle fonction.

Il y a plusieurs moyens pour déterminer l'effet d'une commande. La première méthode est d'utiliser GET DIAGNOSTICS :

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

Cette commande permet la récupération des indicateurs d'état du système. Chaque élément est un mot clé identifiant une valeur d'état devant être affectée à la variable indiquée (qui doit être du bon type de donnée pour que l'affectation puisse se faire sans erreur.) Les éléments 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 dans une table contenant des OID.

Exemple :

GET DIAGNOSTICS var_entier = ROW_COUNT;

La seconde méthode permettant de déterminer les effets d'une commande est la variable spéciale nommée FOUND de type boolean. La variable FOUND est initialisée à false au début de chaque fonction PL/pgSQL. Elle est positionnée par chacun des types d'instructions suivants :

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