35.6. Instructions de base

35.6.1. Assignation

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;

35.6.2. SELECT INTO

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 35.6.6) 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;

35.6.3. Ex�cuter une expression ou requ�te sans r�sultat

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 create_mv('cs_session_page_requests_mv', my_query);

35.6.4. Ne rien faire du tout

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.

35.6.5. Ex�cuter des Commandes Dynamiques

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;

o� cha�ne-commande est une expression manipulant une cha�ne (de type text) contenant la commande � �tre ex�cut�e. Cette cha�ne est litt�ralement donn�e � manger au moteur SQL.

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.

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

Les r�sultats des commandes SELECT sont rejet�s par EXECUTE, et SELECT INTO n'est pas actuellement g�r� � l'int�rieur d'une instruction EXECUTE. Donc, il n'existe aucune fa�on d'extraire un r�sultat � partir d'un SELECT cr�� dynamiquement en utilisant la commande EXECUTE standard. N�anmoins, il existe deux autres fa�ons de le faire : la premi�re est d'utiliser FOR-IN-EXECUTE d�crite dans Section 35.7.4, et la deuxi�me est d'utiliser un curseur avec OPEN-FOR-EXECUTE, comme d�crit dans Section 35.8.2.

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 35.2.1, 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(colname)
    || ' = '
    || quote_literal(newvalue)
    || ' WHERE key = '
    || quote_literal(keyvalue);
    

Cet exemple montre l'utilisation des fonctions quote_ident(text) et quote_literal(text). Pour plus de s�ret�, les variables contenant les identifiants des colonnes et des tables doivent �tre pass�s � la fonction quote_ident. Les variables 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 35-6, qui construit et ex�cute une commande CREATE FUNCTION pour d�finir une nouvelle fonction.

35.6.6. Obtention du Statut du R�sultat

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.

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.

• Une instruction SELECT INTO positionne FOUND � true si elle renvoie une ligne, false si aucune ligne n'est renvoy�e.

• Une instruction PERFORM positionne FOUND � true si elle produit (rejette) une ligne, faux si aucune ligne n'est produite.

• Les instructions UPDATE, INSERT, et DELETE positionnent FOUND � true si au moins une ligne est affect�e, false si aucune ligne n'est affect�e.

• Une instruction FETCH positionne FOUND � true si elle renvoie une ligne, false si aucune ligne n'est renvoy�e.

• La commande FOR positionne FOUND � true si elle effectue une it�ration une ou plusieurs fois, sinon elle renvoie false. Ceci s'applique aux trois variantes de l'instruction FOR (boucles FOR integer, FOR record-set, et FOR record-set dynamique). FOUND n'est positionn� de cette fa�on que quand la boucle FOR s'ach�ve ; dans l'ex�cution de la cha�ne, FOUND n'est pas modifi�e par l'instruction FOR, bien qu'il puisse �tre modifi� par l'ex�cution d'autres instructions situ�es dans le corps de la boucle.

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.