Documentation PostgreSQL 14.22 » Programmation serveur » PL/pgSQL -- Langage de procédures SQL » Déclarations

Précédent | Niveau supérieur | Suivant
43.2. Structure de PL/pgSQL | Sommaire | 43.4. Expressions

## 43.3. Déclarations

Toutes les variables utilisées dans un bloc doivent être déclarées dans la section déclaration du bloc. Les seules exceptions sont que la variable de boucle d'une boucle `FOR` effectuant une itération sur des valeurs entières est automatiquement déclarée comme variable entière (type integer), et de la même façon une variable de boucle `FOR` effectuant une itération sur le résultat d'un curseur est automatiquement déclarée comme variable de type record.

Les variables PL/pgSQL peuvent être de n'importe quel type de données tels que `integer`, `varchar` et `char`.

Quelques exemples de déclaration de variables :

    id_utilisateur integer;
    quantité numeric(5);
    url varchar;
    ma_ligne nom_table%ROWTYPE;
    mon_champ nom_table.nom_colonne%TYPE;
    une_ligne RECORD;

La syntaxe générale d'une déclaration de variable est :

    nom [ CONSTANT ] type [ COLLATE nom_collationnement ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];

La clause `DEFAULT`, si indiquée, spécifie la valeur initiale affectée à la variable quand on entre dans le bloc. Si la clause `DEFAULT` n'est pas indiquée, la variable est initialisée à la valeur SQL NULL. L'option `CONSTANT` empêche la modification de la variable après initialisation, de sorte que sa valeur reste constante pour la durée du bloc. L'option `COLLATE` indique le collationnement à utiliser pour la variable (voir Section 43.3.6). Si `NOT NULL` est spécifié, l'affectation d'une valeur NULL aboutira à une erreur d'exécution. Les valeurs par défaut de toutes les variables déclarées `NOT NULL` doivent être précisées, donc non NULL. Le signe d'égalité (`=`) peut être utilisé à la place de `:=`, qui lui est conforme au PL/SQL.

La valeur par défaut d'une variable est évaluée et affectée à la variable à chaque entrée du bloc (pas seulement une fois lors de l'appel de la fonction). Ainsi, par exemple, l'affectation de `now()` à une variable de type `timestamp` donnera à la variable l'heure de l'appel de la fonction courante, et non l'heure au moment où la fonction a été précompilée.

Exemples :

    quantité integer DEFAULT 32;
    url varchar := 'http://mysite.com';
    id_utilisateur CONSTANT integer := 10;

### 43.3.1. Déclarer des paramètres de fonctions

Les paramètres passés aux fonctions sont nommés par les identifiants `$1`, `$2`, etc. Éventuellement, des alias peuvent être déclarés pour les noms de paramètres de type `$`n`` afin d'améliorer la lisibilité. L'alias ou l'identifiant numérique peuvent être utilisés indifféremment pour se référer à la valeur du paramètre.

Il existe deux façons de créer un alias. La façon préférée est de donner un nom au paramètre dans la commande `CREATE FUNCTION`, par exemple :

    CREATE FUNCTION taxe_ventes(sous_total real) RETURNS real AS $$
    BEGIN
        RETURN sous_total * 0.06;
    END;
    $$ LANGUAGE plpgsql;

L'autre façon est de déclarer explicitement un alias en utilisant la syntaxe de déclaration :

    nom ALIAS FOR $n;

Le même exemple dans ce style ressemble à ceci :

    CREATE FUNCTION taxe_ventes(real) RETURNS real AS $$
    DECLARE
        sous_total ALIAS FOR $1;
    BEGIN
        RETURN sous_total * 0.06;
    END;
    $$ LANGUAGE plpgsql;

### Note

Ces deux exemples ne sont pas complètement identiques. Dans le premier cas, `sous_total` peut être référencé comme `taxe_ventes.sous_total`, alors que ce n'est pas possible dans le second cas. (Si nous avions attaché un label au bloc interne, `sous_total` aurait pu utiliser ce label à la place.)

Quelques exemples de plus :

    CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$
    DECLARE
        v_string ALIAS FOR $1;
        index ALIAS FOR $2;
    BEGIN
        -- quelques traitements utilisant ici v_string et index
    END;
    $$ LANGUAGE plpgsql;

    CREATE FUNCTION concat_champs_selectionnes(in_t un_nom_de_table) RETURNS text AS $$
    BEGIN
        RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
    END;
    $$ LANGUAGE plpgsql;

Quand une fonction PL/pgSQL est déclarée avec des paramètres en sortie, ces derniers se voient attribués les noms `$`n`` et des alias optionnels de la même façon que les paramètres en entrée. Un paramètre en sortie est une variable qui commence avec la valeur NULL ; il devrait se voir attribuer une valeur lors de l'exécution de la fonction. La valeur finale du paramètre est ce qui est renvoyée. Par exemple, l'exemple taxe_ventes peut s'écrire de cette façon :

    CREATE FUNCTION taxe_ventes(sous_total real, OUT taxe real) AS $$
    BEGIN
        taxe := sous_total * 0.06;
    END;
    $$ LANGUAGE plpgsql;

Notez que nous avons omis `RETURNS real`. Nous aurions pu l'inclure mais cela aurait été redondant.

Pour appeler une fonction avec des paramètres `OUT`, omettez les paramètres en sortie dans l'appel de la fonction :

    SELECT sales_tax(100.00);

Les paramètres en sortie sont encore plus utiles lors du retour de plusieurs valeurs. Un exemple trivial est :

    CREATE FUNCTION somme_n_produits(x int, y int, OUT somme int, OUT produit int) AS $$
    BEGIN
        somme := x + y;
        produit := x * y;
    END;
    $$ LANGUAGE plpgsql;

    SELECT * FROM somme_n_produits(2, 4);
     somme | produit
    -------+---------
         6 |       8

D'après ce qui a été vu dans la Section 38.5.4, ceci crée réellement un type d'enregistrement anonyme pour les résultats de la fonction. Si une clause `RETURNS` est donnée, elle doit spécifier `RETURNS record`.

Ceci fonctionne aussi avec les procédures, par exemple :

    CREATE PROCEDURE sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
    BEGIN
        sum := x + y;
        prod := x * y;
    END;
    $$ LANGUAGE plpgsql;

Dans un appel à une procédure, tous les paramètres doivent être spécifiés. Pour les paramètres en sortie, `NULL` pourrait être indiqué lors de l'appel de la procédure en SQL :

    CALL sum_n_product(2, 4, NULL, NULL);

     sum | prod
    -----+------
       6 |    8

Néanmoins, lors de l'appel d'une procédure à partir de PL/pgSQL, vous devez écrire à la place une variable pour chaque paramètre en sortie ; la variable recevra le résultat de l'appel. Voir Section 43.6.3 pour les détails.

Voici une autre façon de déclarer une fonction PL/pgSQL, cette fois avec `RETURNS TABLE` :

    CREATE FUNCTION extended_sales(p_itemno int)
    RETURNS TABLE(quantity int, total numeric) AS $$
    BEGIN
        RETURN QUERY SELECT s.quantity, s.quantity * s.price FROM sales AS s
                     WHERE s.itemno = p_itemno;
    END;
    $$ LANGUAGE plpgsql;

C'est exactement équivalent à déclarer un ou plusieurs paramètres `OUT` et à spécifier `RETURNS SETOF `un_type``.

Lorsque le type de retour d'une fonction PL/pgSQL est déclaré comme type polymorphe (see Section 38.2.5), un paramètre spécial `$0` est créé. Son type de donnée est le type effectif de retour de la fonction, déduit d'après les types en entrée (voir la Section 38.2.5). Ceci permet à la fonction d'accéder à son type de retour réel comme on le voit ici avec la Section 43.3.3. `$0` est initialisé à NULL et peut être modifié par la fonction, de sorte qu'il peut être utilisé pour contenir la variable de retour si besoin est, bien que cela ne soit pas requis. On peut aussi donner un alias à `$0`. Par exemple, cette fonction s'exécute comme un opérateur `+` pour n'importe quel type de données :

    CREATE FUNCTION ajoute_trois_valeurs(v1 anyelement, v2 anyelement, v3 anyelement)
    RETURNS anyelement AS $$
    DECLARE
        resultat ALIAS FOR $0;
    BEGIN
        resultat := v1 + v2 + v3;
        RETURN resultat;
    END;
    $$ LANGUAGE plpgsql;

Le même effet peut être obtenu en déclarant un ou plusieurs paramètres polymorphes en sortie de types. Dans ce cas, le paramètre spécial `$0` n'est pas utilisé ; les paramètres en sortie servent ce même but. Par exemple :

    CREATE FUNCTION ajoute_trois_valeurs(v1 anyelement, v2 anyelement, v3 anyelement,
                                     OUT somme anyelement)
    AS $$
    BEGIN
        somme := v1 + v2 + v3;
    END;
    $$ LANGUAGE plpgsql;

En pratique, il pourrait être plus utile de déclarer une fonction polymorphique en utilisant la famille de types `anycompatible`, pour que survienne la promotion automatique des arguments en entrée vers un type commune. Par exemple :

    CREATE FUNCTION ajoute_trois_valeurs(v1 anycompatible, v2 anycompatible, v3 anycompatible)
    RETURNS anycompatible AS $$
    BEGIN
        RETURN v1 + v2 + v3;
    END;
    $$ LANGUAGE plpgsql;

Avec cet exemple, un appel tel que

    SELECT ajoute_trois_valeurs(1, 2, 4.7);

fonctionnera, promouvant automatiquement les arguments entiers en numériques. La fonction utilisant `anyelement` nécessiterait que vous convertissiez manuellement les trois arguments sur le même type de données.

### 43.3.2. `ALIAS`

    nouveaunom ALIAS FOR anciennom;

La syntaxe `ALIAS` est plus générale que la section précédente pourrait faire croire : vous pouvez déclarer un alias pour n'importe quelle variable et pas seulement des paramètres de fonction. L'utilisation principale de cette instruction est l'attribution d'un autre nom aux variables aux noms prédéterminés, telles que `NEW` ou `OLD` au sein d'une fonction trigger.

Exemples:

    DECLARE
      anterieur ALIAS FOR old;
      misajour ALIAS FOR new;

`ALIAS` créant deux manières différentes de nommer le même objet, son utilisation à outrance peut prêter à confusion. Il vaut mieux ne l'utiliser uniquement pour se passer des noms prédéterminés.

### 43.3.3. Copie de types

    variable%TYPE

`%TYPE` fournit le type de données d'une variable ou d'une colonne de table. Vous pouvez l'utiliser pour déclarer des variables qui contiendront des valeurs de base de données. Par exemple, disons que vous avez une colonne nommée `id_utilisateur` dans votre table `utilisateurs`. Pour déclarer une variable du même type de données que `utilisateurs.id_utilisateur`, vous pouvez écrire :

    id_utilisateur utilisateurs.id_utilisateur%TYPE;

En utilisant `%TYPE` vous n'avez pas besoin de connaître le type de données de la structure à laquelle vous faites référence et, plus important, si le type de données de l'objet référencé change dans le futur (par exemple : vous changez le type de `id_utilisateur` de `integer` à `real`), vous pouvez ne pas avoir besoin de changer votre définition de fonction.

`%TYPE` est particulièrement utile dans le cas de fonctions polymorphes puisque les types de données nécessaires aux variables internes peuvent changer d'un appel à l'autre. Des variables appropriées peuvent être créées en appliquant `%TYPE` aux arguments de la fonction ou à la variable fictive de résultat.

### 43.3.4. Types ligne

    nom nom_table%ROWTYPE;
    nom nom_type_composite;

Une variable de type composite est appelée variable ligne (ou variable row-type). Une telle variable peut contenir une ligne entière de résultat de requête `SELECT` ou `FOR`, du moment que l'ensemble de colonnes de la requête correspond au type déclaré de la variable. Les champs individuels de la valeur row sont accessibles en utilisant la notation pointée, par exemple `varligne.champ`.

Une variable ligne peut être déclarée de façon à avoir le même type que les lignes d'une table ou d'une vue existante, en utilisant la notation `nom_table``%ROWTYPE`. Elle peut aussi être déclarée en donnant un nom de type composite. Chaque table ayant un type de données associé du même nom, il importe peu dans PostgreSQL que vous écriviez `%ROWTYPE` ou pas. Cependant, la forme utilisant `%ROWTYPE` est plus portable.

Les paramètres d'une fonction peuvent être des types composites (lignes complètes de tables). Dans ce cas, l'identifiant correspondant `$`n`` sera une variable ligne à partir de laquelle les champs peuvent être sélectionnés avec la notation pointée, par exemple `$1.id_utilisateur`.

Voici un exemple d'utilisation des types composites. `table1` et `table2` sont des tables ayant au moins les champs mentionnés :

    CREATE FUNCTION assemble_champs(t_ligne table1) RETURNS text AS $$
    DECLARE
        t2_ligne table2%ROWTYPE;
    BEGIN
        SELECT * INTO t2_ligne FROM table2 WHERE ... ;
        RETURN t_ligne.f1 || t2_ligne.f3 || t_ligne.f5 || t2_ligne.f7;
    END;
    $$ LANGUAGE plpgsql;

    SELECT assemble_champs(t.*) FROM table1 t WHERE ... ;

### 43.3.5. Types record

    nom RECORD;

Les variables record sont similaires aux variables de type ligne mais n'ont pas de structure prédéfinie. Elles empruntent la structure effective de type ligne de la ligne à laquelle elles sont affectées durant une commande `SELECT` ou `FOR`. La sous-structure d'une variable record peut changer à chaque fois qu'on l'affecte. Une conséquence de cela est qu'elle n'a pas de sous-structure jusqu'à ce qu'elle ait été affectée, et toutes les tentatives pour accéder à un de ses champs entraînent une erreur d'exécution.

Notez que `RECORD` n'est pas un vrai type de données mais seulement un paramètre fictif (placeholder). Il faut aussi réaliser que lorsqu'une fonction PL/pgSQL est déclarée renvoyer un type `record`, il ne s'agit pas tout à fait du même concept qu'une variable record, même si une telle fonction peut aussi utiliser une variable record pour contenir son résultat. Dans les deux cas, la structure réelle de la ligne n'est pas connue quand la fonction est écrite mais, dans le cas d'une fonction renvoyant un type `record`, la structure réelle est déterminée quand la requête appelante est analysée, alors qu'une variable record peut changer sa structure de ligne à la volée.

### 43.3.6. Collationnement des variables PL/pgSQL

Quand une fonction PL/pgSQL a un ou plusieurs paramètres dont le type de données est collationnable, un collationnement est identifié pour chaque appel de fonction dépendant des collationnements affectés aux arguments réels, comme décrit dans Section 24.2. Si un collationnement est identifié avec succès (autrement dit, qu'il n'y a pas de conflit de collationnements implicites parmi les arguments), alors tous les paramètres collationnables sont traités comme ayant un collationnement implicite. Ceci affectera le comportement des opérations sensibles au collationnement dans la fonction. Par exemple, avec cette fonction

    CREATE FUNCTION plus_petit_que(a text, b text) RETURNS boolean AS $$
    BEGIN
        RETURN a < b;
    END;
    $$ LANGUAGE plpgsql;

    SELECT plus_petit_que(champ_text_1, champ_text_2) FROM table1;
    SELECT plus_petit_que(champ_text_1, champ_text_2 COLLATE "C") FROM table1;

La première utilisation de `less_than` utilisera le collationnement par défaut de `champ_text_1` et de `champ_text_2` pour la comparaison alors que la seconde utilisation prendra le collationnement `C`.

De plus, le collationnement identifié est aussi considéré comme le collationnement de toute variable locale de type collationnable. Du coup, cette procédure stockée ne fonctionnera pas différemment de celle-ci :

    CREATE FUNCTION plus_petit_que(a text, b text) RETURNS boolean AS $$
    DECLARE
        local_a text := a;
        local_b text := b;
    BEGIN
        RETURN local_a < local_b;
    END;
    $$ LANGUAGE plpgsql;

S'il n'y a pas de paramètres pour les types de données collationnables ou qu'aucun collationnement commun ne peut être identifié pour eux, alors les paramètres et les variables locales utilisent le collationnement par défaut de leur type de données (qui est habituellement le collationnement par défaut de la base de données mais qui pourrait être différent pour les variables des types domaines).

Une variable locale d'un type de données collationnable peut avoir un collationnement différent qui lui est associé en incluant l'option `COLLATE` dans sa déclaration, par exemple

    DECLARE
        local_a text COLLATE "en_US";

Cette option surcharge le collationnement qui serait normalement donné à la variable d'après les règles ci-dessus.

De plus, les clauses `COLLATE` explicites peuvent être écrites à l'intérieur d'une fonction si forcer l'utilisation d'un collationnement particulier est souhaité pour une opération particulière. Par exemple,

    CREATE FUNCTION plus_petit_que_c(a text, b text) RETURNS boolean AS $$
    BEGIN
        RETURN a < b COLLATE "C";
    END;
    $$ LANGUAGE plpgsql;

Ceci surcharge les collationnements associés avec les colonnes de la table, les paramètres ou la variable locales utilisées dans l'expression, comme cela arriverait dans une commande SQL simple.