Синтаксис SQL

В этой главе описывается синтаксис SQL. Он формирует основу для понимания следующих глав, в которых подробно рассматриваются способы применения команд SQL для определения и изменения данных.

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

Лексическая структура

Предложение на SQL состоит из последовательности команд. Команда состоит из последовательности токенов, оканчивающейся точкой с запятой («;»). Конец потока ввода также завершает команду. Какие токены являются действительными, зависит от синтаксиса конкретной команды.

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

Например, следующее (синтаксически) допустимый ввод SQL:

SELECT * FROM MY_TABLE;
UPDATE MY_TABLE SET A = 5;
INSERT INTO MY_TABLE VALUES (3, 'hi there');

Это последовательность из трех команд, по одной на строку (хотя это и не требуется; в одной строке может быть несколько команд, и команды могут быть с пользой разбиты на строки).

Кроме того, комментарии могут появляться во входных данных SQL. Они не являются токенами, фактически они эквивалентны пробелам.

Синтаксис SQL не очень согласован относительно того, какие токены идентифицируют команды, а какие являются операндами или параметрами. Первые несколько токенов обычно являются именем команды, поэтому в приведенном выше примере мы обычно говорим о командах «SELECT», «UPDATE» и «INSERT». Но, например, команда UPDATE всегда требует, чтобы токен SET появлялся в определенной позиции, и этот конкретный вариант INSERT также требует VALUES для завершения. Точные правила синтаксиса для каждой команды описаны в главе Команды SQL.

Идентификаторы и ключевые слова

Токены, такие как SELECT, UPDATE или VALUES в приведенном выше примере, являются примерами ключевых слов, то есть слов, которые имеют фиксированное значение в языке SQL. MY_TABLE и A являются примерами идентификаторов. Они идентифицируют имена таблиц, столбцов или других объектов базы данных в зависимости от команды, в которой они используются. Поэтому их иногда просто называют «именами». Ключевые слова и идентификаторы имеют одинаковую лексическую структуру, что означает, что невозможно знать, является ли токен идентификатором или ключевым словом, не зная языка. Полный список ключевых слов, используемых стандарте SQL и в QHB можно найти в Ключевые слова SQL

Идентификаторы SQL и ключевые слова должны начинаться с буквы (a - z, но также с букв с диакритическими знаками и нелатинскими буквами) или подчеркивания (_). Последующие символы в идентификаторе или ключевом слове могут быть буквами, подчеркиванием, цифрами (0 - 9) или знаками доллара ($). Обратите внимание, что знаки доллара не допускаются в идентификаторах в соответствии с буквой стандарта SQL, поэтому их использование может сделать приложения менее переносимыми. Стандарт SQL не будет определять ключевое слово, которое содержит цифры, начинается или заканчивается подчеркиванием, поэтому идентификаторы этой формы защищены от возможного конфликта с будущими расширениями стандарта.

Система использует не более NAMEDATALEN-1 байтов идентификатора; более длинные имена могут быть записаны в командах, но они будут усечены. По умолчанию NAMEDATALEN равен 64, поэтому максимальная длина идентификатора составляет 63 байта.

Ключевые слова и идентификаторы без кавычек не чувствительны к регистру. Следовательно:

UPDATE MY_TABLE SET A = 5;

можно эквивалентно записать как:

uPDaTE my_TabLE SeT a = 5;

Часто используемое соглашение заключается в написании ключевых слов в верхнем регистре и имен в нижнем регистре, например:

UPDATE my_table SET a = 5;

Существует второй тип идентификатора: идентификатор с разделителями (delimited identifier) или идентификатор в кавычках (quoted identifier). Он формируется путем включения произвольной последовательности символов в двойные кавычки ("). Идентификатор с разделителями всегда является идентификатором, а не ключевым словом. Таким образом, "select" может использоваться для ссылки на столбец или таблицу с именем "select", тогда как select без кавычек был бы взят как ключевое слово и поэтому вызвал бы ошибку синтаксического анализа при использовании там, где ожидается имя таблицы или столбца. Пример может быть написан с помощью заключенных в кавычки идентификаторов как это:

UPDATE "my_table" SET "a" = 5;

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

Вариант заключенных в кавычки идентификаторов позволяет включать экранированные символы Юникода, идентифицируемые их кодовыми точками. Этот вариант рассмотрен в разделе Строковые константы с экранированием Unicode. Заключение в кавычки идентификатора также делает его чувствительным к регистру, тогда как имена без кавычек всегда свертываются в нижний регистр. Например, идентификаторы FOO, foo и "foo" в QHB считаются одинаковыми, но "Foo" и "FOO" отличаются друг от друга. (Свертывание имен без кавычек в нижний регистр в QHB несовместимо со стандартом SQL, который говорит, что имена без кавычек должны быть приведены в верхний регистр. Таким образом, foo должен быть эквивалентен "FOO", а не "foo" в соответствии со стандартом. Если Вы хотите писать переносимые приложения. Рекомендуется всегда указывать конкретное имя или никогда его не указывать).

Константы

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

Строковые константы

Строковая константа в SQL — это произвольная последовательность символов, ограниченная одинарными кавычками (’), например, ’This is a string’. Чтобы включить символ одинарной кавычки в строковую константу, напишите две соседние одинарные кавычки, например, ’Dianne”s horse’. Обратите внимание, что это не то же самое, что символ двойной кавычки (").

Две строковые константы, которые разделены только пробелами хотя бы с одной новой строкой, объединяются и эффективно обрабатываются так, как если бы строка была записана как одна константа. Например:

SELECT 'foo'
'bar';

эквивалентно:

SELECT ’foobar’;

но:

SELECT 'foo' 'bar';

неверный синтаксис (Это немного странное поведение определяется SQL и QHB следует стандарту).

Строковые константы с экранированием в стиле C

QHB также принимает строковые константы с экранированием, которые являются расширением стандарта SQL. Константная строка с экранированием указывается путем написания буквы E (в верхнем или нижнем регистре) непосредственно перед открывающей одинарной кавычкой, например, E’foo’. (При продолжении строковые константы с экранированием через строки пишите E только перед первой открывающей кавычкой). Внутри строки с экранированием символ обратной косой черты (\) начинает C-подобную escape- последовательность с обратной косой чертой, в которой комбинация обратной косой черты и следующего(их) символа(ов) представляет специальное байтовое значение, как показано в таблице:

Escape-последовательности с обратной косой чертой

Escape-последовательность с обратной косой чертойОписание
\bbackspace
\fform feed
\nnewline
\rcarriage return
\ttab
\o, \oo, \ooo (o = 0 - 7)octal byte value
\xh, \xhh (h = 0 - 9, A - F)hexadecimal byte value
\uxxxx, \Uxxxxxxxx (x = 0 - 9, A - F)16 or 32-bit hexadecimal Unicode character value

Любой другой символ после обратной косой черты воспринимается буквально. Таким образом, чтобы включить символ обратной косой черты, напишите две обратные косые черты (\\). Кроме того, одиночная кавычка может быть включена в escape-строку, с помощью \’, в дополнение к обычному способу ''.

Вы несете ответственность за то, что создаваемые вами последовательности байтов, особенно при использовании восьмеричного или шестнадцатеричного экранирования, составляют допустимые символы в кодировке набора символов сервера. Если кодировкой сервера является UTF-8, вместо этого следует использовать кодировку Unicode или альтернативный синтаксис escape Unicode, описанный в разделе Строковые константы с экранированием Unicode. Альтернативой может быть ручное кодирование UTF-8 и запись байтов, что будет очень громоздким.

Экранирующий синтаксис Unicode работает полностью, только если кодировка сервера UTF8. При использовании других серверных кодировок можно указывать только кодовые точки в диапазоне ASCII (до \u007F). Как 4-значная, так и 8-значная форма могут использоваться для указания суррогатных пар UTF-16 для составления символов с кодовыми точками, большими, чем U + FFFF, хотя наличие 8-значной формы технически делает это ненужным. (Если суррогатные пары используются, когда кодировкой сервера является UTF8, они сначала объединяются в одну кодовую точку, которая затем кодируется в UTF-8).

Символ с нулевым кодом не может быть строковой константой.

Строковые константы с экранированием Unicode

QHB также поддерживает другой тип синтаксиса с экранированием для строк, который позволяет указывать произвольные символы Юникода по кодовой точке. Константа escape-строки Unicode начинается с U& (заглавная или строчная буква U, за которой следует амперсанд) непосредственно перед открывающей кавычкой, без пробелов между ними, например, U&’foo’. (Обратите внимание, что это создает неоднозначность с оператором &. Используйте пробелы вокруг оператора, чтобы избежать этой проблемы). Внутри кавычек символы Unicode могут быть указаны в экранированной форме путем написания обратной косой черты с последующим четырехзначным шестнадцатеричным номером кодовой точки или в качестве альтернативы обратный слеш, за которым следует знак плюс, за которым следует шестизначный шестнадцатеричный номер кода. Например, строка ’data’ может быть записана как

U&'d\0061t\+000061'

Следующий менее тривиальный пример пишет русское слово «слон» кириллическими буквами:

U&'\0441\043B\043E\043D'

Если требуется другой escape-символ, чем обратная косая черта, его можно указать с помощью оператора UESCAPE после строки, например:

U&'d!0061t!+000061' UESCAPE '!'

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

Экранирующий синтаксис Unicode работает только в том случае, если кодировка сервера UTF8. При использовании других серверных кодировок можно указывать только кодовые точки в диапазоне ASCII (до \007F). Как 4-значная, так и 6-значная форма могут использоваться для указания суррогатных пар UTF-16 для составления символов с кодовыми точками, большими, чем U + FFFF, хотя технически наличие 6-значной формы делает это ненужным. (Если суррогатные пары используются, когда кодировкой сервера является UTF8, они сначала объединяются в одну кодовую точку, которая затем кодируется в UTF-8).

Кроме того, escape-синтаксис Unicode для строковых констант работает только при включенном параметре конфигурации standard_conforming_strings. Это связано с тем, что в противном случае этот синтаксис может запутать клиентов, которые анализируют операторы SQL, до такой степени, что это может привести к инъекциям SQL и аналогичным проблемам безопасности. Если параметр отключен, этот синтаксис будет отклонен с сообщением об ошибке.

Чтобы буквально включить escape-символ в строку, напишите его дважды.

Строковые константы в долларовых кавычках

Хотя стандартный синтаксис для указания строковых констант обычно удобен, бывает трудно понять, когда нужная строка содержит много одинарных кавычек или обратной косой черты, поскольку каждая из них должна быть удвоена. Чтобы разрешить более удобочитаемые запросы в таких ситуациях, QHB предоставляет другой способ, называемый «цитирование в долларах», для записи строковых констант. Строковая константа, заключенная в кавычки, состоит из знака доллара ($), необязательного «тега», состоящего из нуля или более символов, другого знака доллара, произвольной последовательности символов, составляющих содержимое строки, знака доллара, того же тега, которым началась эта цитата доллара и знак доллара. Например, вот два разных способа указать строку «Dianne's horse» с использованием долларовых кавычек:

$$Dianne's horse$$
$SomeTag$Dianne's horse$SomeTag$

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

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

$function$
BEGIN
    RETURN ($1 ~ $q$[\t\r\n\v\\]$q$);
END;
$function$

Здесь последовательность $q$[\t\r\n\v\\]$q$ представляет заключенную в доллары текстовую строку [\t\r\n\v\\], которая будет распознаваться, когда тело функции будет выполняться QHB. Но так как последовательность не соответствует внешнему разделителю долларовых кавычек $function$, это просто еще несколько символов внутри константы, поскольку речь идет о внешней строке.

Тэг строки в кавычках, если таковой имеется, следует тем же правилам, что и идентификатор без кавычек, за исключением того, что он не может содержать знак доллара. Теги чувствительны к регистру, поэтому $tag$String content$tag$ является правильным, но $TAG$String content$tag$ - нет.

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

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

Константы битовых строк

Константы битовых строк выглядят как обычные строковые константы с буквой B (в верхнем или нижнем регистре) непосредственно перед открывающей кавычкой (без пробелов), например, B'1001'. В битовых строковых константах допускаются только символы 0 и 1.

В качестве альтернативы константы битовой строки могут быть указаны в шестнадцатеричном формате с использованием начального X (верхний или нижний регистр), например, X'1FF'. Это обозначение эквивалентно константе битовой строки с четырьмя двоичными цифрами для каждой шестнадцатеричной цифры.

Обе формы константы битовой строки могут быть продолжены между строками так же, как обычные строковые константы. «Цитирование в долларах» нельзя использовать в константе битовой строки.

Числовые константы

Числовые константы принимаются в следующих общих формах:

digits
digits.[digits][e[+-]digits]
[digits].digits[e[+-]digits]
digitse[+-]digits

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

Вот несколько примеров допустимых числовых констант:

42
3.5
4.
.001
5e2
1.925e-3

Числовая константа, которая не содержит ни десятичной точки, ни экспоненты, изначально считается типом integer если ее значение соответствует типу integer (32 бита); в противном случае предполагается, что это тип bigint если его значение соответствует типу bigint (64 бита); в противном случае он считается типом numeric. Константы, которые содержат десятичные точки и/или показатели, всегда изначально считаются numeric.

Первоначально назначенный тип данных числовой константы является лишь отправной точкой для алгоритмов разрешения типов. В большинстве случаев константа будет автоматически приведена к наиболее подходящему типу в зависимости от контекста. При необходимости вы можете принудительно интерпретировать числовое значение как определенный тип данных путем его приведения. Например, вы можете заставить числовое значение обрабатываться как тип real (float4), написав:

REAL '1.23'  -- string style
1.23::REAL   -- PostgreSQL (historical) style

На самом деле это просто особые случаи общих обозначений приведения, обсуждаемых далее.

Константы других типов

Константа произвольного типа может быть введена с использованием любого из следующих обозначений:

type 'string'
'string'::type
CAST ( 'string' AS type )

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

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

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

typename ( 'string' )

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

CAST(), «::» и функционально-подобный синтаксис также можно использовать для указания преобразований типов во время выполнения произвольных выражений, как обсуждается в разделе Приведение типов. Чтобы избежать синтаксической неоднозначности, синтаксис type 'string' может использоваться только для указания типа простой литеральной константы. Другое ограничение синтаксиса type type 'string' заключается в том, что он не работает для типов массивов; используйте «::» или CAST() чтобы указать тип константы массива.

Синтаксис CAST() соответствует SQL. Синтаксис type 'string' является обобщением стандарта: SQL определяет этот синтаксис только для нескольких типов данных, но QHB допускает его для всех типов.

Операторы

Имя оператора — это последовательность символов длиной до 63 символов из следующего списка:

+ - * / < > = ~ ! @ # % ^ & | ` ?

Однако есть несколько ограничений на имена операторов:

  • -- и /* нигде не могут появляться в имени оператора, так как они будут приняты в качестве начала комментария.

  • Имя оператора из нескольких символов не может заканчиваться на + или -, если только имя не содержит хотя бы один из следующих символов:

~ ! @ # % ^ & | ` ?

Например, @- это разрешенное имя оператора, а *- нет. Это ограничение позволяет QHB анализировать SQL-совместимые запросы без пробелов между токенами.

При работе с нестандартными именами операторов вам обычно нужно разделять соседние операторы пробелами, чтобы избежать двусмысленности. Например, если вы определили левый унарный оператор с именем @, вы не можете написать X*@Y; вы должны написать X* @Y чтобы QHB прочитал его как два оператора, а не как один.

Специальные символы

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

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

  • Круглые скобки (()) имеют обычное значение для группирования выражений и обеспечения приоритета. В некоторых случаях скобки требуются как часть фиксированного синтаксиса конкретной команды SQL.

  • Скобки ([]) используются для выбора элементов массива. См. раздел Массивы для получения дополнительной информации о массивах.

  • Запятые (,) используются в некоторых синтаксических конструкциях для разделения элементов списка.

  • Точка с запятой (;) завершает команду SQL. Он не может появляться где-либо внутри команды, кроме как внутри строковой константы или идентификатора в кавычках.

  • Двоеточие (:) используется для выбора «кусочков» из массивов. (См. раздел Массивы. В некоторых диалектах SQL (таких как Embedded SQL) двоеточие используется для добавления префиксов к именам переменных.

  • Звездочка (*) используется в некоторых контекстах для обозначения всех полей строки таблицы или составного значения. Он также имеет особое значение при использовании в качестве аргумента агрегатной функции, а именно, что агрегат не требует какого-либо явного параметра.

  • Точка (.) Используется в числовых константах и для разделения имен схем, таблиц и столбцов.

Комментарии

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

-- This is a standard SQL comment

В качестве альтернативы можно использовать комментарии блока в стиле C:

/* multiline comment
 * with nesting: /* nested block comment */
 */

где комментарий начинается с /* и распространяется на совпадение вхождения */. Эти блочные комментарии вложены, как указано в стандарте SQL, но в отличие от C, так что можно закомментировать большие блоки кода, которые могут содержать существующие блочные комментарии.

Комментарий удаляется из входного потока перед дальнейшим синтаксическим анализом и фактически заменяется пробелом.

Приоритет оператора

Таблица "Приоритет оператора" показывает приоритет и ассоциативность операторов в QHB. Большинство операторов имеют одинаковый приоритет и являются левоассоциативными. Приоритет и ассоциативность операторов встроены в синтаксический анализатор.

Иногда вам нужно будет добавить скобки при использовании комбинаций бинарных и унарных операторов. Например:

SELECT 5 ! - 6;

будет проанализирован как:

SELECT 5 ! (- 6);

потому что синтаксический анализатор понятия не имеет - пока не стало слишком поздно – «!» определяется как постфиксный оператор, а не как инфиксный. Чтобы получить желаемое поведение в этом случае, вы должны написать:

SELECT (5 !) - 6;

Приоритет оператора (от высшего к низшему)

Оператор / ЭлементАссоциативностьОписание
.leftразделитель имени таблицы/столбца
::leftопределение типа в стиле QHB
[ ]leftвыбор элемента массива
+ -rightунарный плюс, унарный минус
^leftэкспоненцирование
* / %leftумножение, деление, по модулю
+ -leftсложение, вычитание
(любой другой оператор)leftвсе остальные собственные и пользовательские операторы
BETWEEN IN LIKE ILIKE SIMILARограничение диапазона, набор членов, сопоставление строк
< > = <= >= <>операторы сравнения
IS ISNULL NOT NULLIS TRUE, IS FALSE, IS NULL, IS DISTINCT FROM т.д.
NOTrightлогическое отрицание
ANDleftлогическое соединение
ORleftлогическая дизъюнкция

Обратите внимание, что правила приоритета операторов также применяются к пользовательским операторам, имена которых совпадают с именами встроенных операторов, упомянутых выше. Например, если вы определите оператор «+» для некоторого пользовательского типа данных, он будет иметь тот же приоритет, что и встроенный оператор «+», независимо от того, что делает ваш.

Когда полное имя схемы используется в синтаксисе OPERATOR, как, например, в:

SELECT 3 OPERATOR(pg_catalog.+) 4;

конструкция OPERATOR имеет приоритет по умолчанию, показанный в таблице 9 для «любого другого оператора». Это верно независимо от того, какой конкретный оператор появляется внутри OPERATOR().

Выражения значения

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

Выражением значения может быть:

  • Постоянное или буквальное значение;

  • Ссылка на столбец;

  • Ссылка на позиционный параметр в теле определения функции или подготовленного оператора;

  • Выражение подзапроса;

  • Выражение выбора поля;

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

  • Вызов функции;

  • Агрегатное выражение;

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

  • Приведение типа;

  • Сортировка выражения;

  • Скалярный подзапрос;

  • Конструктор массива;

  • Конструктор строк.

  • Другое выражение значения в скобках (используется для группировки подвыражений и переопределения приоритета).

В дополнение к этому списку, существует ряд конструкций, которые могут быть классифицированы как выражения, но не следуют никаким общим правилам синтаксиса. Как правило, они имеют семантику функции или оператора и объясняются в соответствующем месте в главе Функции и операторы. Примером является предложение IS NULL.

Константы уже обсуждались в разделе Константы. В следующих подразделах обсуждаются остальные варианты.

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

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

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]

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

В общем случае массив 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 (binary infix operator)
operator expression (unary prefix operator)
expression operator (unary postfix operator)

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

OPERATOR(schema.operatorname)

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

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

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

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

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

sqrt(2)

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

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

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

Функция, которая принимает один аргумент составного типа, может вызываться с использованием синтаксиса выбора поля, и наоборот, выбор поля может быть написан в функциональном стиле. То есть обозначения col(table) и table.col являются взаимозаменяемыми. Такое поведение не является стандартом SQL, но предоставляется в QHB, поскольку позволяет использовать функции для эмуляции «вычисляемых полей».

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

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

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

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; -- неверно

Последний синтаксически допустим, но он представляет собой вызов однопараметрической агрегатной функции с двумя ключами 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 и передаются в агрегатную функцию в качестве входных аргументов. (Это непохоже на случай non-WITHIN GROUP order_by_clause, которой не рассматривается как аргумент(ы) для агрегатной функции). Выражения аргументов, предшествующие WITHIN GROUP, если таковые имеются, называются прямыми аргументами, чтобы отличать их от агрегатных аргументов, перечисленных в order_by_clause. В отличие от обычных агрегатных аргументов, прямые аргументы вычисляются только один раз за вызов агрегата, а не один раз на каждую входную строку. Это означает, что они могут содержать переменные, только если эти переменные сгруппированы по GROUP BY; это ограничение такое же, как если бы прямые аргументы вообще не были внутри агрегатного выражения. Прямые аргументы обычно используются для таких вещей, как процентильные дроби, которые имеют смысл только как одно значение для расчета агрегации. Список прямых аргументов может быть пустым; в этом случае просто напишите () не (*). (QHB фактически принимает любое написание, но только первый способ соответствует стандарту 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)

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

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

Когда агрегатное выражение появляется в подзапросе (см.раздел Скалярные подзапросы и раздел Выражения подзапроса, агрегат обычно вычисляется по строкам подзапроса. Но возникает исключение, если аргументы агрегата (и 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 ...); последнее подразумевает копирование и изменение определения окна и будет отклонено, если спецификация окна, на которое ссылается ссылка, включает предложение frame.

Предложение 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.

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

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

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

  • В режиме ROWS offset должно давать ненулевое, неотрицательное целое число, а параметр означает, что кадр начинается или заканчивается указанным числом строк до или после текущей строки.

  • В режиме GROUPS offset снова должно давать ненулевое, неотрицательное целое число, а параметр означает, что кадр начинается или заканчивается за указанное число одноранговых групп до или после группы текущей строки, где одноранговые группы представляют набор строк, которые эквивалентных в порядке ORDER BY. (Для использования режима GROUPS в определении окна должно быть предложение ORDER BY).

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

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

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

Опция 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 PRECEDING не допускается. Но, например, ROWS BETWEEN 7 PRECEDING AND 8 PRECEDING разрешены, даже если они никогда не будут выбирать какие-либо строки.

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

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

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

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

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

Приведение типов

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

CAST ( expression AS type )
expression::type

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

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

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

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

typename (expression)

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

Функционально-подобный синтаксис на самом деле является просто вызовом функции. Когда один из двух стандартных синтаксисов преобразования используется для выполнения преобразования во время выполнения, он будет внутренне вызывать зарегистрированную функцию для выполнения преобразования. По соглашению, эти функции преобразования имеют то же имя, что и их тип вывода, и, таким образом, «функционально-подобный синтаксис» является не чем иным, как прямым вызовом базовой функции преобразования. Очевидно, что это не то, на что портативное приложение должно положиться. Для получения дополнительной информации см. CREATE CAST.

Сортировка выражений

Предложение 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";

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

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

Скалярный подзапрос — это обычный запрос 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)

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

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

Элементы конструктора многомерного массива могут быть чем угодно, дающим массив правильного вида, а не только конструкцией sub-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,2413}
(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.*, который будет расширен до списка элементов значения строки, как это происходит, когда синтаксис .* используется на верхнем уровне списка SELECT (см. раздел Использование составных типов в запросах). Например, если таблица 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

Подробнее см. раздел Сравнение строк и массивов. Конструкторы строк также могут использоваться в связи с подзапросами, как обсуждается в разделе Выражения подзапроса.

Правила вычисления выражений

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

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

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.

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

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

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

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

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

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

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

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

CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false)
RETURNS text
AS
$$
 SELECT CASE
        WHEN $3 THEN UPPER($1 || ' ' || $2)
        ELSE LOWER($1 || ' ' || $2)
        END;
$$
LANGUAGE SQL IMMUTABLE STRICT;

Функция concat_lower_or_upper имеет два обязательных параметра, a и b. Кроме того, есть один необязательный параметр uppercase который по умолчанию равен false. Входы a и b будут объединены и переведены в верхний или нижний регистр в зависимости от параметра uppercase. Остальные детали этого определения функции здесь не важны (см. главу Расширение SQL для получения дополнительной информации).

Использование позиционной нотации

Позиционная нотация — это традиционный механизм передачи аргументов функциям в QHB. Примером является:

SELECT concat_lower_or_upper('Hello', 'World', true);
 concat_lower_or_upper
-----------------------
 HELLO WORLD
(1 row)

Все аргументы указаны в порядке. Результат - верхний регистр, поскольку uppercase указан как true. Другой пример:

SELECT concat_lower_or_upper('Hello', 'World');
 concat_lower_or_upper
-----------------------
 hello world
(1 row)

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

Использование именованных обозначений

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

SELECT concat_lower_or_upper(a => 'Hello', b => 'World');
 concat_lower_or_upper
-----------------------
 hello world
(1 row)

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

SELECT concat_lower_or_upper(a => 'Hello', b => 'World', uppercase => true);
 concat_lower_or_upper
-----------------------
 HELLO WORLD
(1 row)

SELECT concat_lower_or_upper(a => 'Hello', uppercase => true, b => 'World');
 concat_lower_or_upper
-----------------------
 HELLO WORLD
(1 row)

Более старый синтаксис, основанный на ":=", поддерживается для обратной совместимости:

SELECT concat_lower_or_upper(a := 'Hello', uppercase := true, b := 'World');
 concat_lower_or_upper
-----------------------
 HELLO WORLD
(1 row)

Использование смешанной нотации

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

SELECT concat_lower_or_upper('Hello', 'World', uppercase => true);
 concat_lower_or_upper
-----------------------
 HELLO WORLD
(1 row)

В приведенном выше запросе аргументы a и b указываются позиционно, а uppercase - именем. В этом примере это мало что добавляет, кроме документации. С более сложной функцией, имеющей многочисленные параметры со значениями по умолчанию, именованные или смешанные обозначения могут сэкономить много времени и уменьшить вероятность ошибок.

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