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

• Логические операторы
• Функции и операторы сравнения
• Математические функции и операторы
• Строковые функции и операторы
  • format
• Функции и операторы для двоичных строк
• Функции и операторы для битовых строк
• Сопоставление с шаблоном
  • LIKE
  • Регулярные выражения SIMILAR TO
  • Регулярные выражения POSIX
    • Подробное описание регулярных выражений
    • Выражения в квадратных скобках
    • Управляющие символы в регулярных выражениях
    • Метасинтаксис регулярных выражений
    • Правила сопоставления регулярных выражений
    • Пределы и совместимость
    • Базовые регулярные выражения
    • Отличия от XQuery (LIKE_REGEX)
• Функции форматирования типов данных
• Функции и операторы даты/времени
  • EXTRACT, date_part
  • date_trunc
  • date_bin
  • AT TIME ZONE
  • Текущая дата/время
  • Задержка выполнения
• Функции поддержки перечислений
• Геометрические функции и операторы
• Функции и операторы для сетевых адресов
• Функции и операторы текстового поиска
• Функции генерирования UUID
• Функции 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
  • Расширенные XML функции
    • xml_append_child
    • xml_insert_before
    • xml_insert_after
    • xml_insert_child_after
    • xml_update
    • xml_delete
    • xml_extract_value
    • xml_extract
    • xml_exists_node
• Функции и операторы JSON
  • Обработка и создание данных JSON
  • Язык путей SQL/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

QHB предоставляет огромное количество функций и операторов для встроенных типов данных. В этой главе описаны основные из них, тогда как некоторые специальные функции описываются в соответствующих разделах данного руководства. Кроме того, пользователи могут определять свои собственные функции и операторы, как описано в главе Серверное программирование. Для вывода списка всех доступных функций и операторов можно воспользоваться командами psql \df и \do соответственно.

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

repeat ( text, integer ) → text

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

repeat('Pg', 4) → PgPgPgPg

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

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

Среди логических операторов доступны обычные:

boolean AND boolean → boolean
boolean OR boolean → boolean
NOT boolean → boolean

SQL использует трехзначную логическую систему: true (истина), false (ложь) и NULL («не определено»). Рассмотрите следующие таблицы истинности:

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

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

Среди операторов сравнения доступны обычные операторы, перечисленные в Таблице 1.

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

Примечание
Запись <> принята в стандарте SQL для условия «не равно». Запись != является псевдонимом, который преобразуется в <> на самом раннем этапе синтаксического анализа. Как следствие, реализовать операторы != и <> так, чтобы они работали по-разному, невозможно.

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

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

Как показано выше, все операторы сравнения являются бинарными операторами, которые возвращают значения типа boolean. Таким образом, выражения типа 1 < 2 < 3 недопустимы (потому что нет оператора < для сравнения логического значения с 3). Для проверки принадлежности диапазону используйте предикат BETWEEN, описанный ниже

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

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

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

a BETWEEN x AND y

равнозначно

a >= x AND a <= y

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

Различные варианты BETWEEN реализуются посредством обычных операторов сравнения, и поэтому они будут работать с любыми типами данных, которые можно сравнивать.

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

Обычные операторы сравнения выдают NULL (что означает «неопределенность»), а не true или false, когда любой из аргументов 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 был нормальным значением данных, а не «неопределенным».

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

выражение IS NULL
выражение IS NOT NULL

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

выражение ISNULL
выражение NOTNULL

Не пишите выражение = NULL, потому что NULL «не равно» NULL. (Значение NULL представляет неопределенность, и неизвестно, равны ли два неопределенных значения).

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

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

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

логическое_выражение IS TRUE
логическое_выражение IS NOT TRUE
логическое_выражение IS FALSE
логическое_выражение IS NOT FALSE
логическое_выражение IS UNKNOWN
логическое_выражение IS NOT UNKNOWN

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

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

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

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

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

В Таблице 4 перечислены математические операторы, доступные для стандартных числовых типов. Если не указано иное, операторы, показанные как принимающие числовой_тип, доступны для типов smallint, integer, bigint, numeric, real и double precision. Операторы, показанные как принимающие целочисленный_тип, доступны для типов smallint, integer и bigint. За исключением отмеченных случаев, все формы операторов возвращают тот же тип данных, какой имеет их аргумент (или аргументы). Вызовы с неодинаковыми типами аргументов, например integer + numeric, разрешаются использованием типа, идущего в этих списках последним.

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

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

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

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

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

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

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

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

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

В Таблице 8 перечислены доступные гиперболические функции.

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

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

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

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

Примечание
Оператор конкатенации строк (\|\|) примет нестроковый аргумент, если хотя бы один аргумент имеет строковый тип, как показано в Таблице 9. В других случаях, чтобы оператор принял нестроковый аргумент, можно использовать явное преобразование в text.

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

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

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

    regexp_matches('foobarbequebaz', 'ba.', 'g') →
         {bar}
         {baz}

    regexp_split_to_table('hello world', '\s+') →
         hello
         world

    string_to_table('xx~^~yy~^~zz', '~^~', 'yy') →
         xx
         NULL
         zz

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

Также обратите внимание на агрегатную функцию string_agg в разделе Агрегатные функции и функции для преобразования текста в bytea и наоборот в Таблице 13.

format

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

format(стр_формата text [, форматир_аргумент "any" [, ...] ])

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

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

%[позиция][флаги][ширина]тип

где полями компонентов являются:

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

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

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

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

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

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

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

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

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

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

SELECT format('Hello% s', 'World');
Результат: Hello World

SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');
Результат: Testing one, two, three, %

SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');
Результат: INSERT INTO "Foo bar" VALUES('O''Reilly')

SELECT format('INSERT INTO %I VALUES(%L)', 'locations', 'C:\Program Files');
Результат: INSERT INTO locations VALUES('C:\Program Files')

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

SELECT format('|%10s|', 'foo');
Результат: |       foo|

SELECT format('|%-10s|', 'foo');
Результат: |foo       |

SELECT format('|%*s|', 10, 'foo');
Результат: |       foo|

SELECT format('|%*s|', -10, 'foo');
Результат: |foo       |

SELECT format('|%-*s|', 10, 'foo');
Результат: |foo       |

SELECT format('|%-*s|', -10, 'foo');
Результат: |foo       |

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

SELECT format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three');
Результат: Testing three, two, one

SELECT format('|%*2$s|', 'foo', 10, 'bar');
Результат: |       bar|

SELECT format('|%1$*2$s|', 'foo', 10, 'bar');
Результат: |       foo|

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

SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
Результат: Testing three, two, three

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

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

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

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

Таблица 11. Функции и операторы SQL для двоичных строк

В QHB доступны также дополнительные функции для обработки двоичных строк, перечисленные в Таблице 12. Некоторые из них используются для внутренней реализации стандартных строковых функций SQL, перечисленных в Таблице 11.

Таблица 12. Другие функции для двоичных строк

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

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

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

Таблица 13. Функции для преобразования текстовых/двоичных строк

Функции encode и decode поддерживают следующие текстовые форматы:

base64
Формат base64 описан в RFC 2045, разделе 6.8. Согласно этому RFC, закодированные строки разбиваются по 76 символов. Однако вместо маркеров CRLF (как положено по стандарту MIME) строки завершаются только символом новой строки. Функция decode игнорирует символы возврата каретки, новой строки, пробелы и табуляции. В иных случаях если на вход decode поступают некорректные данные base64, возникает ошибка — в том числе, когда некорректно завершающее заполнение.

escape
Формат escape преобразует нулевые байты и байты с установленным старшим битом в восьмеричные экранированные последовательности (\nnn), а обратные слэши дублируются. Другие байтовые значения представляются в буквальном виде. Если за обратным слэшем не следует еще один обратный слэш или три восьмеричных цифры, функция decode выдаст ошибку; другие байтовые значения она принимает без изменений

hex
Формат hex представляет каждые 4 бита данных в виде одной шестнадцатеричной цифры, от 0 до f, первой записывая старший бит каждого байта. Функция encode выводит шестнадцатеричные цифры a-f в нижнем регистре. Поскольку наименьшая единица данных — 8 битов, функция encode всегда возвращает четное количество символов. Функция decode принимает символы a-f либо в верхнем, либо в нижнем регистре. Если на вход decode поступают некорректные шестнадцатеричные данные, возникает ошибка — в том числе, когда вводится нечетное количество символов.

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

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

В этом разделе описываются функции и операторы для проверки и обработки битовых строк, то есть значений типов bit и bit varying. (Хотя в этих таблицах упоминается только тип bit, вместо них можно использовать значения типа bit varying) Битовые строки поддерживают обычные операторы сравнения, показанные в Таблице 1, а также операторы, перечисленные в Таблице 14.

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

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

Таблица 15. Функции для битовых строк

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

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

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

Сопоставление с шаблоном

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

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

ВНИМАНИЕ!
Хотя по большей части поиск по регулярным выражениям может выполняться очень быстро, есть вероятность создания регулярных выражений, обработка которых занимает некоторое количество времени и памяти. Остерегайтесь шаблонов поиска по регулярным выражениям, поступающих из неблагоприятных источников. Если вам приходится это делать, рекомендуется установить тайм-аут для операторов.

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

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

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

LIKE

строка LIKE шаблон [ESCAPE управляющий_символ]
строка NOT LIKE шаблон [ESCAPE управляющий_символ]

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

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

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

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

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

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

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

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

Согласно стандарту SQL, отсутствие указания ESCAPE означает, что управляющий символ не определен (а не становится по умолчанию обратным слэшем), а пустое значение в ESCAPE не допускается. Таким образом, в этом отношении поведение QHB значительно отличается от стандартного.

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

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

Фразы LIKE, ILIKE, NOT LIKE и NOT ILIKE в синтаксисе QHB обычно обрабатываются как операторы; например, их можно использовать в конструкциях выражение оператор ANY (подзапрос), хотя предложение ESCAPE сюда включить нельзя. В некоторых сложных случаях может понадобиться использовать вместо них имена нижележащих операторов.

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

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

строка SIMILAR TO шаблон [ESCAPE управляющий_символ]
строка NOT SIMILAR TO шаблон [ESCAPE управляющий_символ]

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

Как и 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 ''.

Согласно стандарту SQL, отсутствие указания ESCAPE означает, что управляющий символ не определен (а не становится по умолчанию обратным слэшем), а пустое значение в ESCAPE не допускается. Таким образом, в этом отношении поведение QHB значительно отличается от стандартного.

Еще одно нестандартное расширение заключается в том, что следующая за управляющим символом буква или цифра открывает доступ к управляющим последовательностям, определенным для регулярных выражений POSIX; см. Таблицу 20, Таблицу 21 и Таблицу 22 ниже.

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

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

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

substring(строка similar шаблон escape управляющий_символ)

или используя синтаксис ныне устаревшего SQL:1999:

substring(строка from шаблон for управляющий_символ)

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

substring(строка, шаблон, управляющий_символ)

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

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

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

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

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

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

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

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

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

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

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

'abcd' ~ 'bc'     true
'abcd' ~ 'a.c'    true — точка соответствует любому символу
'abcd' ~ 'a.*d'   true — * повторяет предыдущий элемент шаблона
'abcd' ~ '(b|x)'  true — | означает ИЛИ для группы в скобках
'abcd' ~ '^a'     true — ^ привязывает шаблон к началу строки
'abcd' ~ '^(b|c)' false — совпадение не найдено по причине привязки

Более подробно язык шаблонов POSIX описан ниже.

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

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

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

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

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

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(строка, шаблон [, флаги ]). Если совпадений нет, результатом будет NULL. Если совпадение найдено и шаблон не содержит подвыражений в скобках, то результатом будет текстовый массив из одного элемента, содержащий подстроку, соответствующую всему шаблону. Если совпадение найдено и шаблон содержит подвыражения в скобках, то результатом будет текстовый массив, чей n-й элемент является подстрокой, соответствующей n-му подвыражению в скобках шаблона (не считая «не захватывающих» скобок; подробности см. ниже). Параметр флаги представляет собой необязательную текстовую строку, содержащую ноль или более однобуквенных флагов, изменяющих поведение функции. Поддерживаемые флаги описаны в Таблице 24.

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

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. Каждая возвращаемая строка является текстовым массивом, содержащим всю сопоставляемую подстроку или подстроки, соответствующие подвыражениям в скобках шаблона, как описано выше для regexp_match. Функция regexp_matches принимает все флаги, перечисленные в Таблице 24, плюс флаг 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(строка, шаблон [, флаги ]). Если нет совпадения с шаблоном, функция возвращает строку. Если найдено хотя бы одно совпадение, для каждого его вхождения она возвращает текст от конца предыдущего вхождения (или начала строки) до начала этого вхождения. Дойдя до последнего вхождения, она возвращает текст от конца этого последнего вхождения до конца строки. Параметр флаги представляет собой необязательную текстовую строку, содержащую ноль или более однобуквенных флагов, изменяющих поведение функции. regexp_split_to_table поддерживает флаги, описанные в Таблице 24.

Функция regexp_split_to_array ведет себя так же, как regexp_split_to_table, за исключением того, что regexp_split_to_array возвращает свой результат в виде массива элементов типа text. Она имеет синтаксис regexp_split_to_array(строка, шаблон [, флаги ]). Параметры те же, что и для 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_match и regexp_matches, но обычно на практике является наиболее удобным поведением. Аналогичные определения используют и другие программные системы, например Perl.

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

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

Регулярные выражения (Regular expression, 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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Управляющие символы в регулярных выражениях

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

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

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

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

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

Таблица 20. Особые обозначения символов в регулярных выражениях

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

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

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

Таблица 21. Коды классов в регулярных выражениях

Коды классов также работают в выражениях в квадратных скобках, хотя показанные выше определения не вполне синтаксически допустимы в этом контексте. Например, выражение [a-c\d] равнозначно [a-c[:digit:]].

Таблица 22. Символы ограничений в регулярных выражениях

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

Таблица 23. Обратные ссылки в регулярных выражениях

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

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

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

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

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

Таблица 24. Буквы встроенных параметров ARE

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

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

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

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

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

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

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

Чтобы задать атрибут жадности или нежадности для подвыражения или целого RE, можно использовать квантификаторы {1,1} и {1,1}? соответственно. Это полезно, когда требуется, чтобы весь 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 продолжают соответствовать только началу или концу строки. Кроме того, коды классов символов \D и \W будут соответствовать переводу строки независимо от этого режима. (В старых версиях QHB они не соответствовали переводу строки в режиме с учетом перевода строк. Для включения старого поведения напишите [^[:digit:]] или [^[:word:]].)

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

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

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

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

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

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

Базовые регулярные выражения

BRE отличаются от ERE в нескольких отношениях. В BRE |, + и ? являются обычными символами, и их функциональность не имеет аналогов. Разделителями для границ являются \{ и \}, причем сами по себе { и } — обычные символы. Скобками для вложенных подвыражений являются \( и \), а сами по себе ( и ) — обычные символы. ^ — обычный символ, если не стоит в начале RE или в начале подвыражения в скобках, $ — обычный символ, если не стоит в конце RE или в конце подвыражения в скобках, а * — обычный символ, если находится в начале RE или в начале подвыражения в скобках (возможно, после начального ^). Наконец, в BRE доступны обратные ссылки с одной цифрой, а \< и \> являются синонимами для [[:<:]] и [[:>:]] соответственно; в 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 (см. Таблицу 21), интерпретируются в соответствии с преобладающей локалью (которой можно управлять, добавляя предложение COLLATE к оператору или функции). В XQuery эти классы определяются с помощью ссылок на свойства символов Unicode, поэтому равнозначное поведение возможно только с локалью, подчиняющейся правилам Unicode.

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

• Из особых обозначений символов, описанных в Таблице 20, XQuery поддерживает только \n, \r и \t.

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

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

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

• Буквы флагов регулярного выражения, определенные в XQuery, имеют нечто общее с буквами параметров для POSIX (Таблица 24), но отличаются них. Только параметры i и q одинаковы, остальные ведут себя совершенно по-другому:

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

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

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

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

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

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

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

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

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

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

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

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

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

• TM подавляет конечные пробелы независимо от указания FM.

• Функции to_timestamp и to_date игнорируют регистр букв во входных данных; поэтому, например, для кодов MON, Mon и mon подойдут одни и те же строки. При использовании модификатора 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') полю TZH соответствует -10, но в to_timestamp('2000 -10', 'YYYY TZH') полю TZH соответствует 10.

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

  Совет
  Чтобы пропустить во входной строке произвольный текст, можно использовать в шаблоне буквы. Например, шаблоны to_timestamp('2000y6m1d', 'yyyytMMtDDt') и to_timestamp('2000y6m1d', 'yyyy"y"MM"m"DD"d"') пропускают буквы y, m и d.

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

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

• В функциях to_timestamp and to_date отрицательные значения годов обрабатываются как обозначающие годы до н. э. Если одновременно написать отрицательный год и явно указать код BC (индикатор годов до н. э.), год будет относиться к н. э. Введение нулевого года воспринимается как 1 год до н. э.

• В функциях to_timestamp и to_date преобразование YYYY имеет ограничение, когда обрабатывается год, состоящий из более чем 4 цифр. Следует написать после YYYY какой-либо нецифровой символ или подходящий код, иначе год всегда будет интерпретироваться как 4 цифры. Например, to_date('200001131', 'YYYYMMDD') (с годом 20000) будет интерпретироваться как год с 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 понятие «месяц» или «день месяца» не имеет смысла. В контексте григорианского года не имеет смысла неделя по ISO.

  Внимание!
  В то время как 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 выводит значение часа полностью, так что в значении interval результат может быть выше 23.

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

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

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

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

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

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

• Знак, отформатированный с помощью кодов 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. В функции to_number код V аналогичным образом делит значения. Функции to_char и to_number не поддерживают использование V в сочетании с десятичной точкой (например, 99.9V99 не допускается).

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

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

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

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

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

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

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

Кроме того, для типов даты/времени доступны обычные операторы сравнения, перечисленные в Таблице 1. Даты и метки времени (с часовым поясом или без него) можно сравнивать как угодно, тогда как время (с часовым поясом или без него) и интервалы можно сравнивать только со значениями того же типа. При сравнении метки времени без часового пояса и метки времени с часовым поясом предполагается, что первое значение задано в часовом поясе, установленном параметром конфигурации TimeZone, и оно меняется на UTC для сравнения со вторым значением (которое внутри уже представлено в UTC). Аналогично при сравнении даты с меткой времени предполагается, что значение даты представляет полночь в часовом поясе TimeZone.

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

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

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

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

(начало1, конец1) OVERLAPS (начало2, конец2)
(начало1, длительность1) OVERLAPS (начало2, длительность2)

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

SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Результат: true
SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Результат: false
SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS
       (DATE '2001-10-30', DATE '2001-10-31');
Результат: false
SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS
       (DATE '2001-10-30', DATE '2001-10-31');
Результат: true

При добавлении к значению timestamp with time zone значения interval (или при вычитании из него значения interval) компонент дней увеличивает (или уменьшает) дату timestamp with time zone на указанное количество суток, а время суток не меняется. При пересечении границы летнего времени (когда часовой пояс сеанса распознает летнее время) это означает, что interval '1 day' не обязательно равен interval '24 hours'. Например, в часовом поясе America/Denver (Америка/Денвер):

SELECT timestamp with time zone '2005-04-02 12:00:00-07' + interval '1 day';
Результат: 2005-04-03 12:00:00-06
SELECT timestamp with time zone '2005-04-02 12:00:00-07' + interval '24 hours';
Результат: 2005-04-03 13:00:00-06

Это происходит потому, что вследствие произошедшего 2005-04-03 02:00:00 в часовом поясе America/Denver перехода на летнее время был пропущен час.

Обратите внимание на возможную неоднозначность в поле 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');
Результат: 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;
Результат: 121.958333333333
SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00';
Результат: 121 days 23:00:00
SELECT age(timestamptz '2013-07-01 12:00:00', timestamptz '2013-03-01 12:00:00');
Результат: 4 mons

EXTRACT, date_part

EXTRACT(поле FROM источник)

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

century

Век

SELECT EXTRACT(CENTURY FROM TIMESTAMP ’2000-12-16 12:21:13’);
Результат: 20
SELECT EXTRACT(CENTURY FROM TIMESTAMP ’2001-02-16 20:38:40’);
Результат: 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’);
Результат: 16
SELECT EXTRACT(DAY FROM INTERVAL ’40 days 1 minute’);
Результат: 40

decade

Поле года, разделенное на 10

SELECT EXTRACT(DECADE FROM TIMESTAMP ’2001-02-16 20:38:40’);
Результат: 200

dow

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

SELECT EXTRACT(DOW FROM TIMESTAMP ’2001-02-16 20:38:40’);
Результат: 5

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

doy

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

SELECT EXTRACT(DOY FROM TIMESTAMP ’2001-02-16 20:38:40’);
Результат: 47

epoch

Для значений 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’);
Результат: 982384720.12
SELECT EXTRACT(EPOCH FROM INTERVAL ’5 days 3 hours’);
Результат: 442800

Значение эпохи можно преобразовать обратно в timestamp with time zone с помощью функции to_timestamp:

SELECT to_timestamp(982384720.12);
Результат: 2001-02-17 04:38:40.12+00

Имейте в виду, что применение to_timestamp к времени эпохи, извлеченному из значения date или timestamp, может выдать искаженный результат: по сути этот результат будет получен, исходя из предположения, что оригинальное значение было задано в часовом поясе UTC, а это может быть не так.

hour

Поле часа (0-23)

SELECT EXTRACT(HOUR FROM TIMESTAMP ’2001-02-16 20:38:40’);
Результат: 20

isodow

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

SELECT EXTRACT(ISODOW FROM TIMESTAMP ’2001-02-18 20:38:40’);
Результат: 7

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

isoyear

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

SELECT EXTRACT(ISOYEAR FROM DATE ’2006-01-01’);
Результат: 2005
SELECT EXTRACT(ISOYEAR FROM DATE ’2006-01-02’);
Результат: 2006

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

julian

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

SELECT EXTRACT(JULIAN FROM DATE '2006-01-01');
Результат: 2453737
SELECT EXTRACT(JULIAN FROM TIMESTAMP '2006-01-01 12:00');
Результат: 2453737.50000000000000000000

microseconds

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

SELECT EXTRACT(MICROSECONDS FROM TIME ’17:12:28.5’);
Результат: 28500000

millennium

Тысячелетие

SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP ’2001-02-16 20:38:40’);
Результат: 3

Годы в 20 века относятся ко второму тысячелетию. Третье тысячелетие началось 1 января 2001 года.

milliseconds

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

SELECT EXTRACT(MILLISECONDS FROM TIME ’17:12:28.5’);
Результат: 28500

minute

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

SELECT EXTRACT(MINUTE FROM TIMESTAMP ’2001-02-16 20:38:40’);
Результат: 38

month

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

SELECT EXTRACT(MONTH FROM TIMESTAMP ’2001-02-16 20:38:40’);
Результат: 2
SELECT EXTRACT(MONTH FROM INTERVAL ’2 years 3 months’);
Результат: 3
SELECT EXTRACT(MONTH FROM INTERVAL ’2 years 13 months’);
Результат: 1

quarter

Квартал года (1-4), к которому относится дата

SELECT EXTRACT(QUARTER FROM TIMESTAMP ’2001-02-16 20:38:40’);
Результат: 1

second

Поле секунд, включая дробные части

SELECT EXTRACT(SECOND FROM TIMESTAMP ’2001-02-16 20:38:40’);
Результат: 40
SELECT EXTRACT(SECOND FROM TIME ’17:12:28.5’);
Результат: 28.5

timezone

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

timezone_hour

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

timezone_minute

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

week

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

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

SELECT EXTRACT(WEEK FROM TIMESTAMP ’2001-02-16 20:38:40’);
Результат: 7

year

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

SELECT EXTRACT(YEAR FROM TIMESTAMP ’2001-02-16 20:38:40’);
Результат: 2001

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

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

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

date_part('поле', источник)

Обратите внимание, что здесь параметр поле должен быть строковым значением, а не именем. Допустимые имена полей для date_part такие же, как и для extract. По историческим причинам функция date_part возвращает значения типа double precision, что может в некоторых случаях привести к потере точности. Поэтому вместо нее рекомендуется использовать extract.

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
Результат: 16

SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
Результат: 4

date_trunc

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

date_trunc(поле, источник [, часовой_пояс ])

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

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

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

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

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

Примеры (предполагается, что местный часовой пояс — America/New_York (Америка/Нью-Йорк)):

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Результат: 2001-02-16 20:00:00

SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Результат: 2001-01-01 00:00:00

SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
Результат: 2001-02-16 00:00:00-05

SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');
Результат: 2001-02-16 08:00:00-05

SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
Результат: 3 days 02:00:00

date_bin

Функция date_bin «укладывает» входную метку времени в заданный интервал (шаг), установленный с указанным начальным моментом.

date_bin(шаг, источник, начальный_момент)

Здесь источник — это выражение значения типа timestamp или timestamp with time zone. (Значения типа date автоматически приводятся к типу timestamp.) Под шагом подразумевается выражение значения типа interval. Возвращаемое значение, также имеющее тип timestamp или timestamp with time zone, отмечает начало позиции, в которую помещается источник.

Примеры:

SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01');
Результат: 2020-02-11 15:30:00

SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01 00:02:30');
Результат: 2020-02-11 15:32:30

В случае целых единиц измерения (1 минута, 1 час и т. д.), она дает тот же результат, что и аналогичный вызов функции date_trunc, но разница в том, что date_bin может усечь дату до произвольного интервала.

Шаговый интервал должен быть больше нуля и не может содержать единицы измерения, начиная с месяца, и выше.

AT TIME ZONE

Оператор AT TIME ZONE преобразует метку времени без часового пояса в метку времени с часовым поясом и обратно, а значения типа time with time zone пересчитывает для разных часовых поясов. В Таблице 33 показаны его варианты.

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

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

Примеры (предполагается, что текущее значение параметра TimeZone — America/ Los_Angeles (Америка/Лос-Анджелес)):

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';
Результат: 2001-02-16 19:38:40-08

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver';
Результат: 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';
Результат: 2001-02-16 05:38:40

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

Функция timezone (часовой_пояс, метка_времени) равнозначна совместимой с SQL конструкции метка_времени AT TIME ZONE часовой_пояс.

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

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

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME(точность)
CURRENT_TIMESTAMP(точность)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME(точность)
LOCALTIMESTAMP(точность)

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

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

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

SELECT CURRENT_TIME;
Результат: 14:39:53.662522-05

SELECT CURRENT_DATE;
Результат: 2001-12-23

SELECT CURRENT_TIMESTAMP;
Результат: 2001-12-23 14:39:53.662522-05

SELECT CURRENT_TIMESTAMP(2);
Результат: 2001-12-23 14:39:53.66-05

SELECT LOCALTIMESTAMP;
Результат: 2001-12-23 14:39:53.662522

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

Примечание
В других СУБД эти значения могут меняться чаще.

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

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

Функция transaction_timestamp() равнозначна функции CURRENT_TIMESTAMP, но названа так, чтобы явно отражать, что именно она возвращает. Функция statement_timestamp() возвращает время начала текущего оператора (а точнее, время получения последнего командного сообщения от клиента). Функции statement_timestamp() и transaction_timestamp() возвращают одинаковое значение в ходе первой команды транзакции, но при выполнении последующих команд их результаты могут отличаться. Функция clock_timestamp() возвращает фактическое текущее время, и поэтому ее значение меняется даже в рамках одной команды SQL. Функция timeofday() существует в QHB по историческим причинам. Как и clock_timestamp(), она возвращает фактическое текущее время, но в виде форматированной строки типа text, а не значения timestamp with time zone. Функция now() представляет собой традиционный для QHB эквивалент функции transaction_timestamp().

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

SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP ’now’; -- но см. совет ниже

Совет
Не используйте третью форму при указании значения, которое будет вычисляться позднее, например, в предложении DEFAULT для столбца таблицы. Система преобразует now в значение timestamp сразу после анализа константы, поэтому когда потребуется такое значение по умолчанию, будет использовано время создания таблицы! Первые две формы не будут вычисляться, пока не понадобится значение по умолчанию, так как они являются вызовами функций. Таким образом, они выдадут желаемое поведение к моменту добавления строки в таблицу. (См. также подраздел Специальные значения)

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

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

pg_sleep ( double precision )
pg_sleep_for ( interval )
pg_sleep_until ( timestamp with time zone )

Функция pg_sleep переводит процесс текущего сеанса в спящий режим вплоть до истечения указанного числа секунд (можно указать дробное число). Вспомогательная функция pg_sleep_for позволяет задать длительность спящего режима в виде значения типа interval. Вспомогательная функция 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 или ее вариантов убедитесь, что ваш сеанс не содержит больше блокировок, чем это необходимо. В противном случае другим сеансам, возможно, придется ждать завершения вашего спящего режима, замедляя работу всей системы.

Функции поддержки перечислений

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

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

Таблица 34. Функции поддержки перечислений

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

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

Для геометрических типов point (точка), box (прямоугольник), lseg (отрезок), line (прямая), path (путь), polygon (многоугольник) и circle (окружность) существует большой набор встроенных вспомогательных функций и операторов, приведенных в Таблице 35, Таблице 36 и Таблице 37.

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

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

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

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

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

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

Типы cidr и inet, предназначенные для сетевых IP-адресов, поддерживают обычные операторы сравнения, приведенные в Таблице 1, а также специальные операторы и функции, приведенные в Таблице 38 и Таблице 39.

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

Таблица 38. Операторы для IP-адресов

Таблица 39. Функции для IP-адресов

Совет
Функции abbrev, host и text предназначены в основном для отображения IP-адресов в альтернативных форматах.

Типы для MAC-адресов, macaddr и macaddr8, поддерживают обычные операторы сравнения, , приведенные в Таблице 1, а также специальные функции, приведенные в Таблице 40. Кроме того, они поддерживают побитовые логические операторы ~, & и | (НЕ, И и ИЛИ), показанные выше для IP-адресов.

Таблица 40. Функции для MAC-адресов

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

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

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