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.14, « 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. 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, par un nom de schéma dans le deuxième cas.

Il existe trois 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)

expression opérateur (opérateur unaire suffixe)

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 :

nom_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 or a window function call.

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.18, « 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.10, « Sous-requêtes scalaires » et la Section 9.20, « 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.

Un appel de fonction window représente l'application d'une fonction de type agrégat sur une portion des lignes sélectionnées par une requpete. 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 window est capable de parcourir 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 window. La syntaxe d'un appel de fonction window est une des suivantes :

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

où définition_window a comme syntaxe :

[ nom_window_existante ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING opérateur ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ clause_frame ]

et la clause clause_frame optionnelle fait partie de :

RANGE UNBOUNDED PRECEDING
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
ROWS UNBOUNDED PRECEDING
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Ici, expression représente toute expression de valeur qui ne contient pas elle-mêle d'appel à des fonctions window. Les listes PARTITION BY et ORDER BY ont essentiellement la même syntaxe et la même sémantique que les clauses GROUP BY et ORDER BY de la requête complète, sauf que leur expressions sont toujours seulement des expressions et ne peuvent pas être des noms ou des numéros de colonnes en sortie. nom_window est une référence à la spécification d'une window nommée, définie dans la clause WINDOW de la requête. Autrement, une définition_window complète peut être donnée dans des parenthèses, en utilisant la même syntaxe que pour la défintion d'une fenêtre nommée dans la clause WINDOW ; voir la page de référence SELECT pour les détails. Il est préférable de préciser que OVER nom_fenêtre n'est pas strictement équivalent à OVER (nom_fenêtre) ; ce dernier signifie la copie et la modification de la définition de la fenêtre, et sera rejeté si la spécification de la fenêtre référencée inclut une clause de portée.

La clause clause_frame indique l'ensemble de lignes constituant le frame window, pour les fonctions window qui agissent sur le frame et non pas sur la partition entière. L'option de frame par défaut est RANGE UNBOUNDED PRECEDING, qui est identique à RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ; elle sélectionne les lignes jusqu'au dernier pair de la ligne courante dans l'ordre établi par la clause ORDER BY (ce qui signifie toutes les lignes s'il n'y a pas de clause ORDER BY). Les options RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING et ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING sont aussi équivalentes ; elles sélectionnent toujours toutes les lignes dans la partition. Enfin, ROWS UNBOUNDED PRECEDING ou son équivalent verbeux ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW sélectionnent toutes les lignes jusqu'à la ligne actuelle (sans considération pour les lignes dupliquées). Attention que cette option peut produire des résultats dépendants de l'implantation si la clause ORDER BY n'ordonne pas les lignes de façon unique.

Les fonctions window internes sont décrites dans Tableau 9.44, « Fonctions Window généralistes ». D'autres fonctions window peuvent être ajoutées par l'utilisateur. De plus, toute fonction d'agrégat interne ou définie par l'utilisateur peut être utilisée comme fonction window.

Les syntaxes utilisant * sont utilisées pour appeler des fonctions d'agrégats sans paramètres en tant que fonctions window. Par exemple : count(*) OVER (PARTITION BY x ORDER BY y). * n'est habituellement pas utilisé pour les fonctions window qui ne sont pas des agrégats. Les fonctions window agrégats, contrairement aux fonctions d'agrégats normales, n'autorisent pas l'utilisation de DISTINCT dans la liste des arguments de la fonction.

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

Section 3.5, « Fonctions de fenêtrage » et Section 7.2.4, « Traitement de fonctions Window » proposent plus d'informations sur les fonctions window.

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.7, « 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.

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.20, « 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 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, « Constructions UNION, CASE et constructions relatives »). 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 Section 4.2.9, « Conversions de type ».

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. 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%');
                          ?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.14, « 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;

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.21, « 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.20, « 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.16, « 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).