38.5. Instructions de base

L'affectation d'une valeur à une variable PL/pgSQL ou à un champ row/record s'écrit ainsi :

variable := expression;

Comme expliqué plus haut, l'expression dans cette 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 peut 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 pour cette fonction.

Exemples :

taxe := sous_total * 0.06;
mon_enregistrement.id_utilisateur := 20;

Pour toute commande SQL qui ne renvoie pas de lignes, par exemple INSERT sans clause RETURNING, vous pouvez exécuter la commande à l'intérieur d'une fonction PL/pgSQL rien qu'en écrivant la commande.

Tout nom de variable PL/pgSQL apparaissant dans le texte de la commande est remplacé par un symbole de paramètre, puis la valeur actuelle de la variable est fournie comme valeur du paramètre à l'exécution. C'est le traitement exact décrit précédemment pour les expressions. Pour les détails, voir la Section 38.10.1, « Substitution de variables ». Par exemple, si vous écrivez

DECLARE
    cle TEXT;
    delta INTEGER;
BEGIN
    ...
    UPDATE matable SET val = val + delta WHERE id = cle;

le texte de la commande envoyée par le moteur SQL ressemble à ceci :

    UPDATE matable SET val = val + $1 WHERE id = $2;

Bien que vous n'ayez pas à y penser, il est utile de le savoir pour mieux comprendre les messages d'erreur de syntaxe.

Lors de l'exécution d'une commande SQL de cette façon, PL/pgSQL planifie la commande une fois et ré-utilise ce plan lors des prochaines exécutions, pour la durée de vie de la connexion. Les implications de ceci sont discutées en détail dans la Section 38.10.2, « Mise en cache du plan ».

Parfois, il est utile d'évaluer une expression ou une requête SELECT mais sans récupérer le résultat, par exemple lors de l'appel d'une fonction qui a des effets de bord mais dont la valeur du résultat n'est pas utile. Pour faire cela en PL/pgSQL, utilisez l'instruction PERFORM :

PERFORM requête;

Ceci exécute la requête et ne tient pas compte du résultat. Écrivez la requête de la même façon que vous écririez une commande SELECT mais remplacez le mot clé initial SELECT avec PERFORM. Les variables PL/pgSQL seront substituées dans la requête comme pour les commandes qui ne renvoient pas de résultat. Le plan est mis en cache de la même façon. La variable spéciale FOUND est configurée à true si la requête a produit au moins une ligne, false dans le cas contraire (voir la Section 38.5.5, « Obtention du statut du résultat »).

Un exemple :

PERFORM creer_vuemat('cs_session_page_requests_mv', ma_requete);

Le résultat d'une commande SQL ne ramenant qu'une seule ligne (mais avec une ou plusieurs colonnes) peut être affecté à une variable de type record, row ou à une liste de variables scalaires. Ceci se fait en écrivant la commande SQL de base et en ajoutant une clause INTO. Par exemple,

SELECT expressions_select INTO [STRICT] cible FROM ...;
INSERT ... RETURNING expressions INTO [STRICT] cible;
UPDATE ... RETURNING expressions INTO [STRICT] cible;
DELETE ... RETURNING expressions INTO [STRICT] cible;

où cible peut être une variable de type record, row ou une liste de variables ou de champs record/row séparées par des virgules. Les variables PL/pgSQL seront substituées dans le reste de la requête, et le plan est mis en cache comme décrit ci-dessus pour les commandes qui ne renvoient pas de lignes. Ceci fonctionne pour SELECT, INSERT/UPDATE/DELETE avec RETURNING, et les commandes utilitaires qui renvoient des résultats de type rowset (comme EXPLAIN). Sauf pour la clause INTO, la commande SQL est identique à celle qui aurait été écrite en dehors de PL/pgSQL.

Si une ligne ou une liste de variables est utilisée comme cible, les colonnes du résultat de la requête doivent correspondre exactement à la structure de la cible (nombre de champs et types de données). Dans le cas contraire, une erreur sera rapportée à l'exécution. Quand une variable record est la cible, elle se configure automatiquement avec le type row des colonnes du résultat de la requête.

La clause INTO peut apparaître pratiquement partout dans la commande SQL. Elle est écrite soit juste avant soit juste après la liste d'expressions_select dans une commande SELECT, ou à la fin de la commande pour d'autres types de commande. Il est recommandé de suivre cette convention au cas où l'analyseur PL/pgSQL devient plus strict dans les versions futures.

Si STRICT n'est pas spécifié dans la clause INTO, alors cible sera configuré avec la première ligne renvoyée par la requête ou à NULL si la requête n'a renvoyé aucune ligne. (Notez que « la première ligne » n'est bien définie que si vous avez utilisé ORDER BY.) Toute ligne résultat après la première ligne est annulée. Vous pouvez vérifier la valeur de la variable spéciale FOUND (voir la Section 38.5.5, « Obtention du statut du résultat ») pour déterminer si une ligne a été renvoyée :

SELECT * INTO monrec FROM emp WHERE nom = mon_nom;
IF NOT FOUND THEN
    RAISE EXCEPTION 'employé % introuvable', mon_nom;
END IF;

Si l'option STRICT est indiquée, la requête doit renvoyer exactement une ligne. Dans le cas contraire, une erreur sera rapportée à l'exécution, soit NO_DATA_FOUND (aucune ligne) soit TOO_MANY_ROWS (plus d'une ligne). Vous pouvez utiliser un bloc d'exception si vous souhaitez récupérer l'erreur, par exemple :

BEGIN
    SELECT * INTO STRICT monrec FROM emp WHERE nom = mon_nom;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE EXCEPTION 'employé % introuvable', mon_nom;
        WHEN TOO_MANY_ROWS THEN
            RAISE EXCEPTION 'employé % non unique', mon_nom;
END;

Une exécution réussie de la commande avec STRICT renvoie toujours true pour FOUND.

Pour INSERT/UPDATE/DELETE avec RETURNING, PL/pgSQL rapporte une erreur si plus d'une ligne est renvoyée, même quand STRICT n'est pas spécifié. Ceci est dû au fait qu'il n'y a pas d'option comme ORDER BY qui pourrait déterminer la ligne à renvoyer.

Pour gérer les cas où vous avez besoin de traiter plusieurs lignes de résultat à partir d'une requête SQL, voir la Section 38.6.4, « Boucler dans les résultats de requêtes ».

Créer dynamique des requêtes SQL est un besoin habituel dans les fonctions PL/pgSQL, par exemple des requêtes 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 (voir la Section 38.10.2, « Mise en cache du plan ») ne fonctionneront pas dans de tels scénarios. Pour gérer ce type de problème, l'instruction EXECUTE est proposée :

EXECUTE chaîne-commande [ INTO [STRICT] cible ];

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

Aucune substitution des variables PL/pgSQL ne se fait dans la chaîne de commande calculée. Toutes les valeurs des variables requises doivent être insérées dans la chaîne de commande au moment de sa construction.

De plus, il n'y a pas mise en cache des commandes exécutées via EXECUTE. À la place, la commande est préparée à chaque fois que l'instruction est lancée. La chaîne commande peut être créée dynamiquement à l'intérieur de la fonction pour agir sur des tables ou colonnes différentes.

La clause INTO spécifie où devraient être affectés les résultats d'une commande SQL renvoyant des lignes. Si une ligne ou une liste de variable est fournie, elle doit correspondre exactement à la structure des résultats de la requête (quand une variable de type record est utilisée, elle sera automatiquement typée 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 de la requête sont ignorés.

Si l'option STRICT est indiquée, une erreur est rapportée sauf si la requête produit exactement une ligne.

SELECT INTO n'est actuellement pas supporté à l'intérieur de EXECUTE ; à la place, exécutez une commande SELECT et spécifiez INTO comme faisant parti lui-même d'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 38.11.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ées à la fonction quote_ident. Les expressions contenant des valeurs de type chaîne de caractères doivent être passées à quote_literal. Ce sont les étapes appropriées pour renvoyer le texte en entrée entouré par des guillemets doubles ou simples respectivement, en échappant tout caractère spécial.

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

    EXECUTE 'UPDATE tbl SET '
    || quote_ident(nom_colonne)
    || ' = $$'
    || nouvelle_valeur
    || '$$ WHERE cle = '
    || quote_literal(valeur_cle);

car cela casserait si le contenu de newvalue pouvait contenir $$. La même objection s'applique à 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 38.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.

Quelque fois, une instruction qui ne fait rien est utile. Par exemple, elle 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 question de goût.