PostgreSQL - Выражения значений

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

Выражение значения может быть одним из следующих:

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

Ссылки на столбцы

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

correlation.columnname

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

Позиционные параметры

Ссылка на позиционный параметр используется для указания значения, которое предоставляется извне для оператора 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]

Скобки в последнем примере обязательны.

Выбор поля

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

expression.fieldname

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

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

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

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

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

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

(compositecol).*

Вызовы оператора

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

expression operator expression(бинарный инфиксный оператор)

operator expression(унарный префиксный оператор)

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

OPERATOR(schema.operatorname)

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

Вызовы функций

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

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

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

sqrt(2)

Агрегированные выражения

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

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(*)агрегатной функции. Последняя форма используется с агрегатными функциями упорядоченного набора , которые описаны ниже.

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

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

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

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

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

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

Пример агрегатного вызова упорядоченного набора:

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

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

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

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)

Другие агрегатные функции могут быть добавлены пользователем.

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

Когда агрегатное выражение появляется в подзапросе, агрегат обычно вычисляется по строкам подзапроса. Но возникает исключение, если аргументы агрегата (и filter_clauseесли они есть) содержат только переменные внешнего уровня: тогда агрегат принадлежит ближайшему такому внешнему уровню и оценивается по строкам этого запроса. Совокупное выражение в целом является внешней ссылкой для подзапроса, в котором оно появляется, и действует как константа при любой оценке этого подзапроса. Ограничение появления только в списке результатов или HAVINGпредложении применяется в отношении уровня запроса, к которому принадлежит агрегат.

Вызовы оконных функций

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

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предложении. Стоит отметить, что OVER wnameэто не совсем эквивалентно OVER (wname ...); последнее подразумевает копирование и изменение определения окна и будет отклонено, если указанная спецификация окна включает предложение фрейма.

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

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

A frame_startof UNBOUNDED PRECEDINGозначает, что фрейм начинается с первой строки раздела, и аналогично означает frame_end, UNBOUNDED FOLLOWINGчто фрейм заканчивается последней строкой раздела.

В режиме RANGEor GROUPSa frame_startof CURRENT ROWозначает, что кадр начинается с первой равноправной строки текущей строки (строка, которую предложение окна ORDER BYсортирует как эквивалентную текущей строке), а a frame_endof CURRENT ROWозначает, что кадр заканчивается последней равноправной строкой текущей строки. В ROWSрежиме CURRENT ROWпросто означает текущую строку.

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

В ROWSрежиме offsetдолжно возвращаться ненулевое неотрицательное целое число, а параметр означает, что кадр начинается или заканчивается на указанное количество строк до или после текущей строки.
В GROUPSрежиме offsetснова должно возвращаться ненулевое, неотрицательное целое число, а параметр означает, что кадр начинает или заканчивает указанное количество групп одноранговых узлов до или после группы одноранговых элементов текущей строки, где группа одноранговых узлов представляет собой набор строки, которые эквивалентны в ORDER BYпорядке. (Для использования режима ORDER BYв определении окна должно быть предложение.)GROUPS
В RANGEрежиме эти параметры требуют, чтобы в ORDER BYпредложении был указан ровно один столбец. Указывает offsetмаксимальную разницу между значением этого столбца в текущей строке и его значением в предыдущих или последующих строках кадра. Тип данных offsetвыражения зависит от типа данных столбца упорядочивания. Для числовых столбцов упорядочивания он обычно имеет тот же тип, что и столбец упорядочивания, но для столбцов упорядочивания даты и времени это тип interval. Например, если столбец упорядочения имеет тип dateили timestamp, можно написать RANGE BETWEEN '1 day' PRECEDING AND '10 days' FOLLOWING. По offset-прежнему требуется, чтобы он был ненулевым и неотрицательным, хотя значение « неотрицательного » зависит от его типа данных.

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

Обратите внимание, что в обоих режимах ROWSи эквивалентны . Это обычно выполняется и в режиме для соответствующего значения « zero » , специфичного для типа данных .GROUPS0 PRECEDING0 FOLLOWINGCURRENT ROWRANGE

Этот 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, это означает, что все строки раздела включены в рамку окна, поскольку все строки становятся равными текущей строке.

Ограничения заключаются в том, что frame_startне может быть UNBOUNDED FOLLOWING, frame_endне может быть UNBOUNDED PRECEDING, и frame_endвыбор не может появиться в приведенном выше списке frame_startи frame_endвариантов раньше, чем frame_startвыбор — например , не разрешено. Но, например, разрешено, даже если он никогда не выберет ни одной строки.RANGE BETWEEN CURRENT ROW AND offset PRECEDINGROWS BETWEEN 7 PRECEDING AND 8 PRECEDING

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

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

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

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

Type Casts

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

CAST ( expression AS type )
expression::type

Синтаксис CASTсоответствует SQL; синтаксис with ::является историческим использованием PostgreSQL .

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

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

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

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

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

Но это ошибка:

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

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

Скалярные подзапросы

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

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

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)

По умолчанию тип элемента массива является общим типом выражений-членов, определяемым по тем же правилам, что и конструкции for UNIONили CASE

Вы можете переопределить это, явно приведя конструктор массива к нужному типу, например:

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

Это имеет тот же эффект, что и индивидуальное приведение каждого выражения к типу элемента массива.

Значения многомерного массива могут быть созданы с помощью конструкторов вложенных массивов. Во внутренних конструкторах ключевое слово 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всегда начинаются с единицы.

Конструкторы строк

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

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

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

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

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

Правила оценки выражений

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

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

SELECT true OR somefunc();

тогда somefunc()бы (вероятно) вообще не вызывали. То же самое было бы, если бы кто-то написал:

SELECT somefunc() OR true;

Обратите внимание, что это не то же самое, что « замыкание » булевых операторов слева направо, встречающееся в некоторых языках программирования.

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

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

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

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

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

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

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

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