39.5. Instructions de base

L'affectation d'une valeur � une variable PL/pgSQL s'écrit ainsi :

variable := expression;

Comme expliqué précédemment, 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 (éventuellement une valeur de rangée, si cette variable est une variable de rangée ou d'enrengistrement). La variable cible peut �tre une simple varible (éventuellement qualifiée avec un nom de bloc), un champ d'une rangée ou variable d'enrengistrement ou un élément de tableau qui se trouve �tre une simple variable ou champ.

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 traité comme un 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 39.10.1, � Substitution de variables �.

Lors de l'exécution d'une commande SQL de cette fa�on, PL/pgSQL peut placer le plan en cache et le réutiliser plus tard, comme indiqué dans Section 39.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. Pour les requ�tes WITH, utilisez PERFORM puis placez la requ�te entre parenth�ses. (De cette fa�on, la requ�te peut seulement renvoyer une ligne.) 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 39.5.5, � Obtention du statut du résultat �).

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

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.

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

Note

L'option STRICT correspond au comportement du SELECT INTO d'Oracle PL/SQL et des instructions relatives.

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 39.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 39.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 command-string [ INTO [STRICT] target ] [ USING expression [, ...] ];

o� cha�ne-commande est une expression manipulant une cha�ne (de type text) contenant la commande � exécuter. La cible optionnelle 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, dans lesquels les résultats de la commande seront enregistrés. Les expressions USING optionnelles fournissent des valeurs � insérer dans la commande.

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 ; ou vous pouvez utiliser des param�tres comme décrits ci-dessous.

De plus, il n'y a pas mise en cache des commandes exécutées via EXECUTE. � la place, la commande est planifié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.

La cha�ne de commande peut utiliser des valeurs de param�tres, référencées dans la commande avec $1, $2, etc. Ces symboles font référence aux valeurs fournies dans la clause USING. Cette méthode est souvent préférable � l'insertion des valeurs en texte dans une cha�ne de commande : cela évite la surcharge � l'exécution pour la conversion des valeurs en texte et vice-versa. C'est aussi moins sensible aux attaques par injection SQL car il n'est pas nécessaire de mettre entre guillemets ou d'échapper les valeurs. Voici un exemple :

EXECUTE 'SELECT count(*) FROM matable WHERE insere_par = $1 AND insere <= $2'
   INTO c
   USING utilisateur_verifie, date_verifiee;

Notez que les symboles de param�tres peuvent seulement �tre utilisés pour des valeurs de données -- si vous voulez utiliser des noms de tables et/ou colonnes déterminés dynamiquement, vous devez les insérer dans la cha�ne de commande en texte. Par exemple, si la requ�te précédente devait se faire avec une table sélectionnée dynamiquement, vous devriez faire ceci :

EXECUTE 'SELECT count(*) FROM '
    || tabname::regclass
    || ' WHERE insere_par = $1 AND insere <= $2'
   INTO c
   USING utilisateur_verifie, date_verifiee;

Une autre restriction sur les symboles de param�tres est qu'ils ne marchent que dans les commandes SELECT, INSERT, UPDATE et DELETE. Dans les autres types d'instructions (appellés de mani�re générique commandes utilitaires), vous devez insérer les valeurs sous forme de texte m�me si ce ne sont que des données.

Un EXECUTE avec une cha�ne de commande constante et des param�tres USING, comme dans le premier exemple ci-dessus, est équivalent fonctionnellement � l'écriture simple d'une commande directement dans PL/pgSQL et permet le remplacement automatique des variables PL/pgSQL. La différence importante est que EXECUTE va planifier de nouveau la commande pour chaque exécution, générant un plan qui est spécifique aux valeurs actuelles des param�tres ; alors que PL/pgSQL pourrait sinon créer un plan générique et le stocke pour le réutiliser. Dans des situations o� le meilleur plan dépend fortement des valeurs des param�tres, cela peut �tre utile d'utiliser EXECUTE pour s'assurer qu'un plan générique n'est pas sélectionné.

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.

Note

L'instruction EXECUTE de PL/pgSQL n'a pas de relation avec l'instruction SQL EXECUTE(7) supportée par le serveur PostgreSQL™. L'instruction EXECUTE du serveur ne peut pas �tre utilisée directement dans les fonctions PL/pgSQL. En fait, elle n'est pas nécessaire.

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

EXECUTE 'UPDATE tbl SET '
        || quote_ident(nom_colonne)
        || ' = '
        || quote_nullable(nouvelle_valeur)
        || ' WHERE key = '
        || quote_nullable(valeur_clé);

     'WHERE key = ' || quote_nullable(valeur_clé)

     'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)

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

EXECUTE format('UPDATE tbl SET %I = %L WHERE key = %L', colname, newvalue, keyvalue);

EXECUTE format('UPDATE tbl SET %I = $1 WHERE cle = $2', nom_colonne)
   USING nouvellevaleur, clevaleur;

Un exemple bien plus important d'une commande dynamique et d'EXECUTE est disponible dans l'Exemple 39.8, � 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 [ CURRENT ] 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 :

Les autres instructions PL/pgSQL ne changent pas l'état de FOUND. Notez que la commande EXECUTE modifie la sortie de GET DIAGNOSTICS mais ne change pas FOUND.

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.

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.