На столбец можно ссылаться так:

correlation.columnname

correlation - это имя таблицы (возможно, с указанием схемы), или псевдоним для таблицы, определенный с помощью предложения FROM. Имя и разделительную точку можно опустить, если имя столбца уникально для всех используемых таблиц в текущем запросе. (См. также Глава 7).

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

$number

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

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

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

Если выражение возвращает значение типа массива, то конкретный элемент значения массива можно извлечь, написав

expression[subscript]

или несколько соседних элементов (срез “массива”) можно извлечь, написав

expression[lower_subscript:upper_subscript]

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

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

mytable.arraycolumn[4]
mytable.two_d_column[17][34]
$1[10:42]
(arrayfunction(a,b))[42]

Скобки в последнем примере обязательны. См. Раздел 8.15 для получения дополнительной информации о массивах.

Если выражение возвращает значение составного типа (тип строки), то конкретное поле строки можно извлечь, написав

expression.fieldname

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

mytable.mycolumn
$1.somecolumn
(rowfunction(a,b)).col3

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

(compositecol).somefield
(mytable.compositecol).somefield

Скобки здесь нужны, чтобы показать, что compositecol - это имя столбца, а не имя таблицы, и что mytable - это имя таблицы, а не имя схемы во втором случае.

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

(compositecol).*

Эта запись ведет себя по-разному в зависимости от контекста; см. Раздел 8.16.5 для получения более подробной информации.

Есть два возможных синтаксиса для вызова оператора:

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

OPERATOR(schema.operatorname)

Какие конкретные операторы существуют и являются ли они унарными или бинарными, зависит от того, какие операторы были определены системой или пользователем. В Глава 9 описываются встроенные операторы.

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

function_name ([expression [, expression ... ]] )

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

sqrt(2)

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

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

Аргументам могут быть присвоены необязательные имена. См. Раздел 4.3 для получения подробной информации.

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

aggregate_name (expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name (ALL expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name ( * ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name ( [ expression [ , ... ] ] ) WITHIN GROUP ( order_by_clause ) [ FILTER ( WHERE filter_clause ) ]

где aggregate_name - это название ранее определенной агрегатной функции (возможно, с указанием имени схемы), а expression - это любое выражение значения, которое само не содержит агрегатного выражения или вызова оконной функции. Опциональные предложения order_by_clause и filter_clause описаны ниже.

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

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

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

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

SELECT array_agg(a ORDER BY b DESC) FROM table;

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

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

но не так:

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

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

Если в дополнение к order_by_clause указано DISTINCT, то все выражения ORDER BY должны соответствовать обычным аргументам агрегата; то есть нельзя сортировать по выражению, которое не включено в список DISTINCT.

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

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

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

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

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

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.23), агрегат обычно вычисляется по строкам подзапроса. Однако возникает исключение, если аргументы агрегата (и filter_clause, если есть) содержат только переменные внешнего уровня: агрегат тогда относится к ближайшему внешнему уровню и вычисляется по строкам этого запроса. Агрегатное выражение является внешней ссылкой для соответствующего подзапроса, и действует как константа для каждого вычисления этого подзапроса. При этом агрегатная функция появляется только в списке результатов или в предложении HAVING и применяется к уровню запроса, к которому относится агрегат.

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

function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER window_name
function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER window_name
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )

где window_definition записывается в виде

[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]

Необязательное определение frame_clause можно записать так

{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]

где frame_start и frame_end можно записать одним из следующих способов

UNBOUNDED PRECEDING
offset PRECEDING
CURRENT ROW
offset FOLLOWING
UNBOUNDED FOLLOWING

и frame_exclusion можно записать одним из следующих способов

EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS

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

window_name - это ссылка на именованную спецификацию окна, определенную в предложении WINDOW запроса. В качестве альтернативы, можно указать полное window_definition в скобках, используя ту же синтаксическую конструкцию, что и для определения именованного окна в предложении WINDOW; см. страницу справки SELECT для получения подробной информации. Стоит отметить, что OVER wname не совсем эквивалентно OVER (wname ...); второй вариант подразумевает копирование и изменение определения окна и будет отклонено, если указанная спецификация окна, включает определение рамки.

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

Предложение frame_clause определяет набор строк, составляющих window frame, который является подмножеством текущей секции для тех оконных функций, которые работают с рамкой, а не со всей секцией. Набор строк в рамке может изменяться в зависимости от текущей строки. Рамка может быть указана в режиме RANGE, ROWS или GROUPS; в каждом случае он запускается от frame_start до frame_end. Если frame_end не указан, рамка заканчивается на текущей строке CURRENT ROW.

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

В режиме RANGE или GROUPS, frame_start со значением CURRENT ROW означает, что рамка начинается с первой строки peer (строки, которую ORDER BY сортирует как эквивалентную текущей строке), а frame_end со значением CURRENT ROW означает, что рамка заканчивается последней строкой peer для текущей строки. В режиме ROWS, CURRENT ROW просто означает текущую строку.

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

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

Обратите внимание, что в режиме ROWS и GROUPS 0 PRECEDING и 0 FOLLOWING эквивалентны CURRENT ROW. Обычно это также справедливо в режиме RANGE, с соответствующим типо-зависимым значением “ноль”.

Опция frame_exclusion позволяет исключить строки вокруг текущей строки из рамки, даже если они должны быть включены в соответствии с настройками начала и конца рамки. 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 нельзя указать как frame_start, а UNBOUNDED PRECEDING - в качестве frame_end, и frame_end не может идти раньше, чем frame_start в списке вышеуказанных вариантов frame_start и frame_end - например, RANGE BETWEEN CURRENT ROW AND offset PRECEDING не допускается. Однако, например, ROWS BETWEEN 7 PRECEDING AND 8 PRECEDING разрешено, хотя рамка не захватит ни одной строки.

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

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

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

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

Более подробную информацию по оконным функциям можно найти в Раздел 3.5, Раздел 9.22 и Раздел 7.2.5.

Приведение типов определяет преобразование одного типа данных в другой. Tantor BE поддерживает два эквивалентных синтаксиса для приведения типов:

CAST ( expression AS type )
expression::type

Синтаксис CAST соответствует SQL; синтаксис с :: является историческим наследием Tantor BE.

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

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

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

typename ( expression )

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

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

expr COLLATE collation

где collation является идентификатором, который может быть дополнен указанием схемы. Предложение 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 к более чем одному аргументу является ошибкой. Дополнительные сведения см. в разделе Раздел 22.2). Таким образом, мы получаем тот же результат, что и предыдущий пример:

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

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

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

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

Скалярный подзапрос - это обычный запрос SELECT в скобках, который возвращает ровно одну строку с одним столбцом. (См. Глава 7 для получения информации о написании запросов). Запрос SELECT выполняется, и единственный возвращаемый результат используется в окружающем его выражении. Использование запроса, который возвращает более одной строки или более одного столбца, в качестве скалярного подзапроса является ошибкой. (Но если во время выполнения подзапрос не возвращает строк, ошибки нет; скалярный результат считается равным null). Подзапрос может ссылаться на переменные из окружающего запроса, которые будут считаться константами во время любого вычисления подзапроса. См. также Раздел 9.23 для других выражений, включающих подзапросы.

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

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, открывающей скобки, нуля или нескольких выражений (разделенных запятыми) для значений полей строки и закрывающей скобки. Например:

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

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

Конструктор строки поддерживает синтаксис rowvalue.*, и значение будет развернуто в список элементов значения строки, так же, как это происходит при использовании синтаксиса .* на верхнем уровне списка 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;

-- No cast needed since only one getf1() exists
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;

-- Now we need a cast to indicate which function to call:
SELECT getf1(ROW(1,2.5,'this is a test'));
ERROR:  function getf1(record) is not unique

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)

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

SELECT ROW(1,2.5,'this is a test') = ROW(1, 3, 'not the same');

SELECT ROW(table.*) IS NULL FROM table;  -- detect all-null rows

Для получения более подробной информации см. Раздел 9.24. Конструкторы строк также могут использоваться в связке с подзапросами, как описано в Раздел 9.23.

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

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

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 не является универсальным средством для решения таких проблем. Одним из ограничений приведенного выше варианта является то, что он не предотвращает раннее вычисление константных подвыражений. Как описано в Раздел 35.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, чтобы предотвратить попадание проблемных строк в агрегатную функцию.