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.
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).
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.
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 elle-même une expression, qui 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.
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.
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, 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.
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. 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, veillez à respecter certaines mesures de sécurité disponibles dans Section 10.3 lors de l'écriture des appels de fonctions.
En option, les arguments peuvent avoir leur nom attaché. Voir la Section 4.3 pour les détails.
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.
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 ( WHEREclause_filtre
) ]nom_agregat
(ALLexpression
[ , ... ] [clause_order_by
] ) [ FILTER ( WHEREclause_filtre
) ]nom_agregat
(DISTINCTexpression
[ , ... ] [clause_order_by
] ) [ FILTER ( WHEREclause_filtre
) ]nom_agregat
( * ) [ FILTER ( WHEREclause_filtre
) ]nom_agregat
( [expression
[ , ... ] ] ) WITHIN GROUP (clause_order_by
) [ FILTER ( WHEREclause_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
.
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 avec le 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.20. 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.22), 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.
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 ( WHEREclause_filtre
) ] OVERnom_window
nom_fonction
([expression
[,expression
... ]]) [ FILTER ( WHEREclause_filtre
) ] OVER (définition_window
)nom_fonction
( * ) [ FILTER ( WHEREclause_filtre
) ] OVERnom_window
nom_fonction
( * ) [ FILTER ( WHEREclause_filtre
) ] OVER (définition_window
)
où définition_fenêtrage
a comme
syntaxe :
[nom_fenêtrage_existante
] [ PARTITION BYexpression
[, ...] ] [ ORDER BYexpression
[ ASC | DESC | USINGopérateur
] [ NULLS { FIRST | LAST } ] [, ...] ] [clause_portée
]
et la clause clause_portée
optionnelle fait partie de :
{ RANGE | ROWS }début_portée
{ RANGE | ROWS } BETWEENdébut_portée
ANDfin_portée
avec début_portée
et
fin_portée
pouvant faire partie de
UNBOUNDED PRECEDINGvaleur
PRECEDING CURRENT ROWvaleur
FOLLOWING UNBOUNDED FOLLOWING
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. Le sous-ensemble peut être spécifié avec le mode
RANGE
ou avec le mode ROWS
. Dans les
deux cas, il s'exécute de début_portée
à
fin_portée
. Si fin_portée
est omis, il 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 le mode RANGE
, un
début_portée
à CURRENT ROW
signifie que le sous-ensemble commence avec la ligne suivant la ligne
courante (une ligne que ORDER BY
considère comme
équivalente à la ligne courante), alors qu'un
fin_portée
à CURRENT ROW
signifie que le sous-ensemble se termine avec le ORDER BY
équivalent. Dans
le mode ROWS
, CURRENT ROW
signifie
simplement la ligne courante.
La valeur
PRECEDING
et la
valeur
FOLLOWING
sont
actuellement seulement autorisées dans le mode ROWS
.
Elles indiquent que le sous-ensemble commence ou finit au nombre spécifié
de lignes avant ou après la ligne courante.
valeur
doit être une expression entière ne
contenant pas de variables, de fonctions d'agrégat ou de fonctions de
fenêtrage. La valeur doit être non NULL et positive. Elle peut être égale
à zéro, auquel cas elle sélectionne simplement la ligne courante.
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 le choix de
début_portée
-- par exemple, RANGE
BETWEEN CURRENT ROW AND
n'est pas autorisé.
valeur
PRECEDING
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.57. 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.21 et dans la Section 7.2.5.
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
AStype
)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.
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.
La clause COLLATE
surcharge le collationnement d'une
expression. Elle est ajoutée à l'expression à laquelle elle
s'applique :
expr
COLLATEcollationnement
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 23.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.
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.22 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 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,2413} (1 row) 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.
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;
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.23. Les constructeurs de lignes peuvent aussi être utilisés en relation avec des sous-requêtes, comme discuté dans la Section 9.22.
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.17)
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 37.6, 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.