Uma coluna pode ser referenciada na forma

correlação.nome_da_coluna

onde correlação é o nome da tabela (possivelmente qualificada com o nome do esquema), ou um alias ^[28] para a tabela definido por meio da cláusula FROM. O nome da correlação e o ponto separador podem ser omitidos, se o nome da coluna for único entre todas as tabelas usadas na consulta corrente. (Veja também o Capítulo 7.)

Uma referência a parâmetro posicional é usada para indicar um valor fornecido externamente para uma instrução SQL. Os parâmetros são usados em definições de funções SQL e em consultas preparadas. Algumas bibliotecas cliente também aceitam a atribuição dos valores dos dados em separado da cadeia de comando SQL, caso em que os parâmetros são usados para fazer referência aos valores dos dados externamente. A forma de referência a um parâmetro é:

$número

Por exemplo, considere a definição da função dept como sendo:

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

Nesse exemplo, $1 faz referência ao valor do primeiro argumento da função sempre que essa função é chamada.

Se a expressão produzir um valor do tipo matriz, então o valor de um elemento específico da matriz pode ser extraído escrevendo

expressão[índice]

ou vários elementos adjacentes (uma “fatia da matriz”) podem ser extraídos escrevendo

expressão[índice_inferior:índice_superior]

(Aqui, os colchetes [ ] devem aparecer literalmente.) Cada índice é em si próprio uma expressão, arredondada para o valor inteiro mais próximo.

Em geral, a expressão de matriz deve estar entre parênteses, mas estes podem ser omitidos quando a expressão utilizada como índice for apenas uma referência à coluna ou parâmetro posicional. Além disso, podem ser concatenados vários índices quando a matriz original for multidimensional. Por exemplo:

minha_tabela.linha_da_matriz_1d[4]
minha_tabela.linha_coluna_da_matriz_2d[17][34]
$1[10:42]
(função_de_matriz(a,b))[42]

No último exemplo, os parênteses são requeridos. Veja a Seção 8.15 para obter mais informações sobre matrizes.

Se uma expressão gerar um valor do tipo composto (tipo linha), um campo específico da linha pode ser referenciado escrevendo:

expressão.nome_do_campo

Em geral, a expressão de linha deve estar entre parênteses, mas os parênteses podem ser omitidos quando a expressão da qual a seleção é feita é apenas uma referência a uma tabela ou a um parâmetro posicional. Por exemplo:

minha_tabela.minha_coluna
$1.alguma_coluna
(função_de_linha(a,b)).col3

(Portanto, uma referência de coluna qualificada é, na verdade, apenas um caso especial da sintaxe de seleção de campo.) Um caso especial importante é requisitar um campo de uma coluna de tabela que seja do tipo composto:

(coluna_composta).algum_campo
(minha_tabela.coluna_composta).algum_campo

Aqui os parênteses são requeridos para mostrar que coluna_composta é um nome de coluna e não um nome de tabela, ou que minha_tabela é um nome de tabela e não um nome de esquema no segundo caso.

Você pode requisitar todos os campos do valor composto escrevendo .*:

(coluna_composta).*

Essa notação se comporta de forma diferente dependendo do contexto. Veja a Seção 8.16.5 para obter mais informações.

Existem duas sintaxes possíveis para uma chamada de operador:

^[29] onde o token do operador segue as regras de sintaxe da Seção 4.1.3, ou é uma das palavras-chave AND, OR ou NOT, ou é um nome de operador qualificado na forma:

OPERADOR(esquema.nome_do_operador)

Quais operadores específicos existem, e se são unários ou binários, depende de quais operadores foram definidos pelo sistema ou pelo usuário. O Capítulo 9 descreve os operadores nativos.

A sintaxe para chamada de função é o nome da função (possivelmente qualificada pelo nome de esquema), seguido por sua lista de argumentos entre parênteses:

nome_da_função ([expressão [, expressão ... ]] )

Por exemplo, a função a seguir calcula a raiz quadrada de 2:

sqrt(2)

A relação das funções nativas se encontra no Capítulo 9. Os usuários podem adicionar outras funções.

Ao executar consultas em um banco de dados onde alguns usuários não confiam em outros usuários, devem ser observadas as medidas de segurança na Seção 10.3 ao escrever chamadas de função.

Opcionalmente, os argumentos podem ter nomes anexados. Veja a Seção 4.3 para obter mais detalhes.

Uma expressão de agregação representa a aplicação de uma função de agregação nas linhas selecionadas por uma consulta. Uma função de agregação reduz várias entradas a um único valor de saída, como a soma ou a média das entradas. A sintaxe da expressão de agregação é uma das seguintes:

nome_da_agregação (expressão [ , ... ] [ cláusula_order_by ] ) [ FILTER ( WHERE cláusula_filtro ) ]
nome_da_agregação (ALL expressão [ , ... ] [ cláusula_order_by ] ) [ FILTER ( WHERE cláusula_filtro ) ]
nome_da_agregação (DISTINCT expressão [ , ... ] [ cláusula_order_by ] ) [ FILTER ( WHERE cláusula_filtro ) ]
nome_da_agregação ( * ) [ FILTER ( WHERE cláusula_filtro ) ]
nome_da_agregação ( [ expressão [ , ... ] ] ) WITHIN GROUP ( cláusula_order_by ) [ FILTER ( WHERE cláusula_filtro ) ]

onde nome_da_agregação é uma agregação definida anteriormente (possivelmente qualificada com um nome do esquema) e expressão é qualquer expressão de valor que não contenha uma expressão de agregação, ou uma chamada de função de janela. As cláusulas opcionais cláusula_order_by e cláusula_filtro são descritas abaixo.

A primeira forma de expressão de agregação chama a agregação uma vez para cada linha da entrada. A segunda forma é igual à primeira, porque ALL é o padrão. A terceira forma chama a agregação uma vez para cada valor distinto da expressão (ou conjunto distinto de valores, para múltiplas expressões) encontrado nas linhas da entrada. A quarta forma chama a agregação uma vez para cada linha da entrada; como nenhum valor de entrada em particular é especificado, é geralmente útil apenas para a função de agregação count(*). A última forma é usada com funções de agregação de conjunto ordenado, descritas abaixo.

A maioria das funções de agregação ignora entradas nulas, de modo que as linhas em que uma ou mais expressões resultem no valor nulo são descartadas. Isso pode ser assumido como sendo verdadeiro para todas as agregações internas, a menos que seja especificado de outra forma.

Por exemplo, count(*) retorna o número total de linhas da entrada, enquanto count(f1) retorna o número de linhas da entrada onde f1 não é nulo, porque count ignora os valores nulos; e count(distinct f1) retorna o número de valores distintos não nulos de f1.

Normalmente, as linhas da entrada são passadas para a função de agregação em uma ordem não especificada. Em muitos casos isso não faz diferença; por exemplo, min produz o mesmo resultado não importando a ordem com que recebe as entradas. No entanto, algumas funções de agregação (como array_agg e string_agg) produzem resultados que dependem da ordem das linhas da entrada. Ao se usar esse tipo de agregação, a cláusula opcional cláusula_order_by pode ser usada para definir a ordenação desejada. A cláusula_order_by tem a mesma sintaxe da cláusula ORDER BY no nível de consulta, conforme descrito na Seção 7.5, exceto pelas suas expressões serem sempre expressões simples, não podendo ser nomes ou números das colunas de saída. Por exemplo:

WITH vals (v) AS ( VALUES (1),(3),(4),(3),(2) )
SELECT array_agg(v ORDER BY v DESC) FROM vals;

  array_agg
-------------
 {4,3,3,2,1}

Como jsonb mantém apenas a última chave correspondente, a ordem de suas chaves pode ser significativa:

WITH vals (k, v) AS ( VALUES ('key0','1'), ('key1','3'), ('key1','2') )
SELECT jsonb_object_agg(k, v ORDER BY v) FROM vals;

      jsonb_object_agg
----------------------------
 {"key0": "1", "key1": "3"}

Ao lidar com funções de agregação com vários argumentos, observe que a cláusula ORDER BY vem depois de todos os argumentos da agregação. Por exemplo, escreva isso

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

e não isso

SELECT string_agg(a ORDER BY a, ',') FROM tabela;  -- incorreto

A última é sintaticamente válida, mas representa a chamada para uma função de agregação com argumento único com duas chaves para ORDER BY (a segunda é totalmente inútil, porque é uma constante).

Se for especificado DISTINCT e mais uma cláusula_order_by, as expressões em ORDER BY só podem referenciar colunas que estejam na lista de DISTINCT. Por exemplo:

WITH vals (v) AS ( VALUES (1),(3),(4),(3),(2) )
SELECT array_agg(DISTINCT v ORDER BY v DESC) FROM vals;

 array_agg
-----------
 {4,3,2,1}

Colocar a cláusula ORDER BY na lista de argumentos padrão da agregação, conforme descrito até agora, é usado ao ordenar as linhas de entrada para agregações estatísticas e de propósito geral, para as quais a ordenação é opcional. Há uma subclasse de funções de agregação chamada agregações de conjunto ordenado, para as quais a cláusula_order_by é necessária, porque geralmente o cálculo da agregação só faz sentido em termos da ordenação específica de suas linhas de entrada. Exemplos típicos de agregações de conjunto ordenado incluem cálculos de nível e percentil. Para uma agregação de conjunto ordenado, a cláusula_order_by é escrita dentro de WITHIN GROUP (...), conforme mostrado na alternativa de sintaxe final acima. As expressões na cláusula_order_by são avaliadas uma vez por linha de entrada, como argumentos de agregação regulares, ordenadas segundo os requisitos da cláusula_order_by, e enviadas para a função de agregação como argumentos de entrada. (Isso é diferente do caso de um não-WITHIN GROUP cláusula_order_by, que não é tratado como argumento(s) para a função de agregação.) As expressões de argumento que precedem WITHIN GROUP, se houver, são chamadas de argumentos diretos para distingui-las dos argumentos de agregação listados na cláusula_order_by. Ao contrário dos argumentos de agregação regulares, os argumentos diretos são avaliados apenas uma vez por chamada de agregação, não uma vez por linha da entrada, significando que podem conter variáveis somente se essas variáveis forem agrupadas por GROUP BY; essa restrição é a mesma como se os argumentos diretos não estivessem dentro da expressão de agregação. Os argumentos diretos são normalmente usados para coisas como frações percentuais, que só fazem sentido como um único valor por cálculo de agregação. A lista de argumentos diretos pode estar vazia; nesse caso, escreva apenas (), e não (*). (Na verdade, o PostgreSQL aceita as duas grafias, mas apenas a primeira grafia está em conformidade com o padrão SQL.)

Um exemplo de chamada a uma agregação de conjunto ordenado é:

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY renda) FROM famílias;

 percentile_cont
-----------------
           50489

que obtém o valor do 50º percentil, ou mediana, dos valores da coluna renda da tabela famílias. Aqui, 0.5 é um argumento direto; não faria sentido que a fração percentual fosse um valor que variasse entre as linhas.

Se FILTER for especificado, somente as linhas da entrada para as quais a cláusula_filtro for avaliada como verdadeira serão passadas para a função de agregação; as outras linhas serão descartadas. Por exemplo:

SELECT
    count(*) AS "não filtradas",
    count(*) FILTER (WHERE i < 5) AS "filtradas"
FROM generate_series(1,10) AS s(i);

 não filtradas | filtradas
---------------+-----------
            10 |         4
(1 linha)

As funções de agregação pré-definidas estão descritas na Seção 9.21. Os usuários podem adicionar outras funções de agregação.

Uma expressão de agregação só pode aparecer na lista de resultados ou na cláusula HAVING de um comando SELECT. Não é permitida em outras cláusulas, tal como WHERE, porque essas cláusulas são logicamente avaliadas antes que os cálculos das agregações estejam prontos.

Quando uma expressão de agregação aparece em uma subconsulta (veja a Seção 4.2.11 e a Seção 9.24), a agregação é normalmente avaliada nas linhas da subconsulta. Entretanto, ocorre uma exceção se os argumentos para a agregação (e a cláusula_filtro, se houver) contiverem apenas variáveis de nível externo: então a agregação pertence ao nível externo mais próximo, sendo avaliada nas linhas dessa consulta. A expressão de agregação na sua totalidade é, então, uma referência externa para a subconsulta onde ela aparece, e atua como uma constante em qualquer avaliação dessa subconsulta. A restrição sobre aparecer somente na lista de resultados, ou na cláusula HAVING, se aplica ao nível de consulta ao qual a agregação pertence.

Uma chamada de função de janela representa a aplicação da função de agregação a uma parte das linhas selecionadas por uma consulta. Ao contrário das chamadas de função de agregação padrão (não janela), isso não está relacionado ao agrupamento das linhas selecionadas em uma única linha de resultado — cada linha permanece separada na saída da consulta. No entanto, a função de janela tem acesso a todas as linhas que fazem parte do grupo da linha corrente conforme a especificação de agrupamento (lista do PARTITION BY) da chamada da função de janela. A sintaxe da chamada de função de janela é uma das seguintes:

nome_da_função ([expressão [, expressão ... ]]) [ FILTER ( WHERE cláusula_filtro ) ] OVER nome_da_janela
nome_da_função ([expressão [, expressão ... ]]) [ FILTER ( WHERE cláusula_filtro ) ] OVER ( definição_de_janela )
nome_da_função ( * ) [ FILTER ( WHERE cláusula_filtro ) ] OVER nome_da_janela
nome_da_função ( * ) [ FILTER ( WHERE cláusula_filtro ) ] OVER ( definição_de_janela )

onde definição_de_janela tem a sintaxe:

[ nome_de_janela_existente ]
[ PARTITION BY expressão [, ...] ]
[ ORDER BY expressão [ ASC | DESC | USING operador ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ cláusula_quadro ]

A cláusula_quadro opcional é uma das seguintes:

{ RANGE | ROWS | GROUPS } início_do_quadro [ exclusão_do_quadro ]
{ RANGE | ROWS | GROUPS } BETWEEN início_do_quadro AND fim_do_quadro [ exclusão_do_quadro ]

onde início_do_quadro e fim_do_quadro é uma das seguintes:

UNBOUNDED PRECEDING
deslocamento PRECEDING
CURRENT ROW
deslocamento FOLLOWING
UNBOUNDED FOLLOWING

e exclusão_do_quadro é uma das seguintes:

EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS

Aqui expressão representa qualquer expressão de valor que não contenha chamadas a função de janela.

O nome_da_janela é uma referência a uma especificação de janela com nome definida na cláusula WINDOW da consulta. Como alternativa, uma definição_de_janela completa pode ser fornecida entre parênteses, usando a mesma sintaxe usada para definir uma janela com nome na cláusula WINDOW; veja a página de referência do comando SELECT para obter mais detalhes. Vale ressaltar que OVER wname não é exatamente equivalente a OVER (wname ...); o último implica em copiar e modificar a definição da janela e será rejeitado se a especificação da janela referenciada incluir uma cláusula de quadro.

A cláusula PARTITION BY agrupa as linhas da consulta em partições, que são processadas em separado pela função de janela. PARTITION BY funciona de forma semelhante a uma cláusula GROUP BY no nível de consulta, exceto que suas expressões são sempre apenas expressões, não podendo ser nomes ou números de colunas de saída. Sem PARTITION BY todas as linhas produzidas pela consulta são tratadas como sendo uma única partição. A cláusula ORDER BY define a ordem pela qual as linhas da partição são processadas pela função de janela. Funciona de forma semelhante à cláusula ORDER BY no nível de consulta, mas também não pode usar os nomes ou números das colunas de saída. Sem ORDER BY as linhas são processadas em qualquer ordem.

A cláusula_quadro define o conjunto de linhas que constituem o quadro da janela, que é um subconjunto da partição corrente, para aquelas funções de janela que atuam no quadro de toda a partição. O conjunto de linhas no quadro pode variar dependendo de qual linha é a linha corrente. O quadro pode ser definido no modo RANGE, ROWS ou GROUPS; em cada caso, ele é executado do início_do_quadro ao fim_do_quadro. Se fim_do_quadro for omitido, será usado o valor padrão CURRENT ROW.

Um início_do_quadro igual a UNBOUNDED PRECEDING significa que o quadro começa na primeira linha da partição e, da mesma forma, um fim_do_quadro igual a UNBOUNDED FOLLOWING significa que o quadro termina na última linha da partição.

No modo RANGE ou GROUPS, um início_do_quadro igual a CURRENT ROW significa que o quadro começa na primeira linha que faz par com a linha corrente (uma linha onde a cláusula ORDER BY da janela classifica como equivalente à linha corrente), enquanto um fim_do_quadro igual a CURRENT ROW significa que o quadro termina na última linha de mesmo nível da linha corrente. No modo ROWS, CURRENT ROW significa apenas a linha corrente.

Nas opções de quadro deslocamento PRECEDING e deslocamento FOLLOWING, o deslocamento deve ser uma expressão que não contém variáveis, funções de agregação, ou funções de janela. O significado do deslocamento depende do modo do quadro:

Em qualquer caso, a distância até o final do quadro é limitada pela distância até o final da partição, de modo que, nas linhas próximas às extremidades da partição, o quadro pode conter menos linhas do que em qualquer outro lugar.

Note que nos modos ROWS e GROUPS, 0 PRECEDING e 0 FOLLOWING são equivalentes a CURRENT ROW. Isso normalmente também é válido no modo RANGE, para um significado apropriado de “zero” para o tipo de dados específico.

A opção exclusão_do_quadro permite que as linhas ao redor da linha corrente sejam excluídas do quadro, mesmo que possam ser incluídas segundo as opções de início e fim do quadro. EXCLUDE CURRENT ROW exclui a linha corrente do quadro. EXCLUDE GROUP exclui a linha corrente e seus pares de ordenação do quadro. EXCLUDE TIES exclui todos os pares da linha corrente do quadro, mas não a própria linha corrente. EXCLUDE NO OTHERS apenas define explicitamente o comportamento padrão de não excluir a linha corrente ou seus pares.

A opção de enquadramento padrão é RANGE UNBOUNDED PRECEDING, idêntica a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Com ORDER BY, isso significa que o quadro fica definido como contendo todas as linhas da partição, começando pela linha corrente e indo até o último par da linha corrente, obedecendo à cláusula ORDER BY. Sem ORDER BY, isso significa que todas as linhas da partição são incluídas no quadro da janela, porque todas as linhas se tornam pares da linha corrente.

As restrições são que início_do_quadro não pode ser UNBOUNDED FOLLOWING, fim_do_quadro não pode ser UNBOUNDED PRECEDING e a opção fim_do_quadro não pode aparecer antes na lista acima de opções início_do_quadro e fim_do_quadro do que a opção início_do_quadro — por exemplo, RANGE BETWEEN CURRENT ROW AND deslocamento PRECEDING não é permitido. Mas, por exemplo, ROWS BETWEEN 7 PRECEDING AND 8 PRECEDING é permitido, mesmo que nunca selecione nenhuma linha.

Se FILTER for especificado, somente as linhas de entrada para as quais a cláusula_filtro for avaliada como verdadeira serão enviadas para a função de janela; as outras linhas são descartadas. Somente funções de janela que são agregações aceitam a cláusula FILTER.

As funções de janela internas estão descritas na Tabela 9.67. Outras funções de janela podem ser adicionadas pelo usuário. Além disso, qualquer agregação de propósito geral ou estatístico, nativa ou definida pelo usuário, pode ser usada como função de janela. (Agregações de conjuntos ordenados e conjuntos hipotéticos não podem ser usadas atualmente como funções de janela.)

As sintaxes que usam * são usadas para chamar funções de agregação sem parâmetros como funções de janela, por exemplo, count(*) OVER (PARTITION BY x ORDER BY y). O asterisco (*) normalmente não é usado para funções específicas de janela. Funções específicas de janela não permitem que DISTINCT ou ORDER BY sejam usados na lista de argumentos da função.

Chamadas de função de janela são permitidas apenas na lista do SELECT e na cláusula ORDER BY da consulta.

Mais informações sobre as funções de janela podem ser encontradas na Seção 3.5, na Seção 9.22, e na Seção 7.2.5.

Uma conversão de tipo determina a conversão de um tipo de dados para outro. O PostgreSQL permite duas sintaxes equivalentes para conversão de tipo:

CAST ( expressão AS tipo )
expressão::tipo

A sintaxe CAST está em conformidade com o padrão SQL; a sintaxe com :: é o uso histórico do PostgreSQL.

Quando a conversão é aplicada a uma expressão de valor de um tipo conhecido, ela representa uma conversão de tipo em tempo de execução. A conversão somente será bem-sucedida se uma operação de conversão de tipo adequada tiver sido definida. Note ser sutilmente diferente do uso de conversões com constantes, como descrito na Seção 4.1.2.7. Uma conversão aplicada a uma constante do tipo cadeia de caracteres representa a atribuição inicial do tipo a um valor constante, portanto é bem-sucedida para qualquer tipo (se o conteúdo da constante do tipo cadeia de caracteres tiver uma sintaxe de entrada aceita para o tipo de dados).

Uma conversão de tipo explícito geralmente pode ser omitida se não houver ambiguidade quanto ao tipo que uma expressão de valor deve produzir (por exemplo, quando é atribuída a uma coluna de tabela); o sistema aplicará automaticamente uma conversão de tipo nesses casos. No entanto, a conversão automática só é feita para conversões marcadas como “OK para aplicar implicitamente” nos catálogos do sistema. Outras conversões devem ser chamadas com a sintaxe de conversão explícita. Essa restrição destina-se a evitar que conversões surpreendentes sejam aplicadas silenciosamente.

Também é possível determinar a conversão de tipo usando uma sintaxe tipo função:

nome_do_tipo ( expressão )

Entretanto, isso só funciona para os tipos cujos nomes também são válidos como nomes de função. Por exemplo, double precision não pode ser usado desta forma, mas o equivalente float8 pode. Além disso, os nomes interval, time e timestamp só podem ser usados desta forma se estiverem entre aspas, devido a conflitos sintáticos. Portanto, o uso da sintaxe de conversão do tipo função leva a inconsistências e, provavelmente, deve ser evitado.

A cláusula COLLATE ^[30] sobrepõe-se à ordenação de uma expressão. É anexada à expressão a que se aplica:

expr COLLATE ordenação

onde ordenação é um identificador possivelmente qualificado por um esquema. A cláusula COLLATE tem precedência sobre os operadores. Se for necessário, podem ser usados parênteses.

Se não for especificada nenhuma ordenação explicitamente, o sistema de banco de dados irá inferir a ordenação a partir das colunas envolvidas na expressão, ou será usado o padrão do banco de dados para ordenações se nenhuma coluna estiver envolvida na expressão.

Os dois usos mais comuns da cláusula COLLATE são alterar a ordenação em uma cláusula ORDER BY como, por exemplo,

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

e alterar a ordenação em uma chamada de função, ou de operador, que tem resultados diferentes conforme a localidade, como, por exemplo:

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

Note que no último caso a cláusula COLLATE está vinculada ao argumento de entrada do operador que desejamos afetar. Não importa a qual argumento da chamada de operador ou de função a cláusula COLLATE está vinculada, porque a ordenação aplicada pelo operador ou função é derivada considerando todos os argumentos, e uma cláusula COLLATE explícita substituirá a ordenação de todos os outros argumentos. (Entretanto, anexar cláusulas COLLATE não correspondentes a mais de um argumento é um erro. Para obter mais detalhes, veja a Seção 23.2.) Assim, essa consulta produz o mesmo resultado do exemplo anterior:

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

Mas essa consulta está errada

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

porque tenta aplicar a ordenação ao resultado do operador > que é do tipo boolean, um tipo de dados não passível de ordenação.

Uma subconsulta escalar é uma consulta SELECT comum entre parênteses que retorna exatamente uma linha com uma coluna. (Veja o Capítulo 7 para obter informações sobre como escrever consultas.) A consulta SELECT é executada e o único valor retornado é usado na expressão de valor circundante. É um erro usar uma consulta que retorna mais de uma linha ou mais de uma coluna como uma subconsulta escalar. (Mas se, durante uma execução específica, a subconsulta não retornar nenhuma linha, não há erro; o resultado escalar é considerado nulo.) A subconsulta pode se referir a variáveis da consulta circundante, que atuarão como constantes durante qualquer avaliação da subconsulta. Veja também a Seção 9.24 para outras expressões envolvendo subconsultas.

Por exemplo, a consulta seguinte encontra a maior população de cidade em cada estado:

SELECT nome, (SELECT max(pop)
                  FROM cidades
                  WHERE cidades.estado = estados.nome)
    FROM estados;

Um construtor de matriz é uma expressão que constrói um valor de matriz usando os valores para seus elementos membro. Um construtor de matriz simples consiste da palavra-chave ARRAY, um colchete esquerdo [, uma lista de expressões (separadas por vírgulas) para os valores dos elementos da matriz e, finalmente, um colchete ]. Por exemplo:

SELECT ARRAY[1,2,3+4];

  array
---------
 {1,2,7}
(1 linha)

Por padrão, o tipo do elemento da matriz é o tipo comum das expressões membro, determinado usando as mesmas regras usadas para construções UNION ou CASE (veja a Seção 10.5). Você pode mudar esse comportamento convertendo explicitamente o construtor de matriz para o tipo desejado. Por exemplo

SELECT ARRAY[1,2,22.7]::integer[];

  array
----------
 {1,2,23}
(1 linha)

tem o mesmo efeito que converter cada expressão para o tipo do elemento da matriz individualmente. Para obter mais informações sobre conversões de tipo, veja a Seção 4.2.9.

Os valores de matriz multidimensional podem ser construídos aninhando construtores de matriz. Nos construtores internos, a palavra-chave ARRAY pode ser omitida. Por exemplo, essas expressões produzem o mesmo resultado:

SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]];

     array
---------------
 {{1,2},{3,4}}
(1 linha)

SELECT ARRAY[[1,2],[3,4]];

     array
---------------
 {{1,2},{3,4}}
(1 linha)

Uma vez que as matrizes multidimensionais devem ser retangulares, os construtores internos no mesmo nível devem produzir submatrizes de dimensões idênticas. Qualquer conversão aplicada ao construtor externo ARRAY se propaga, automaticamente, para todos os construtores internos.

Os elementos do construtor de matriz multidimensional podem ser qualquer coisa que produza uma matriz do tipo apropriado, e não apenas um construtor de ARRAY aninhado. Por exemplo:

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

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

SELECT ARRAY[f1, f2, '{{9,10},{11,12}}'::int[]] FROM arr;

                     array
------------------------------------------------
 {{{1,2},{3,4}},{{5,6},{7,8}},{{9,10},{11,12}}}
(1 linha)

Pode ser construída uma matriz vazia, mas como é impossível ter uma matriz sem tipo, a matriz vazia deve ser convertida explicitamente para o tipo desejado. Por exemplo:

SELECT ARRAY[]::integer[];

 array
-------
 {}
(1 linha)

Também é possível construir uma matriz a partir dos resultados de uma subconsulta. Nesse caso, o construtor de matriz é escrito com a palavra-chave ARRAY seguida por uma subconsulta entre parênteses (não entre colchetes). Por exemplo:

SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');

                                                     array
---------------------------------------------------------------------------------------------------------------
 {1244,31,6367,6368,6369,6393,6394,3543,3544,1948,1949,1950,1951,1952,1953,1954,3331,2005,2006,2011,2412,2413}
(1 linha)

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 linha)

A subconsulta deve retornar uma única coluna. Se a coluna de saída da subconsulta for de um tipo não-matriz, a matriz unidimensional resultante terá um elemento para cada linha no resultado da subconsulta, com um tipo de elemento correspondente ao da coluna de saída da subconsulta. Se a coluna de saída da subconsulta for do tipo matriz, o resultado será uma matriz do mesmo tipo, mas com uma dimensão maior; nesse caso, todas as linhas da subconsulta devem produzir matrizes com dimensões idênticas, caso contrário o resultado não seria uma matriz retangular.

Os índices de um valor de matriz construído com ARRAY sempre começam por um. Para obter mais informações sobre matrizes veja a Seção 8.15.

Um construtor de linha é uma expressão que cria um valor de linha (também chamado de valor composto) usando valores para seus campos membro. Um construtor de linha consiste na palavra-chave ROW, um parêntese esquerdo, zero ou mais expressões (separadas por vírgulas) para os valores dos campos da linha e, finalmente, um parêntese direito. Por exemplo:

SELECT ROW(1,2.5,'Esse é um teste');

A palavra-chave ROW é opcional quando há mais de uma expressão na lista.

Um construtor de linha pode incluir a sintaxe valor_de_linha.*, que é expandida para uma lista dos elementos do valor de linha, assim como ocorre quando a sintaxe .* é usada no nível superior da lista do SELECT (veja a Seção 8.16.5). Por exemplo, se a tabela t tiver as colunas f1 e f2, essas duas consultas são idênticas:

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

Por padrão, o valor criado por uma expressão ROW é do tipo registro anônimo. Se necessário, esse pode ser convertido em um tipo composto com nome — seja o tipo de linha de uma tabela ou um tipo composto criado com CREATE TYPE AS. Uma conversão explícita pode ser necessária para evitar ambiguidade. Por exemplo:

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

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

-- Nenhuma conversão explícita se faz necessária,
-- porque só existe uma função getf1(linha)

SELECT getf1(ROW(1,2.5,'Esse é um teste'));

 getf1
-------
     1
(1 linha)

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

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

-- Agora precisamos realizar uma conversão explícita,
-- porque existem duas funções  getf1(linha)

SELECT getf1(ROW(1,2.5,'Esse é um teste'));
ERRO:  a função getf1(record) não é única
LINHA 1: SELECT getf1(ROW(1,2.5,'Esse é um teste'));
                ^
DICA:  Não foi possível escolher uma função que se enquadre melhor.
       Você precisa adicionar conversões de tipo explícitas.

SELECT getf1(ROW(1,2.5,'Esse é um teste')::minha_tabela);

 getf1
-------
     1
(1 linha)

SELECT getf1(CAST(ROW(11,'Esse é um teste',2.5) AS meu_tipo_linha));

 getf1
-------
    11
(1 linha)

Construtores de linha podem ser usados para construir valores compostos a serem armazenados em uma coluna de tabela do tipo composto, ou para serem passados para uma função que recebe parâmetro composto. Além disso, é possível testar linhas usando os operadores de comparação padrão, conforme descrito na Seção 9.2, para comparar uma linha com outra, conforme descrito na Seção 9.25, e para usá-los em conexão com subconsultas, conforme discutido na Seção 9.24.

A ordem de avaliação das subexpressões não é definida. Em particular, as entradas para um operador ou função não são necessariamente avaliadas da esquerda para a direita, ou em qualquer outra ordem fixa.

Além disso, se o resultado da expressão puder ser determinado avaliando apenas algumas de suas partes, outras subexpressões podem nem ser avaliadas. Por exemplo, se for escrito:

SELECT true OR alguma_função();

então alguma_função() (provavelmente) não será chamada. O mesmo aconteceria se fosse escrito:

SELECT alguma_função() OR true;

Note que isso não é idêntico ao “curto-circuito” da esquerda para a direita dos operadores booleanos encontrados em algumas linguagens de programação.

Como consequência, não é recomendável usar funções com efeitos colaterais como parte de expressões complexas. É particularmente perigoso confiar em efeitos colaterais, ou na ordem de avaliação, nas cláusulas WHERE e HAVING, porque essas cláusulas são muito reprocessadas como parte do desenvolvimento do plano de execução. As expressões booleanas (combinações AND/OR/NOT) nessas cláusulas podem ser reorganizadas de qualquer maneira permitida pelas leis da álgebra booleana.

Quando for essencial obrigar a ordem de avaliação, pode ser usada uma construção CASE (veja a Seção 9.18). Por exemplo, essa é uma forma não confiável para tentar evitar a divisão por zero na cláusula WHERE:

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

Mas isso é seguro:

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

Uma construção CASE usada desta forma irá frustrar as tentativas de otimização, então isso só deve ser feito quando for necessário. (Nesse exemplo em particular, seria melhor contornar o problema escrevendo y > 1.5*x.)

No entanto, CASE não é a cura para todos esses problemas. Uma limitação da técnica mostrada acima, é o fato de não impedir a avaliação prévia de subexpressões constantes. Conforme descrito na Seção 36.7, as funções e operadores marcados como IMMUTABLE podem ser avaliados quando a consulta é planejada e não quando é executada. Assim, por exemplo

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

provavelmente resultará em erro de divisão por zero, devido ao planejador tentar simplificar a subexpressão constante, mesmo que todas as linhas da tabela tenham x > 0, e a condição ELSE nunca seja executada.

Embora esse exemplo em particular possa parecer tolo, casos menos óbvios, que não envolvem constantes, podem ocorrer em consultas executadas dentro de funções, porque os valores dos argumentos de função e de variáveis locais podem ser inseridos em consultas como constantes para fins de planejamento. Dentro das funções PL/pgSQL, por exemplo, usar uma instrução IF-THEN-ELSE para proteger uma computação arriscada é muito mais seguro do que apenas aninhar em uma expressão CASE.

Outra limitação dessa técnica, é que uma expressão CASE não pode impedir a avaliação da expressão de agregação contida nela, porque as expressões de agregação são computadas antes das outras expressões em uma lista do SELECT, ou na cláusula HAVING, serem consideradas. Por exemplo, a consulta a seguir pode causar um erro de divisão por zero, apesar de estar aparentemente protegida contra esse tipo de erro:

SELECT CASE WHEN min(funcionários) > 0
            THEN avg(despesas / funcionários)
       END
    FROM departamentos;

As agregações min() e avg() são calculadas ao mesmo tempo sobre todas as linhas de entrada, portanto, se uma linha tiver valor de funcionários igual a zero, o erro de divisão por zero acontecerá antes que exista qualquer oportunidade de testar o resultado de min(). Em vez disso, deve ser usada uma cláusula WHERE ou FILTER para evitar que as linhas de entrada com problema cheguem à função de agregação.