Функции и операторы

QHB предоставляет большое количество функций и операторов для встроенных типов данных. Пользователи также могут определять свои собственные функции и операторы, как описано в части V. Команды qsql \df и \do могут использоваться для вывода списка всех доступных функций и операторов соответственно.

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

• Логические операторы
• Функции сравнения и операторы
• Математические функции и операторы
• Строковые функции и операторы
  • FORMAT
• Двоичные строковые функции и операторы
• Функции и операторы битовых строк
• Сопоставление с образцом
  • LIKE
  • Регулярные выражения SIMILAR TO
  • Регулярные выражения POSIX
    • Детали регулярных выражений
    • Выражения в скобках
    • Экранирование в регулярных выражениях
    • Метасинтаксис регулярных выражений
    • Правила сопоставления регулярных выражений
    • Пределы и совместимость
    • Основные регулярные выражения
    • Отличия от XQuery (LIKE_REGEX)
• Функции форматирования типов данных
• Функции и операторы даты/времени
  • EXTRACT, date_part
  • date_trunc
  • AT TIME ZONE
  • Текущая дата / время
  • Задержка исполнения
• Функции поддержки Enum
• Геометрические функции и операторы
• Функции и операторы сетевых адресов
• Функции текстового поиска и операторы
• Функции XML
  • Создание XML-контента
    • xmlcomment
    • xmlconcat
    • xmlelement
    • xmlforest
    • xmlpi
    • xmlroot
    • xmlagg
  • Предикаты XML
    • IS DOCUMENT
    • IS NOT DOCUMENT
    • XMLEXISTS
    • xml_is_well_formed
  • Обработка XML
    • xpath
    • xpath_exists {#}
    • xmltable
  • Отображение таблиц в XML
• Функции и операторы JSON
  • Обработка и создание данных JSON
  • Язык путей SQL/JSON
    • Строгий и слабый режимы
    • Регулярные выражения
    • Операторы пути и методы SQL/JSON
• Функции управления последовательностями
• Условные выражения
  • CASE
  • COALESCE
  • NULLIF
  • GREATEST и LEAST
• Функции и операторы массива
• Функции диапазона и операторы
• Агрегатные функции
• Оконные функции
• Выражения подзапроса
  • EXISTS
  • IN
  • NOT IN
  • ANY/SOME
  • ALL
  • Сравнение по одной строке
• Сравнение строк и массивов
  • IN
  • NOT IN
  • ANY/SOME (массив)
  • ALL (массив)
  • Сравнение конструкторов строк
  • Сравнение составных типов
• Функции возврата наборов (SET)
• Системные информационные функции и операторы
• Функции системного администрирования
  • Функции настройки конфигурации
  • Функции сигнализации сервера
  • Функции управления резервным копированием
  • Функции управления восстановлением
  • Функции синхронизации снимков
  • Функции репликации
  • Функции управления объектами базы данных
  • Функции обслуживания индекса
  • Общие функции доступа к файлам
  • Функции консультативных блокировок
  • Отправка метрик и аннотаций
    • Отправка метрики таймера
    • Увеличение значения "счётчика"
    • Увеличение значения типа "gauge"
    • Уменьшение значения типа "gauge"
    • Установка значение типа "gauge"
    • Отправка аннотации в графану
• Триггерные функции
• Функции запуска событий
  • Захват изменений в конце команды
  • Обработка объектов, отброшенных командой DDL
  • Обработка события перезаписи таблицы
• Функции статистической информации
  • Проверка списков MCV

Логические операторы

Доступны обычные логические операторы: AND, OR, NOT.

SQL использует трехзначную логическую систему с истиной (TRUE), ложью (FALSE) и неизвестностью (NULL). Соблюдайте следующие таблицы истинности:

Операторы AND и OR являются коммутативными, то есть вы можете переключать левый и правый операнд, не влияя на результат. Но см. раздел Правила вычисления выражений для получения дополнительной информации о порядке вычисления подвыражений.

Функции сравнения и операторы

Доступны обычные операторы сравнения, как показано в таблице 1.

Таблица 1. Операторы сравнения

Операторы сравнения доступны для всех соответствующих типов данных. Все операторы сравнения являются бинарными операторами, которые возвращают значения типа boolean; выражения типа 1 < 2 < 3 недопустимы (потому что нет оператора < для сравнения логического значения с 3).

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

Таблица 2. Предикаты сравнения

Предикат BETWEEN упрощает тестирование диапазона:

a BETWEEN x AND y

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

a >= x AND a <= y

Обратите внимание, что BETWEEN обрабатывает значения конечных точек как включенные в диапазон. NOT BETWEEN делает противоположное сравнение:

a NOT BETWEEN x AND y

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

a < x OR a > y

BETWEEN SYMMETRIC аналогичен BETWEEN за исключением того, что не требуется, чтобы аргумент слева от AND был меньше или равен аргументу справа. Если это не так, эти два аргумента автоматически меняются местами, поэтому всегда подразумевается непустой диапазон.

Обычные операторы сравнения выдают null (что означает «неизвестно»), а не истину или ложь, когда любой из входных операндов null. Например, 7 = NULL возвращает null, как и 7 <> NULL. Когда это поведение не подходит, используйте предикаты IS [NOT] DISTINCT FROM:

a IS DISTINCT FROM b
a IS NOT DISTINCT FROM b

Для не-null входных аргументов IS DISTINCT FROM аналогичен оператору <>. Однако, если оба входных аргумента имеют значение null, он возвращает false, а если только один аргумент имеет null значение, он возвращает true. Аналогично, IS NOT DISTINCT FROM идентичен = для не-null аргументов, но возвращает true, если оба аргумента имеют значение null, и false, если только один аргумент имеет значение null. Таким образом, эти предикаты эффективно действуют так, как если бы null был нормальным значением данных, а не «неизвестным».

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

expression IS NULL
expression IS NOT NULL

или эквивалентные, но нестандартные предикаты:

expression ISNULL
expression NOTNULL

Не пишите expression = NULL, потому что NULL "не равно" NULL. (NULL значение представляет неизвестное значение, и неизвестно, равны ли два неизвестных значения).

Заметка
Некоторые приложения могут ожидать, что expression = NULL возвращает true, если expression оценивается как null значение. Настоятельно рекомендуется изменить эти приложения в соответствии со стандартом SQL. Однако, если это невозможно, доступна переменная конфигурации transform_null_equals. Если он включен, QHB преобразует предложения x = NULL в x IS NULL.

Если expression имеет строковое значение, то IS NULL равно true, если само выражение строки равно нулю или когда все поля строки имеют значение null, а IS NOT NULL равно true, если само выражение строки не равно нулю и все поля строки имеют значение ненулевой. Из-за этого поведения IS NULL и IS NOT NULL не всегда возвращают обратные результаты для выражений со значениями строк; в частности, строковое выражение, содержащее как null, так и не-null поля, вернет false для обоих тестов. В некоторых случаях может быть предпочтительнее записать row IS DISTINCT FROM NULL или row IS NOT DISTINCT FROM NULL, которая просто проверит, является ли значение строки в целом нулевым, без каких-либо дополнительных тестов для полей строки.

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

boolean_expression IS TRUE
boolean_expression IS NOT TRUE
boolean_expression IS FALSE
boolean_expression IS NOT FALSE
boolean_expression IS UNKNOWN
boolean_expression IS NOT UNKNOWN

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

Некоторые функции сравнения также доступны, как показано в таблице 3.

Таблица 3. Функции сравнения

Математические функции и операторы

Математические операторы предоставляются для многих типов QHB. Для типов без стандартных математических соглашений (например, типы даты / времени) мы описываем фактическое поведение в последующих разделах.

Таблица 4 показывает доступные математические операторы.

Таблица 4. Математические операторы

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

Таблица 5 показывает доступные математические функции. В таблице dp указывает на double precision. Многие из этих функций представлены в нескольких формах с различными типами аргументов. Если не указано иное, любая форма функции возвращает тот же тип данных, что и ее аргумент. Функции, работающие с данными double precision, в основном реализованы поверх библиотеки C/RUST хост-системы; поэтому точность и поведение в граничных случаях могут варьироваться в зависимости от хост-системы.

Таблица 5. Математические функции

В таблице 6 показаны функции для генерации случайных чисел.

Таблица 6. Случайные функции

Функция random() использует простой линейный конгруэнтный алгоритм. Это быстро, но не подходит для криптографических приложений; см. модуль qhbcrypto для более безопасной альтернативы. Если вызывается setseed(), то результаты последующих вызовов random() в текущем сеансе повторяются путем повторной выдачи setseed() с тем же аргументом.

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

Таблица 7. Тригонометрические функции

Заметка
Другой способ работы с углами, измеряемыми в градусах, состоит в использовании функций преобразования единиц измерения radians() и degrees() показанных ранее. Однако использование тригонометрических функций, основанных на градусах, является предпочтительным, поскольку этот способ позволяет избежать ошибки округления в особых случаях, таких как sind(30).

Таблица 8 показывает доступные гиперболические функции. Все эти функции принимают аргументы и возвращают значения типа double precision.

Таблица 8. Гиперболические функции

Строковые функции и операторы

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

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

Таблица 9. Строковые функции и операторы SQL

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

Таблица 10. Другие строковые функции

Функции concat, concat_ws и format являются переменными, поэтому можно передавать значения для объединения или форматирования в виде массива, помеченного ключевым словом VARIADIC (см. раздел Функции SQL с переменным числом аргументов). Элементы массива обрабатываются так, как если бы они были отдельными обычными аргументами функции. Если аргумент массива variadic имеет значение NULL, concat и concat_ws возвращают NULL, но format рассматривает NULL как массив с нулевым элементом.

Смотрите также string_agg функцию string_agg в разделе Агрегатные функции.

Таблица 11. Встроенные преобразования

FORMAT

Функция FORMAT создает выходные данные, отформатированные в соответствии со строкой формата, в стиле, аналогичном функции C sprintf.

format(formatstr text [, formatarg "any" [, ...] ])

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

Спецификаторы формата вводятся символом % и имеют форму

%[position][flags][width]type

где поля компонента:

position (необязательно)

Строка вида n$ где n - индекс аргумента для печати. Индекс 1 означает первый аргумент после formatstr. Если position опущена, по умолчанию используется следующий аргумент в последовательности.

flags (необязательно)

Дополнительные параметры, управляющие форматированием вывода спецификатора формата. В настоящее время единственным поддерживаемым флагом является знак минус (-), который приведет к выравниванию вывода спецификатора формата. Это не имеет никакого эффекта, если поле width также не указано.

width (необязательно)

Задает минимальное количество символов, которое используется для отображения выходных данных спецификатора формата. Выходные данные дополняются слева или справа (в зависимости от флага -) с пробелами, необходимыми для заполнения ширины. Слишком маленькая ширина не приводит к усечению вывода, а просто игнорируется. Ширина может быть указана с использованием любого из следующего: положительное целое число; звездочка (\*) для использования следующего аргумента функции в качестве ширины; или строка вида * n $ для использования аргумента n й функции в качестве ширины.

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

type (обязательно)

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

• s форматирует значение аргумента как простую строку. Нулевое значение рассматривается как пустая строка.

• I рассматриваю значение аргумента как идентификатор SQL, заключая его в двойные кавычки, если это необходимо. Ошибка в значении, quote_ident нулю (эквиваленту quote_ident).

• L указывает значение аргумента как литерал SQL. Нулевое значение отображается в виде строки NULL, без кавычек (эквивалент quote_nullable).

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

Вот несколько примеров преобразования основных форматов:

SELECT format('Hello% s', 'World');
Result: Hello World

SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');
Result: Testing one, two, three, %

SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');
Result: INSERT INTO "Foo bar" VALUES('O''Reilly')

SELECT format('INSERT INTO %I VALUES(%L)', 'locations', 'C:\Program Files');
Result: INSERT INTO locations VALUES('C:\Program Files')

Вот примеры использования полей width и флага -:

SELECT format('|%10s|', 'foo');
Result: |       foo|

SELECT format('|%-10s|', 'foo');
Result: |foo       |

SELECT format('|%*s|', 10, 'foo');
Result: |       foo|

SELECT format('|%*s|', -10, 'foo');
Result: |foo       |

SELECT format('|%-*s|', 10, 'foo');
Result: |foo       |

SELECT format('|%-*s|', -10, 'foo');
Result: |foo       |

Эти примеры показывают использование полей position:

SELECT format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three');
Result: Testing three, two, one

SELECT format('|%*2$s|', 'foo', 10, 'bar');
Result: |       bar|

SELECT format('|%1$*2$s|', 'foo', 10, 'bar');
Result: |       foo|

В отличие от стандартной функции C sprintf, функция форматирования QHB позволяет смешивать спецификаторы формата с полями position и без них в одной строке формата. Спецификатор формата без поля position всегда использует следующий аргумент после последнего использованного аргумента. Кроме того, функция format не требует использования всех аргументов функции в строке формата. Например:

SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
Result: Testing three, two, three

Спецификаторы формата %I и %L особенно полезны для безопасного построения динамических операторов SQL. См. Пример 42.1.

Двоичные строковые функции и операторы

В этом разделе описываются функции и операторы для изучения и манипулирования значениями типа bytea.

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

Заметка
В примере результатов, показанных на этой странице, предполагается, что параметр сервера bytea_output установлен на escape (традиционный формат QHB).

Таблица 12. Двоичные строковые функции и операторы SQL

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

Таблица 13. Другие бинарные строковые функции

get_byte и set_byte нумеруют первый байт двоичной строки как байт 0. get_bit и set_bit нумеруют биты справа внутри каждого байта; например, бит 0 является младшим битом первого байта, а бит 15 является старшим битом второго байта.

Обратите внимание, что по историческим причинам функция md5 возвращает шестнадцатеричное значение типа text тогда как функции SHA-2 возвращают тип bytea. Используйте функции encode и decode для преобразования между ними, например, encode(sha256(’abc’), ’hex’) чтобы получить шестнадцатеричное текстовое представление.

См. Также агрегатную функцию string_agg в разделе Агрегатные функции и функции больших объектов в разделе Серверные функции.

Функции и операторы битовых строк

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

Таблица 14. Операторы битовых строк

Следующие стандартные функции SQL работают как с битовыми строками, так и с символьными строками: length, bit_length, octet_length, position, substring, overlay.

Следующие функции работают как с битовыми строками, так и с двоичными строками: get_bit, set_bit. При работе с битовой строкой эти функции нумеруют первый (самый левый) бит строки как бит 0.

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

44::bit(10)                    0000101100
44::bit(3)                     100
cast(-44 as bit(12))           111111010100
'1110'::bit(4)::integer        14

Обратите внимание, что приведение к bit означает приведение к bit(1) и, следовательно, выдаст только младший значащий бит целого числа.

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

Сопоставление с образцом

QHB предлагает три отдельных подхода к сопоставлению с образцом: традиционный оператор SQL LIKE, более поздний оператор SIMILAR TO (добавлен в SQL: 1999) и регулярные выражения в стиле POSIX. Помимо основного «эта строка соответствует этому шаблону?» Операторы, функции доступны для извлечения или замены соответствующих подстрок и для разделения строки в соответствующих местах.

Заметка
Если у вас есть потребности в сопоставлении с образцом, которые выходят за рамки этого, подумайте о написании пользовательской функции на Perl или Tcl.

Предостережение
Хотя большинство поисков по регулярным выражениям могут выполняться очень быстро, могут быть изобретены регулярные выражения, которые занимают произвольное количество времени и памяти для обработки. Остерегайтесь принятия шаблонов поиска регулярных выражений из неблагоприятных источников. Если вы должны сделать это, желательно установить тайм-аут заявления. Поиск с использованием шаблонов SIMILAR TO имеет те же угрозы безопасности, поскольку SIMILAR TO предоставляет многие из тех же возможностей, что и регулярные выражения в стиле POSIX. Поиски типа LIKE, которые намного проще двух других вариантов, безопаснее использовать с возможно неблагоприятными источниками паттернов.

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

LIKE

string LIKE pattern [ESCAPE escape-character]
  string NOT LIKE pattern [ESCAPE escape-character]

Выражение LIKE возвращает true, если string соответствует предоставленному pattern. (Как и ожидалось, выражение NOT LIKE возвращает false, если LIKE возвращает true, и наоборот. Эквивалентное выражение NOT (string LIKE pattern).)

Если pattern не содержит знаков процента или подчеркивания, то шаблон представляет только саму строку; в этом случае LIKE действует как оператор равенства. Подчеркивание (_) в pattern обозначает (соответствует) любой отдельный символ; знак процента (%) соответствует любой последовательности из нуля или более символов.

Несколько примеров:

'abc' LIKE 'abc'    true
'abc' LIKE 'a%'     true
'abc' LIKE '_b_'    true
'abc' LIKE 'c'      false

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

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

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

Также можно выбрать не escape-символ, написав ESCAPE ''. Это эффективно отключает механизм выхода, что делает невозможным отключение специального значения знаков подчеркивания и процентов в шаблоне.

Ключевое слово ILIKE можно использовать вместо LIKE чтобы сопоставить регистр без учета регистра в соответствии с активной локалью. Это не в стандарте SQL, но является расширением QHB.

Оператор ~~ эквивалентен LIKE, а ~~* соответствует ILIKE. Также есть операторы !~~ и !~~* которые представляют NOT LIKE и NOT ILIKE соответственно. Все эти операторы специфичны для QHB.

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

Регулярные выражения SIMILAR TO

string SIMILAR TO pattern [ESCAPE escape-character]
  string NOT SIMILAR TO pattern [ESCAPE escape-character]

Оператор SIMILAR TO возвращает true или false в зависимости от того, соответствует ли его шаблон заданной строке. Он похож на LIKE, за исключением того, что он интерпретирует шаблон, используя определение регулярного выражения в стандарте SQL. Регулярные выражения SQL представляют собой любопытную смесь между нотацией LIKE нотацией обычного регулярного выражения.

Как и LIKE, оператор SIMILAR TO успешно выполняется, только если его шаблон соответствует всей строке; это не похоже на обычное поведение регулярных выражений, когда шаблон может соответствовать любой части строки. Также как LIKE, SIMILAR TO использует символы _ и % качестве подстановочных символов, обозначающих любой отдельный символ и любую строку соответственно (они сопоставимы с . и .* в регулярных выражениях POSIX).

В дополнение к этим возможностям, заимствованным из LIKE, SIMILAR TO поддерживает метасимволы сопоставления с образцом, заимствованные из регулярных выражений POSIX:

• | обозначает чередование (любой из двух вариантов).

• * обозначает повторение предыдущего пункта ноль или более раз.

• + обозначает повторение предыдущего пункта один или несколько раз.

• ? обозначает повторение предыдущего пункта ноль или один раз.

• {m} обозначает повторение предыдущего элемента ровно m раз.

• {m,} обозначает повторение предыдущего элемента m или более раз.

• {m,n} обозначает повторение предыдущего элемента не менее m и не более n раз.

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

• Выражение в скобках [...] определяет класс символов, как в регулярных выражениях POSIX.

Обратите внимание, что точка (.) Не является метасимволом для SIMILAR TO.

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

Несколько примеров:

'abc' SIMILAR TO 'abc'      true
'abc' SIMILAR TO 'a'        false
'abc' SIMILAR TO '%(b|d)%'  true
'abc' SIMILAR TO '(b|c)%'   false

Функция substring с тремя параметрами обеспечивает извлечение подстроки, которая соответствует шаблону регулярного выражения SQL. Функция может быть написана в соответствии с синтаксисом SQL99:

substring(string from pattern for escape-character)

или как простая функция с тремя аргументами:

substring(string, pattern, escape-character)

Как и в случае SIMILAR TO, указанный шаблон должен соответствовать всей строке данных, иначе функция завершится ошибкой и вернет значение NULL. Чтобы указать часть шаблона, для которой интересует соответствующая подстрока данных, шаблон должен содержать два вхождения escape-символа, за которым следует двойная кавычка ("). Текст, соответствующий части шаблона между этими разделителями: возвращается после успешного завершения сравнения.

Escape- символы двойные кавычки фактически делят шаблон substring на три независимых регулярных выражения; например, вертикальная черта (|) в любом из трех разделов влияет только на этот раздел. Кроме того, первое и третье из этих регулярных выражений определяются так, чтобы они соответствовали наименьшему возможному объему текста, а не самому большому, когда есть некоторая неоднозначность относительно того, какая часть строки данных соответствует какому шаблону. (На языке POSIX первое и третье регулярные выражения должны быть не жадными).

В качестве расширения стандарта SQL QHB позволяет использовать только один escape-разделитель с двойными кавычками, и в этом случае третье регулярное выражение считается пустым; или нет разделителей, и в этом случае первое и третье регулярные выражения считаются пустыми.

Несколько примеров с #" разделяющим возвращаемую строку:

substring('foobar' from '%#"o_b#"%' for '#')   oob
substring('foobar' from '#"o_b#"%' for '#')    NULL

Регулярные выражения POSIX

В таблице 15 перечислены доступные операторы для сопоставления с образцом с использованием регулярных выражений POSIX.

Таблица 15. Операторы соответствия регулярного выражения

Регулярные выражения POSIX предоставляют более мощные средства для сопоставления с образцом, чем операторы LIKE и SIMILAR TO. Многие инструменты Unix, такие как egrep, sed или awk используют язык сопоставления с шаблоном, который похож на описанный здесь.

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

Несколько примеров:

'abc' ~ 'abc'    true
'abc' ~ '^a'     true
'abc' ~ '(b|d)'  true
'abc' ~ '^(b|c)' false

Язык паттернов POSIX описан более подробно ниже.

Функция substring с двумя параметрами substring(string from pattern) обеспечивает извлечение подстроки, соответствующей шаблону регулярного выражения POSIX. Возвращает null, если совпадения нет, в противном случае та часть текста, которая соответствует шаблону. Но если шаблон содержит какие-либо круглые скобки, возвращается часть текста, которая соответствует первому подвыражению в скобках (та, чья левая скобка стоит первой). Вы можете поместить круглые скобки вокруг всего выражения, если хотите использовать внутри него круглые скобки, не вызывая этого исключения. Если вам нужны круглые скобки в шаблоне перед подвыражением, которое вы хотите извлечь, смотрите описанные ниже скобки без захвата.

Несколько примеров:

substring('foobar' from 'o.b')     oob
substring('foobar' from 'o(.)b')   o

Функция regexp_replace обеспечивает подстановку нового текста для подстрок, которые соответствуют шаблонам регулярных выражений POSIX. Он имеет синтаксис regexp_replace(source, pattern, replacement [, flags ]). source строка возвращается без изменений, если нет совпадения с pattern. Если есть совпадение, source строка возвращается с replacement строкой, заменяющей соответствующую подстроку. Строка replacement может содержать \n, где n 1 до 9, чтобы указать, что должна быть вставлена исходная подстрока, соответствующая n-му заключенному в скобки подвыражению шаблона, и она может содержать \& чтобы указать, что подстрока соответствия всему шаблону должна быть вставлена. Напишите \\ если вам нужно вставить буквальный слеш в тексте замены. Параметр flags представляет собой необязательную текстовую строку, содержащую ноль или более однобуквенных флагов, которые изменяют поведение функции. Флаг i указывает совпадение без учета регистра, а флаг g указывает замену каждой подходящей подстроки, а не только первой. Поддерживаемые флаги (хотя и не g) описаны в таблице 23.

Несколько примеров:

regexp_replace('foobarbaz', 'b..', 'X')
                                   fooXbaz
regexp_replace('foobarbaz', 'b..', 'X', 'g')
                                   fooXX
regexp_replace('foobarbaz', 'b(..)', 'X\1Y', 'g')
                                   fooXarYXazY

Функция regexp_match возвращает текстовый массив захваченных подстрок, полученных в результате первого совпадения шаблона регулярного выражения POSIX со строкой. Он имеет синтаксис regexp_match(string, pattern [, flags ]). Если совпадений нет, результат равен NULL. Если совпадение найдено, и pattern содержит вложенные выражения в скобках, то результатом является текстовый массив из одного элемента, содержащий подстроку, соответствующую всему шаблону. Если совпадение найдено, и pattern содержит вложенные выражения, заключенные в скобки, то результатом является текстовый массив, чей n-й элемент является подстрокой, соответствующей вложенному выражению в скобках n-го pattern (не считая «не захватывающих» скобок; см. ниже). для деталей). Параметр flags представляет собой необязательную текстовую строку, содержащую ноль или более однобуквенных флагов, которые изменяют поведение функции. Поддерживаемые флаги описаны в таблице 23.

Несколько примеров:

SELECT regexp_match('foobarbequebaz', 'bar.*que');
 regexp_match
--------------
 {barbeque}
(1 row)

SELECT regexp_match('foobarbequebaz', '(bar)(beque)');
 regexp_match
--------------
 {bar,beque}
(1 row)

В общем случае, когда вы просто хотите, чтобы вся совпадающая подстрока или NULL не совпадали, напишите что-то вроде

SELECT (regexp_match('foobarbequebaz', 'bar.*que'))[1];
 regexp_match
--------------
 barbeque
(1 row)

Функция regexp_matches возвращает набор текстовых массивов захваченных подстрок, полученных в результате сопоставления шаблона регулярного выражения POSIX со строкой. Он имеет тот же синтаксис, что и regexp_match. Эта функция не возвращает ни одной строки, если совпадения нет, одна строка, если есть совпадение и флаг g не задан, или N строк, если есть N совпадений и задан флаг g. Каждая возвращаемая строка является текстовым массивом, содержащим всю совпавшую подстроку или подстроки, соответствующие заключенным в скобки подвыражениям pattern, как описано выше для regexp_match. regexp_matches принимает все флаги, показанные в таблице 23, плюс флаг g который выдает команду на возврат всех совпадений, а не только первого.

Несколько примеров:

SELECT regexp_matches('foo', 'not there');
 regexp_matches
----------------
(0 rows)

SELECT regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g');
 regexp_matches
----------------
 {bar,beque}
 {bazil,barf}
(2 rows)

Функция regexp_split_to_table разбивает строку, используя шаблон регулярного выражения POSIX в качестве разделителя. Он имеет синтаксис regexp_split_to_table(string, pattern [, flags ]). Если нет совпадения с pattern, функция возвращает string. Если найдется хотя бы одно совпадение, для каждого совпадения он возвращает текст с конца последнего совпадения (или начала строки) до начала совпадения. Когда совпадений больше нет, он возвращает текст от конца последнего совпадения до конца строки. Параметр flags представляет собой необязательную текстовую строку, содержащую ноль или более однобуквенных флагов, которые изменяют поведение функции. regexp_split_to_table поддерживает флаги, описанные в таблице 23.

Функция regexp_split_to_array ведет себя так же, как и regexp_split_to_table, за исключением того, что regexp_split_to_array возвращает свой результат в виде массива text. Он имеет синтаксис regexp_split_to_array(string, pattern [, flags ]). Параметры те же, что и для regexp_split_to_table.

Несколько примеров:

SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', '\s+') AS foo;
  foo   
-------
 the    
 quick  
 brown  
 fox    
 jumps
 over   
 the    
 lazy   
 dog    
(9 rows)

SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', '\s+');
              regexp_split_to_array             
-----------------------------------------------
 {the,quick,brown,fox,jumps,over,the,lazy,dog}
(1 row)

SELECT foo FROM regexp_split_to_table('the quick brown fox', '\s*') AS foo;
 foo
-----
 t         
 h         
 e         
 q         
 u         
 i         
 c         
 k         
 b         
 r         
 o         
 w         
 n         
 f         
 o         
 x         
(16 rows)

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

Детали регулярных выражений

Регулярные выражения QHB реализованы с использованием программного пакета, написанного Генри Спенсером. Большая часть описания регулярных выражений ниже дословно скопирована из его руководства.

Регулярные выражения (RE), как определено в POSIX 1003.2, имеют две формы: расширенные RE или ERE (примерно такие же, как у egrep) и базовые RE или BRE (примерно те, что у ed). QHB поддерживает обе формы, а также реализует некоторые расширения, которые не входят в стандарт POSIX, но стали широко использоваться благодаря их доступности в таких языках программирования, как Perl и Tcl. RE, использующие эти расширения, отличные от POSIX, в данной документации называются расширенными RE или ARE. ARE являются почти точным набором ERE, но BRE имеют несколько обозначений несовместимости (а также гораздо более ограничены). Сначала мы опишем формы ARE и ERE, отметив функции, которые применяются только к ARE, а затем опишем, как отличаются BRE.

Заметка
QHB всегда изначально предполагает, что регулярное выражение следует правилам ARE. Однако более ограниченные правила ERE или BRE можно выбрать, добавив встроенную опцию к шаблону RE, как описано в разделе Метасинтаксис регулярных выражений. Это может быть полезно для совместимости с приложениями, которые ожидают в точности правил POSIX 1003.2.

Регулярное выражение определяется как одна или несколько ветвей, разделенных знаком |. Это соответствует всему, что соответствует одной из ветвей.

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

Количественный атом — это атом, за которым может следовать один квантификатор. Без квантификатора он соответствует совпадению для атома. С квантификатором он может соответствовать некоторому количеству совпадений атома. Атом может быть любой из возможностей, показанных в таблице 16. Возможные квантификаторы и их значения приведены в таблице 8.17.

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

Таблица 16. Атомы регулярного выражения

RE не может заканчиваться обратной косой чертой (\).

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

Таблица 17. Квантификаторы регулярных выражений

Формы, использующие {...}, называются границами. Числа m и n пределах являются десятичными целыми числами без знака с допустимыми значениями от 0 до 255 включительно.

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

Квантификатор не может сразу следовать за другим квантификатором, например, ** является недействительным. Квантор не может начинать выражение или подвыражение или следовать за ^ или |.

Таблица 18. Ограничения регулярного выражения

Ограничения Lookahead и Lookbehind не могут содержать обратных ссылок (см. раздел Экранирование в регулярных выражениях), и все скобки в них считаются не захватывающими.

Выражения в скобках

Выражение в скобках — это список символов, заключенных в []. Обычно он соответствует любому отдельному символу из списка (но см. ниже). Если список начинается с ^, он соответствует любому отдельному символу, не относящемуся к остальной части списка. Если два символа в списке разделены знаком -, это сокращение для полного диапазона символов между этими двумя (включительно) в последовательности сортировки, например, [0-9] в ASCII соответствует любой десятичной цифре. Недопустимо, чтобы два диапазона совместно использовали конечную точку, например, a-c-e. Диапазоны очень сильно зависят от последовательности сортировки, поэтому переносимые программы не должны полагаться на них.

Чтобы включить литерал ] в список, сделайте его первым символом (после ^, если он используется). Чтобы включить литерал -, сделайте его первым или последним символом или второй конечной точкой диапазона. Чтобы использовать литерал - в качестве первой конечной точки диапазона, заключите его в [. и .] сделать его упорядочивающим элементом (см. ниже). За исключением этих символов, некоторых комбинаций с использованием [ (см. следующие абзацы) и экранировок (только для ARE), все другие специальные символы теряют свое особое значение в выражении в скобках. В частности, \ не является особенным, когда следует правилам ERE или BRE, хотя он является особенным (как введение escape) в ARE.

Внутри выражения в скобках элемент сортировки (символ, многосимвольная последовательность, которая сопоставляется, как если бы это был один символ, или имя последовательности сопоставления для любого из них), заключенный в [. и .] обозначает последовательность символов этого элемента сортировки. Последовательность рассматривается как отдельный элемент списка выражения в скобках. Это позволяет выражению в скобках, содержащему многосимвольный элемент сортировки, соответствовать более чем одному символу, например, если последовательность сортировки включает в себя элемент сортировки ch, то RE [[.ch.]]*c соответствует первым пяти символам chchcc,

Заметка
В настоящее время QHB не поддерживает многосимвольные элементы сортировки. Эта информация описывает возможное будущее поведение.

В выражении в скобках элемент сортировки, заключенный в [= и =] является классом эквивалентности, обозначающим последовательности символов всех элементов сопоставления, эквивалентных этому элементу, включая самого себя. (Если нет других эквивалентных элементов сортировки, обработка выглядит так, как если бы в качестве разделителей использовались [. и .]). Например, если o и ^ являются членами класса эквивалентности, то [[=o=]], [[=^=]] и [o^] являются синонимами. Класс эквивалентности не может быть конечной точкой диапазона.

В выражении в скобках имя класса символов, заключенное в [: и :] обозначает список всех символов, принадлежащих этому классу. Класс символов не может использоваться в качестве конечной точки диапазона. Стандарт POSIX определяет следующие имена классов символов: alnum (буквы и цифры), alpha (буквы), blank (пробел и табуляция), cntrl (символы управления), digit (цифры), graph (печатные символы, кроме пробела), lower (строчные буквы), print (печатаемые символы, включая пробел), пунктуация (пунктуация), space (любой пробел), upper (заглавные буквы) и xdigit (шестнадцатеричные цифры). Поведение этих стандартных классов символов обычно одинаково для разных платформ для символов в 7-битном наборе ASCII. То, считается ли данный не-ASCII символ принадлежащим одному из этих классов, зависит от параметров сортировки, используемых для функции или оператора регулярного выражения (см. раздел Поддержка сортировки), или по умолчанию от настройки языка LC_CTYPE базы данных (см. раздел Поддержка локали). Классификация символов, отличных от ASCII, может варьироваться в зависимости от платформы даже в локалях с одинаковыми именами. (Но языковой стандарт C никогда не считает, что любые не-ASCII-символы принадлежат какому-либо из этих классов). В дополнение к этим стандартным классам символов QHB определяет класс символов ascii, который содержит ровно 7-битный набор ASCII.

Существует два особых случая выражений в скобках: выражения в скобках [[:<:]] и [[:>:]] являются ограничениями, соответствующими пустым строкам в начале и конце слова соответственно. Слово определяется как последовательность символов слова, которая не предшествует и не сопровождается символами слова. Символ слова — это символ alnum (как определено классом символов POSIX, описанным выше) или знак подчеркивания. Это расширение, совместимое с POSIX 1003.2, но не указанное в нем, и его следует использовать с осторожностью в программном обеспечении, предназначенном для переноса на другие системы. Описанные ниже экранирующие ограничения, обычно предпочтительнее; они не являются более стандартными, но их легче набирать.

Экранирование в регулярных выражениях

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

Существуют экранирование ввода символов, чтобы упростить указание непечатаемых и других неудобных символов в RE. Они показаны в таблице 8.19.

Сокращения класса обеспечивают сокращения для некоторых обычно используемых классов символов. Они показаны в таблице 20.

Экранирование ограничения — это ограничение, соответствующее пустой строке, если выполняются определенные условия, записываемое как escape. Они показаны в таблице 21.

Обратная ссылка (\n) соответствует той же строке, которая соответствует предыдущему заключенному в скобки подвыражению, указанному числом n (см. Таблицу 8.22). Например, ([bc])\1 соответствует bb или cc но не bc или cb. Подвыражение должно полностью предшествовать обратной ссылке в RE. Субэкспрессии нумеруются в порядке их ведущих скобок. Неполные скобки не определяют подвыражения.

Таблица 19. Регулярное выражение E-Entry Escape

Шестнадцатеричные цифры 0-9, a-f и A-F Восьмеричные цифры 0-7.

Цифровая символьная запись выходит за пределы, указывая значения вне диапазона ASCII (0-127), значения которых зависят от кодировки базы данных. Когда кодировка UTF-8, escape-значения эквивалентны кодовым точкам Unicode, например, \u1234 означает символ U+1234. Для других многобайтовых кодировок экранирование ввода символов обычно просто указывает конкатенацию байтовых значений для символа. Если escape-значение не соответствует никакому допустимому символу в кодировке базы данных, ошибка не возникает, но она никогда не будет соответствовать никаким данным.

Экранирование ввода символов всегда принимается как обычные символы. Например, \135 является ] в ASCII, но \135 не завершает выражение в скобках.

Таблица 20. Класс регулярных выражений - Сокращения

В выражениях в скобках \d, \s и \w теряют свои внешние скобки, а \D, \S и \W недопустимы. (Так, например, [a-c\d] эквивалентно [a-c[:digit:]]. Кроме того, [a-c\D], что эквивалентно [a-c^[:digit:]], является недопустимым).

Таблица 21. Ограничение регулярных выражений

Слово определяется как в спецификации [[:<:]] и [[:>:]] выше. Экранирование ограничений в выражениях в скобках недопустимо.

Таблица 22. Регулярное выражение обратные ссылки

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

Метасинтаксис регулярных выражений

В дополнение к основному синтаксису, описанному выше, существуют некоторые специальные формы и различные синтаксические средства.

RE может начинаться с одного из двух специальных префиксов директора. Если RE начинается с ***:, остальная часть RE берется как ARE. (Это обычно не имеет никакого эффекта в QHB, поскольку предполагается, что RE являются ARE; но это имеет эффект, если параметром flags для функции regex был задан режим ERE или BRE). Если RE начинается с ***= остальная часть RE принимается за буквальную строку, причем все символы считаются обычными символами.

ARE может начинаться со встроенных опций: последовательность (?xyz) (где xyz - один или несколько буквенных символов) определяет опции, влияющие на остальную часть RE. Эти параметры переопределяют любые ранее определенные параметры - в частности, они могут переопределять поведение с учетом регистра, подразумеваемое оператором регулярного выражения, или параметр flags для функции регулярного выражения. Доступные буквы опций показаны в таблице 23. Обратите внимание, что эти же буквы параметров используются в параметрах флагов функций регулярных выражений.

Таблица 23. ARE символы с встроенными опциями

Встроенные параметры вступают в силу после завершения последовательности. Они могут появляться только в начале ARE (после директора ***: если таковой имеется).

В дополнение к обычному (строгому) синтаксису RE, в котором значимы все символы, существует расширенный синтаксис, доступный путем указания встроенной опции x. В расширенном синтаксисе символы пробела в RE игнорируются, как и все символы между # и следующей новой строкой (или концом RE). Это позволяет создавать абзацы и комментировать сложные RE. Из этого основного правила есть три исключения:

• символ пробела или #, которому предшествует \, сохраняется

• пробел или # в выражении в скобках сохраняется

• пробел и комментарии не могут появляться в многосимвольных обозначениях, таких как (?:

Для этой цели пробелами являются пробелы, символы табуляции, перевода строки и любые символы, принадлежащие классу пробелов.

Наконец, в ARE, вне выражений в скобках, последовательность (?#ttt) (где ttt - любой текст, не содержащий )) — это комментарий, полностью игнорируемый. Опять же, это не допускается между символами многосимвольных обозначений, например (?:. Такие комментарии являются скорее историческим артефактом, чем полезным средством, и их использование не рекомендуется; вместо этого используйте расширенный синтаксис.

Ни одно из этих расширений метасинтаксиса не доступно, если начальный директор ***= указал, что ввод пользователя будет обрабатываться как литеральная строка, а не как RE.

Правила сопоставления регулярных выражений

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

Является ли RE жадным или нет, определяется следующими правилами:

• Большинство атомов и все ограничения не имеют атрибута жадности (поскольку в любом случае они не могут соответствовать переменным объемам текста).

• Добавление скобок вокруг RE не меняет его жадности.

• Квантованный атом с квантификатором с фиксированным повторением ({m} или {m}?) Имеет такую же жадность (возможно, нет), как сам атом.

• Количественный атом с другими нормальными квантификаторами (включая {m,n} с m, равным n) является жадным (предпочитает самое длинное совпадение).

• Количественный атом с не жадным квантификатором (включая {m, n}? С m, равным n) не является жадным (предпочитает кратчайшее совпадение).

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

• RE, состоящее из двух или более ветвей, соединенных оператор всегда жадный.

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

Пример того, что это значит:

SELECT SUBSTRING('XY1234Z', 'Y*([0-9]{1,3})');
Result: 123
SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
Result: 1

В первом случае RE в целом жадный, потому что Y* жадный. Он может совпадать, начиная с Y, и соответствовать самой длинной строке, начинающейся там, то есть Y123. Выводом является часть этого в скобках, или 123. Во втором случае RE в целом не является жадным, потому что Y*? не жадный Он может совпадать, начиная с Y, и соответствовать самой короткой строке, начинающейся там, то есть Y1. Подвыражение [0-9]{1,3} является жадным, но не может изменить решение относительно общей длины совпадения; поэтому он вынужден совпадать только с 1.

Вкратце, когда RE содержит как жадные, так и не жадные подвыражения, общая длина совпадения либо максимально длинная, либо максимально короткая, в соответствии с атрибутом, назначенным всему RE. Атрибуты, назначенные подвыражениям, влияют только на то, сколько из этого соответствия им разрешено «съесть» относительно друг друга.

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

SELECT regexp_match('abc01234xyz', '(.*)(\d+)(.*)');
Result: {abc0123,4,xyz}

Это не сработало: первый .* Жадный, поэтому он «съедает» столько, сколько может, оставляя \d+ совпадать с последним возможным местом, последней цифрой. Мы можем попытаться исправить это, сделав его не жадным:

SELECT regexp_match('abc01234xyz', '(.*?)(\d+)(.*)');
Result: {abc,0,""}

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

SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
Result: {abc,01234,xyz}

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

При принятии решения о том, какое совпадение длиннее или короче, длины совпадений измеряются в символах, а не в элементах сортировки. Пустая строка считается более длинной, чем не совпадающей. Например: bb* соответствует трем средним символам abbbc; (week|wee) (night|knights) соответствует всем десяти символам weeknights; когда (.*).* сопоставляется с abc, подвыражение в скобках соответствует всем трем символам; и когда (a*)* сопоставляется с bc, и все RE, и подвыражение в скобках соответствуют пустой строке.

Если задано независимое от регистра совпадение, эффект будет таким же, как если бы все алфавитные различия исчезли из алфавита. Когда алфавит, существующий во многих случаях, появляется как обычный символ вне выражения в скобках, он фактически преобразуется в выражение в скобках, содержащее оба случая, например, x становится [xX]. Когда оно появляется внутри выражения в скобках, все его аналоги в регистре добавляются в выражение в скобках, например, [x] становится [xX] и [^x] становится [^xX].

Если указано совпадение с учетом новой строки, . и выражения в скобках, использующие ^, никогда не будут совпадать с символом новой строки (так что совпадения никогда не будут пересекать символы новой строки, если RE явно не упорядочит его), а ^ и $ будут совпадать с пустой строкой после и до новой строки соответственно, в дополнение к совпадению в начале и в конце строки соответственно. Но экранированные символы ARE \A и \Z продолжают соответствовать только началу или концу строки.

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

Если указано обратное частичное совпадение с учетом новой строки, это влияет на ^ и $, как и при сопоставлении с учетом новой строки, но не влияет . и выражения в скобках. Это не очень полезно, но предусмотрено для симметрии.

Пределы и совместимость

В этой реализации нет конкретных ограничений на длину RE. Однако программы, предназначенные для обеспечения высокой переносимости, не должны использовать RE длиной более 256 байт, поскольку реализация, совместимая с POSIX, может отказаться принимать такие RE.

Единственная особенность ARE, которая на самом деле несовместима с POSIX ERE, заключается в том, что \ не теряет своего особого значения в выражениях в скобках. Все остальные функции ARE используют синтаксис, который является недопустимым или имеет неопределенные или неопределенные эффекты в ERE POSIX; *** синтаксис директоров также находится за пределами синтаксиса POSIX как для BRE, так и для ERE.

Многие из расширений ARE заимствованы из Perl, но некоторые были изменены, чтобы очистить их, а некоторые расширения Perl отсутствуют. Заметные несовместимости включают в себя \b, \B, отсутствие специальной обработки для завершающего символа новой строки, добавление дополненных выражений в скобках к тем вещам, на которые влияет сопоставление с учетом новой строки, ограничения на круглые скобки и обратные ссылки в ограничениях lookahead/lookbehind, и семантика соответствия самого длинного/самого короткого совпадения (а не первого совпадения).

Основные регулярные выражения

BRE отличаются от ERE в нескольких отношениях. В BREs, |, + и ? являются обычными символами, и их функциональность не имеет аналогов. Ограничителями для границ являются \{ и \}, причем { и } сами по себе являются обычными символами. Круглые скобки для вложенных подвыражений: \( и \), с ( и ) сами по себе обычными символами. ^ - обычный символ, за исключением начала RE или начала заключенного в скобки подвыражения, $ - обычный символ, за исключением конца RE или конца заключенного в скобки подвыражения, а * - обычный символ, если он появляется в начале RE или начале заключенного в скобки подвыражения (после возможного начала ^). Наконец, доступны однозначные обратные ссылки, и \< и \> являются синонимами для [[:<:]] и [[:>:]] соответственно; в BRE нет других выходов.

Отличия от XQuery (LIKE_REGEX)

Начиная с SQL: 2008, стандарт SQL включает в себя оператор LIKE_REGEX, который выполняет сопоставление с шаблоном в соответствии со стандартом регулярных выражений XQuery. QHB еще не реализует этот оператор, но вы можете получить очень похожее поведение, используя функцию regexp_match(), поскольку регулярные выражения XQuery довольно близки к синтаксису ARE, описанному выше.

Существенные различия между существующей функцией регулярных выражений на основе POSIX и регулярными выражениями XQuery включают:

• Вычитание класса символов XQuery не поддерживается. Примером этой функции является использование следующего для соответствия только английским согласным: [a-z-[aeiou]].

• Сокращения класса символов XQuery \c, \C, \i и \I не поддерживаются.

• Элементы класса символов XQuery, использующие \p{UnicodeProperty} или обратное \P{UnicodeProperty}, не поддерживаются.

• POSIX интерпретирует классы символов, такие как \w (см. таблицу 20), в соответствии с преобладающим языковым стандартом (которым вы можете управлять, прикрепляя предложение COLLATE к оператору или функции). XQuery определяет эти классы с помощью ссылок на свойства символов Unicode, поэтому эквивалентное поведение получается только с языком, который следует правилам Unicode.

• Стандарт SQL (не сам XQuery) пытается обслуживать больше вариантов «новой строки», чем POSIX. Описанные выше варианты соответствия с учетом новой строки рассматривают только ASCII NL (\n) как новую строку, но SQL заставил бы нас рассматривать CR (\r), CRLF (\r\n) (новую строку в стиле Windows) и некоторые cимволы только для Unicode, такие как LINE SEPARATOR (U + 2028), а также перевод строки. Примечательно, что . и \s должен считаться \r\n как один символ, а не два в соответствии с SQL.

• Из экранирования ввода символов, описанного в таблице 19, XQuery поддерживает только \n, \r и \t.

• XQuery не поддерживает синтаксис [:name:] для классов символов в выражениях в скобках.

• XQuery не имеет ограничений на просмотр или просмотр назад, а также не выходит за пределы ограничений, описанных в таблице 21.

• Метасинтаксические формы, описанные в разделе Метасинтаксис регулярных выражений, не существуют в XQuery.

• Буквы флагов регулярного выражения, определенные в XQuery, относятся к буквам опций для POSIX, но не совпадают с ними (Таблица 23). В то время как параметры i и q ведут себя одинаково, другие не делают:

• Флаги XQuery (разрешить точку для совпадения с новой строкой) и m (разрешить совпадение ^ и $ для совпадения на новой строке) обеспечивают доступ к тем же режимам поведения, что и флаги POSIX n, p и w, но они не соответствуют поведению флагов POSIX s и m, В частности, обратите внимание, что точка-совпадение-новая строка является поведением по умолчанию в POSIX, но не в XQuery.

• Флаг XQuery x (игнорировать пробелы в шаблоне) заметно отличается от флага расширенного режима POSIX. Флаг x POSIX также позволяет # начинать комментарий в шаблоне, и POSIX не будет игнорировать символ пробела после обратной косой черты.

Функции форматирования типов данных

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

Таблица 24. Функции форматирования

Заметка
Существует также функция to_timestamp с одним аргументом; см. таблицу 31.

Совет
Существуют to_timestamp и to_date для обработки входных форматов, которые нельзя преобразовать простым преобразованием. Для большинства стандартных форматов даты/времени работает простое приведение исходной строки к требуемому типу данных, и это намного проще. Аналогично, to_number не требуется для стандартных числовых представлений.

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

В таблице 25 показаны лекала шаблонов, доступные для форматирования значений даты и времени.

Таблица 25. Лекала шаблонов для форматирования даты / времени

Модификаторы можно применять к любому лекалу шаблона, чтобы изменить его поведение. Например, FMMonth — это шаблон Month с модификатором FM. В таблице 26 показаны шаблоны модификаторов для форматирования даты/времени.

Таблица 26. Модификаторы лекала шаблонов для форматирования даты / времени

Замечания по использованию для форматирования даты / времени:

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

• TM не включает в себя конечные заготовки. to_timestamp и to_date игнорируют модификатор TM .

• • to_timestamp* и to_date пропускают несколько пробелов в начале строки ввода и вокруг значений даты и времени, если не используется опция FX. Например, to_timestamp(’ 2000 JUN’, ’YYYY MON’) и to_timestamp(’2000 - JUN’, ’YYYY-MON’) работают, но to_timestamp('2000 JUN', 'FXYYYY MON') возвращает ошибку, поскольку to_timestamp ожидает только один пробел. FX должен быть указан как первый элемент в шаблоне.

• Разделитель (пробел, или не буквенный / нецифровый символ) в строке шаблона to_timestamp и to_date соответствует любому отдельному разделителю во входной строке или пропускается, если не используется опция FX. Например, to_timestamp('2000JUN', 'YYYY///MON') и to_timestamp('2000/JUN', 'YYYY MON') работают, но to_timestamp('2000//JUN', 'YYYY/MON') возвращает ошибка, так как количество разделителей во входной строке превышает количество разделителей в шаблоне.

Если указан параметр FX, то разделитель в строке шаблона соответствует ровно одному символу во входной строке. Но обратите внимание, что символ входной строки не обязательно должен совпадать с разделителем из строки шаблона. Например, to_timestamp('2000/JUN', 'FXYYYY MON') работает, но to_timestamp('2000/JUN', 'FXYYYY MON') возвращает ошибку, поскольку второй пробел в строке шаблона занимает букву J из строки ввода.

• Шаблон TZH может соответствовать номеру со знаком. Без опции FX знаки минус могут быть неоднозначными и могут интерпретироваться как разделитель. Эта неоднозначность разрешается следующим образом: если число разделителей перед TZH в строке шаблона меньше, чем число разделителей перед знаком минус во входной строке, знак минус интерпретируется как часть TZH. В противном случае знак минус считается разделителем между значениями. Например, to_timestamp('2000 -10', 'YYYY TZH') соответствует -10 для TZH, но to_timestamp('2000 -10', 'YYYY TZH') соответствует 10 для TZH.

• Обычный текст разрешен в шаблонах to_char и будет выводиться буквально. Вы можете поместить подстроку в двойные кавычки, чтобы она интерпретировалась как буквальный текст, даже если она содержит шаблоны. Например, в '"Hello Year "YYYY', YYYY будет заменен данными года, а одиночный Y в Year не будет. В to_date, to_number и to_timestamp буквенный текст и строки в двойных кавычках приводят к пропуску количества символов, содержащихся в строке; например, "XX" пропускает два входных символа (независимо от того, являются ли они XX).

• Если вы хотите, чтобы в выводе была двойная кавычка, вы должны поставить перед ней обратную косую черту, например '\"YYYY Month\"'. Обратные слеши не являются чем-то особенным вне строк в двойных кавычках. Внутри строки в двойных кавычках обратный слеш заставляет следующий символ восприниматься буквально, каким бы он ни был (но это не имеет особого эффекта, если следующий символ не является двойной кавычкой или другим обратным слешем).

• В to_timestamp и to_date, если спецификация формата года меньше четырех цифр, например, YYY, а введенный год меньше четырех цифр, год будет скорректирован так, чтобы быть ближайшим к 2020 году, например, 95 станет 1995 годом.

• В to_timestamp и to_date преобразование YYYY имеет ограничение при обработке лет с более чем 4 цифрами. Вы должны использовать какой-либо нецифровый символ или шаблон после YYYY, в противном случае год всегда интерпретируется как 4 цифры. Например (с 20000 годом): to_date('200001131', 'YYYYMMDD') будет интерпретироваться как год с 4 цифрами; вместо этого используйте разделитель без цифр после года, например to_date('20000-1131', 'YYYY-MMDD') или to_date('20000Nov31', 'YYYYMonDD').

• В to_timestamp и to_date поле CC (столетие) принимается, но игнорируется, если есть поле YYY, YYYY или Y,YYY. Если CC используется с YY или Y то результат вычисляется как этот год в указанном столетии. Если указан век, а год - нет, то подразумевается первый год столетия.

• В to_timestamp и to_date имена или номера дней недели (DAY, D и связанные типы полей) принимаются, но игнорируются для целей вычисления результата. То же самое верно для полей квартала (Q).

• В to_timestamp и to_date дату нумерации недели ISO 8601 (в отличие от григорианской даты) можно указать одним из двух способов:

• Год, номер недели и день недели: например, to_date('2006-42-4', 'IYYY-IW-ID') возвращает дату 2006-10-19. Если вы опускаете день недели, он считается равным 1 (понедельник).

• Год и день года: например, to_date(’2006-291’, ’IYYY-IDDD’) также возвращает 2006-10-19.
  Попытка ввести дату, используя сочетание полей нумерации недели ISO 8601 и полей григорианской даты, не имеет смысла и приведет к ошибке. В контексте года нумерации по ISO 8601 понятие «месяц» или «день месяца» не имеет смысла. В контексте григорианского года неделя ИСО не имеет смысла.

Предостережение
В то время как to_date будет отклонять смесь полей даты нумерации недели по григорианскому стандарту и ISO, to_char не будет, поскольку могут быть полезны спецификации выходного формата, такие как YYYY-MM-DD (IYYY-IDDD). Но избегайте писать что-то вроде IYYY-MM-DD; это дало бы удивительные результаты в начале года. (См. раздел EXTRACT, date_part для получения дополнительной информации).

• В to_timestamp, миллисекунды (MS) или микросекунды (US) используются как цифры секунд после десятичной точки. Например, to_timestamp('12.3', 'SS.MS') составляет не 3 миллисекунды, а 300, потому что преобразование обрабатывает его как 12 + 0,3 секунды. Таким образом, для формата SS.MS входные значения 12.3, 12.30 и 12.300 указывают одинаковое количество миллисекунд. Чтобы получить три миллисекунды, нужно записать 12.003, что для преобразования означает 12 + 0,003 = 12,003 секунды.
  Вот более сложный пример: to_timestamp('15:12:02.020.001230', 'HH24:MI:SS.MS.US') составляет 15 часов, 12 минут и 2 секунды + 20 миллисекунд + 1230 микросекунд = 2,021230 секунд,

• Нумерация дня недели в to_char(..., ’ID’) соответствует функции extract(isodow from ...), а в to_char(..., ’D’) - не соответствует extract(dow from ...) день нумерации.

• to_char(interval) форматирует HH и HH12 как показано на 12-часовых часах, например, ноль часов и 36 часов выводят как 12, в то время как HH24 выводит значение полного часа, которое может превышать 23 в значении interval.

В таблице 27 показаны лекала шаблонов, доступные для форматирования числовых значений.

Таблица 27. Лекала шаблонов для числового форматирования

Замечания по использованию для числового форматирования:

• 0 указывает позицию цифры, которая будет всегда печататься, даже если она содержит начальный / конечный ноль. 9 также указывает позицию цифры, но если это ведущий ноль, то он будет заменен пробелом, а если это конечный ноль и указан режим заполнения, то он будет удален. (Для to_number() эти два символа шаблона эквивалентны).

• Шаблонные символы S, L, D и G представляют знак, символ валюты, десятичную точку и символы разделителя тысяч, определенные текущей локалью (см. Lc_monetary и lc_numeric). Шаблон символов точки и запятая представляют эти точные символы со значениями десятичной точки и разделителя тысяч независимо от локали.

• Если в to_char() явного положения не предусмотрено, для знака будет зарезервирован один столбец, и он будет привязан (отображается слева от номера). Если S появляется слева от некоторых из 9, он также будет привязан к числу.

• Знак, отформатированный с использованием SG, PL или MI, не привязан к номеру; например, to_char(-12, 'MI9999') выдает '- 12' to_char(-12, 'S9999') выдает ' -12'. (Реализация Oracle не позволяет использовать MI до 9, а требует, чтобы 9 предшествовал MI).

• TH не преобразует значения меньше нуля и не преобразует дробные числа.

• PL, SG и TH являются расширениями QHB.

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

• V с to_char умножает входные значения на 10^n, где n - количество цифр после V. V с to_number делится аналогичным образом. to_char и to_number не поддерживают использование V сочетании с десятичной точкой (например, 99.9V99 не допускается).

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

Некоторые модификаторы могут быть применены к любому лекалу шаблона, чтобы изменить его поведение. Например, FM99.99 — это шаблон 99.99 с модификатором FM. В таблице 28 показаны шаблоны модификаторов для числового форматирования.

Таблица 28. Модификаторы шаблонов для числового форматирования

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

Таблица 29. Примеры to_char

Функции и операторы даты/времени

В таблице 31 показаны доступные функции для обработки значения даты/времени, подробности представлены в следующих подразделах. Таблица 8.30 иллюстрирует поведение основных арифметических операторов (+, * и т.д.). Функции форматирования см. в разделе Функции форматирования типов данных. Вы должны быть знакомы с исходной информацией о типах данных даты/времени из раздела Типы даты/времени.

Все функции и операторы, описанные ниже, которые принимают входные данные time или timestamp фактически бывают двух вариантов: один, который требует time with time zone или timestamp with time zone, и один, который требует time without time zone или timestamp without time zone. Для краткости эти варианты не показаны отдельно. Кроме того, операторы + и * входят в коммутативные пары (например, и дата + целое число и целое число + дата); мы показываем только одну из каждой такой пары.

Таблица 30. Операторы даты/времени

Таблица 31. Функции даты/времени

В дополнение к этим функциям поддерживается оператор SQL OVERLAPS:

(start1, end1) OVERLAPS (start2, end2)
(start1, length1) OVERLAPS (start2, length2)

Это выражение дает истину, когда два периода времени (определенные их конечными точками) перекрываются, ложь, когда они не перекрываются. Конечные точки могут быть указаны как пары дат, времени или отметок времени; или как дата, время или отметка времени с последующим интервалом. Когда предоставляется пара значений, сначала можно записать начало или конец; OVERLAPS автоматически принимает более раннее значение пары в качестве начала. Каждый период времени считается представляющим начало полуоткрытого интервала start <= time < end если только start и end не равны, и в этом случае он представляет этот единственный момент времени. Это означает, например, что два периода времени с общей конечной точкой не перекрываются.

SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Result: true
SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Result: false
SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS
       (DATE '2001-10-30', DATE '2001-10-31');
Result: false
SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS
       (DATE '2001-10-30', DATE '2001-10-31');
Result: true

При добавлении значения interval к (или вычитании значения interval из) значению timestamp with time zone компонент дней увеличивает или уменьшает дату timestamp with time zone на указанное количество дней. В зависимости от изменения летнего времени (когда часовой пояс сеанса установлен на часовой пояс, который распознает летнее время), это означает, что interval '1 day' не обязательно равен interval '24 hours'. Например, если для часового пояса сеанса установлено значение CST7CDT, timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' будет выводитьtimestamp with time zone '2005-04-03 12:00-06', при добавлении interval '24 hours' к тому же начальному значению, получим timestamp with time zone '2005-04-03 13:00-06', поскольку в
часовом поясе CST7CDT происходит изменение летнего времени в 2005-04-03 02:00.

Обратите внимание, что может быть неоднозначность в поле months возвращаемом по age, потому что разные месяцы имеют разное количество дней. Подход QHB использует месяц от более ранней из двух дат при расчете неполных месяцев. Например, age('2004-06-01', '2004-04-30') использует апрель, чтобы получить 1 mon 1 day, в то время как использование мая даст 1 mon 2 days потому что май имеет 31 день, а апрель - только 30,

Вычитание дат и временных меток также может быть сложным. Один концептуально простой способ выполнить вычитание - преобразовать каждое значение в количество секунд, используя EXTRACT(EPOCH FROM ...), а затем вычесть результаты; это производит количество секунд между двумя значениями. Это позволит настроить количество дней в каждом месяце, изменения часового пояса и настройки летнего времени. Вычитание значений даты или метки времени с помощью оператора « - » возвращает количество дней (24 часа) и часов / минут / секунд между значениями, выполняя те же настройки. Функция age возвращает годы, месяцы, дни и часы / минуты / секунды, выполняя вычитание от поля к полю и затем корректируя отрицательные значения поля. Следующие запросы иллюстрируют различия в этих подходах. Результаты выборки были получены с timezone = ’US/Eastern’; существует переход на летнее время между двумя использованными датами:

SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
       EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00');
Result: 10537200
SELECT (EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
        EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00'))
        / 60 / 60 / 24;
Result: 121.958333333333
SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00';
Result: 121 days 23:00:00
SELECT age(timestamptz '2013-07-01 12:00:00', timestamptz '2013-03-01 12:00:00');
Result: 4 mons

EXTRACT, date_part

EXTRACT(field FROM source)

Функция extract извлекает подполя, такие как год или час, из значений даты / времени. source должен быть выражением значения типа timestamp, time или interval. (Выражения типа date приводятся к timestamp и поэтому могут также использоваться). field - это идентификатор или строка, которая выбирает, какое поле извлечь из исходного значения. Функция extract возвращает значения типа double precision. Ниже приведены допустимые имена полей:

Century

• Век

SELECT EXTRACT(CENTURY FROM TIMESTAMP ’2000-12-16 12:21:13’);
Result: 20
SELECT EXTRACT(CENTURY FROM TIMESTAMP ’2001-02-16 20:38:40’);
Result: 21

Первый век начинается в 0001-01-01 00:00:00 нашей эры, хотя они не знали этого в то время. Это определение относится ко всем странам григорианского календаря. Нет века № 0, вы переходите от -1 века к 1 веку.

day

• Для значений timestamp - поле дня (месяца) (1 - 31); для значений interval количество дней

SELECT EXTRACT(DAY FROM TIMESTAMP ’2001-02-16 20:38:40’);
Result: 16
SELECT EXTRACT(DAY FROM INTERVAL ’40 days 1 minute’);
Result: 40

decade

– номер текущего десятилетия (поле года, разделенное на 10)

SELECT EXTRACT(DECADE FROM TIMESTAMP ’2001-02-16 20:38:40’);
Result: 200

dow

• день недели с воскресенья (0) по субботу (6)

SELECT EXTRACT(DOW FROM TIMESTAMP ’2001-02-16 20:38:40’);
Result: 5

Обратите внимание, что нумерация дней недели в extract отличается от функции to_char(..., ’D’) .

doy

• день года (1 - 365/366)

SELECT EXTRACT(DOY FROM TIMESTAMP ’2001-02-16 20:38:40’);
Result: 47

epoch

• эпоха UNIX. Для timestamp with time zone значениями timestamp with time zone - количество секунд с 1970-01-01 00:00:00 UTC (может быть отрицательным); для значений date и timestamp - количество секунд с 1970-01-01 по 00:00:00 по местному времени; для значений interval общее количество секунд в интервале

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE ’2001-02-16
20:38:40.12-08’);
Result: 982384720.12
SELECT EXTRACT(EPOCH FROM INTERVAL ’5 days 3 hours’);
Result: 442800

Вы можете преобразовать значение эпохи обратно во временную to_timestamp с помощью to_timestamp:

SELECT to_timestamp(982384720.12);
Result: 2001-02-17 04:38:40.12+00

hour

• поле часа (0 - 23)

SELECT EXTRACT(HOUR FROM TIMESTAMP ’2001-02-16 20:38:40’);
Result: 20

isodow

• день недели с понедельника (1) по воскресенье (7)

SELECT EXTRACT(ISODOW FROM TIMESTAMP ’2001-02-18 20:38:40’);
Result: 7

Идентично dow кроме воскресенья. Это соответствует дню нумерации недели по ISO 8601.

isoyear

• годовой номер недели в ISO 8601, к которому относится дата (не относится к интервалам)

  SELECT EXTRACT(ISOYEAR FROM DATE ’2006-01-01’);
  Result: 2005
  SELECT EXTRACT(ISOYEAR FROM DATE ’2006-01-02’);
  Result: 2006
  

  Каждый год нумерации ISO 8601 начинается с понедельника недели, содержащей 4 января, поэтому в начале января или конце декабря год ISO может отличаться от григорианского года. Смотрите поле week для получения дополнительной информации.

microseconds

• поле секунд, включая дробные части, умноженное на 1 000 000; обратите внимание, что это включает в себя полные секунды

  SELECT EXTRACT(MICROSECONDS FROM TIME ’17:12:28.5’);
  Result: 28500000
  

millennium

• миллениум

  SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP ’2001-02-16 20:38:40’);
  Result: 3
  

  Годы в 1900-х годах во втором тысячелетии. Третье тысячелетие началось 1 января 2001 года.

milliseconds

• поле секунд, включая дробные части, умножается на 1000. Обратите внимание, что это включает полные секунды.

  SELECT EXTRACT(MILLISECONDS FROM TIME ’17:12:28.5’);
  Result: 28500
  

minute

• поле минут (0 - 59)

  SELECT EXTRACT(MINUTE FROM TIMESTAMP ’2001-02-16 20:38:40’);
  Result: 38
  

month

• для значений timestamp - номер месяца в году (1 - 12); для interval значений число месяцев по модулю 12 (0 - 11)

  SELECT EXTRACT(MONTH FROM TIMESTAMP ’2001-02-16 20:38:40’);
  Result: 2
  SELECT EXTRACT(MONTH FROM INTERVAL ’2 years 3 months’);
  Result: 3
  SELECT EXTRACT(MONTH FROM INTERVAL ’2 years 13 months’);
  Result: 1
  

quarter

• Квартал года (1 - 4)

  SELECT EXTRACT(QUARTER FROM TIMESTAMP ’2001-02-16 20:38:40’);
  Result: 1
  

second

• поле секунд, включая дробные части (0 - 59²)

  SELECT EXTRACT(SECOND FROM TIMESTAMP ’2001-02-16 20:38:40’);
  Result: 40
  SELECT EXTRACT(SECOND FROM TIME ’17:12:28.5’);
  Result: 28.5
  

timezone

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

timezone_hour

• часовая составляющая смещения часового пояса

timezone_minute

• минутная составляющая смещения часового пояса

week

• номер недели в году по нумерации ISO 8601. По определению, недели ISO начинаются по понедельникам, а первая неделя года содержит 4 января этого года. Другими словами, первый четверг года - первая неделя этого года.

  В системе нумерации недель ИСО даты начала января могут быть частью 52-й или 53-й недели предыдущего года, а даты конца декабря - частью первой недели следующего года. Например, 2005-01-01 является частью 53-й недели 2004 года, а 2006-01-01 является частью 52-й недели 2005 года, а 2012-12-31 является частью первой недели 2013 года. Рекомендуется использовать поле isoyear вместе с week чтобы получить согласованные результаты.

  SELECT EXTRACT(WEEK FROM TIMESTAMP ’2001-02-16 20:38:40’);
  Result: 7
  

year

• поле года. Имейте в виду, что отсутствует 0 год нашей эры (AD), поэтому вычитать годы до Р. Х. (BC) из лет после Р. Х. (AD) следует с осторожностью.

  SELECT EXTRACT(YEAR FROM TIMESTAMP ’2001-02-16 20:38:40’);
  Result: 2001
  

Заметка
Когда входное значение равно +/- Бесконечность, extract возвращает +/- Бесконечность для монотонно увеличивающихся полей (epoch, julian, year, isoyear, decade, century и millennium). Для других полей возвращается NULL.

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

Функция date_part смоделирована на традиционном Ingres, эквивалентном extract стандартной SQL- функции:

date_part('field', source)

Обратите внимание, что здесь параметр field должен быть строковым значением, а не именем. Допустимые имена полей для date_part такие же, как и для extract.

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
Result: 16

SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
Result: 4

date_trunc

Функция date_trunc концептуально аналогична функции trunc для чисел.

date_trunc(field, source [, time_zone ])

source - это выражение значения типа timestamp, timestamp with time zone или interval. (Значения типа date и time автоматически timestamp в timestamp или interval, соответственно). field выбирает, с какой точностью обрезать входное значение. Возвращаемое значение также имеет тип timestamp, timestamp with time zone или interval, и оно имеет все поля, которые менее значимы, чем выбранное, установленное в ноль (или одно, для дня и месяца).

Допустимые значения для field:

microseconds
milliseconds
second
minute
hour
day
week
month
quarter
year
decade
century
millennium

Когда входное значение имеет тип timestamp with time zone, усечение выполняется относительно определенного часового пояса; например, усечение до day приводит к значению полуночи в этой зоне. По умолчанию усечение выполняется относительно текущей настройки TimeZone, но можно указать необязательный аргумент time_zone чтобы указать другой часовой пояс. Название часового пояса можно указать любым из способов, описанных в разделе Часовые пояса.

Часовой пояс не может быть указан при обработке timestamp without time zone ввода timestamp without time zone или interval.

Примеры (при условии, что местный часовой пояс - America/New_York):

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00

SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00

SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
Result: 2001-02-16 00:00:00-05

SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');
Result: 2001-02-16 08:00:00-05

SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
Result: 3 days 02:00:00

AT TIME ZONE

AT TIME ZONE преобразует временную метку без временной зоны в/из временной метки с часовым поясом и значениями времени в разные часовые пояса. Таблица 32 показывает его варианты.

Таблица 32. AT TIME ZONE Варианты

В этих выражениях желаемый часовой пояс может быть указан либо в виде текстовой строки (например, ’America/Los_Angeles’), либо в качестве интервала (например, INTERVAL ’-08:00’). В текстовом случае имя часового пояса можно указать любым из способов, описанных в разделе Часовые пояса.

Примеры (при условии, что местным часовым поясом является America/Los_Angeles):

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';
Result: 2001-02-16 19:38:40-08

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver';
Result: 2001-02-16 18:38:40

SELECT TIMESTAMP '2001-02-16 20:38:40-05' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago';
Result: 2001-02-16 05:38:40

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

Функция timezone (zone, timestamp) эквивалентна timestamp AT TIME ZONE zone соответствующей конструкции SQL, в timestamp AT TIME ZONE zone.

Текущая дата / время

QHB предоставляет ряд функций, которые возвращают значения, связанные с текущей датой и временем. Все эти стандартные функции SQL возвращают значения, основанные на времени начала текущей транзакции:

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME(precision)
CURRENT_TIMESTAMP(precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME(precision)
LOCALTIMESTAMP(precision)

CURRENT_TIME и CURRENT_TIMESTAMP доставляют значения с часовым поясом; LOCALTIME и LOCALTIMESTAMP доставляют значения без часового пояса.

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

Несколько примеров:

SELECT CURRENT_TIME;
Result: 14:39:53.662522-05

SELECT CURRENT_DATE;
Result: 2001-12-23

SELECT CURRENT_TIMESTAMP;
Result: 2001-12-23 14:39:53.662522-05

SELECT CURRENT_TIMESTAMP(2);
Result: 2001-12-23 14:39:53.66-05

SELECT LOCALTIMESTAMP;
Result: 2001-12-23 14:39:53.662522

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

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

transaction_timestamp()
statement_timestamp()
clock_timestamp()
timeofday()
now()

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

SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP ’now’; -- incorrect for use with DEFAULT

Задержка исполнения

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

pg_sleep(seconds)
pg_sleep_for(interval)
pg_sleep_until(timestamp with time zone)

pg_sleep переводит процесс текущего сеанса в спящий режим до тех пор, пока не истекут указанные секунды. seconds это значение типа double precision, поэтому можно указать задержки в долях секунды. pg_sleep_for - это удобная функция для больших времен ожидания указанных в качестве интервала. pg_sleep_until - это удобная функция, когда требуется определенное время пробуждения. Например:

SELECT pg_sleep(1.5);
SELECT pg_sleep_for('5 minutes');
SELECT pg_sleep_until('tomorrow 03:00');

Эффективное разрешение интервала ожидания зависит от платформы; 0,01 секунды — это обычное значение. Задержка сна будет по крайней мере такой же, как указано. Это может быть дольше в зависимости от таких факторов, как нагрузка на сервер. В частности, pg_sleep_until не гарантированно проснется точно в указанное время, но не проснется раньше.

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

Функции поддержки Enum

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

CREATE TYPE rainbow AS ENUM (’red’, ’orange’, ’yellow’, ’green’, ’blue’, ’purple’);

Таблица 33. Функции поддержки Enum

Обратите внимание, что за исключением формы enum_range с двумя аргументами, эти функции игнорируют определенное значение, переданное им; они заботятся только о своем объявленном типе данных. Может быть передано либо нулевое, либо определенное значение типа с тем же результатом. Как правило, эти функции применяются к столбцу таблицы или аргументу функции, а не к имени типа с проводным подключением, как показано в примерах.

Геометрические функции и операторы

Геометрические типы point, box, lseg, line, path, polygon и circle имеют большой набор собственных вспомогательных функций и операторов, показанных в таблице 34, таблице 35 и таблице 36.

Предупреждение

Обратите внимание, что оператор «тот же, что и», ~=, представляет обычное понятие равенства для типов point, box, polygon и circle. Некоторые из этих типов также имеют оператор =, но = сравнивает только для равных областей. Другие скалярные операторы сравнения (<= и т. д.) также сравнивают области для этих типов.

Таблица 34. Геометрические операторы

Таблица 35. Геометрические функции

Таблица 36. Функции преобразования геометрических типов

Можно получить доступ к двум номерам компонентов point как если бы точка была массивом с индексами 0 и 1. Например, если t.p является столбцом point то SELECT p\[0\] FROM t извлекает координату X и UPDATE t SET p[1] = ... изменяет координату Y. Таким же образом значение типа box или lseg можно рассматривать как массив из двух point значений.

Функция area работает для типов box, circle и path. Функция area работает только с типом данных path если точки в path не пересекаются. Например, path ’((0,0),(0,1),(2,1),(2,2),(1,2),(1,0),(0,0))’::PATH не будет работать; однако следующий визуально идентичный path ’((0,0),(0,1),(1,1),(1,2),(2,2),(2,1),(1,1),(1,0),(0,0))’::PATH будет работать. Если концепция пересекающегося и непересекающегося path вводит в заблуждение, нарисуйте оба вышеупомянутых path рядом на листе миллиметровки.

Функции и операторы сетевых адресов

В таблице 37 показаны операторы, доступные для типов cidr и inet. Операторы <<, <<=, >>, >>= и && проверяют наличие подсети. Они рассматривают только сетевые части двух адресов (игнорируя любую часть хоста) и определяют, является ли одна сеть идентичной или подсетью другой.

Таблица 37. cidr и inet

В таблице 38 показаны функции, доступные для использования с типам cidr и inet. Функции abbrev, host и text в первую очередь предназначены для предоставления альтернативных форматов отображения.

Таблица 38. cidr и inet

Любое значение cidr может быть приведено к inet неявно или явно; следовательно, функции, показанные выше, как работающие с inet также работают со значениями cidr. (Там, где есть отдельные функции для inet и cidr, это происходит потому, что поведение должно отличаться для двух случаев). Кроме того, разрешено приводить значение inet к cidr. Когда это будет сделано, любые биты справа от маски маски будут cidr обнулены для создания действительного значения cidr. Кроме того, вы можете преобразовать текстовое значение в inet или cidr используя обычный синтаксис приведения: например, inet(expression) или colname::cidr.

В таблице 39 показаны функции, доступные для использования с типом macaddr. Функция trunc(macaddr) возвращает MAC-адрес с последними 3 байтами, установленными в ноль. Это может быть использовано для связи оставшегося префикса с производителем.

Таблица 39. Функции macaddr

Тип macaddr также поддерживает стандартные реляционные операторы (>, <= и т. Д). Для лексикографического упорядочения и побитовые арифметические операторы (~, & и ) для NOT, AND и OR.

В таблице 40 показаны функции, доступные для использования с типом macaddr8. Функция trunc(macaddr8) возвращает MAC-адрес с последними 5 байтами, установленными в ноль. Это может быть использовано для связи оставшегося префикса с производителем.

Таблица 40. Функции macaddr8

Тип macaddr8 также поддерживает стандартные реляционные операторы (>, <= и т. д). Для упорядочения и побитовые арифметические операторы (~, & и ) для NOT, AND и OR.

Функции текстового поиска и операторы

В таблице 41, таблице 42 и таблице 43 обобщены функции и операторы, которые предоставляются для полнотекстового поиска.

Таблица 41. Операторы текстового поиска

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

В дополнение к операторам, показанным в таблице, обычные операторы сравнения B-деревьев (=, < и т.д.). Определены для типов tsvector и tsquery. Они не очень полезны для текстового поиска, но позволяют, например, создавать уникальные индексы на столбцах этих типов.

Таблица 42. Функции текстового поиска

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

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

Таблица 43. Функции отладки текстового поиска

Функции XML

Функции и функционально-подобные выражения, описанные в этом разделе, работают со значениями типа xml. См. раздел Тип XML для получения информации о типе xml. Функциональные выражения xmlparse и xmlserialize для преобразования в тип xml и из него описаны там, а не в этом разделе.

Использование большинства этих функций требует, чтобы QHB был собран с помощью configure --with-libxml.

Создание XML-контента

Набор функций и функциональных выражений доступен для создания XML-контента из данных SQL. Как таковые, они особенно подходят для форматирования результатов запросов в XML-документы для обработки в клиентских приложениях.

xmlcomment

xmlcomment(text)

Функция xmlcomment создает значение XML, содержащее комментарий XML с указанным текстом в качестве содержимого. Текст не может содержать «--» или заканчиваться на «-», поэтому полученная конструкция является допустимым комментарием XML. Если аргумент равен нулю, результат равен нулю.

Пример:

SELECT xmlcomment('hello');

  xmlcomment
--------------
 <!--hello-->

xmlconcat

xmlconcat(xml[, ...])

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

Пример:

SELECT xmlconcat('<abc/>', '<bar>foo</bar>');

      xmlconcat
----------------------
 <abc/><bar>foo</bar>

Объявления XML, если они есть, объединяются следующим образом. Если все значения аргументов имеют одно и то же объявление версии XML, эта версия используется в результате, иначе версия не используется. Если все значения аргумента имеют отдельное значение объявления «да», то это значение используется в результате. Если все значения аргумента имеют отдельное значение объявления и по крайней мере одно - «нет», то это используется в результате. В противном случае результат не будет иметь отдельной декларации. Если для результата определено, что требуется отдельное объявление, но нет объявления версии, будет использоваться объявление версии с версией 1.0, поскольку для XML требуется, чтобы объявление XML содержало объявление версии. Декларации кодирования игнорируются и удаляются во всех случаях.

Пример:

SELECT xmlconcat('<?xml version="1.1"?><foo/>', '<?xml version="1.1" standalone="no"?><bar/>');

             xmlconcat
-----------------------------------
 <?xml version="1.1"?><foo/><bar/>

xmlelement

xmlelement(name name [, xmlattributes(value [AS attname] [, ... ])] [, content, ...])

Выражение xmlelement создает элемент XML с заданным именем, атрибутами и содержимым.

Примеры:

SELECT xmlelement(name foo);

 xmlelement
------------
 <foo/>

SELECT xmlelement(name foo, xmlattributes('xyz' as bar));

    xmlelement
------------------
 <foo bar="xyz"/>

SELECT xmlelement(name foo, xmlattributes(current_date as bar), 'cont', 'ent');

             xmlelement
-------------------------------------
 <foo bar="2007-01-26">content</foo>

Имена элементов и атрибутов, которые не являются допустимыми именами XML, экранируются путем замены нарушающих символов последовательностью _x HHHH _, где HHHH - это HHHH Unicode символа в шестнадцатеричной записи. Например:

SELECT xmlelement(name "foo$bar", xmlattributes('xyz' as "a&b"));

            xmlelement
----------------------------------
 <foo_x0024_bar a_x0026_b="xyz"/>

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

CREATE TABLE test (a xml, b xml);
SELECT xmlelement(name test, xmlattributes(a, b)) FROM test;

Но это не так:

SELECT xmlelement(name test, xmlattributes('constant'), a, b) FROM test;
SELECT xmlelement(name test, xmlattributes(func(a, b))) FROM test;

Содержимое элемента, если оно указано, будет отформатировано в соответствии с его типом данных. Если сам контент имеет тип xml, можно создавать сложные XML-документы. Например:

SELECT xmlelement(name foo, xmlattributes('xyz' as bar),
                            xmlelement(name abc),
                            xmlcomment('test'),
                            xmlelement(name xyz));

                  xmlelement
----------------------------------------------
 <foo bar="xyz"><abc/><!--test--><xyz/></foo>

Содержимое других типов будет отформатировано в допустимые символьные данные XML. В частности, это означает, что символы <,> и & будут преобразованы в объекты. Двоичные данные (тип данных bytea) будут представлены в bytea base64 или hex, в зависимости от настройки параметра конфигурации xmlbinary. Ожидается, что конкретное поведение для отдельных типов данных будет развиваться, чтобы выровнять сопоставления QHB с сопоставлениями, указанными в SQL: 2006 и более поздних версия.

xmlforest

xmlforest(content [AS name] [, ...])

Выражение xmlforest создает XML-лес (последовательность) элементов с использованием заданных имен и содержимого.

Примеры:

SELECT xmlforest('abc' AS foo, 123 AS bar);

          xmlforest
------------------------------
 <foo>abc</foo><bar>123</bar>


SELECT xmlforest(table_name, column_name)
FROM information_schema.columns
WHERE table_schema = 'pg_catalog';

                                         xmlforest
-------------------------------------------------------------------------------------------
 <table_name>pg_authid</table_name><column_name>rolname</column_name>
 <table_name>pg_authid</table_name><column_name>rolsuper</column_name>
 ...

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

Имена элементов, которые не являются допустимыми именами XML, экранируются, как показано выше для xmlelement. Точно так же данные содержимого экранируются для создания допустимого содержимого XML, если оно уже не имеет тип xml.

Обратите внимание, что деревья XML не являются действительными документами XML, если они состоят из более чем одного элемента, поэтому может быть полезно обернуть выражения xmlforest в xmlelement.

xmlpi

xmlpi(name target [, content])

Выражение xmlpi создает инструкцию обработки XML. Содержимое, если оно присутствует, не должно содержать последовательность символов ?>.

Пример:

SELECT xmlpi(name php, 'echo "hello world";');

            xmlpi
-----------------------------
 <?php echo "hello world";?>

xmlroot

xmlroot(xml, version text | no value [, standalone yes|no|no value])

Выражение xmlroot изменяет свойства корневого узла значения XML. Если указана версия, она заменяет значение в объявлении версии корневого узла; если указан автономный параметр, он заменяет значение в автономном объявлении корневого узла.

SELECT xmlroot(xmlparse(document '<?xml version="1.1"?><content>abc</content>'),
               version '1.0', standalone yes);

                xmlroot
----------------------------------------
 <?xml version="1.0" standalone="yes"?>
 <content>abc</content>

xmlagg

xmlagg(xml)

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

Пример:

CREATE TABLE test (y int, x xml);
INSERT INTO test VALUES (1, '<foo>abc</foo>');
INSERT INTO test VALUES (2, '<bar/>');
SELECT xmlagg(x) FROM test;
        xmlagg
----------------------
 <foo>abc</foo><bar/>

Чтобы определить порядок объединения, в совокупный вызов может быть добавлено предложение ORDER BY как описано в разделе Агрегатные выражения. Например:

SELECT xmlagg(x ORDER BY y DESC) FROM test;
        xmlagg
----------------------
 <bar/><foo>abc</foo>

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

SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab;
        xmlagg
----------------------
 <bar/><foo>abc</foo>

Предикаты XML

Выражения, описанные в этом разделе, проверяют свойства значений xml.

IS DOCUMENT

xml IS DOCUMENT

Выражение IS DOCUMENT возвращает значение true, если значение аргумента XML является правильным документом XML, значение false, если это не так (то есть фрагмент содержимого), или значение null, если аргумент равен null. См. раздел Тип XML о разнице между документами и фрагментами контента.

IS NOT DOCUMENT

xml IS NOT DOCUMENT

Выражение IS NOT DOCUMENT возвращает значение false, если значение аргумента XML является правильным документом XML, значение true, если это не так (то есть фрагмент содержимого), или значение NULL, если аргумент равен NULL.

XMLEXISTS

XMLEXISTS(text PASSING [BY { REF | VALUE }] xml [BY { REF | VALUE }])

Функция xmlexists оценивает выражение XPath 1.0 (первый аргумент) с переданным значением XML в качестве элемента контекста. Функция возвращает false, если в результате этой оценки получен пустой набор узлов, true, если она возвращает любое другое значение. Функция возвращает ноль, если любой аргумент равен нулю. Ненулевое значение, передаваемое в качестве элемента контекста, должно быть документом XML, а не фрагментом содержимого или любым не-XML-значением.

Пример:

SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY VALUE '<towns><town>Toronto</town><town>Ottawa</town></towns>');

 xmlexists
------------
 t
(1 row)

Предложения BY REF и BY VALUE принимаются в QHB, но игнорируются. В стандарте SQL функция xmlexists оценивает выражение на языке XML Query, но QHB допускает только выражение XPath 1.0.

xml_is_well_formed

xml_is_well_formed(text)
xml_is_well_formed_document(text)
xml_is_well_formed_content(text)

Эти функции проверяют, является ли text строка правильно сформированным XML, возвращая логический результат. xml_is_well_formed_document проверяет правильно сформированный документ, а xml_is_well_formed_content проверяет правильно сформированный контент. xml_is_well_formed выполняет первое, если для параметра конфигурации xmloption задано значение DOCUMENT, или второе, если для него задано CONTENT. Это означает, что xml_is_well_formed полезен для определения успешности простого приведения к типу xml, тогда как две другие функции полезны для определения того, будут ли успешными соответствующие варианты XMLPARSE.

Примеры:

SET xmloption TO DOCUMENT;
SELECT xml_is_well_formed('<>');
 xml_is_well_formed
--------------------
 f
(1 row)

SELECT xml_is_well_formed('<abc/>');
 xml_is_well_formed
--------------------
 t
(1 row)

SET xmloption TO CONTENT;
SELECT xml_is_well_formed('abc');
 xml_is_well_formed
--------------------
 t
(1 row)

SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</pg:foo>');
 xml_is_well_formed_document
-----------------------------
 t
(1 row)

SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</my:foo>');
 xml_is_well_formed_document
-----------------------------
 f
(1 row)

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

Обработка XML

Для обработки значений типа данных xml QHB предлагает функции xpath и xpath_exists, которые оценивают выражения XPath 1.0, и XMLTABLE функцию XMLTABLE.

xpath

xpath(xpath, xml [, nsarray])

Функция xpath вычисляет выражение XPath 1.0 xpath (text значение) по отношению к XML-значению xml. Он возвращает массив XML-значений, соответствующих набору узлов, созданному выражением XPath. Если выражение XPath возвращает скалярное значение, а не набор узлов, то возвращается массив из одного элемента.

Второй аргумент должен быть правильно сформированным документом XML. В частности, он должен иметь один элемент корневого узла.

Необязательный третий аргумент функции — это массив отображений пространства имен. Этот массив должен быть двумерным text массивом с длиной второй оси, равной 2 (т.е. это должен быть массив массивов, каждый из которых состоит ровно из 2 элементов). Первый элемент каждой записи массива — это имя пространства имен (псевдоним), второй - URI пространства имен. Не требуется, чтобы псевдонимы, предоставленные в этом массиве, были такими же, как и используемые в самом документе XML (другими словами, как в документе XML, так и в контексте функции xpath, псевдонимы являются локальными).

Пример:

SELECT xpath('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>',
             ARRAY[ARRAY['my', 'http://example.com']]);

 xpath  
--------
 {test}
(1 row)

Чтобы работать с пространствами имен по умолчанию (анонимными), сделайте что-то вроде этого:

SELECT xpath('//mydefns:b/text()', '<a xmlns="http://example.com"><b>test</b></a>',
             ARRAY[ARRAY['mydefns', 'http://example.com']]);

 xpath
--------
 {test}
(1 row)

xpath_exists {#}

xpath_exists(xpath, xml [, nsarray])

Функция xpath_exists является специализированной формой функции xpath. Вместо того чтобы возвращать отдельные значения XML, которые удовлетворяют выражению XPath 1.0, эта функция возвращает логическое значение, указывающее, был ли выполнен запрос или нет (в частности, было ли получено какое-либо значение, отличное от пустого набора узлов). Эта функция эквивалентна предикату XMLEXISTS, за исключением того, что она также поддерживает аргумент сопоставления пространства имен.

Пример:

SELECT xpath_exists('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>',
                     ARRAY[ARRAY['my', 'http://example.com']]);

 xpath_exists  
--------------
 t
(1 row)

xmltable

xmltable( [XMLNAMESPACES(namespace uri AS namespace name[, ...]), ]
          row_expression PASSING [BY { REF | VALUE }] document_expression [BY { REF | VALUE }]
          COLUMNS name { type [PATH column_expression] [DEFAULT default_expression] [NOT NULL | NULL]
                        | FOR ORDINALITY }
                   [, ...]
)

Функция xmltable создает таблицу на основе заданного значения XML, фильтра XPath для извлечения строк и набора определений столбцов.

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

Обязательный аргумент row_expression — это выражение XPath 1.0, которое оценивается, передавая document_expression в качестве элемента контекста, для получения набора узлов XML. Эти узлы - то, что xmltable превращает в выходные строки. Строки не будут создаваться, если document_expression имеет значение null, а также если row_expression создает пустой набор узлов или любое значение, отличное от набора узлов.

document_expression предоставляет элемент контекста для row_expression. Это должен быть правильно сформированный XML-документ; фрагменты/леса не принимаются. Предложения BY REF и BY VALUE принимаются, но игнорируются. В стандарте SQL функция xmltable оценивает выражения на языке XML Query, но QHB допускает только выражения XPath 1.0.

Обязательное предложение COLUMNS указывает список столбцов в выходной таблице. Каждая запись описывает один столбец. Смотрите краткое описание синтаксиса для формата выше. Имя и тип столбца обязательны; предложения path, default и nullability являются необязательными.

Столбец, помеченный FOR ORDINALITY будет заполнен номерами строк, начиная с 1, в порядке узлов, полученных из результирующего набора строк row_expression. Максимум один столбец может быть помечен FOR ORDINALITY.

column_expression для столбца — это выражение XPath 1.0, которое оценивается для каждой строки, с текущим узлом из результата row_expression качестве элемента контекста, чтобы найти значение столбца. Если column_expression не указано, то имя столбца используется как неявный путь.

Если выражение XPath столбца возвращает не XML-значение (ограничено строкой, логическим или двойным в XPath 1.0) и столбец имеет тип QHB, отличный от xml, столбец будет установлен так, как если бы строковое представление значения было присвоено типу QHB. (Если значение является логическим, его строковое представление принимается равным 1 или 0 если категория типа выходного столбца является числовой, в противном случае - true или false).

Если выражение XPath столбца возвращает непустой набор узлов XML и тип столбца QHB - xml, столбцу будет точно присвоен результат выражения, если он имеет форму документа или содержимого³.

Результат не в формате XML, назначенный выходному столбцу xml создает содержимое, отдельный текстовый узел со строковым значением результата. Результат XML, назначенный столбцу любого другого типа, может содержать не более одного узла, или возникает ошибка. Если существует ровно один узел, столбец будет установлен так, как если бы он присваивал строковое значение узла (как определено для string функции XPath 1.0) типу QHB.

Строковое значение элемента XML представляет собой объединение в порядке документа всех текстовых узлов, содержащихся в этом элементе и его потомках. Строковое значение элемента без текстовых узлов-потомков является пустой строкой (не NULL). Любые атрибуты xsi:nil игнорируются. Обратите внимание, что text() только для пробелов text() узел text() между двумя нетекстовыми элементами сохраняется, а начальные пробелы в text() узле не сглаживаются. string функция XPath 1.0 может использоваться для правил, определяющих строковое значение других типов узлов XML и не-XML-значений.

Представленные здесь правила преобразования не совсем соответствуют стандарту SQL.

Если выражение пути возвращает пустой набор узлов (обычно, когда он не совпадает) для данной строки, столбцу будет присвоено NULL, если не указано default_expression; затем используется значение, полученное в результате оценки этого выражения.

Столбцы могут быть помечены как NOT NULL. Если column_expression для столбца NOT NULL ничего не соответствует и отсутствует DEFAULT или default_expression также имеет значение null, выдается сообщение об ошибке.

default_expression вместо немедленной оценки при xmltable оценивается каждый раз, когда для столбца требуется значение по умолчанию. Если выражение квалифицируется как стабильное или неизменное, повторная оценка может быть пропущена. Это означает, что вы можете с пользой использовать изменчивые функции, такие как nextval в default_expression.

Примеры:

CREATE TABLE xmldata AS SELECT
xml $$
<ROWS>
  <ROW id="1">
    <COUNTRY_ID>AU</COUNTRY_ID>
    <COUNTRY_NAME>Australia</COUNTRY_NAME>
  </ROW>
  <ROW id="5">
    <COUNTRY_ID>JP</COUNTRY_ID>
    <COUNTRY_NAME>Japan</COUNTRY_NAME>
    <PREMIER_NAME>Shinzo Abe</PREMIER_NAME>
    <SIZE unit="sq_mi">145935</SIZE>
  </ROW>
  <ROW id="6">
    <COUNTRY_ID>SG</COUNTRY_ID>
    <COUNTRY_NAME>Singapore</COUNTRY_NAME>
    <SIZE unit="sq_km">697</SIZE>
  </ROW>
</ROWS>
$$ AS data;

SELECT xmltable.*
  FROM xmldata,
       XMLTABLE('//ROWS/ROW'
                PASSING data
                COLUMNS id int PATH '@id',
                        ordinality FOR ORDINALITY,
                        "COUNTRY_NAME" text,
                        country_id text PATH 'COUNTRY_ID',
                        size_sq_km float PATH 'SIZE[@unit = "sq_km"]',
                        size_other text PATH
                             'concat(SIZE[@unit!="sq_km"], " ", SIZE[@unit!="sq_km"]/@unit)',
                        premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified') ;

 id | ordinality | COUNTRY_NAME | country_id | size_sq_km |  size_other  | premier_name  
----+------------+--------------+------------+------------+--------------+---------------
  1 |          1 | Australia    | AU         |            |              | not specified
  5 |          2 | Japan        | JP         |            | 145935 sq_mi | Shinzo Abe
  6 |          3 | Singapore    | SG         |        697 |              | not specified

В следующем примере показано объединение нескольких узлов text (), использование имени столбца в качестве фильтра XPath, а также обработка пробелов, комментариев XML и инструкций по обработке:

REATE TABLE xmlelements AS SELECT
xml $$
  <root>
   <element>  Hello<!-- xyxxz -->2a2<?aaaaa?> <!--x-->  bbb<x>xxx</x>CC  </element>
  </root>
$$ AS data;

SELECT xmltable.*
  FROM xmlelements, XMLTABLE('/root' PASSING data COLUMNS element text);
         element         
-------------------------
   Hello2a2   bbbxxxCC  

В следующем примере показано, как можно использовать предложение XMLNAMESPACES для указания списка пространств имен, используемых в документе XML, а также в выражениях XPath:

WITH xmldata(data) AS (VALUES ('
<example xmlns="http://example.com/myns" xmlns:B="http://example.com/b">
 <item foo="1" B:bar="2"/>
 <item foo="3" B:bar="4"/>
 <item foo="4" B:bar="5"/>
</example>'::xml)
)
SELECT xmltable.*
  FROM XMLTABLE(XMLNAMESPACES('http://example.com/myns' AS x,
                              'http://example.com/b' AS "B"),
             '/x:example/x:item'
                PASSING (SELECT data FROM xmldata)
                COLUMNS foo int PATH '@foo',
                  bar int PATH '@B:bar');
 foo | bar
-----+-----
   1 |   2
   3 |   4
   4 |   5
(3 rows)

Отображение таблиц в XML

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

table_to_xml(tbl regclass, nulls boolean, tableforest boolean, targetns text)
query_to_xml(query text, nulls boolean, tableforest boolean, targetns text)
cursor_to_xml(cursor refcursor, count int, nulls boolean,
              tableforest boolean, targetns text)

Тип ответа каждой функции - xml.

table_to_xml отображает содержимое именованной таблицы, передаваемой как параметр tbl. Тип regclass принимает строки, идентифицирующие таблицы, используя обычные обозначения, включая необязательные квалификации схемы и двойные кавычки. query_to_xml выполняет запрос, текст которого передается как query параметра, и отображает набор результатов. cursor_to_xml извлекает указанное количество строк из курсора, указанного параметром cursor. Этот вариант рекомендуется, если необходимо сопоставить большие таблицы, поскольку значение результата создается в памяти каждой функцией.

Если tableforest имеет значение false, итоговый XML-документ выглядит следующим образом:

<tablename>
  <row>
    <columnname1>data</columnname1>
    <columnname2>data</columnname2>
  </row>

  <row>
    ...
  </row>

  ...
</tablename>

Если tableforest имеет значение true, результатом является фрагмент содержимого XML, который выглядит следующим образом:

<tablename>
  <columnname1>data</columnname1>
  <columnname2>data</columnname2>
</tablename>

<tablename>
  ...
</tablename>

...

Если имя таблицы недоступно, то есть при отображении запроса или курсора table строк используется в первом формате, row во втором.

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

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

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

<columnname xsi:nil="true"/>

где xsi - префикс пространства имен XML для экземпляра схемы XML. Соответствующее объявление пространства имен будет добавлено к значению результата. Если false, столбцы, содержащие нулевые значения, просто не включаются в вывод.

Параметр targetns указывает желаемое пространство имен XML для результата. Если конкретное пространство имен не требуется, должна быть передана пустая строка.

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

table_to_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns text)
query_to_xmlschema(query text, nulls boolean, tableforest boolean, targetns text)
cursor_to_xmlschema(cursor refcursor, nulls boolean, tableforest boolean, targetns text)

Важно, чтобы одни и те же параметры были переданы для получения соответствующих сопоставлений данных XML и документов схемы XML.

Следующие функции создают сопоставления данных XML и соответствующую схему XML в одном документе (или лесу), связанных вместе. Они могут быть полезны, когда требуются автономные и самоописываемые результаты:

table_to_xml_and_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns text)
query_to_xml_and_xmlschema(query text, nulls boolean, tableforest boolean, targetns text)

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

schema_to_xml(schema name, nulls boolean, tableforest boolean, targetns text)
schema_to_xmlschema(schema name, nulls boolean, tableforest boolean, targetns text)
schema_to_xml_and_xmlschema(schema name, nulls boolean, tableforest boolean, targetns text)

database_to_xml(nulls boolean, tableforest boolean, targetns text)
database_to_xmlschema(nulls boolean, tableforest boolean, targetns text)
database_to_xml_and_xmlschema(nulls boolean, tableforest boolean, targetns text)

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

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

<schemaname>

table1-mapping

table2-mapping

...

</schemaname>

где формат отображения таблицы зависит от параметра tableforest как описано выше.

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

<dbname>

<schema1name>
  ...
</schema1name>

<schema2name>
  ...
</schema2name>

...

</dbname>

где отображение схемы такое же, как указано выше.

В качестве примера использования выходных данных, создаваемых этими функциями, на рисунке 1 показана table_to_xml_and_xmlschema стилей XSLT, которая преобразует выходные данные table_to_xml_and_xmlschema в документ HTML, содержащий табличное представление данных таблицы. Аналогичным образом результаты этих функций могут быть преобразованы в другие форматы на основе XML.

Рисунок 1. Таблица стилей XSLT для преобразования вывода SQL/XML в HTML

<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns="http://www.w3.org/1999/xhtml"
>

  <xsl:output method="xml"
      doctype-system="http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"
      doctype-public="-//W3C/DTD XHTML 1.0 Strict//EN"
      indent="yes"/>

  <xsl:template match="/*">
    <xsl:variable name="schema" select="//xsd:schema"/>
    <xsl:variable name="tabletypename"
                  select="$schema/xsd:element[@name=name(current())]/@type"/>
    <xsl:variable name="rowtypename"
                  select="$schema/xsd:complexType[@name=$tabletypename]/xsd:sequence/xsd:element[@name='row']/@type"/>

    <html>
      <head>
        <title><xsl:value-of select="name(current())"/></title>
      </head>
      <body>
        <table>
          <tr>
            <xsl:for-each select="$schema/xsd:complexType[@name=$rowtypename]/xsd:sequence/xsd:element/@name">
              <th><xsl:value-of select="."/></th>
            </xsl:for-each>
          </tr>

          <xsl:for-each select="row">
            <tr>
              <xsl:for-each select="*">
                <td><xsl:value-of select="."/></td>
              </xsl:for-each>
            </tr>
          </xsl:for-each>
        </table>
      </body>
    </html>
  </xsl:template>

</xsl:stylesheet>

Функции и операторы JSON

Этот раздел описывает:

• функции и операторы для обработки и создания данных JSON

• язык путей SQL/JSON

Чтобы узнать больше о стандарте SQL/JSON, обратитесь к стандарту ISO/IEC TR 19075-6.

Обработка и создание данных JSON

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

Таблица 44. jsonb и jsonb Операторы

Существуют параллельные варианты этих операторов для типов json и jsonb. Операторы извлечения поля/элемента/пути возвращают тот же тип, что и их левый ввод (json или jsonb), за исключением тех, которые указаны как возвращающий text, которые приводят значение к тексту. Операторы извлечения поля/элемента/пути возвращают NULL вместо сбоя, если вход JSON не имеет правильной структуры, соответствующей запросу; например, если такого элемента не существует. Все операторы извлечения поля/элемента/пути, которые принимают индексы целочисленных JSON-массивов, поддерживают отрицательную подписку с конца массивов.

Стандартные операторы сравнения, показанные в таблице 1, доступны для jsonb, но не для json. Они следуют правилам упорядочения операций B-дерева, изложенным в разделе Индексирование jsonb.

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

Таблица 45. Дополнительные операторы jsonb

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

Операторы «@?» и «@@» подавляют следующие ошибки: отсутствие поля объекта или элемента массива, непредвиденный тип элемента JSON и числовые ошибки. Это может быть полезно при поиске в коллекциях документов JSON различной структуры.

В таблице 46 показаны функции, доступные для создания значений json и jsonb. (Не существует эквивалентных функций для jsonb, функций row_to_json и array_to_json. Однако функция to_jsonb предоставляет практически те же функции, что и эти функции).

Таблица 46. Функции создания JSON

Примечание
array_to_json и row_to_json ведут себя так же, как to_json за исключением того, что предлагают красивую опцию печати. Поведение, описанное для to_json также применимо к каждому отдельному значению, преобразованному другими функциями создания JSON.

Примечание
Расширение hstore имеет приведение от hstore к json, поэтому значения hstore преобразованные с помощью функций создания JSON, будут представлены как объекты JSON, а не как примитивные строковые значения.

В таблице 47 показаны функции, доступные для обработки значений json и jsonb.

Таблица 47. Функции обработки JSON

 key | value
-----+-------
 a   | "foo"
 b   | "bar"

 key | value
-----+-------
 a   | foo
 b   | bar

 json_object_keys
-----------------
 f1
 f2

 a |   b       |      c
---+-----------+-------------
 1 | {2,"a b"} | (4,"a b c")

 a | b
---+---
 1 | 2
 3 | 4

   value
-----------
 1
 true
 [2,false]

   value
-----------
 foo
 bar

 a |    b    |    c    | d |       r
---+---------+---------+---+---------------
 1 | [1,2,3] | {1,2,3} |   | (123,"a b c")

 a |  b
---+-----
 1 | foo
 2 |

[
    {
        "f1": 1,
        "f2": null
    },
    2,
    null,
    3
]

 jsonb_path_query
------------------
 2
 3
 4

Примечание
Многие из этих функций и операторов преобразуют экранирование Unicode в строках JSON в соответствующий одиночный символ. Это не проблема, если вход имеет тип jsonb, потому что преобразование уже выполнено; но для ввода json это может привести к ошибке, как отмечено в разделе Типы JSON.

Примечание
Функции json[b]_populate_record, json[b]_populate_recordset, json[b]_to_record и json[b]_to_recordset работают с объектом JSON или массивом объектов и извлекают значения, связанные с ключами, имена которых соответствуют именам столбцов тип выходной строки. Поля объекта, которые не соответствуют ни одному имени выходного столбца, игнорируются, а выходные столбцы, которые не соответствуют ни одному полю объекта, будут заполнены пустыми значениями. Для преобразования значения JSON в тип SQL выходного столбца последовательно применяются следующие правила:

• NULL значение JSON во всех случаях преобразуется в нулевое значение SQL.
• Если выходной столбец имеет тип json или jsonb, значение JSON точно воспроизводится.
• Если выходной столбец является составным (строковым) типом, а значение JSON является JSON-объектом, поля объекта преобразуются в столбцы выходного строкового типа путем рекурсивного применения этих правил.
• Аналогично, если выходной столбец является типом массива, а значение JSON является массивом JSON, элементы массива JSON преобразуются в элементы выходного массива путем рекурсивного применения этих правил.
• В противном случае, если значение JSON является строковым литералом, содержимое строки передается в функцию преобразования ввода для типа данных столбца.
• В противном случае обычное текстовое представление значения JSON подается в функцию преобразования ввода для типа данных столбца. Хотя в примерах для этих функций используются константы, обычно используется ссылка на таблицу в предложении FROM и использование одного из ее столбцов json или jsonb в качестве аргумента функции. Затем на извлеченные значения ключей можно ссылаться в других частях запроса, таких как WHERE и целевые списки. Таким образом, извлечение нескольких значений может повысить производительность по сравнению с извлечением их отдельно с помощью ключевых операторов.

Примечание
Все элементы параметра path jsonb_set а также jsonb_insert кроме последнего, должны присутствовать в target. Если create_missing имеет значение false, должны присутствовать все элементы параметра path jsonb_set. Если эти условия не выполнены, target возвращается без изменений.

Примечание
Если последний элемент пути является ключом объекта, он будет создан, если он отсутствует, и ему будет присвоено новое значение. Если последний элемент пути является индексом массива, если он положительный, то элемент для установки определяется путем подсчета слева, а если отрицательный - путем подсчета справа - -1 обозначает самый правый элемент и так далее. Если элемент выходит за пределы диапазона -array_length .. array_length -1, а create_missing имеет значение true, новое значение добавляется в начало массива, если элемент отрицательный, и в конец массива, если он положительный.

Примечание
json_typeof возвращаемое значение функции json_typeof не следует путать с SQL NULL. В то время как вызов json_typeof(’null’::json) вернет null, вызов json_typeof(NULL::json) вернет SQL NULL.

Примечание
Если аргумент json_strip_nulls содержит повторяющиеся имена полей в любом объекте, результат может быть семантически несколько другим, в зависимости от порядка их появления. Это не проблема для jsonb_strip_nulls так jsonb значения jsonb никогда не имеют повторяющихся имен полей объекта.

Примечание
Функции jsonb_path_exists, jsonb_path_match, jsonb_path_query, jsonb_path_query_array и jsonb_path_query_first имеют необязательные vars и silent аргументы. Если указан аргумент vars, он предоставляет объект, содержащий именованные переменные, для подстановки в выражение jsonpath.Если silent аргумент указан и имеет true значение, эти функции подавляют те же ошибки, что и @? и @@ операторы.

См. также раздел Агрегатные функции, где приведена агрегатная функция json_agg которая агрегирует значения записей в виде JSON, и агрегатная функция json_object_agg которая агрегирует пары значений в объект JSON и их эквиваленты jsonb_agg, jsonb_agg и jsonb_object_agg.

Язык путей SQL/JSON

Выражения пути SQL/JSON задают элементы, которые нужно извлечь из данных JSON, аналогично выражениям XPath, используемым для доступа SQL к XML. В QHB выражения пути реализованы как тип данных jsonpath и могут использовать любые элементы, описанные в разделе Тип jsonpath.

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

Выражение пути состоит из последовательности элементов, разрешенных типом данных jsonpath. Выражение пути оценивается слева направо, но вы можете использовать скобки, чтобы изменить порядок операций. Если оценка прошла успешно, создается последовательность элементов SQL/JSON (последовательность SQL/JSON), и результат оценки возвращается в функцию запроса JSON, которая завершает указанное вычисление.

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

Например, предположим, у вас есть некоторые данные JSON с GPS-трекера, которые вы хотели бы проанализировать, например:

{
  "track": {
    "segments": [
      {
        "location": [ 47.763, 13.4034 ],
        "start time": "2018-10-14 10:05:14",
        "HR": 73
      },
      {
        "location": [ 47.706, 13.2635 ],
        "start time": "2018-10-14 10:39:21",
        "HR": 135
      }
    ]
  }
}

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

'$.track.segments'

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

'$.track.segments[*].location'

Чтобы вернуть координаты только первого сегмента, вы можете указать соответствующий индекс в операторе доступа []. Обратите внимание, что массивы SQL/JSON являются 0-относительными:

'$.track.segments[0].location'

Результат каждого шага оценки пути может обрабатываться одним или несколькими операторами и методами jsonpath, перечисленными в разделе Операторы пути и методы SQL/JSON. Каждому имени метода должна предшествовать точка. Например, вы можете получить размер массива:

'$.track.segments.size()'

Дополнительные примеры использования операторов и методов jsonpath в выражениях пути см. раздел Операторы пути и методы SQL/JSON.

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

? (condition)

Выражения фильтра должны быть указаны сразу после шага оценки пути, к которому они применяются. Результат этого шага фильтруется, чтобы включить только те элементы, которые удовлетворяют предоставленному условию. SQL/JSON определяет трехзначную логику, поэтому условие может быть true, false или unknown. unknown значение играет ту же роль, что и SQL NULL и его можно проверить с помощью неизвестного предиката. Дальнейшие шаги оценки пути используют только те элементы, для которых выражения фильтра возвращают true.

Функции и операторы, которые можно использовать в выражениях фильтра, перечислены в таблице 49. Результат оценки пути, подлежащий фильтрации, обозначается переменной @. Чтобы сослаться на элемент JSON, хранящийся на более низком уровне вложенности, добавьте один или несколько операторов доступа после @.

Предположим, вы хотите получить все значения сердечного ритма, превышающие 130. Это можно сделать с помощью следующего выражения:

'$.track.segments[*].HR ? (@ > 130)'

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

'$.track.segments[*] ? (@.HR > 130)."start time"'

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

'$.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time"'

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

'$.track.segments[*] ? (@.location[1] < 13.4).HR ? (@ > 130)'`

Вы также можете вкладывать выражения фильтра друг в друга:

'$.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size()'`

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

Реализация QHB языка путей SQL/JSON имеет следующие отклонения от стандарта SQL/JSON:

• Метод элемента .datetime() еще не реализован, главным образом потому, что неизменяемые функции и операторы jsonpath не могут ссылаться на часовой пояс сеанса, который используется в некоторых операциях datetime. Поддержка даты и времени будет добавлена в jsonpath в будущих версиях QHB.

• Выражение пути может быть логическим предикатом, хотя стандарт SQL/JSON допускает предикаты только в фильтрах. Это необходимо для реализации оператора @@. Например, следующее выражение jsonpath допустимо в QHB:

'$.track.segments[*].HR < 70'

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

Строгий и слабый режимы

При запросе данных JSON выражение пути может не соответствовать фактической структуре данных JSON. Попытка получить доступ к несуществующему члену объекта или элемента массива приводит к структурной ошибке. У выражений пути SQL/JSON есть два режима обработки структурных ошибок:

• lax (по умолчанию) - механизм пути неявно адаптирует запрошенные данные к указанному пути. Все оставшиеся структурные ошибки подавляются и преобразуются в пустые последовательности SQL/JSON.

• strict - при возникновении структурной ошибки формируется ошибка.

Режим lax облегчает сопоставление структуры документа JSON и выражения пути, если данные JSON не соответствуют ожидаемой схеме. Если операнд не соответствует требованиям конкретной операции, он может быть автоматически упакован в массив SQL/JSON или развернут путем преобразования его элементов в последовательность SQL/JSON перед выполнением этой операции. Кроме того, операторы сравнения автоматически распаковывают свои операнды в режиме lax, поэтому вы можете сравнивать массивы SQL/JSON "из коробки". Массив размером 1 считается равным своему единственному элементу. Автоматическое развертывание не выполняется только тогда, когда:

• Выражение пути содержит методы type() или size() которые возвращают тип и количество элементов в массиве соответственно.

• Запрашиваемые данные JSON содержат вложенные массивы. В этом случае развернут только самый внешний массив, а все внутренние массивы остаются неизменными. Таким образом, неявное развертывание может идти только на один уровень вниз на каждом этапе оценки пути.

Например, при запросе данных GPS, перечисленных выше, вы можете абстрагироваться от того факта, что при использовании режима lax он хранит массив сегментов:

lax $.track.segments.location’

В строгом strict режиме указанный путь должен точно соответствовать структуре запрашиваемого документа JSON, чтобы вернуть элемент SQL/JSON, поэтому использование этого выражения пути приведет к ошибке. Чтобы получить тот же результат, что и в режиме lax, вы должны явно развернуть массив segments:

’strict $.track.segments\[\*\].location’

Регулярные выражения

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

'$[*] ? (@ like_regex "^[aeiou]" flag "i")'

Необязательная строка флага может включать один или несколько символов i для соответствия без учета регистра, m для разрешения ^ и $ для соответствия в новых строках, s для разрешения, чтобы соответствовать новой строке и q, чтобы процитировать весь шаблон (сводя поведение к простому совпадению подстроки).

Стандарт SQL/JSON заимствует свое определение для регулярных выражений из оператора LIKE_REGEX, который, в свою очередь, использует стандарт XQuery. QHB в настоящее время не поддерживает оператор LIKE_REGEX. Поэтому фильтр like_regex реализован с использованием механизма регулярных выражений POSIX, описанного в разделе Регулярные выражения POSIX. Это приводит к различным незначительным несоответствиям стандартного поведения SQL/JSON, которые каталогизированы в разделе Отличия от XQuery (LIKE_REGEX). Однако обратите внимание, что описанные здесь несовместимости флаговых букв не применяются к SQL/JSON, поскольку он переводит буквенные символы XQuery в соответствие с тем, что ожидает механизм POSIX.

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

'$ ? (@ like_regex "^\\d+$")'

Операторы пути и методы SQL/JSON

В таблице 48 показаны операторы и методы, доступные в jsonpath. Таблица 49 показывает доступные элементы выражения фильтра.

Таблица 48. Операторы и методы jsonpath

Таблица 49. Элементы выражения фильтра jsonpath

Функции управления последовательностями

В этом разделе описываются функции для работы с объектами последовательности, также называемыми генераторами последовательностей или просто последовательностями. Объекты последовательности — это специальные однорядные таблицы, созданные с помощью CREATE SEQUENCE. Объекты последовательности обычно используются для генерации уникальных идентификаторов для строк таблицы. Функции последовательности, перечисленные в таблице 50, предоставляют простые, многопользовательские безопасные методы для получения последовательных значений последовательности из объектов последовательности.

Таблица 50. Функции последовательности

Последовательность, которой должна управлять функция последовательности, задается аргументом regclass, который является просто OID последовательности в системном каталоге pg_class. Однако вам не нужно искать OID вручную, так как преобразователь ввода типа данных regclass сделает всю работу за вас. Просто напишите имя последовательности, заключенное в одинарные кавычки, чтобы оно выглядело как буквальная константа. Для совместимости с обработкой обычных имен SQL строка будет преобразована в нижний регистр, если она не содержит двойных кавычек вокруг имени последовательности. Таким образом:

nextval(’foo’) operates on sequence foo
nextval(’FOO’) operates on sequence foo
nextval(’"Foo"’) operates on sequence Foo

При необходимости имя последовательности может быть дополнено схемой:

nextval(’myschema.foo’) operates on myschema.foo
nextval(’"myschema".foo’) same as above
nextval(’foo’) searches search path for foo

См. раздел Типы идентификаторов объектов для получения дополнительной информации о regclass.

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

Доступны следующие функции последовательности:

nextval

Переместите объект последовательности к следующему значению и верните это значение. Это делается атомарно: даже если несколько сессий выполняются nextval одновременно, каждый из них безопасно получит отдельное значение последовательности. Если объект последовательности был создан с параметрами по умолчанию, последовательные вызовы nextval будут возвращать последовательные значения, начинающиеся с 1. Другие варианты поведения могут быть получены с помощью специальных параметров в команде CREATE SEQUENCE; см. справочную страницу команд для получения дополнительной информации.

Важно!!!
Чтобы избежать блокировки параллельных транзакций, которые получают числа из той же последовательности, операция nextval никогда не откатывается; то есть, как только значение было получено, оно считается использованным и больше не будет возвращено. Это верно, даже если окружающая транзакция позднее прерывается, или если вызывающий запрос заканчивается тем, что не использует значение. Например, INSERT с предложением ON CONFLICT будет вычислять вставляемый кортеж, включая выполнение любых необходимых вызовов nextval, прежде чем обнаруживать любой конфликт, который заставит его следовать правилу ON CONFLICT. Такие случаи оставят неиспользованные «дыры» в последовательности назначенных значений. Таким образом, объекты последовательностей QHB не могут использоваться для получения последовательностей «без промежутков». Эта функция требует привилегии USAGE или UPDATE для последовательности.

currval

Вернуть значение, полученное последним значением nextval для этой последовательности в текущем сеансе. (Сообщается об ошибке, если nextval никогда не вызывался для этой последовательности в этом сеансе). Поскольку он возвращает локальное значение сеанса, он дает предсказуемый ответ, выполняли ли другие сеансы nextval после текущего сеанса. Эта функция требует привилегии USAGE или SELECT для последовательности.

lastval

Вернуть значение, которое было возвращено последним значением nextval в текущем сеансе. Эта функция идентична currval, за исключением того, что вместо того, чтобы принимать имя последовательности в качестве аргумента, она ссылается на любую nextval последовательность, к которой nextval был применен в текущем сеансе. lastval будет lastval если nextval еще не был вызван в текущем сеансе. Эта функция требует привилегии USAGE или SELECT для последней использованной последовательности.

setval

Сброс значения счетчика объекта последовательности. last_value форма устанавливает в поле last_value последовательности указанное значение и устанавливает для его поля is_called значение true, что означает, что следующий nextval будет продвигать последовательность перед возвратом значения. Значение, сообщаемое currval, также устанавливается на указанное значение. В форме с тремя параметрами is_called может быть установлен в true или false. true имеет тот же эффект, что и форма с двумя параметрами. Если для этого параметра установлено значение false, следующее следующее значение вернет точно указанное значение, и продвижение последовательности начинается со следующего следующего nextval. Кроме того, значение, сообщаемое currval, в этом случае не изменяется. Например,

SELECT setval(’foo’, 42); Next nextval will return 43
SELECT setval(’foo’, 42, true); Same as above
SELECT setval(’foo’, 42, false); Next nextval will return 42

Результат, возвращаемый setval является просто значением его второго аргумента.

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

Условные выражения

В этом разделе описываются SQL- совместимые условные выражения, доступные в QHB

Хотя COALESCE, GREATEST и LEAST синтаксически похожи на функции, они не являются обычными функциями и поэтому не могут использоваться с явными VARIADIC массива VARIADIC.

CASE

Выражение SQL CASE является общим условным выражением, аналогичным операторам if/else в других языках программирования:

CASE WHEN condition THEN result
   [WHEN ...]
   [ELSE result]
END

CASE могут использоваться везде, где выражение допустимо. Каждое условие «condition» является выражением, которое возвращает логический результат. Если результат условия равен true, значением выражения CASE является result, следующий за условием, а остальная часть выражения CASE не обрабатывается. Если результат условия неверен, любые последующие предложения WHEN проверяются таким же образом. Если условия WHEN не возвращает true, значением выражения CASE является результатом предложения ELSE. Если предложение ELSE опущено и ни одно из условий не является истинным, результат будет нулевым.

Пример:

SELECT * FROM test;

 a
---
 1
 2
 3


SELECT a,
       CASE WHEN a=1 THEN 'one'
            WHEN a=2 THEN 'two'
            ELSE 'other'
       END
    FROM test;

 a | case
---+-------
 1 | one
 2 | two
 3 | other

Типы данных всех выражений result должны быть преобразованы в один тип вывода.

Существует «простая» форма выражения CASE которая является вариантом общей формы выше:

CASE expression
    WHEN value THEN result
  [WHEN ...]
  [ELSE result]
END

Первое expression вычисляется, затем сравнивается с каждым из выражений value в предложениях WHEN пока не будет найдено одно значение, равное ему. Если совпадений не найдено, возвращается result предложения ELSE (или нулевое значение). Это похоже на оператор switch в C.

Приведенный выше пример может быть написан с использованием простого синтаксиса CASE:

SELECT a,
       CASE a WHEN 1 THEN 'one'
              WHEN 2 THEN 'two'
              ELSE 'other'
       END
    FROM test;

 a | case
---+-------
 1 | one
 2 | two
 3 | other

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

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

Как описано в разделе Правила вычисления выражений, существуют различные ситуации, в которых подвыражения выражения оцениваются в разное время, поэтому принцип «CASE оценивает только необходимые подвыражения» не является гарантированным. Например, постоянное подвыражение 1/0 обычно приводит к сбою деления на ноль во время планирования, даже если он находится внутри плеча CASE, которое никогда не будет задействовано во время выполнения.

COALESCE

COALESCE(value [, ...])

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

SELECT COALESCE(description, short_description, '(none)') ...

Это возвращает description если оно не равно нулю, иначе short_description если оно не равно нулю, иначе (none).

Как и выражение CASE, COALESCE оценивает только те аргументы, которые необходимы для определения результата; то есть аргументы справа от первого ненулевого аргумента не оцениваются. Эта стандартная функция SQL предоставляет возможности, аналогичные NVL и IFNULL, которые используются в некоторых других системах баз данных.

NULLIF

NULLIF(value1, value2)

Функция NULLIF возвращает нулевое значение, если value1 равно value2; в противном случае он возвращает value1. Это может быть использовано для выполнения обратной операции примера COALESCE приведенного выше:

SELECT NULLIF(value, ’(none)’) ...

В этом примере, если value равно (none), возвращается значение null, в противном случае возвращается значение value.

GREATEST и LEAST

GREATEST(value [, ...])

LEAST(value [, ...])

Функции GREATEST и LEAST выбирают наибольшее или наименьшее значение из списка любого числа выражений. Все выражения должны быть преобразованы в общий тип данных, который будет типом результата . Значения NULL в списке игнорируются. Результатом будет NULL, только если все выражения оцениваются как NULL.

Обратите внимание, что GREATEST и LEAST не входят в стандарт SQL, но являются распространенным расширением. Некоторые другие базы данных заставляют их возвращать NULL, если любой аргумент равен NULL, а не только тогда, когда все имеют значение NULL.

Функции и операторы массива

В таблице 51 показаны операторы, доступные для типов массивов.

Таблица 51. Операторы массива

Операторы упорядочения массива (<, >= и т.д.) сравнивают содержимое массива поэлементно, используя функцию сравнения B-дерева по умолчанию для типа данных элемента, и сортируют на основе первого различия. В многомерных массивах элементы посещаются в главном порядке строк (последний индекс изменяется наиболее быстро). Если содержимое двух массивов одинаково, но размерность отличается, первое различие в информации о размерности определяет порядок сортировки.

Операторы содержания массива (<@ и @>) считают, что один массив должен содержаться в другом, если каждый из его элементов появляется в другом. Дубликаты специально не обрабатываются, поэтому считается, что ARRAY[1] и ARRAY[1,1] содержат друг друга.

См. раздел Массивы для более подробной информации о поведении оператора массива. См. раздел Типы индексов для более подробной информации о том, какие операторы поддерживают индексированные операции.

В таблице 52 показаны функции, доступные для использования с типами массивов. См. раздел Массивы для получения дополнительной информации и примеров использования этих функций.

Таблица 52. Функции массива

В array_position и array_positions каждый элемент массива сравнивается с искомым значением с использованием семантики IS NOT DISTINCT FROM.

В array_position возвращается NULL если значение не найдено.

В array_positions NULL возвращается, только если массив NULL; если значение не найдено в массиве, вместо него возвращается пустой массив.

В string_to_array, если параметр разделителя равен NULL, каждый символ во входной строке станет отдельным элементом в результирующем массиве. Если разделитель является пустой строкой, тогда вся входная строка возвращается как массив из одного элемента. В противном случае входная строка разделяется при каждом появлении строки-разделителя.

В string_to_array, если параметр null-string пропущен или равен NULL, ни одна из подстрок ввода не будет заменена на NULL. В array_to_string, если параметр null-string опущен или равен NULL, любые нулевые элементы в массиве просто пропускаются и не представляются в выходной строке.

См. также раздел Агрегатные функции для использования с массивами агрегатной функции array_agg.

Функции диапазона и операторы

См. раздел Диапазонные типы для обзора диапазонных типов.

В таблице 53 показаны операторы, доступные для диапазонных типов.

Таблица 53. Операторы диапазона

Простые операторы сравнения <, >, <= и >= сначала сравнивают нижние границы, и только если они равны, сравнивают верхние границы. Эти сравнения обычно не очень полезны для диапазонов, но предоставлены, чтобы позволить строить B-деревья на диапазонах.

Операторы left-of/right-of/adjacent всегда возвращают false, когда задействован пустой диапазон; то есть пустой диапазон не считается ни до, ни после любого другого диапазона.

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

В таблице 54 показаны функции, доступные для использования с диапазонными типами.

Таблица 54. Функции диапазона

lower и upper функции возвращают ноль, если диапазон пуст или запрошенная граница бесконечна. Функции lower_inc, upper_inc, lower_inf и upper_inf возвращают false для пустого диапазона.

Агрегатные функции

Агрегатные функции вычисляют один результат из набора входных значений. Встроенные агрегатные функции общего назначения перечислены в таблице 8.55, а статистические агрегаты - в таблице 56. Встроенные внутригрупповые агрегатные функции упорядоченного набора перечислены в таблице 57, а встроенные внутригрупповые гипотетически заданные функции - в таблице 58. Операции группировки, которые тесно связаны с агрегатными функциями, перечислены в таблице 59. Особые синтаксические соображения для агрегатных функций описаны в «Руководстве пользователя» в разделе Агрегатные выражения. Обратитесь к разделу руководства "Агрегатные функции" за дополнительной вводной информацией.

Таблица 55. Агрегатные функции общего назначения

Следует отметить, что кроме count эти функции возвращают нулевое значение, когда строки не выбраны. В частности, sum без строк возвращает null, а не ноль, как можно было ожидать, и array_agg возвращает null, а не пустой массив, когда нет входных строк. Функция coalesce может использоваться для замены нуля или пустого массива на ноль при необходимости.

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

Агрегатные функции array_agg, json_agg, jsonb_agg, json_object_agg, jsonb_object_agg, string_agg и xmlagg, а также аналогичные пользовательские агрегатные функции выдают значимо разные значения результата в зависимости от порядка входных значений. Этот порядок не указан по умолчанию, но им можно управлять, написав предложение ORDER BY в совокупном вызове, как показано в «Руководстве пользователя» в разделе Агрегатные выражения. Альтернативно, подача входных значений из отсортированного подзапроса обычно работает. Например:

SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;

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

В таблице 56 показаны агрегатные функции, обычно используемые в статистическом анализе. (Они отделены просто для того, чтобы избежать загромождения списка наиболее часто используемых агрегатов). Если в описании упоминается N, это означает количество входных строк, для которых все входные выражения не равны NULL. Во всех случаях ноль возвращается, если вычисление не имеет смысла, например, когда N равно нулю.

Таблица 56. Агрегатные функции для статистики