Синтаксис 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;

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

UPDATE "my_table" SET "a" = 5;

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

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

Еще один вариант идентификаторов в кавычках позволяет использовать экранированные символы Unicode, идентифицируемые по их кодам. Этот вариант начинается с U& (строчная или прописная U, потом амперсанд), а за ними сразу без пробелов идет кавычка, например U&"foo". (Обратите внимание, что это создает неоднозначность с оператором &. Чтобы избежать этой проблемы, ставьте вокруг этого оператора пробелы.) Внутри кавычек можно писать символы Unicode, экранированные либо обратным слэшем, за которым идет код символа из четырех шестнадцатеричных цифр, либо, как вариант, обратным слэшем, за которым идет знак плюс и код из шести шестнадцатеричных цифр. Например, идентификатор "data" можно записать как

U&"d\0061t\+000061"

В следующем, менее тривиальном примере, закодировано русское слово «слон» на кириллице:

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

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

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

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

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

Чтобы указать суррогатные пары UTF-16 для составления символов с кодами больше чем U+FFFF, можно использовать либо четырех-, либо шестизначную форму, хотя доступность шестизначной формы технически делает это ненужным. (Суррогатные пары не сохраняются напрямую, а объединяются в один код UTF-8.)

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

Константы

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

Таблица 1. Управляющие последовательности с обратным слэшем

Управляющая последовательность с обратным слэшем	Описание
\b	возврат на символ
\f	перевод страницы
\n	перевод строки
\r	возврат каретки
\t	табуляция
\o, \oo, \ooo (o = 0-7)	восьмеричное значение байта
\xh, \xhh (h = 0-9, A-F)	шестнадцатеричное значение байта
\uxxxx, \Uxxxxxxxx (x = 0-9, A-F)	16- или 32-битное шестнадцатеричное значение символа Unicode

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

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

ВНИМАНИЕ!
Если параметр конфигурации standard_conforming_strings имеет значение off, то QHB распознает обратный слэш как управляющий символ и в обычных строках и в управляющих строковых константах. Однако по умолчанию этот параметр имеет значение on, а это означает, что обратный слэш распознается только в управляющих строковых константах. Это поведение больше соответствует стандарту, хотя может нарушить работу приложений, полагающихся на поведение, когда обратный слэш распознается всегда. В качестве обходного решения можно установить этот параметр в off, но лучше просто отказаться от использования обратного слэша в качестве управляющего символа. Если вам нужно, чтобы обратный слэш представлял специальный символ, напишите строковую константу с E.

В дополнение к standard_conforming_strings обращением с обратными слэшами в строковых константах управляют параметры конфигурации escape_string_warning и backslash_quote.

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

Строковые константы с управляющими последовательностями Unicode

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

U&'d\0061t\+000061'

В следующем, менее тривиальном примере, закодировано русское слово «слон» на кириллице:

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

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

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

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

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

Строковые константы, заключенные в знаки доллара

Хотя стандартный синтаксис для указания строковых констант обычно удобен, в нем бывает трудно разобраться, когда нужная строка содержит много апострофов или обратных слэшей, поскольку каждый из них должен быть удвоен. Чтобы обеспечить в таких ситуациях более удобочитаемые запросы, 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'. Это обозначение равнозначно константе битовой строки с четырьмя двоичными цифрами для каждой шестнадцатеричной цифры.

Обе формы константы битовой строки можно разносить на несколько строк так же, как и обычные строковые константы. Заключать в знаки доллара константы битовых строк нельзя.

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

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

цифры
цифры.[цифры][e[+-]цифры]
[цифры].цифры[e[+-]цифры]
цифры[+-]цифры

где цифры — это одна или несколько десятичных цифр (от 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'  -- строковый стиль
1.23::REAL   -- стиль PostgreSQL (недействующий)

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

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

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

тип 'строка'
'строка'::тип
CAST ( 'строка' AS тип )

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

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

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

имя_типа ( 'строка' )

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

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

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

Операторы

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

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

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

В имени оператора не могут фигурировать -- и /*, поскольку они будут приняты за начало комментария.
Имя оператора из нескольких символов может заканчиваться на + или -, только если это имя содержит хотя бы один из следующих символов:
```
~ ! @ # % ^ & | ` ?
```

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

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

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

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

Знак доллара ($), за которым следуют цифры, используется для представления позиционного параметра в теле определения функции или подготовленного оператора. В других контекстах знак доллара может быть частью идентификатора или строковой константы в долларовых кавычках.
Круглые скобки (()) имеют обычное значение и применяются для группирования выражений и обеспечения приоритета. В некоторых случаях скобки требуются как часть фиксированного синтаксиса конкретной команды SQL.
Квадратные скобки ([]) используются для выбора элементов массива. Дополнительную информацию о массивах см. в разделе Массивы.
Запятые (,) используются в некоторых синтаксических конструкциях для разделения элементов списка.
Точка с запятой (;) завершает команду SQL. Она не может находиться где-либо внутри команды, кроме как строковых констант или идентификаторов в кавычках.
Двоеточие (:) используется для выбора «срезов» из массивов. (См. раздел Массивы. В некоторых диалектах SQL (например в Embedded SQL) двоеточие используется в качестве префиксов в именах переменных.
Звездочка (*) используется в некоторых контекстах для обозначения всех полей табличной строки или составного значения. Она также имеет особое значение при использовании в качестве аргумента агрегатной функции, а именно, указывает, что агрегат не требует какого-либо явного параметра.
Точка (.) Используется в числовых константах и для разделения имен схем, таблиц и столбцов.

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

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

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

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

Таблица 2. Приоритеты операторов (от высшего к низшему)

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

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

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

SELECT 3 OPERATOR(pg_catalog.+) 4;

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

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

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

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

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

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

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

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

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

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

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

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

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

$число

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

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

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

Индексы массивов

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

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

или можно извлечь несколько смежных элементов («срез массива»), написав

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

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

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

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

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

Выбор поля

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

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

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

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

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

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

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

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

(compositecol).*

Эта запись ведет себя по-разному в зависимости от контекста; подробную информацию см. в подразделе Использование составных типов в запросах.

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

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

выражение оператор выражение (бинарный инфиксный оператор)
оператор выражение (унарный префиксный оператор)

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

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

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

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

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

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

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

sqrt(2)

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

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

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

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

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

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

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

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

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

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

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

Обычно входные строки подаются в агрегатную функцию в неопределенном порядке. Во многих случаях это не имеет значения; например, min выдает одинаковый результат независимо от того, в каком порядке она принимает входные данные. Однако некоторые агрегатные функции (например array_agg и string_agg) выдают результаты, зависящие от порядка входных строк. При использовании такого агрегата для указания желаемого порядка можно добавить необязательное предложение_order_by. У предложения_order_by тот же синтаксис, что и у предложения ORDER BY уровня запроса, как описано в разделе Сортировка строк (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 (второй довольно бесполезен, поскольку является константой).

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

Примечание
Возможность указывать в агрегатной функции одновременно DISTINCT и ORDER BY является расширением QHB.

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

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

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

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

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

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

где определение_окна имеет синтаксис:

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

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

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

где начало_рамки и конец_рамки могут иметь вид:

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

а исключение_рамки может иметь вид:

EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS

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

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

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

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

Вместе с UNBOUNDED PRECEDING начало_рамки означает, что эта рамка начинается с первой строки партиции, а конец_рамки с UNBOUNDED FOLLOWING аналогичным образом означает, что рамка заканчивается последней строкой партиции.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выражения правил сортировки

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

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

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

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

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

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

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

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

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

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

Но этот вариант будет ошибкой:

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

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

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

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

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

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 (см. раздел 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,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 необязательно.

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

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

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

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

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

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

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

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

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

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

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

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

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

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