37.6. Instructions de base

L'assignation d'une valeur à une variable PL/pgSQL 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;

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

Tout nom de variable PL/pgSQL apparaissant dans le texte de la variable 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. Ceci permet à la même requête textuelle de faire des choses différentes lors d'appels différents à la fonction.

Note

Ce processus en deux étapes permet à PL/pgSQL de planifier la requête seulement une fois et de réutiliser la planification sur les exécutions suivantes. Par exemple, si vous écrivez

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

le texte de la requête envoyée par le moteur SQL ressemblera à ceci

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

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

Attention

PL/pgSQL substitutera pour tout identifiant une variable déclarée de la fonction. Du coup, une mauvaise idée serait d'utiliser un nom de variable identique à celui d'une table ou d'une colonne que vous avez besoin d'utiliser dans la fonction. Quelque fois, vous pouvez contourner ceci en utilisant des noms qualifiés dans la requête : PL/pgSQL ne substituera pas le nom qualifié foo . bar , même si foo ou bar est un nom de variable déclaré.

Quelque fois, 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 d'habitude. De plus, la variable spéciale FOUND est configurée à true si la requête a produit au moins une ligne, false dans le cas contraire.

Note

Vous pourriez vous attendre à ce que l'utilisation directe de SELECT aboutisse au même résultat mais, actuellement, la seule façon acceptée de le faire est d'utiliser PERFORM . Une commande SQL qui peut renvoyer des lignes comme SELECT sera rejetée comme une erreur si elle n'a pas de clause INTO, ce qui est discuté dans la section suivante.

Un exemple :

PERFORM create_mv('cs_session_page_requests_mv', my_query);

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 simple 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 comme d'habitude. 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.

Astuce

Notez que cette interprétation de SELECT avec INTO est assez différente de la commande habituelle SELECT INTO où la cible INTO est une table nouvellement créée. Si vous voulez créer une table à partir du résultat d'un SELECT à l'intérieur d'une fonction PL/pgSQL, utilisez la syntaxe CREATE TABLE ... AS SELECT .

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.

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.

Note

Dans le PL/SQL d'Oracle, les listes d'instructions vides ne sont pas autorisées et, du coup, les instructions NULL sont requises dans les situations telles que celles-ci. PL/pgSQL vous permet d'écrire simplement rien.

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 [STRICT] 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 calculée. 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 SQL renvoyant des lignes devraient être affectés. 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 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 de la requête sont annulées.

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 .

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 dans une table contenant des OID.

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.

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