4.2. Expressions de valeurs

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 ou un des mots clés NEW ou OLD (NEW et OLD peuvent seulement apparaître dans les règles de réécriture alors que les autres noms de corrélation peuvent être utilisés dans toute instruction SQL). 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, Requêtes).

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 commande 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 comme

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

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

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]

ou des éléments adjacents (un « morceau de tableau ») peuvent être extrait en écrivant

expression[indice_bas:indice_haut]

Ici, les crochets [ ] doivent apparaître littéralement. Chaque indice est lui-même une expression, qui doit contenir une valeur entière.

En général, l'expression de type tableau doit être entre parenthèses mais ces dernières peuvent être omises lorsque l'expression à indicer est seulement une référence de colonne ou une position de paramètre. De plus, les indices multiples peuvent être concaténés lorsque le tableau original est multi-dimensionnel. 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.10, « Tableaux » pour plus d'informations sur les tableaux.

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

Donc, une référence de colonne qualifiée est réellement un cas spécial de syntaxe de sélection de champ.

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

où le jeton opérateur suit les règles de syntaxe de la Section 4.1.3, « Opérateurs », 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)

Quel opérateur particulier existe et est-il unaire ou binaire dépend des opérateurs définis par le système ou l'utilisateur. Le Chapitre 9, Fonctions et opérateurs décrit les opérateurs internes.

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 :

fonction([expression [,expression ...]] )

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

sqrt(2)

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

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 [ , ... ] )
nom_agregat (ALL expression)
nom_agregat (DISTINCT expression [ , ... ] )
nom_agregat ( * )

où nom_agregat est un agrégat précédemment défini (parfois qualifié d'un nom de schéma) et expression est toute expression de valeur qui ne contient pas lui-même une expression d'agrégat.

La première forme d'expression d'agrégat appelle l'agrégat pour toutes les lignes en entrée pour lesquelles l'expression donnée ne trouve pas des valeurs NULL (en fait, c'est à la fonction d'agrégat de savoir si elle doit ignorer ou non les valeurs NULL... mais toutes les fonctions standards le font). La seconde forme est identique à la première car ALL est ajouté par défaut. La troisième forme implique l'agrégat pour toutes les valeurs non NULL et distinctes des expressions trouvées dans les lignes en entrée. La dernière forme appelle l'agrégat une fois pour chaque ligne en entrée qu'elle soit NULL ou non ; 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(*).

Par exemple, count(*) trouve le nombre total de lignes en entrée ; count(f1) récupère le nombre de lignes en entrée pour lesquelles f1 n'est pas NULL ; count(distinct f1) retrouve le nombre de valeurs distinctes non NULL de f1.

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

Une expression d'agrégat pourrait apparaître dans la liste de résultat 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 formés.

Lorsqu'une expression d'agrégat apparaît dans une sous-requête (voir la Section 4.2.9, « Sous-requêtes scalaires » et la Section 9.16, « Expressions de sous-requêtes »), l'agrégat est normalement évalué sur les lignes de la sous-requête. Mais, une exception arrive si les arguments de l'agrégat 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 en un tout 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ésultat ou dans la clause HAVING s'applique avec respect du niveau de requête auquel appartient l'agrégat.

Note

PostgreSQL™ ne supporte pas actuellement un DISTINCT avec plus d'une expression en entrée.

Une conversion de type spécifie une conversion à partir d'un type de données en 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.5, « Constantes d'autres types ». Une conversion appliquée à une chaîne littérale représente l'affectation initiale d'un type pour une valeur constante littérale, et donc cela réussira pour tout type (si le contenu de la chaîne littérale 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'application 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 inconsistances et devrait probablement être évitée dans les nouvelles applications. (La syntaxe style 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 style 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.)

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, Requêtes 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.16, « Expressions de sous-requêtes » 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;

Un constructeur de tableau est une expression qui construit une valeur de tableau à partir de valeurs de ses membres. Un constructeur de tableau simple utilise le mot clé ARRAY, un crochet ouvrant [, une ou plusieurs 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)

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, « Constructions UNION, CASE et constructions relatives »).

Les valeurs de tableaux multidimensionnels peuvent être construits 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.

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)

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%');
                          ?column?
-------------------------------------------------------------
 {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31}
(1 row)

La sous-requête doit renvoyer une seule colonne. 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.

Les indices d'une valeur de tableau construit avec ARRAY commencent toujours à un. Pour plus d'informations sur les tableaux, voir la Section 8.10, « Tableaux ».

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 plus d'une expression (séparées par des virgules) pour les valeurs des champs de la ligne, et finalement 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 rowvalue.*, qui sera étendue en une liste d'éléments de la valeur ligne, ce qui arrive lorsque la syntaxe .* est utilisée au niveau haut d'une liste SELECT. Par exemple, si la table t a les colonnes f1 et f2, elles 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, pour que écrire ROW(t.*, 42) crée une ligne à deux champs dont le premier est une autre valeur de ligne. Le nouveau comportement est habituellement 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 une 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é à une fonction qui accepte un paramètre composite. De plus, il est possible de comparer deux valeurs de lignes ou pour 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.17, « Comparaisons de lignes et de tableaux ». Les constructeurs de lignes peuvent aussi être utilisés en connexion avec des sous-requêtes, comme discuté dans la Section 9.16, « Expressions de sous-requêtes ».

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 nécessairement é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.13, « Expressions conditionnelles ») peut être utilisée. Par exemple, c'est une façon, non sûre, d'essayer d'éviter une division par zéro 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 fait que si nécessaire (dans cet exemple particulier, il serait sans doute mieux de contourner le problème en écrivant y > 1.5*x).