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

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

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.

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

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 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.64. 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.

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.

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.

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;

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.

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;
    

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.

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.