Documentation PostgreSQL 14.22 » Langage SQL » Syntaxe SQL » Expressions de valeurs

Précédent | Niveau supérieur | Suivant
4.1. Structure lexicale | Sommaire | 4.3. Fonctions appelantes

## 4.2. Expressions de valeurs

Les expressions de valeurs sont utilisées dans une grande variété de contextes, tels que dans la liste cible d'une commande `SELECT`, dans les nouvelles valeurs de colonnes d'une commande `INSERT` ou `UPDATE`, ou dans les conditions de recherche d'un certain nombre de commandes. Le résultat d'une expression de valeurs est quelquefois appelé scalaire, pour le distinguer du résultat d'une expression de table (qui est une table). Les expressions de valeurs sont aussi appelées des expressions scalaires (voire simplement des expressions). La syntaxe d'expression permet le calcul des valeurs à partir de morceaux primitifs en utilisant les opérations arithmétiques, logiques, d'ensemble et autres.

Une expression de valeur peut être :

- une constante ou une valeur constante ;

- une référence de colonne ;

- une référence de la position d'un paramètre, dans le corps d'une définition de fonction ou d'instruction préparée ;

- une expression indicée ;

- une expression de sélection de champs ;

- un appel d'opérateur ;

- un appel de fonction ;

- une expression d'agrégat ;

- un appel de fonction de fenêtrage ;

- une conversion de type ;

- une expression de collationnement ;

- une sous-requête scalaire ;

- un constructeur de tableau ;

- un constructeur de ligne ;

- toute expression de valeur entre parenthèses, utile pour grouper des sous-expressions et surcharger la précédence.

En plus de cette liste, il existe un certain nombre de constructions pouvant être classées comme une expression, mais ne suivant aucune règle de syntaxe générale. Elles ont généralement la sémantique d'une fonction ou d'un opérateur et sont expliquées au Chapitre 9. Un exemple est la clause `IS NULL`.

Nous avons déjà discuté des constantes dans la Section 4.1.2. Les sections suivantes discutent des options restantes.

### 4.2.1. Références de colonnes

Une colonne peut être référencée avec la forme :

    correlation.nom_colonne

`correlation` est le nom d'une table (parfois qualifié par son nom de schéma) ou un alias d'une table définie au moyen de la clause `FROM`. Le nom de corrélation et le point de séparation peuvent être omis si le nom de colonne est unique dans les tables utilisées par la requête courante (voir aussi le Chapitre 7).

### 4.2.2. Paramètres de position

Un paramètre de position est utilisé pour indiquer une valeur fournie en externe par une instruction SQL. Les paramètres sont utilisés dans des définitions de fonction SQL et dans les requêtes préparées. Quelques bibliothèques clients supportent aussi la spécification de valeurs de données séparément de la chaîne de commandes SQL, auquel cas les paramètres sont utilisés pour référencer les valeurs de données en dehors. Le format d'une référence de paramètre est :

    $numéro

Par exemple, considérez la définition d'une fonction : `dept` :

    CREATE FUNCTION dept(text) RETURNS dept
        AS $$ SELECT * FROM dept WHERE nom = $1 $$
        LANGUAGE SQL;

Dans cet exemple, `$1` référence la valeur du premier argument de la fonction à chaque appel de cette commande.

### 4.2.3. Indices

Si une expression récupère une valeur de type tableau, alors un élément spécifique du tableau peut être extrait en écrivant :

    expression[indice]

Des éléments adjacents (un « morceau de tableau ») peuvent être extraits en écrivant :

    expression[indice_bas:indice_haut]

Les crochets `[ ]` doivent apparaître réellement. Chaque `indice` est lui-même une expression, dont la valeur sera arrondie à la valeur entière la plus proche.

En général, l'`expression` de type tableau doit être entre parenthèses, mais ces dernières peuvent être omises lorsque l'expression utilisée comme indice est seulement une référence de colonne ou un paramètre de position. De plus, les indices multiples peuvent être concaténés lorsque le tableau original est multidimensionnel. Par exemple :

    ma_table.colonnetableau[4]
    ma_table.colonnes_deux_d[17][34]
    $1[10:42]
    (fonctiontableau(a,b))[42]

Dans ce dernier exemple, les parenthèses sont requises. Voir la Section 8.15 pour plus d'informations sur les tableaux.

### 4.2.4. Sélection de champs

Si une expression récupère une valeur de type composite (type row), alors un champ spécifique de la ligne est extrait en écrivant :

    expression.nom_champ

En général, l'`expression` de ligne doit être entre parenthèses, mais les parenthèses peuvent être omises lorsque l'expression à partir de laquelle se fait la sélection est seulement une référence de table ou un paramètre de position. Par exemple :

    ma_table.macolonne
    $1.unecolonne
    (fonctionligne(a,b)).col3

En fait, une référence de colonne qualifiée est un cas spécial de syntaxe de sélection de champ. Un cas spécial important revient à extraire un champ de la colonne de type composite d'une table :

    (colcomposite).unchamp
    (matable.colcomposite).unchamp

Les parenthèses sont requises ici pour montrer que `colcomposite` est un nom de colonne, et non pas un nom de table, ou que `matable` est un nom de table, pas un nom de schéma dans le deuxième cas.

Vous pouvez demander tous les champs d'une valeur composite en écrivant `.*` :

    (compositecol).*

Cette syntaxe se comporte différemment suivant le contexte. Voir Section 8.16.5 pour plus de détails.

### 4.2.5. Appels d'opérateurs

Il existe deux syntaxes possibles pour l'appel d'un opérateur :

`expression` `opérateur` `expression` (opérateur binaire préfixe)
`opérateur` `expression` (opérateur unaire préfixe)

où le jeton `opérateur` suit les règles de syntaxe de la Section 4.1.3, ou est un des mots-clés `AND`, `OR` et `NOT`, ou est un nom d'opérateur qualifié de la forme

    OPERATOR(schema.nom_operateur)

Le fait qu'opérateur particulier existe et qu'il soit unaire ou binaire dépend des opérateurs définis par le système ou l'utilisateur. Le Chapitre 9 décrit les opérateurs internes.

### 4.2.6. Appels de fonctions

La syntaxe pour un appel de fonction est le nom d'une fonction (qualifié ou non du nom du schéma) suivi par sa liste d'arguments entre parenthèses :

    nom_fonction([expression [,expression ...]] )

Par exemple, ce qui suit calcule la racine carrée de 2 :

    sqrt(2)

La liste des fonctions intégrées se trouve dans le Chapitre 9. D'autres fonctions pourraient être ajoutées par l'utilisateur.

Lors de l'exécution de requêtes dans une base de données où certains utilisateurs ne font pas confiance aux autres utilisateurs, observez quelques mesures de sécurité disponibles dans Section 10.3 lors de l'appel de fonctions.

En option, les arguments peuvent avoir leur nom attaché. Voir la Section 4.3 pour les détails.

### Note

Une fonction qui prend un seul argument de type composite peut aussi être appelée en utilisant la syntaxe de sélection de champ. Du coup, un champ peut être écrit dans le style fonctionnel. Cela signifie que les notations `col(table)` et `table.col` sont interchangeables. Ce comportement ne respecte pas le standard SQL, mais il est fourni dans PostgreSQL, car il permet l'utilisation de fonctions émulant les « champs calculés ». Pour plus d'informations, voir la Section 8.16.5.

### 4.2.7. Expressions d'agrégat

Une expression d'agrégat représente l'application d'une fonction d'agrégat à travers les lignes sélectionnées par une requête. Une fonction d'agrégat réduit les nombres entrés en une seule valeur de sortie, comme la somme ou la moyenne des valeurs en entrée. La syntaxe d'une expression d'agrégat est une des suivantes :

    nom_agregat (expression [ , ... ] [ clause_order_by ] ) [ FILTER ( WHERE clause_filtre ) ]
    nom_agregat (ALL expression [ , ... ] [ clause_order_by ] ) [ FILTER ( WHERE clause_filtre ) ]
    nom_agregat (DISTINCT expression [ , ... ] [ clause_order_by ] ) [ FILTER ( WHERE clause_filtre ) ]
    nom_agregat ( * ) [ FILTER ( WHERE clause_filtre ) ]
    nom_agregat ( [ expression [ , ... ] ] ) WITHIN GROUP ( clause_order_by ) [ FILTER ( WHERE clause_filtre ) ]

où `nom_agregat` est un agrégat précédemment défini (parfois qualifié d'un nom de schéma), `expression` est toute expression de valeur qui ne contient pas elle-même une expression d'agrégat ou un appel à une fonction de fenêtrage. Les clauses optionnelles `clause_order_by` et `clause_filtre` sont décrites ci-dessous.

La première forme d'expression d'agrégat appelle l'agrégat une fois pour chaque ligne en entrée. La seconde forme est identique à la première, car `ALL` est une clause active par défaut. La troisième forme fait appel à l'agrégat une fois pour chaque valeur distincte de l'expression (ou ensemble distinct de valeurs, pour des expressions multiples) trouvée dans les lignes en entrée. La quatrième forme appelle l'agrégat une fois pour chaque ligne en entrée ; comme aucune valeur particulière en entrée n'est spécifiée, c'est généralement utile pour la fonction d'agrégat `count(*)`. La dernière forme est utilisée avec les agrégats à ensemble trié qui sont décrits ci-dessous.

La plupart des fonctions d'agrégats ignorent les entrées NULL, pour que les lignes qui renvoient une ou plusieurs expressions NULL soient disqualifiées. Ceci peut être considéré comme vrai pour tous les agrégats internes sauf indication contraire.

Par exemple, `count(*)` trouve le nombre total de lignes en entrée, alors que `count(f1)` récupère le nombre de lignes en entrée pour lesquelles `f1` n'est pas NULL. En effet, la fonction `count` ignore les valeurs NULL, mais `count(distinct f1)` retrouve le nombre de valeurs distinctes non NULL de `f1`.

D'habitude, les lignes en entrée sont passées à la fonction d'agrégat dans un ordre non spécifié. Dans la plupart des cas, cela n'a pas d'importance. Par exemple, `min` donne le même résultat quel que soit l'ordre dans lequel il reçoit les données. Néanmoins, certaines fonctions d'agrégat (telles que `array_agg` et `string_agg`) donnent un résultat dépendant de l'ordre des lignes en entrée. Lors de l'utilisation de ce type d'agrégat, la clause `clause_order_by` peut être utilisée pour préciser l'ordre de tri désiré. La clause `clause_order_by` a la même syntaxe que la clause `ORDER BY` d'une requête, qui est décrite dans la Section 7.5, sauf que ses expressions sont toujours des expressions simples et ne peuvent pas être des noms de colonne en sortie ou des numéros. Par exemple :

    SELECT array_agg(a ORDER BY b DESC) FROM table;

Lors de l'utilisation de fonctions d'agrégat à plusieurs arguments, la clause `ORDER BY` arrive après tous les arguments de l'agrégat. Par exemple, il faut écrire ceci :

    SELECT string_agg(a, ',' ORDER BY a) FROM table;

et non pas ceci :

    SELECT string_agg(a ORDER BY a, ',') FROM table;  -- incorrect

Ce dernier exemple est syntaxiquement correct, mais il concerne un appel à une fonction d'agrégat à un seul argument avec deux clés pour le `ORDER BY` (le deuxième étant inutile, car il est constant).

Si `DISTINCT` est indiqué en plus de la clause `clause_order_by`, alors toutes les expressions de l'`ORDER BY` doivent correspondre aux arguments de l'agrégat ; autrement dit, vous ne pouvez pas trier sur une expression qui n'est pas incluse dans la liste `DISTINCT`.

### Note

La possibilité de spécifier à la fois `DISTINCT` et `ORDER BY` dans une fonction d'agrégat est une extension de PostgreSQL.

Placer la clause `ORDER BY` dans la liste des arguments standards de l'agrégat, comme décrit jusqu'ici, est utilisé pour un agrégat de type général et statistique pour lequel le tri est optionnel. Il existe une sous-classe de fonctions d'agrégat appelée agrégat d'ensemble trié pour laquelle la clause `clause_order_by` est requise, habituellement parce que le calcul de l'agrégat est seulement sensible à l'ordre des lignes en entrée. Des exemples typiques d'agrégat avec ensemble trié incluent les calculs de rang et de pourcentage. Pour un agrégat d'ensemble trié, la clause `clause_order_by` est écrite à l'intérieur de `WITHIN GROUP (...)`, comme indiqué dans la syntaxe alternative finale. Les expressions dans `clause_order_by` sont évaluées une fois par ligne en entrée, comme n'importe quel argument d'un agrégat, une fois triées suivant la clause `clause_order_by`, et envoyées à la fonction en tant qu'arguments en entrée. (Ceci est contraire au cas de la clause `clause_order_by` en dehors d'un `WITHIN GROUP` , qui n'est pas traité comme argument de la fonction d'agrégat.) Les expressions d'argument précédant `WITHIN GROUP`, s'il y en a, sont appelées des arguments directs pour les distinguer des arguments agrégés listés dans `clause_order_by`. Contrairement aux arguments normaux d'agrégats, les arguments directs sont évalués seulement une fois par appel d'agrégat et non pas une fois par ligne en entrée. Cela signifie qu'ils peuvent contenir des variables seulement si ces variables sont regroupées par `GROUP BY` ; cette restriction équivaut à des arguments directs qui ne seraient pas dans une expression d'agrégat. Les arguments directs sont typiquement utilisés pour des fractions de pourcentage, qui n'ont de sens qu'en tant que valeur singulière par calcul d'agrégat. La liste d'arguments directs peut être vide ; dans ce cas, écrivez simplement `()`, et non pas `(*)`. (PostgreSQL accepte actuellement les deux écritures, mais seule la première est conforme au standard SQL.)

Voici un exemple d'appel d'agrégat à ensemble trié :

    SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY revenu) FROM proprietes;
     percentile_cont
    -----------------
               50489

qui obtient le 50e pourcentage ou le médian des valeurs de la colonne `revenu` de la table `proprietes`. Ici, `0.5` est un argument direct ; cela n'aurait pas de sens si la fraction de pourcentage était une valeur variant suivant les lignes.

Si la clause `FILTER` est spécifiée, alors seules les lignes en entrée pour lesquelles `filter_clause` est vraie sont envoyées à la fonction d'agrégat ; les autres lignes sont ignorées. Par exemple :

    SELECT
        count(*) AS nonfiltres,
        count(*) FILTER (WHERE i < 5) AS filtres
    FROM generate_series(1,10) AS s(i);
     nonfiltres | filtres
    ------------+---------
             10 |       4
    (1 row)

Les fonctions d'agrégat prédéfinies sont décrites dans la Section 9.21. D'autres fonctions d'agrégat pourraient être ajoutées par l'utilisateur.

Une expression d'agrégat peut seulement apparaître dans la liste de résultats ou dans la clause `HAVING` d'une commande `SELECT`. Elle est interdite dans d'autres clauses, telles que `WHERE`, parce que ces clauses sont logiquement évaluées avant que les résultats des agrégats ne soient calculés.

Lorsqu'une expression d'agrégat apparaît dans une sous-requête (voir la Section 4.2.11 et la Section 9.23), l'agrégat est normalement évalué sur les lignes de la sous-requête. Cependant, une exception survient si les arguments de l'agrégat (et `clause_filtre` si fourni) contiennent seulement des niveaux externes de variables : ensuite, l'agrégat appartient au niveau externe le plus proche et est évalué sur les lignes de cette requête. L'expression de l'agrégat est une référence externe pour la sous-requête dans laquelle il apparaît et agit comme une constante sur toute évaluation de cette requête. La restriction apparaissant seulement dans la liste de résultats ou dans la clause `HAVING` s'applique avec respect du niveau de requête auquel appartient l'agrégat.

### 4.2.8. Appels de fonction de fenêtrage

Un appel de fonction de fenêtrage représente l'application d'une fonction de type agrégat sur une portion des lignes sélectionnées par une requête. Contrairement aux appels de fonction d'agrégat standard, ce n'est pas lié au groupement des lignes sélectionnées en une seule ligne résultat -- chaque ligne reste séparée dans les résultats. Néanmoins, la fonction de fenêtrage a accès à toutes les lignes qui font partie du groupe de la ligne courante d'après la spécification du groupe (liste `PARTITION BY`) de l'appel de la fonction de fenêtrage. La syntaxe d'un appel de fonction de fenêtrage est une des suivantes :

    nom_fonction ([expression [, expression ... ]]) [ FILTER ( WHERE clause_filtre ) ] OVER nom_window
    nom_fonction ([expression [, expression ... ]]) [ FILTER ( WHERE clause_filtre ) ] OVER ( définition_window )
    nom_fonction ( * ) [ FILTER ( WHERE clause_filtre ) ] OVER nom_window
    nom_fonction ( * ) [ FILTER ( WHERE clause_filtre ) ] OVER ( définition_window )

où `définition_fenêtrage` a comme syntaxe :

    [ nom_fenêtrage_existante ]
    [ PARTITION BY expression [, ...] ]
    [ ORDER BY expression [ ASC | DESC | USING opérateur ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ clause_portée ]

et la clause `clause_portée` optionnelle fait partie de :

    { RANGE | ROWS | GROUPS } début_portée [ frame_exclusion ]
    { RANGE | ROWS | GROUPS } BETWEEN début_portée AND fin_portée [ frame_exclusion ]

avec `début_portée` et `fin_portée` pouvant faire partie de

    UNBOUNDED PRECEDING
    décalage PRECEDING
    CURRENT ROW
    décalage FOLLOWING
    UNBOUNDED FOLLOWING

et `frame_exclusion` peut valoir

    EXCLUDE CURRENT ROW
    EXCLUDE GROUP
    EXCLUDE TIES
    EXCLUDE NO OTHERS

Ici, `expression` représente toute expression de valeur qui ne contient pas elle-même d'appel à des fonctions de fenêtrage.

`nom_fenêtrage` est une référence à la spécification d'une fenêtre nommée, définie dans la clause `WINDOW` de la requête. Les spécifications de fenêtres nommées sont habituellement référencées avec `OVER` `nom_fenêtrage`, mais il est aussi possible d'écrire un nom de fenêtre entre parenthèses, puis de fournir en option une clause de tri et/ou une clause de portée (la fenêtre référencée ne doit pas avoir ces clauses si elles sont fournies ici). Cette dernière syntaxe suit les mêmes règles que la modification d'un nom de fenêtre existant dans une clause `WINDOW` ; voir la page de référence de SELECT pour les détails.

La clause `PARTITION BY` groupe les lignes de la requête en partitions, qui sont traitées séparément par la fonction de fenêtrage. `PARTITION BY` fonctionne de la même façon qu'une clause `GROUP BY` au niveau de la requête, sauf que ses expressions sont toujours des expressions et ne peuvent pas être des noms ou des numéros de colonnes en sortie. Sans `PARTITION BY`, toutes les lignes produites par la requête sont traitées comme une seule partition. La clause `ORDER BY` détermine l'ordre dans lequel les lignes d'une partition sont traitées par la fonction de fenêtrage. Cela fonctionne de la même façon que la clause `ORDER BY` d'une requête, mais ne peut pas non plus utiliser les noms ou les numéros des colonnes en sortie. Sans `ORDER BY`, les lignes sont traitées dans n'importe quel ordre.

La clause `clause_portée` indique l'ensemble de lignes constituant la portée de la fenêtre, qui est un sous-ensemble de la partition en cours, pour les fonctions de fenêtrage qui agissent sur ce sous-ensemble plutôt que sur la partition entière. L'ensemble de lignes dans la portée peut varier suivant la ligne courante. Le sous-ensemble peut être spécifié avec le mode `RANGE`, avec le mode `ROWS` ou avec le mode `GROUPS`. Dans les deux cas, il s'exécute de `début_portée` à `fin_portée`. Si `fin_portée` est omis, la fin vaut par défaut `CURRENT ROW`.

Un `début_portée` à `UNBOUNDED PRECEDING` signifie que le sous-ensemble commence avec la première ligne de la partition. De la même façon, un `fin_portée` à `UNBOUNDED FOLLOWING` signifie que le sous-ensemble se termine avec la dernière ligne de la partition.

Dans les modes `RANGE` et `GROUPS`, un `début_portée` à `CURRENT ROW` signifie que le sous-ensemble commence avec la ligne suivant la ligne courante (une ligne que la clause `ORDER BY` de la fenêtre considère comme équivalente à la ligne courante), alors qu'un `fin_portée` à `CURRENT ROW` signifie que le sous-ensemble se termine avec la dernière ligne homologue de la ligne en cours. Dans le mode `ROWS`, `CURRENT ROW` signifie simplement la ligne courante.

Dans les options de portée, `offset` de `PRECEDING` et `offset` de `FOLLOWING`, le `offset` doit être une expression ne contenant ni variables, ni fonctions d'agrégat, ni fonctions de fenêtrage. La signification de `offset` dépend du mode de porté :

- Dans le mode `ROWS`, `offset` doit renvoyer un entier non négatif non NULL, et l'option signifie que la portée commence ou finit au nombre spécifié de lignes avant ou après la ligne courante.

- Dans le mode `GROUPS`, `offset` doit de nouveau renvoyer un entier non négatif non NULL, et l'option signifie que la portée commence ou finit au nombre spécifié de groupes de lignes équivalentes avant ou après le groupe de la ligne courante, et où un groupe de lignes équivalentes est un ensemble de lignes équivalentes dans le tri `ORDER BY`. (Il doit y avoir une clause `ORDER BY` dans la définition de la fenêtre pour utiliser le mode `GROUPS`.)

- Dans le mode `RANGE`, ces options requièrent que la clause `ORDER BY` spécifient exactement une colonne. `offset` indique la différence maximale entre la valeur de cette colonne dans la ligne courante et sa valeur dans les lignes précédentes et suivantes de la portée. Le type de données de l'expression `offset` varie suivant le type de données de la colonne triée. Pour les colonnes ordonnées numériques, il s'agit habituellement du même type que la colonne ordonnée. Mais pour les colonnes ordonnées de type date/heure, il s'agit d'un `interval`. Par exemple, si la colonne ordonnée est de type `date` ou `timestamp`, on pourrait écrire `RANGE BETWEEN '1 day' PRECEDING AND '10 days' FOLLOWING`. `offset` est toujours requis pour être non NULL et non négatif, bien que la signification de « non négatif » dépend de son type de données.

Dans tous les cas, la distance jusqu'à la fin de la portée est limitée par la distance jusqu'à la fin de la partition, pour que les lignes proche de la fin de la partition, la portée puisse contenir moins de lignes qu'ailleurs.

Notez que dans les deux modes `ROWS` et `GROUPS`, `0 PRECEDING` et `0 FOLLOWING` sont équivalents à `CURRENT ROW`. Le mode `RANGE` en fait aussi partie habituellement, pour une signification appropriée de « zéro » pour le type de données spécifique.

L'option `frame_exclusion` permet aux lignes autour de la ligne courante d'être exclues de la portée, même si elles seraient incluses d'après les options de début et de fin de portée. `EXCLUDE CURRENT ROW` exclut la ligne courante de la portée. `EXCLUDE GROUP` exclut la ligne courante et ses équivalents dans l'ordre à partir de la portée. `EXCLUDE TIES` exclut de la portée tout équivalent de la ligne courante mais pas la ligne elle-même. `EXCLUDE NO OTHERS` spécifie explicitement le comportement par défaut lors de la non exclusion de la ligne courante ou de ses équivalents.

L'option par défaut est `RANGE UNBOUNDED PRECEDING`, ce qui est identique à `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`. Avec `ORDER BY`, ceci configure le sous-ensemble pour contenir toutes les lignes de la partition à partir de la ligne courante. Sans `ORDER BY`, toutes les lignes de la partition sont incluses dans le sous-ensemble de la fenêtre, car toutes les lignes deviennent voisines de la ligne en cours.

Les restrictions sont que `début_portée` ne peut pas valoir `UNBOUNDED FOLLOWING`, `fin_portée` ne peut pas valoir `UNBOUNDED PRECEDING`, et le choix de `fin_portée` ne peut pas apparaître avant la liste ci-dessus des options `début_portée` et `fin_portée` que le choix de `frame_start` -- par exemple, `RANGE BETWEEN CURRENT ROW AND `valeur` PRECEDING` n'est pas autorisé. Par exemple, `ROWS BETWEEN 7 PRECEDING AND 8 PRECEDING` est autorisé, même s'il ne sélectionnera aucune ligne.

Si `FILTER` est indiqué, seules les lignes en entrée pour lesquelles `clause_filtre` est vrai sont envoyées à la fonction de fenêtrage. Les autres lignes sont simplement ignorées. Seules les fonctions de fenêtrage qui sont des agrégats acceptent une clause `FILTER`.

Les fonctions de fenêtrage internes sont décrites dans la Tableau 9.62. D'autres fonctions de fenêtrage peuvent être ajoutées par l'utilisateur. De plus, toute fonction d'agrégat de type général ou statistique peut être utilisée comme fonction de fenêtrage. Néanmoins, les agrégats d'ensemble trié et d'ensemble hypothétique ne peuvent pas être utilisés actuellement comme des fonctions de fenêtrage.

Les syntaxes utilisant `*` sont utilisées pour appeler des fonctions d'agrégats sans paramètres en tant que fonctions de fenêtrage. Par exemple : `count(*) OVER (PARTITION BY x ORDER BY y)`. Le symbole `*` n'est habituellement pas utilisé pour les fonctions de fenêtrage. Les fonctions de fenêtrage n'autorisent pas l'utilisation de `DISTINCT` ou `ORDER BY` dans la liste des arguments de la fonction.

Les appels de fonctions de fenêtrage sont autorisés seulement dans la liste `SELECT` et dans la clause `ORDER BY` de la requête.

Il existe plus d'informations sur les fonctions de fenêtrages dans la Section 3.5, dans la Section 9.22 et dans la Section 7.2.5.

### 4.2.9. Conversions de type

Une conversion de type spécifie une conversion à partir d'un type de données vers un autre. PostgreSQL accepte deux syntaxes équivalentes pour les conversions de type :

    CAST ( expression AS type )
    expression::type

La syntaxe `CAST` est conforme à SQL ; la syntaxe avec `::` est historique dans PostgreSQL.

Lorsqu'une conversion est appliquée à une expression de valeur pour un type connu, il représente une conversion de type à l'exécution. Cette conversion réussira seulement si une opération convenable de conversion de type a été définie. Notez que ceci est subtilement différent de l'utilisation de conversion avec des constantes, comme indiqué dans la Section 4.1.2.7. Une conversion appliquée à une chaîne constante représente l'affectation initiale d'un type pour une valeur constante, et donc cela réussira pour tout type (si le contenu de la chaîne constante est une syntaxe acceptée en entrée pour le type de donnée).

Une conversion de type explicite pourrait être habituellement omise s'il n'y a pas d'ambiguïté sur le type qu'une expression de valeur pourrait produire (par exemple, lorsqu'elle est affectée à une colonne de table) ; le système appliquera automatiquement une conversion de type dans de tels cas. Néanmoins, la conversion automatique est réalisée seulement pour les conversions marquées « OK pour application implicite » dans les catalogues système. D'autres conversions peuvent être appelées avec la syntaxe de conversion explicite. Cette restriction a pour but d'empêcher l'exécution silencieuse de conversions surprenantes.

Il est aussi possible de spécifier une conversion de type en utilisant une syntaxe de type fonction :

    nom_type ( expression )

Néanmoins, ceci fonctionne seulement pour les types dont les noms sont aussi valides en tant que noms de fonctions. Par exemple, `double precision` ne peut pas être utilisé de cette façon, mais son équivalent `float8` le peut. De même, les noms `interval`, `time` et `timestamp` peuvent seulement être utilisés de cette façon s'ils sont entre des guillemets doubles, à cause des conflits de syntaxe. Du coup, l'utilisation de la syntaxe de conversion du style fonction amène à des incohérences et devrait probablement être évitée.

### Note

La syntaxe par fonction est en fait seulement un appel de fonction. Quand un des deux standards de syntaxe de conversion est utilisé pour faire une conversion à l'exécution, elle appellera en interne une fonction enregistrée pour réaliser la conversion. Par convention, ces fonctions de conversion ont le même nom que leur type de sortie et, du coup, la syntaxe par fonction n'est rien de plus qu'un appel direct à la fonction de conversion sous-jacente. Évidemment, une application portable ne devrait pas s'y fier. Pour plus d'informations, voir la page de manuel de CREATE CAST.

### 4.2.10. Expressions de collationnement

La clause `COLLATE` surcharge le collationnement d'une expression. Elle est ajoutée à l'expression à laquelle elle s'applique :

    expr COLLATE collationnement

où `collationnement` est un identificateur pouvant être qualifié par son schéma. La clause `COLLATE` a priorité par rapport aux opérateurs ; des parenthèses peuvent être utilisées si nécessaire.

Si aucun collationnement n'est spécifiquement indiqué, le système de bases de données déduit cette information du collationnement des colonnes impliquées dans l'expression. Si aucune colonne ne se trouve dans l'expression, il utilise le collationnement par défaut de la base de données.

Les deux utilisations principales de la clause `COLLATE` sont la surcharge de l'ordre de tri dans une clause `ORDER BY`, par exemple :

    SELECT a, b, c FROM tbl WHERE ... ORDER BY a COLLATE "C";

et la surcharge du collationnement d'une fonction ou d'un opérateur qui produit un résultat sensible à la locale, par exemple :

    SELECT * FROM tbl WHERE a > 'foo' COLLATE "C";

Notez que, dans le dernier cas, la clause `COLLATE` est attachée à l'argument en entrée de l'opérateur. Peu importe l'argument de l'opérateur ou de la fonction qui a la clause `COLLATE`, parce que le collationnement appliqué à l'opérateur ou à la fonction est dérivé en considérant tous les arguments, et une clause `COLLATE` explicite surchargera les collationnements des autres arguments. (Attacher des clauses `COLLATE` différentes sur les arguments aboutit à une erreur. Pour plus de détails, voir la Section 24.2.) Du coup, ceci donne le même résultat que l'exemple précédent :

    SELECT * FROM tbl WHERE a COLLATE "C" > 'foo';

Mais ceci n'est pas valide :

    SELECT * FROM tbl WHERE (a > 'foo') COLLATE "C";

car cette requête cherche à appliquer un collationnement au résultat de l'opérateur `>`, qui est du type `boolean`, type non sujet au collationnement.

### 4.2.11. Sous-requêtes scalaires

Une sous-requête scalaire est une requête `SELECT` ordinaire entre parenthèses renvoyant exactement une ligne avec une colonne (voir le Chapitre 7 pour plus d'informations sur l'écriture des requêtes). La requête `SELECT` est exécutée et la seule valeur renvoyée est utilisée dans l'expression de valeur englobante. C'est une erreur d'utiliser une requête qui renvoie plus d'une ligne ou plus d'une colonne comme requête scalaire. Mais si, lors d'une exécution particulière, la sous-requête ne renvoie pas de lignes, alors il n'y a pas d'erreur ; le résultat scalaire est supposé NULL. La sous-requête peut référencer des variables de la requête englobante, qui agiront comme des constantes durant toute évaluation de la sous-requête. Voir aussi la Section 9.23 pour d'autres expressions impliquant des sous-requêtes.

Par exemple, ce qui suit trouve la ville disposant de la population la plus importante dans chaque état :

    SELECT nom, (SELECT max(pop) FROM villes WHERE villes.etat = etat.nom)
        FROM etats;

### 4.2.12. Constructeurs de tableaux

Un constructeur de tableau est une expression qui construit une valeur de tableau à partir de la valeur de ses membres. Un constructeur de tableau simple utilise le mot-clé `ARRAY`, un crochet ouvrant `[`, une liste d'expressions (séparées par des virgules) pour les valeurs des éléments du tableau et finalement un crochet fermant `]`. Par exemple :

    SELECT ARRAY[1,2,3+4];
      array
    ---------
     {1,2,7}
    (1 row)

Par défaut, le type d'élément du tableau est le type commun des expressions des membres, déterminé en utilisant les mêmes règles que pour les constructions `UNION` ou `CASE` (voir la Section 10.5). Vous pouvez surcharger ceci en convertissant explicitement le constructeur de tableau vers le type désiré. Par exemple :

    SELECT ARRAY[1,2,22.7]::integer[];
      array
    ----------
     {1,2,23}
    (1 row)

Ceci a le même effet que la conversion de chaque expression vers le type d'élément du tableau individuellement. Pour plus d'informations sur les conversions, voir la Section 4.2.9.

Les valeurs de tableaux multidimensionnels peuvent être construites par des constructeurs de tableaux imbriqués. Pour les constructeurs internes, le mot-clé `ARRAY` peut être omis. Par exemple, ces expressions produisent le même résultat :

    SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]];
         array
    ---------------
     {{1,2},{3,4}}
    (1 row)

    SELECT ARRAY[[1,2],[3,4]];
         array
    ---------------
     {{1,2},{3,4}}
    (1 row)

Comme les tableaux multidimensionnels doivent être rectangulaires, les constructeurs internes du même niveau doivent produire des sous-tableaux de dimensions identiques. Toute conversion appliquée au constructeur `ARRAY` externe se propage automatiquement à tous les constructeurs internes.

Les éléments d'un constructeur de tableau multidimensionnel peuvent être tout ce qui récupère un tableau du bon type, pas seulement une construction d'un tableau imbriqué. Par exemple :

    CREATE TABLE tab(f1 int[], f2 int[]);

    INSERT INTO tab VALUES (ARRAY[[1,2],[3,4]], ARRAY[[5,6],[7,8]]);

    SELECT ARRAY[f1, f2, '{{9,10},{11,12}}'::int[]] FROM tab;
                         array
    ------------------------------------------------
     {{{1,2},{3,4}},{{5,6},{7,8}},{{9,10},{11,12}}}
    (1 row)

Vous pouvez construire un tableau vide, mais comme il est impossible d'avoir un tableau sans type, vous devez convertir explicitement votre tableau vide dans le type désiré. Par exemple :

    SELECT ARRAY[]::integer[];
     array
    -------
     {}
    (1 row)

Il est aussi possible de construire un tableau à partir des résultats d'une sous-requête. Avec cette forme, le constructeur de tableau est écrit avec le mot-clé `ARRAY` suivi par une sous-requête entre parenthèses (et non pas des crochets). Par exemple :

    SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');
                                  array
    ------------------------------------------------------------------
     {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31,2412}

    SELECT ARRAY(SELECT ARRAY[i, i*2] FROM generate_series(1,5) AS a(i));
                  array
    ----------------------------------
     {{1,2},{2,4},{3,6},{4,8},{5,10}}
    (1 row)

La sous-requête doit renvoyer une seule colonne. Si la sortie de la sous- requête n'est pas de type tableau, le tableau à une dimension résultant aura un élément pour chaque ligne dans le résultat de la sous-requête, avec un type élément correspondant à celui de la colonne en sortie de la sous- requête. Si la colonne en sortie de la sous-requête est de type tableau, le résultat sera un tableau du même type, mais avec une dimension supplémentaire ; dans ce cas, toutes les lignes de la sous-requête doivent renvoyer des tableaux de dimension identique (dans le cas contraire, le résultat ne serait pas rectangulaire).

Les indices d'un tableau construit avec `ARRAY` commencent toujours à un. Pour plus d'informations sur les tableaux, voir la Section 8.15.

### 4.2.13. Constructeurs de lignes

Un constructeur de ligne est une expression qui construit une valeur de ligne (aussi appelée une valeur composite) à partir des valeurs de ses membres. Un constructeur de ligne consiste en un mot-clé `ROW`, une parenthèse gauche, zéro ou une ou plus d'une expression (séparées par des virgules) pour les valeurs des champs de la ligne, et enfin une parenthèse droite. Par exemple :

    SELECT ROW(1,2.5,'ceci est un test');

Le mot-clé `ROW` est optionnel lorsqu'il y a plus d'une expression dans la liste.

Un constructeur de ligne peut inclure la syntaxe `valeurligne``.*`, qui sera étendue en une liste d'éléments de la valeur ligne, ce qui est le comportement habituel de la syntaxe `.*` utilisée au niveau haut d'une liste `SELECT` (voir Section 8.16.5). Par exemple, si la table `t` a les colonnes `f1` et `f2`, ces deux requêtes sont identiques :

    SELECT ROW(t.*, 42) FROM t;
    SELECT ROW(t.f1, t.f2, 42) FROM t;

### Note

Avant PostgreSQL 8.2, la syntaxe `.*` n'était pas étendue dans les constructeurs de lignes. De ce fait, `ROW(t.*, 42)` créait une ligne à deux champs dont le premier était une autre valeur de ligne. Le nouveau comportement est généralement plus utile. Si vous avez besoin de l'ancien comportement de valeurs de ligne imbriquées, écrivez la valeur de ligne interne sans `.*`, par exemple `ROW(t, 42)`.

Par défaut, la valeur créée par une expression `ROW` est d'un type d'enregistrement anonyme. Si nécessaire, il peut être converti en un type composite nommé -- soit le type de ligne d'une table, soit un type composite créé avec `CREATE TYPE AS`. Une conversion explicite pourrait être nécessaire pour éviter toute ambiguïté. Par exemple :

    CREATE TABLE ma_table(f1 int, f2 float, f3 text);

    CREATE FUNCTION recup_f1(ma_table) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL;

    -- Aucune conversion nécessaire parce que seul un recup_f1() existe
    SELECT recup_f1(ROW(1,2.5,'ceci est un test'));
     recup_f1
    ----------
    1
    (1 row)

    CREATE TYPE mon_typeligne AS (f1 int, f2 text, f3 numeric);

    CREATE FUNCTION recup_f1(mon_typeligne) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL;

    -- Maintenant, nous avons besoin d'une conversion
    -- pour indiquer la fonction à appeler
    SELECT recup_f1(ROW(1,2.5,'ceci est un test'));
    ERROR:  function recup_f1(record) is not unique

    SELECT recup_f1(ROW(1,2.5,'ceci est un test')::ma_table);
     getf1
    -------
     1
    (1 row)

    SELECT recup_f1(CAST(ROW(11,'ceci est un test',2.5) AS mon_typeligne));
     getf1
    -------
     11
    (1 row)

Les constructeurs de lignes peuvent être utilisés pour construire des valeurs composites à stocker dans une colonne de table de type composite ou pour être passés à une fonction qui accepte un paramètre composite. De plus, il est possible de comparer deux valeurs de lignes ou de tester une ligne avec `IS NULL` ou `IS NOT NULL`, par exemple

    SELECT ROW(1,2.5,'ceci est un test') = ROW(1, 3, 'pas le même');

    SELECT ROW(table.*) IS NULL FROM table; -- détecte toutes les lignes non NULL

Pour plus de détails, voir la Section 9.24. Les constructeurs de lignes peuvent aussi être utilisés en relation avec des sous-requêtes, comme discuté dans la Section 9.23.

### 4.2.14. Règles d'évaluation des expressions

L'ordre d'évaluation des sous-expressions n'est pas défini. En particulier, les entrées d'un opérateur ou d'une fonction ne sont pas obligatoirement évaluées de la gauche vers la droite ou dans un autre ordre fixé.

De plus, si le résultat d'une expression peut être déterminé par l'évaluation de certaines parties de celle-ci, alors d'autres sous-expressions devraient ne pas être évaluées du tout. Par exemple, si vous écrivez :

    SELECT true OR une_fonction();

alors `une_fonction()` pourrait (probablement) ne pas être appelée du tout. Pareil dans le cas suivant :

    SELECT une_fonction() OR true;

Notez que ceci n'est pas identique au « court-circuitage » de gauche à droite des opérateurs booléens utilisé par certains langages de programmation.

En conséquence, il est déconseillé d'utiliser des fonctions ayant des effets de bord dans une partie des expressions complexes. Il est particulièrement dangereux de se fier aux effets de bord ou à l'ordre d'évaluation dans les clauses `WHERE` et `HAVING`, car ces clauses sont reproduites de nombreuses fois lors du développement du plan d'exécution. Les expressions booléennes (combinaisons `AND`/`OR`/`NOT`) dans ces clauses pourraient être réorganisées d'une autre façon autorisée dans l'algèbre booléenne.

Quand il est essentiel de forcer l'ordre d'évaluation, une construction `CASE` (voir la Section 9.18) peut être utilisée. Voici un exemple qui ne garantit pas qu'une division par zéro ne soit pas faite dans une clause `WHERE` :

    SELECT ... WHERE x > 0 AND y/x > 1.5;

Mais ceci est sûr :

    SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

Une construction `CASE` utilisée de cette façon déjouera les tentatives d'optimisation, donc cela ne sera à faire que si c'est nécessaire (dans cet exemple particulier, il serait sans doute mieux de contourner le problème en écrivant `y > 1.5*x`).

Néanmoins, `CASE` n'est pas un remède à tout. Une limitation à la technique illustrée ci-dessus est qu'elle n'empêche pas l'évaluation en avance des sous-expressions constantes. Comme décrit dans Section 38.7, les fonctions et les opérateurs marqués `IMMUTABLE` peuvent être évalués quand la requête est planifiée plutôt que quand elle est exécutée. Donc, par exemple :

     SELECT CASE WHEN x > 0 THEN x ELSE 1/0 END FROM tab;

va produire comme résultat un échec pour division par zéro, car le planificateur a essayé de simplifier la sous-expression constante, même si chaque ligne de la table a `x > 0` de façon à ce que la condition `ELSE` ne soit jamais exécutée.

Bien que cet exemple particulier puisse sembler stupide, il existe de nombreux cas moins évidents, n'impliquant pas de constantes, mais plutôt des requêtes exécutées par des fonctions, quand les valeurs des arguments des fonctions et de variables locales peuvent être insérées dans les requêtes en tant que constantes toujours dans le but de la planification. À l'intérieur de fonctions PL/pgSQL, par exemple, utiliser une instruction `IF`-`THEN`- `ELSE` pour protéger un calcul risqué est beaucoup plus sûr qu'une expression `CASE`.

Une autre limitation de cette technique est qu'une expression `CASE` ne peut pas empêcher l'évaluation d'une expression d'agrégat contenue dans cette expression, car les expressions d'agrégat sont calculées avant les expressions « scalaires » dans une liste `SELECT` ou dans une clause `HAVING`. Par exemple, la requête suivante peut provoquer une erreur de division par zéro bien qu'elle semble protégée contre ce type d'erreurs :

    SELECT CASE WHEN min(employees) > 0
                THEN avg(expenses / employees)
           END
        FROM departments;

Les agrégats `min()` et `avg()` sont calculés en même temps avec toutes les lignes en entrée, donc si une ligne a une valeur 0 pour la colonne `employees`, l'erreur de division par zéro surviendra avant d'avoir pu tester le résultat de `min()`. Il est préférable d'utiliser une clause `WHERE` ou une clause `FILTER` pour empêcher les lignes problématiques en entrée d'atteindre la fonction d'agrégat.