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

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

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

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

Функция/оператор
	Описание Пример(ы)
bytea || bytea → bytea
	Конкатенирует две двоичные строки. '\x123456'::bytea || '\x789a00bcde'::bytea → \x123456789a00bcde
bit_length ( bytea ) → integer
	Возвращает количество битов в двоичной строке (это число в 8 раз больше octet_length). bit_length('\x123456'::bytea) → 24
octet_length ( bytea ) → integer
	Возвращает количество байтов в двоичной строке. octet_length('\x123456'::bytea) → 3
overlay ( двоичная_строка bytea PLACING новая_подстрока bytea FROM начало integer [ FOR число integer ] ) → bytea
	Заменяет в двоичной_строке подстроку, начинающуюся с байта на позиции начало и длиной в заданное число байт, на новую_подстроку. Если число опущено, по умолчанию количество заменяемых байтов определяется длиной новой_подстроки. overlay('\x1234567890'::bytea placing '\002\003'::bytea from 2 for 3) → \x12020390
position ( подстрока bytea IN двоичная_строка bytea ) → integer
	Возвращает начальную позицию первого вхождения подстроки в двоичной_строке или ноль, если такого вхождения нет. position('\x5678'::bytea in '\x1234567890'::bytea) → 3
substring ( двоичная_строка bytea [ FROM начало integer ] [ FOR число integer ] ) → bytea
	Извлекает из двоичной_строки подстроку, начиная с байта на позиции начало (если она указана) и останавливаясь после заданного числа байт, если оно указано. Должен присутствовать хотя бы один из аргументов: начало или число. substring('\x1234567890'::bytea from 3 for 2) → \x5678
trim ( [ LEADING | TRAILING | BOTH ] удаляемые_байты bytea FROM двоичная_строка bytea ) → bytea
	Удаляет самую длинную подстроку, содержащую удаляемые_байты, с начала (LEADING), конца (TRAILING) или с обеих сторон (BOTH) (по умолчанию BOTH) двоичной_строки. trim('\x9012'::bytea from '\x1234567890'::bytea) → \x345678
trim ( [ LEADING | TRAILING | BOTH ] [ FROM ] двоичная_строка bytea, удаляемые_байты bytea ) → bytea
	Это нестандартный синтаксис функции trim(). trim(both from '\x1234567890'::bytea, '\x9012'::bytea) → \x345678

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

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

Функция
	Описание Пример(ы)
bit_count ( двоичная_строка bytea ) → bigint
	Возвращает количество установленных битов (единиц) в двоичной строке (эта операция также известна как «popcount»). bit_count('\x1234567890'::bytea) → 15
btrim ( двоичная_строка bytea, удаляемые_байты bytea ) → bytea
	Удаляет самую длинную строку, содержащую только заданные удаляемые_байты, с начала и конца двоичной_строки. btrim('\x1234567890'::bytea, '\x9012'::bytea) → \x345678
get_bit ( двоичная_строка bytea, n bigint ) → integer
	Извлекает n-й бит из двоичной строки. get_bit('\x1234567890'::bytea, 30) → 1
get_byte ( двоичная_строка bytea, n integer ) → integer
	Извлекает n-й байт из двоичной строки. get_byte('\x1234567890'::bytea, 4) → 144
length ( bytea ) → integer
	Возвращает количество байтов в двоичной строке. length('\x1234567890'::bytea) → 5
length ( двоичная_строка bytea, кодировка name ) → integer
	Возвращает количество символов в двоичной строке, предполагая, что она содержит текст в заданной кодировке. length('jose'::bytea, 'UTF8') → 4
ltrim ( двоичная_строка bytea, удаляемые_байты bytea ) → bytea
	Удаляет самую длинную подстроку, содержащую только заданные удаляемые_байты, с начала двоичной_строки. ltrim('\x1234567890'::bytea, '\x9012'::bytea) → \x34567890
md5 ( bytea ) → text
	Вычисляет MD5-хеш двоичной строки с результатом в шестнадцатеричном виде. md5('Th\000omas'::bytea) → 8ab2d3c9689aaf18​b4958c334c82d8b1
rtrim ( двоичная_строка bytea, удаляемые_байты bytea ) → bytea
	Удаляет самую длинную подстроку, содержащую только заданные удаляемые_байты, с конца двоичной_строки. rtrim('\x1234567890'::bytea, '\x9012'::bytea) → \x12345678
set_bit ( двоичная_строка bytea, n bigint, новое_значение integer ) → bytea
	Задает для n-го бита в двоичной строке новое_значение. set_bit('\x1234567890'::bytea, 30, 0) → \x1234563890
set_byte ( двоичная_строка bytea, n integer, новое_значение integer ) → bytea
	Задает для n-го байта в двоичной строке новое_значение. set_byte('\x1234567890'::bytea, 4, 64) → \x1234567840
sha224 ( bytea ) → bytea
	Вычисляет хеш SHA-224 для двоичной строки. sha224('abc'::bytea) → \x23097d223405d8228642a477bda2​55b32aadbce4bda0b3f7e36c9da7
sha256 ( bytea ) → bytea
	Вычисляет хеш SHA-256 для двоичной строки. sha256('abc'::bytea) → \xba7816bf8f01cfea414140de5dae2223​b00361a396177a9cb410ff61f20015ad
sha384 ( bytea ) → bytea
	Вычисляет хеш SHA-384 для двоичной строки. sha384('abc'::bytea) → \xcb00753f45a35e8bb5a03d699ac65007​272c32ab0eded1631a8b605a43ff5bed​8086072ba1e7cc2358baeca134c825a7
sha512 ( bytea ) → bytea
	Вычисляет хеш SHA-512 для двоичной строки. sha512('abc'::bytea) → \xddaf35a193617abacc417349ae204131​12e6fa4e89a97ea20a9eeee64b55d39a​2192992a274fc1a836ba3c23a3feebbd​454d4423643ce80e2a9ac94fa54ca49f
substr ( двоичная_строка bytea, начало integer [, число integer ] ) → bytea
	Извлекает из двоичной_строки подстроку, начинающуюся с байта на позиции начало и длиной в заданное число байт, если оно указано. (То же, что и функция substring(двоичная_строка from начало for число).) substr('\x1234567890'::bytea, 3, 2) → \x5678

Функции 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. Функции для преобразования текстовых/двоичных строк

Функция
	Описание Пример(ы)
convert ( двоичная_строка bytea, исходная_кодировка name, новая_кодировка name ) → bytea
	Преобразует двоичную строку, представляющую текст в исходной_кодировке, в двоичную строку в новой_кодировке (допустимые преобразования см. в подразделе Доступные перекодировки). convert('text_in_utf8', 'UTF8', 'LATIN1') → \x746578745f696e5f75746638
convert_from ( двоичная_строка bytea, исходная_кодировка name ) → text
	Преобразует двоичную строку, представляющую текст в исходной_кодировке, в текст в кодировке базы данных (допустимые преобразования см. в подразделе Доступные перекодировки). convert_from('text_in_utf8', 'UTF8') → text_in_utf8
convert_to ( строка text, новая_кодировка name ) → bytea
	Преобразует строку типа text (в кодировке базы данных) в двоичную строку в новой_кодировке (допустимые преобразования см. в подразделе Доступные перекодировки). convert_to('some_text', 'UTF8') → \x736f6d655f74657874
encode ( двоичная_строка bytea, формат text ) → text
	Кодирует двоичные данные в текстовое представление; поддерживаемые значения формата: base64, escape, hex. encode('123\000\001', 'base64') → MTIzAAE=
decode ( строка text, формат text ) → bytea
	Декодирует двоичные данные из текстового представления; поддерживаются те же значения формата, что и для функции encode. decode('MTIzAAE=', 'base64') → \x3132330001

Функции 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 в разделе Агрегатные функции и функции для больших объектов в разделе Серверные функции.