Ссылку на столбец можно записать в форме:

отношение.имя_столбца

Здесь отношение — имя таблицы (возможно, полное, с именем схемы) или её псевдоним, определённый в предложении FROM. Это имя и разделяющую точку можно опустить, если имя столбца уникально среди всех таблиц, задействованных в текущем запросе. (См. также Главу 7.)

Ссылка на позиционный параметр применяется для обращения к значению, переданному в SQL-оператор извне. Параметры используются в определениях SQL-функций и подготовленных операторов. Некоторые клиентские библиотеки также поддерживают передачу значений данных отдельно от самой SQL-команды, и в этом случае параметры позволяют ссылаться на такие значения. Ссылка на параметр записывается в следующей форме:

$число

Например, рассмотрим следующее определение функции dept:

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

Здесь $1 всегда будет ссылаться на значение первого аргумента функции.

Если в выражении вы имеете дело с массивом, то можно извлечь определённый его элемент, написав:

выражение[индекс]

или несколько соседних элементов («срез массива»):

выражение[нижний_индекс:верхний_индекс]

(Здесь квадратные скобки [ ] должны присутствовать буквально.) Каждый индекс сам по себе является выражением, результат которого округляется к ближайшему целому.

В общем случае выражение массива должно заключаться в круглые скобки, но их можно опустить, когда выражение с индексом — это просто ссылка на столбец или позиционный параметр. Кроме того, можно соединить несколько индексов, если исходный массив многомерный. Например:

моя_таблица.столбец_массив[4]
моя_таблица.столбец_массив_2d[17][34]
$1[10:42]
(функция_массив(a,b))[42]

В последней строке круглые скобки необходимы. Подробнее массивы рассматриваются в Разделе 8.15.

Если результат выражения — значение составного типа (строка таблицы), тогда определённое поле этой строки можно извлечь, написав:

выражение.имя_поля

В общем случае выражение такого типа должно заключаться в круглые скобки, но их можно опустить, когда это ссылка на таблицу или позиционный параметр. Например:

моя_таблица.столбец
$1.столбец
(функция_кортеж(a,b)).стол3

(Таким образом, полная ссылка на столбец — это просто частный случай выбора поля.) Важный особый случай здесь — извлечение поля из столбца составного типа:

(составной_столбец).поле
(моя_таблица.составной_столбец).поле

Здесь скобки нужны, чтобы показать, что составной_столбец — это имя столбца, а не таблицы, и что моя_таблица — имя таблицы, а не схемы.

Вы можете запросить все поля составного значения, написав .*:

(составной_столбец).*

Эта запись действует по-разному в зависимости от контекста; подробнее об этом говорится в Подразделе 8.16.5.

Существуют два возможных синтаксиса применения операторов:

где оператор соответствует синтаксическим правилам, описанным в Подразделе 4.1.3, либо это одно из ключевых слов AND, OR и NOT, либо полное имя оператора в форме:

OPERATOR(схема.имя_оператора)

Существование конкретных операторов и их тип (унарный или бинарный) зависит от того, как и какие операторы определены системой и пользователем. Встроенные операторы описаны в Главе 9.

Вызов функции записывается просто как имя функции (возможно, дополненное именем схемы) и список аргументов в скобках:

имя_функции ([выражение [, выражение ... ]])

Например, так вычисляется квадратный корень из 2:

sqrt(2)

Список встроенных функций приведён в Главе 9. Пользователь также может определить и другие функции.

Выполняя запросы в базе данных, где одни пользователи могут не доверять другим, в записи вызовов функций соблюдайте меры предосторожности, описанные в Разделе 10.3.

Аргументам могут быть присвоены необязательные имена. Подробнее об этом см. Раздел 4.3.

Агрегатное выражение представляет собой применение агрегатной функции к строкам, выбранным запросом. Агрегатная функция сводит множество входных значений к одному выходному, как например, сумма или среднее. Агрегатное выражение может записываться следующим образом:

агрегатная_функция (выражение [ , ... ] [ предложение_order_by ] ) [ FILTER ( WHERE условие_фильтра ) ]
агрегатная_функция (ALL выражение [ , ... ] [ предложение_order_by ] ) [ FILTER ( WHERE условие_фильтра ) ]
агрегатная_функция (DISTINCT выражение [ , ... ] [ предложение_order_by ] ) [ FILTER ( WHERE условие_фильтра ) ]
агрегатная_функция ( * ) [ FILTER ( WHERE условие_фильтра ) ]
агрегатная_функция ( [ выражение [ , ... ] ] ) WITHIN GROUP ( предложение_order_by ) [ FILTER ( WHERE условие_фильтра ) ]

Здесь агрегатная_функция — имя ранее определённой агрегатной функции (возможно, дополненное именем схемы), выражение — любое выражение значения, не содержащее в себе агрегатного выражения или вызова оконной функции. Необязательные предложения предложение_order_by и условие_фильтра описываются ниже.

В первой форме агрегатного выражения агрегатная функция вызывается для каждой строки. Вторая форма эквивалентна первой, так как указание ALL подразумевается по умолчанию. В третьей форме агрегатная функция вызывается для всех различных значений выражения (или набора различных значений, для нескольких выражений), выделенных во входных данных. В четвёртой форме агрегатная функция вызывается для каждой строки, так как никакого конкретного значения не указано (обычно это имеет смысл только для функции count(*)). В последней форме используются сортирующие агрегатные функции, которые будут описаны ниже.

Большинство агрегатных функций игнорируют значения NULL, так что строки, для которых выражения выдают одно или несколько значений NULL, отбрасываются. Это можно считать истинным для всех встроенных операторов, если явно не говорится об обратном.

Например, count(*) подсчитает общее количество строк, а count(f1) только количество строк, в которых f1 не NULL (так как count игнорирует NULL), а count(distinct f1) подсчитает число различных и отличных от NULL значений столбца f1.

Обычно строки данных передаются агрегатной функции в неопределённом порядке и во многих случаях это не имеет значения, например функция min выдаёт один и тот же результат независимо от порядка поступающих данных. Однако некоторые агрегатные функции (такие как array_agg и string_agg) выдают результаты, зависящие от порядка данных. Для таких агрегатных функций можно добавить предложение_order_by и задать нужный порядок. Это предложение_order_by имеет тот же синтаксис, что и предложение ORDER BY на уровне запроса, как описано в Разделе 7.5, за исключением того, что его выражения должны быть просто выражениями, а не именами результирующих столбцов или числами. Например:

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}

При работе с jsonb порядок ключей имеет значение, потому что при наличии одинаковых ключей в объекте этого типа сохраняется только последний:

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"}

Заметьте, что при использовании агрегатных функций с несколькими аргументами, предложение ORDER BY идёт после всех аргументов. Например, надо писать так:

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

а не так:

SELECT string_agg(a ORDER BY a, ',') FROM table;  -- неправильно

Последний вариант синтаксически допустим, но он представляет собой вызов агрегатной функции одного аргумента с двумя ключами ORDER BY (при этом второй не имеет смысла, так как это константа).

Если предложение_order_by дополнено указанием DISTINCT, тогда выражения ORDER BY могут ссылаться только на столбцы в списке DISTINCT. Например:

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}

При добавлении ORDER BY в обычный список аргументов агрегатной функции, описанном до этого, выполняется сортировка входных строк для универсальных и статистических агрегатных функций, для которых сортировка необязательна. Но есть подмножество агрегатных функций, сортирующие агрегатные функции, для которых предложение_order является обязательным, обычно потому, что вычисление этой функции имеет смысл только при определённой сортировке входных строк. Типичными примерами сортирующих агрегатных функций являются вычисления ранга и процентиля. Для сортирующей агрегатной функции предложение_order_by записывается внутри WITHIN GROUP (...), что иллюстрирует последний пример, приведённый выше. Выражения в предложении_order_by вычисляются однократно для каждой входной строки как аргументы обычной агрегатной функции, сортируются в соответствии с требованием предложения_order_by и поступают в агрегатную функции как входящие аргументы. (Если же предложение_order_by находится не в WITHIN GROUP, оно не передаётся как аргумент(ы) агрегатной функции.) Выражения-аргументы, предшествующие WITHIN GROUP, (если они есть), называются непосредственными аргументами, а выражения, указанные в предложении_order_by — агрегируемыми аргументами. В отличие от аргументов обычной агрегатной функции, непосредственные аргументы вычисляются однократно для каждого вызова функции, а не для каждой строки. Это значит, что они могут содержать переменные, только если эти переменные сгруппированы в GROUP BY; это суть то же ограничение, что действовало бы, будь эти непосредственные аргументы вне агрегатного выражения. Непосредственные аргументы обычно используются, например, для указания значения процентиля, которое имеет смысл, только если это конкретное число для всего расчёта агрегатной функции. Список непосредственных аргументов может быть пуст; в этом случае запишите просто (), но не (*). (На самом деле Postgres Pro примет обе записи, но только первая соответствует стандарту SQL.)

Пример вызова сортирующей агрегатной функции:

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY income) FROM households;
 percentile_cont
-----------------
           50489

она получает 50-ый процентиль, или медиану, значения столбца income из таблицы households. В данном случае 0.5 — это непосредственный аргумент; если бы дробь процентиля менялась от строки к строке, это не имело бы смысла.

Если добавлено предложение FILTER, агрегатной функции подаются только те входные строки, для которых условие_фильтра вычисляется как истинное; другие строки отбрасываются. Например:

SELECT
    count(*) AS unfiltered,
    count(*) FILTER (WHERE i < 5) AS filtered
FROM generate_series(1,10) AS s(i);
 unfiltered | filtered
------------+----------
         10 |        4
(1 row)

Предопределённые агрегатные функции описаны в Разделе 9.21. Пользователь также может определить другие агрегатные функции.

Агрегатное выражение может фигурировать только в списке результатов или в предложении HAVING команды SELECT. Во всех остальных предложениях, например WHERE, они запрещены, так как эти предложения логически вычисляются до того, как формируются результаты агрегатных функций.

Когда агрегатное выражение используется в подзапросе (см. Подраздел 4.2.11 и Раздел 9.24), оно обычно вычисляется для всех строк подзапроса. Но если в аргументах (или в условии_filter) агрегатной функции есть только переменные внешнего уровня, агрегатная функция относится к ближайшему внешнему уровню и вычисляется для всех строк соответствующего запроса. Такое агрегатное выражение в целом является внешней ссылкой для своего подзапроса и на каждом вычислении считается константой. При этом допустимое положение агрегатной функции ограничивается списком результатов и предложением HAVING на том уровне запросов, где она находится.

Вызов оконной функции представляет собой применение функции, подобной агрегатной, к некоторому набору строк, выбранному запросом. В отличие от вызовов не оконных агрегатных функций, при этом не происходит группировка выбранных строк в одну — каждая строка остаётся отдельной в результате запроса. Однако оконная функция имеет доступ ко всем строкам, вошедшим в группу текущей строки согласно указанию группировки (списку PARTITION BY) в вызове оконной функции. Вызов оконной функции может иметь следующие формы:

имя_функции ([выражение [, выражение ... ]]) [ FILTER ( WHERE предложение_фильтра ) ] OVER имя_окна
имя_функции ([выражение [, выражение ... ]]) [ FILTER ( WHERE предложение_фильтра ) ] OVER ( определение_окна )
имя_функции ( * ) [ FILTER ( WHERE предложение_фильтра ) ] OVER имя_окна
имя_функции ( * ) [ FILTER ( WHERE предложение_фильтра ) ] OVER ( определение_окна )

Здесь определение_окна записывается в виде

[ имя_существующего_окна ]
[ PARTITION BY выражение [, ...] ]
[ ORDER BY выражение [ ASC | DESC | USING оператор ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ определение_рамки ]

Необязательное определение_рамки может иметь вид:

{ RANGE | ROWS | GROUPS } начало_рамки [ исключение_рамки ]
{ RANGE | ROWS | GROUPS } BETWEEN начало_рамки AND конец_рамки [ исключение_рамки ]

Здесь начало_рамки и конец_рамки задаются одним из следующих способов:

UNBOUNDED PRECEDING
смещение PRECEDING
CURRENT ROW
смещение FOLLOWING
UNBOUNDED FOLLOWING

и исключение_рамки может быть следующим:

EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS

Здесь выражение — это любое выражение значения, не содержащее вызовов оконных функций.

имя_окна — ссылка на именованное окно, определённое предложением WINDOW в данном запросе. Также возможно написать в скобках полное определение_окна, используя тот же синтаксис определения именованного окна в предложении WINDOW; подробнее это описано в справке по SELECT. Стоит отметить, что запись OVER имя_окна не полностью равнозначна OVER (имя_окна ...); последний вариант подразумевает копирование и изменение определения окна и не будет допустимым, если определение этого окна включает определение рамки.

Указание PARTITION BY группирует строки запроса в разделы, которые затем обрабатываются оконной функцией независимо друг от друга. PARTITION BY работает подобно предложению GROUP BY на уровне запроса, за исключением того, что его аргументы всегда просто выражения, а не имена выходных столбцов или числа. Без PARTITION BY все строки, выдаваемые запросом, рассматриваются как один раздел. Указание ORDER BY определяет порядок, в котором оконная функция обрабатывает строки раздела. Оно так же подобно предложению ORDER BY на уровне запроса и так же не принимает имена выходных столбцов или числа. Без ORDER BY строки обрабатываются в неопределённом порядке.

определение_рамки задаёт набор строк, образующих рамку окна, которая представляет собой подмножество строк текущего раздела и используется для оконных функций, работающих с рамкой, а не со всем разделом. Подмножество строк в рамке может меняться в зависимости от того, какая строка является текущей. Рамку можно задать в режимах RANGE, ROWS или GROUPS; в каждом случае она начинается с положения начало_рамки и заканчивается положением конец_рамки. Если конец_рамки не задаётся явно, подразумевается CURRENT ROW (текущая строка).

Если начало_рамки задано как UNBOUNDED PRECEDING, рамка начинается с первой строки раздела, а если конец_рамки определён как UNBOUNDED FOLLOWING, рамка заканчивается последней строкой раздела.

В режиме RANGE или GROUPS начало_рамки, заданное как CURRENT ROW, определяет в качестве начала первую родственную строку (строку, которая при сортировке согласно указанному для окна предложению ORDER BY считается равной текущей), тогда как конец_рамки, заданный как CURRENT ROW, определяет концом рамки последнюю родственную строку. В режиме ROWS вариант CURRENT ROW просто обозначает текущую строку.

В вариантах определения рамки смещение PRECEDING и смещение FOLLOWING в качестве смещения должно задаваться выражение, не содержащее какие-либо переменные и вызовы агрегатных или оконных функций. Что именно будет означать смещение, определяется в зависимости от режима рамки:

В любом случае расстояние до конца рамки ограничивается расстоянием до конца раздела, так что для строк, которые находятся у конца раздела, рамка может содержать меньше строк, чем для других.

Заметьте, что в режимах ROWS и GROUPS указания 0 PRECEDING и 0 FOLLOWING равнозначны указанию CURRENT ROW. Обычно это справедливо и для режима RANGE, в случае подходящего для типа данных определения значения «нуля».

Дополнение исключение_рамки позволяет исключить из рамки строки, которые окружают текущую строку, даже если они должны быть включены согласно указаниям, определяющим начало и конец рамки. EXCLUDE CURRENT ROW исключает из рамки текущую строку. EXCLUDE GROUP исключает из рамки текущую строку и родственные ей согласно порядку сортировки. EXCLUDE TIES исключает из рамки все родственные строки для текущей, но не собственно текущую строку. EXCLUDE NO OTHERS просто явно выражает поведение по умолчанию — не исключает ни текущую строку, ни родственные ей.

По умолчанию рамка определяется как RANGE UNBOUNDED PRECEDING, что равносильно расширенному определению RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. С указанием ORDER BY это означает, что рамка будет включать все строки от начала раздела до последней строки, родственной текущей (для ORDER BY). Без ORDER BY это означает, что в рамку включаются все строки раздела, так как все они считаются родственными текущей.

Действуют также следующие ограничения: в качестве начала_рамки нельзя задать UNBOUNDED FOLLOWING, в качестве конца_рамки не допускается UNBOUNDED PRECEDING и конец_рамки не может идти в показанном выше списке указаний начало_рамки AND конец_рамки перед началом_рамки. В частности, синтаксис RANGE BETWEEN CURRENT ROW AND смещение PRECEDING не допускается. Но при этом, например, определение ROWS BETWEEN 7 PRECEDING AND 8 PRECEDING допустимо, хотя оно и не выберет никакие строки.

Если добавлено предложение FILTER, оконной функции подаются только те входные строки, для которых условие_фильтра вычисляется как истинное; другие строки отбрасываются. Предложение FILTER допускается только для агрегирующих оконных функций.

Встроенные оконные функции описаны в Таблице 9.65, но пользователь может расширить этот набор, создавая собственные функции. Кроме того, в качестве оконных функций можно использовать любые встроенные или пользовательские универсальные, а также статистические агрегатные функции. (Сортирующие и гипотезирующие агрегатные функции в настоящее время использовать в качестве оконных нельзя.)

Запись со звёздочкой (*) применяется при вызове не имеющих параметров агрегатных функций в качестве оконных, например count(*) OVER (PARTITION BY x ORDER BY y). Звёздочка (*) обычно не применяется для исключительно оконных функций. Такие функции не допускают использования DISTINCT и ORDER BY в списке аргументов функции.

Вызовы оконных функций разрешены в запросах только в списке SELECT и в предложении ORDER BY.

Дополнительно об оконных функциях можно узнать в Разделе 3.5, Разделе 9.22 и Подразделе 7.2.5.

Приведение типа определяет преобразование данных из одного типа в другой. Postgres Pro воспринимает две равносильные записи приведения типов:

CAST ( выражение AS тип )
выражение::тип

Запись с CAST соответствует стандарту SQL, тогда как вариант с :: — историческое наследие Postgres Pro.

Когда приведению подвергается значение выражения известного типа, происходит преобразование типа во время выполнения. Это приведение будет успешным, только если определён подходящий оператор преобразования типов. Обратите внимание на небольшое отличие от приведения констант, описанного в Подразделе 4.1.2.7. Приведение строки в чистом виде представляет собой начальное присваивание строковой константы и оно будет успешным для любого типа (конечно, если строка содержит значение, приемлемое для данного типа данных).

Явное приведение типа можно опустить, если возможно однозначно определить, какой тип должно иметь выражение (например, когда оно присваивается столбцу таблицы); в таких случаях система автоматически преобразует тип. Однако автоматическое преобразование выполняется только для приведений с пометкой «допускается неявное применение» в системных каталогах. Все остальные приведения должны записываться явно. Это ограничение позволяет избежать сюрпризов с неявным преобразованием.

Также можно записать приведение типа как вызов функции:

имя_типа ( выражение )

Однако это будет работать только для типов, имена которых являются также допустимыми именами функций. Например, double precision так использовать нельзя, а float8 (альтернативное название того же типа) — можно. Кроме того, имена типов interval, time и timestamp из-за синтаксического конфликта можно использовать в такой записи только в кавычках. Таким образом, запись приведения типа в виде вызова функции провоцирует несоответствия и, возможно, лучше будет её не применять.

Предложение COLLATE переопределяет правило сортировки выражения. Оно добавляется после выражения:

выражение COLLATE правило_сортировки

где правило_сортировки — идентификатор правила, возможно дополненный именем схемы. Предложение COLLATE связывает выражение сильнее, чем операторы, так что при необходимости следует использовать скобки.

Если правило сортировки не определено явно, система либо выбирает его по столбцам, которые используются в выражении, либо, если таких столбцов нет, переключается на установленное для базы данных правило сортировки по умолчанию.

Предложение COLLATE имеет два распространённых применения: переопределение порядка сортировки в предложении ORDER BY, например:

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

и переопределение правил сортировки при вызове функций или операторов, возвращающих языкозависимые результаты, например:

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

Заметьте, что в последнем случае предложение COLLATE добавлено к аргументу оператора, на действие которого мы хотим повлиять. При этом не имеет значения, к какому именно аргументу оператора или функции добавляется COLLATE, так как правило сортировки, применяемое к оператору или функции, выбирается при рассмотрении всех аргументов, а явное предложение COLLATE переопределяет правила сортировки для всех других аргументов. (Однако добавление разных предложений COLLATE к нескольким аргументам будет ошибкой. Подробнее об этом см. Раздел 23.2.) Таким образом, эта команда выдаст тот же результат:

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

Но это будет ошибкой:

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

здесь правило сортировки нельзя применить к результату оператора >, который имеет несравниваемый тип данных boolean.

Скалярный подзапрос — это обычный запрос SELECT в скобках, который возвращает ровно одну строку и один столбец. (Написание запросов освещается в Главе 7.) После выполнения запроса SELECT его единственный результат используется в окружающем его выражении. В качестве скалярного подзапроса нельзя использовать запросы, возвращающие более одной строки или столбца. (Но если в результате выполнения подзапрос не вернёт строк, скалярный результат считается равным NULL.) В подзапросе можно ссылаться на переменные из окружающего запроса; в процессе одного вычисления подзапроса они будут считаться константами. Другие выражения с подзапросами описаны в Разделе 9.24.

Например, следующий запрос находит самый населённый город в каждом штате:

SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name)
    FROM states;

Конструктор массива — это выражение, которое создаёт массив, определяя значения его элементов. Конструктор простого массива состоит из ключевого слова ARRAY, открывающей квадратной скобки [, списка выражений (разделённых запятыми), задающих значения элементов массива, и закрывающей квадратной скобки ]. Например:

SELECT ARRAY[1,2,3+4];
  array
---------
 {1,2,7}
(1 row)

По умолчанию типом элементов массива считается общий тип для всех выражений, определённый по правилам, действующим и для конструкций UNION и CASE (см. Раздел 10.5). Вы можете переопределить его явно, приведя конструктор массива к требуемому типу, например:

SELECT ARRAY[1,2,22.7]::integer[];
  array
----------
 {1,2,23}
(1 row)

Это равносильно тому, что привести к нужному типу каждое выражение по отдельности. Подробнее приведение типов описано в Подразделе 4.2.9.

Многомерные массивы можно образовывать, вкладывая конструкторы массивов. При этом во внутренних конструкторах слово ARRAY можно опускать. Например, результат работы этих конструкторов одинаков:

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)

Многомерные массивы должны быть прямоугольными, и поэтому внутренние конструкторы одного уровня должны создавать вложенные массивы одинаковой размерности. Любое приведение типа, применённое к внешнему конструктору ARRAY, автоматически распространяется на все внутренние.

Элементы многомерного массива можно создавать не только вложенными конструкторами ARRAY, но и другими способами, позволяющими получить массивы нужного типа. Например:

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

Вы можете создать и пустой массив, но так как массив не может быть не типизированным, вы должны явно привести пустой массив к нужному типу. Например:

SELECT ARRAY[]::integer[];
 array
-------
 {}
(1 row)

Также возможно создать массив из результатов подзапроса. В этом случае конструктор массива записывается так же с ключевым словом ARRAY, за которым в круглых скобках следует подзапрос. Например:

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}
(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)

Такой подзапрос должен возвращать один столбец. Если этот столбец имеет тип, отличный от массива, результирующий одномерный массив будет включать элементы для каждой строки-результата подзапроса и типом элемента будет тип столбца результата. Если же тип столбца — массив, будет создан массив того же типа, но большей размерности; в любом случае во всех строках подзапроса должны выдаваться массивы одинаковой размерности, чтобы можно было получить прямоугольный результат.

Индексы массива, созданного конструктором ARRAY, всегда начинаются с одного. Подробнее о массивах вы узнаете в Разделе 8.15.

Конструктор табличной строки — это выражение, создающее строку или кортеж (или составное значение) из значений его аргументов-полей. Конструктор строки состоит из ключевого слова ROW, открывающей круглой скобки, нескольких выражений (разделённых запятыми), определяющих значения полей, и закрывающей скобки. При этом выражения могут отсутствовать, то есть конструктор имеет вид ROW(). Например:

SELECT ROW(1,2.5,'this is a test');

Если в списке более одного выражения, ключевое слово ROW можно опустить.

Конструктор строки поддерживает запись составное_значение.*, при этом данное значение будет развёрнуто в список элементов, так же, как в записи .* на верхнем уровне списка SELECT (см. Подраздел 8.16.5). Например, если таблица t содержит столбцы f1 и f2, эти записи равнозначны:

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

По умолчанию значение, созданное выражением ROW, имеет тип анонимной записи. Если необходимо, его можно привести к именованному составному типу — либо к типу строки таблицы, либо составному типу, созданному оператором CREATE TYPE AS. Явное приведение может потребоваться для достижения однозначности. Например:

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

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

-- Приведение не требуется, так как существует только одна getf1()
SELECT getf1(ROW(1,2.5,'this is a test'));
 getf1
-------
     1
(1 row)

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

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

-- Теперь приведение необходимо для однозначного выбора функции:
SELECT getf1(ROW(1,2.5,'this is a test'));
ОШИБКА:  функция getf1(record) не уникальна

SELECT getf1(ROW(1,2.5,'this is a test')::mytable);
 getf1
-------
     1
(1 row)

SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype));
 getf1
-------
    11
(1 row)

Используя конструктор строк (кортежей), можно создавать составное значение для сохранения в столбце составного типа или для передачи функции, принимающей составной параметр. Кроме того, можно тестировать строки, используя стандартные операторы сравнения, а также сравнивать строки между собой, как описано в Разделе 9.25, и использовать их в сочетании с подзапросами, как описано в Разделе 9.24.

Порядок вычисления подвыражений не определён. В частности, аргументы оператора или функции не обязательно вычисляются слева направо или в любом другом фиксированном порядке.

Более того, если результат выражения можно получить, вычисляя только некоторые его части, тогда другие подвыражения не будут вычисляться вовсе. Например, если написать:

SELECT true OR somefunc();

тогда функция somefunc() не будет вызываться (возможно). То же самое справедливо для записи:

SELECT somefunc() OR true;

Заметьте, что это отличается от «оптимизации» вычисления логических операторов слева направо, реализованной в некоторых языках программирования.

Как следствие, в сложных выражениях не стоит использовать функции с побочными эффектами. Особенно опасно рассчитывать на порядок вычисления или побочные эффекты в предложениях WHERE и HAVING, так как эти предложения тщательно оптимизируются при построении плана выполнения. Логические выражения (сочетания AND/OR/NOT) в этих предложениях могут быть видоизменены любым способом, допустимым законами Булевой алгебры.

Когда порядок вычисления важен, его можно зафиксировать с помощью конструкции CASE (см. Раздел 9.18). Например, такой способ избежать деления на ноль в предложении WHERE ненадёжен:

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

Безопасный вариант:

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

Применяемая так конструкция CASE защищает выражение от оптимизации, поэтому использовать её нужно только при необходимости. (В данном случае было бы лучше решить проблему, переписав условие как y > 1.5*x.)

Однако CASE не всегда спасает в подобных случаях. Показанный выше приём плох тем, что не предотвращает раннее вычисление константных подвыражений. Как описано в Разделе 38.7, функции и операторы, помеченные как IMMUTABLE, могут вычисляться при планировании, а не выполнении запроса. Поэтому в примере

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

, скорее всего, произойдёт деление на ноль из-за того, что планировщик попытается упростить константное подвыражение, даже если во всех строках в таблице x > 0, а значит во время выполнения ветвь ELSE никогда не будет выполняться.

Хотя этот конкретный пример может показаться надуманным, похожие ситуации, в которых неявно появляются константы, могут возникать и в запросах внутри функций, так как значения аргументов функции и локальных переменных при планировании могут быть заменены константами. Поэтому, например, в функциях PL/pgSQL гораздо безопаснее для защиты от рискованных вычислений использовать конструкцию IF-THEN-ELSE, чем выражение CASE.

Ещё один подобный недостаток этого подхода в том, что CASE не может предотвратить вычисление заключённого в нём агрегатного выражения, так как агрегатные выражения вычисляются перед всеми остальными в списке SELECT или предложении HAVING. Например, в следующем запросе может возникнуть ошибка деления на ноль, несмотря на то, что он вроде бы защищён от неё:

SELECT CASE WHEN min(employees) > 0
            THEN avg(expenses / employees)
       END
    FROM departments;

Агрегатные функции min() и avg() вычисляются независимо по всем входным строкам, так что если в какой-то строке поле employees окажется равным нулю, деление на ноль произойдёт раньше, чем станет возможным проверить результат функции min(). Поэтому, чтобы проблемные входные строки изначально не попали в агрегатную функцию, следует воспользоваться предложениями WHERE или FILTER.