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'enregistrement). La variable cible peut être une simple variable (éventuellement qualifiée avec un nom de bloc), un champ d'une rangée ou variable d'enrengistrement ou un élément ou partie de tableau cible. Le signe d'égalité (=) peut être utilisé à la place de :=, qui lui est conforme au PL/SQL.

Si le type de données du résultat de l'expression ne correspond pas au type de donnée de la variable, la valeur sera convertie via une conversion d'affectation (cf Section 10.4. Si aucune conversion d'affectation n'est connue pour les deux types de données concernées, l'interpréteur PL/pgSQL tentera de convertir le résultat textuellement, c'est-à-dire en appliquant successivement la fonction de sortie du type résultat puis la fonction d'entrée du type de la variable. Notez que la fonction d'entrée peut générer des erreurs à l'exécution si la chaîne passée en paramètre n'est pas acceptable pour le type de la variable.

Exemples :

taxe := sous_total * 0.06;
mon_enregistrement.id_utilisateur := 20;
my_array[j] := 20;
my_array[1:3] := array[1,2,3];
complex_array[n].realpart = 12.3;
    

En général, toute commande SQL qui ne renvoie pas de lignes peut être exécutée à l'intérieur d'une fonction PL/pgSQL en écrivant simplement la commande. Par exemple, vous pouvez créer et remplir une table en écrivant :

CREATE TABLE mytable (id int primary key, data text);
INSERT INTO mytable VALUES (1,'one'), (2,'two');

Si la commande renvoie des lignes (par exemple SELECT, ou INSERT/UPDATE/DELETE avec RETURNING), il existe deux façons de procéder. Quand la commande renverra au plus une ligne ou quand vous faites seulement attention à la première ligne en sortie, écrivez la commande comme d'habitude mais ajoutez une clause INTO pour capturer la sortie, comme décrit dans Section 43.5.3. Pour traiter toutes les lignes en sortie, écrivez la commande comme la source de données pour une boucle FOR, comme décrit dans Section 43.6.6.

Habituellement, il n'est pas suffisant de simplement exécuter des commandes SQL statiques. Typiquement, vous voudrez une commande à utiliser des valeurs de données variables ou même pour varier de façon plus fondamentale en utilisant par exemple des noms de tables différents à différents moments. Encore une fois, ilexiste deux façons de procéder suivant la situation.

Les valeurs des variables PL/pgSQL peuvent être insérées automatiquement dans des commandes SQL optimisables, comme SELECT, INSERT, UPDATE, DELETE, MERGE et certaines commandes utilitaires qui les incorporent, telles que EXPLAIN et CREATE TABLE ... AS SELECT. Dans ces commandes, tout nom de variable PL/pgSQL apparaissant dans le texte de la commande est remplace par un paramètre de la requête, 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 43.11.1.

Lors de l'exécution d'une commande SQL optimisable de cette façon, PL/pgSQL peut placer le plan en cache et le réutiliser plus tard, comme indiqué dans Section 43.11.2.

Les commandes SQL non optimisables (aussi appelées commandes utilitaires) ne sont pas capables d'accepter des paramètres aux requêtes. Donc la substitution automatique des variables PL/pgSQL ne fonctionne pas sur ces commandes. Pour inclure un texte non constant dans une commande utilitaire exécutée à partir de PL/pgSQL, vous devez construire la commande utilitaire avec une chaîne de caractères, puis l'exécuter avec EXECUTE, comme indiqué dans discussed in Section 43.5.4.

EXECUTE doit aussi être utilisé si vous voulez modifier la commande de toute autre façon que de fournir une valeur de donnée, par exemple en changeant le nom d'une table.

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 décrit ci-dessus. 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 43.5.5).

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 commande (c'est-à-dire, tout sauf la clause INTO décrite ci-dessus), et le plan est mis en cache de la même façon. Ceci fonctionne pour SELECT, INSERT/UPDATE/DELETE avec RETURNING, et certaines 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 variable de ligne ou une liste de variables est utilisée comme cible, les colonnes du résultat de la commande 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 commande.

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 commande ou à NULL si la commande 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 43.5.5) 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 commande 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 les commandes INSERT / UPDATE / DELETE utilisées avec la clause RETURNING, PL/pgSQL renvoie une erreur si plus d'une ligne est renvoyée, même si la clause STRICT n'est pas indiquée. Ceci est dû au fait qu'il n'existe pas d'option ORDER BY qui permettrait de déterminer la ligne affectée à renvoyer.

Si print_strict_params est activé pour cette fonction, alors, quand une erreur est renvoyée parce que les conditions de STRICT ne sont pas rencontrées, la partie DETAIL du message d'erreur incluera les informations sur les paramètres passés à la commande. Vous pouvez modifier la configuration de print_strict_params pour toutes les fonctions en configurant plpgsql.print_strict_params, bien que seules les compilations suivantes des fonctions seront affectées. Vous pouvez aussi l'activer fonction par fonction en utilisant une option du compilateur, par exemple :

CREATE FUNCTION get_userid(username text) RETURNS int
AS $$
#print_strict_params on
DECLARE
userid int;
BEGIN
    SELECT users.userid INTO STRICT userid
        FROM users WHERE users.username = get_userid.username;
    RETURN userid;
END;
$$ LANGUAGE plpgsql;
    

En cas d'échec, cette fonction pourrait renvoyer un message d'erreur tel que :

ERROR:  query returned no rows
DETAIL:  parameters: $1 = 'nosuchuser'
CONTEXT:  PL/pgSQL function get_userid(text) line 6 at SQL statement
    

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 43.11.2) 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 variable de ligne ou une liste de variable est fournie, elle doit correspondre exactement à la structure des résultats de la commande. Si 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 (ou les) variables 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 commande sont ignorés.

Si l'option STRICT est indiquée, une erreur est rapportée sauf si la commande 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 '
    || quote_ident(tabname)
    || ' WHERE insere_par = $1 AND insere <= $2'
   INTO c
   USING utilisateur_verifie, date_verifiee;
    

Une meilleure solution est d'utiliser la spécification de formatage %I de la fonction format() pour insérer les noms de table ou de colonne avec des guillemets automatiques :

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

(Cet exemple se base sur la règle SQL qui dit que les constantes de chaînes séparées par un retour à la ligne sont implicitement concaténées.)

Une autre restriction sur les symboles de paramètres est qu'ils ne fonctionnent que dans les commandes SQL optimisables (SELECT, INSERT, UPDATE, DELETE, MERGE et certaines commandes contenant l'une d'entre elles). Dans les autres types d'instructions(appelé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.

EXECUTE format('UPDATE table SET %I = $1 '
   'WHERE clef = $2', nom_colonne) USING nouvelle_valeur, valeur_clef;
     

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 matable SET %I = %L '
   'WHERE clef = %L', nom_colonne, nouvelle_valeur, valeur_clef);
     

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 43.10, 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 récupère les indicateurs de statut du système. CURRENT est un mot optionnel (mais voir aussi GET STACKED DIAGNOSTICS dans Section 43.6.8.1). Chaque élément est un mot clé identifiant une valeur de statut à affecter à la variable indiquée (qui doit être du bon type de données pour la recevoir). Les éléments de statut actuellement disponibles sont affichés dans Tableau 43.1. L'opérateur deux-points- égal (:=) peut être utilisé à la place de l'opérateur = qui lui est compatible avec le standard SQL. 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.