Postgres Pro Enterprise
1817161514131211109.6
Postgres Pro Standard
1817161514131211109.69.5
PostgreSQL
1817161514131211109.69.59.4
RUENRU EN
9.5. Функции и операторы двоичных строк
Пред. НаверхГлава 9. Функции и операторыНачало След.

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

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

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

Таблица 9.11. SQL-функции и операторы для работы с двоичными строками

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

Описание

Пример(ы)

bytea || bytea → bytea

Соединяет две двоичные строки.

'\x123456'::bytea || '\x789a00bcde'::bytea → \x123456789a00bcde

bit_length ( bytea ) → integer

Возвращает число бит в двоичной строке (это число в 8 раз больше octet_length).

bit_length('\x123456'::bytea) → 24

btrim ( bytes bytea, bytesremoved bytea ) → bytea

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

btrim('\x1234567890'::bytea, '\x9012'::bytea) → \x345678

ltrim ( bytes bytea, bytesremoved bytea ) → bytea

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

ltrim('\x1234567890'::bytea, '\x9012'::bytea) → \x34567890

octet_length ( bytea ) → integer

Возвращает число байт в двоичной строке.

octet_length('\x123456'::bytea) → 3

overlay ( bytes bytea PLACING newsubstring bytea FROM start integer [FOR count integer] ) → bytea

Заменяет подстроку в bytes, начиная с байта с номером start, длиной count байт, на подстроку newsubstring. В отсутствие параметра count количество заменяемых байтов определяется длиной newsubstring.

overlay('\x1234567890'::bytea placing '\002\003'::bytea from 2 for 3) → \x12020390

position ( substring bytea IN bytes bytea ) → integer

Возвращает начальную позицию первого вхождения substring в bytes либо 0, если такого вхождения нет.

position('\x5678'::bytea in '\x1234567890'::bytea) → 3

rtrim ( bytes bytea, bytesremoved bytea ) → bytea

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

rtrim('\x1234567890'::bytea, '\x9012'::bytea) → \x12345678

substring ( bytes bytea [FROM start integer] [FOR count integer] ) → bytea

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

substring('\x1234567890'::bytea from 3 for 2) → \x5678

trim ( [LEADING | TRAILING | BOTH] bytesremoved bytea FROM bytes bytea ) → bytea

Удаляет наибольшую строку, содержащую только байты, заданные в параметре bytesremoved, с начала, с конца или с обеих сторон (по умолчанию подразумевается указание BOTH) строки bytes.

trim('\x9012'::bytea from '\x1234567890'::bytea) → \x345678

trim ( [LEADING | TRAILING | BOTH] [FROM] bytes bytea, bytesremoved bytea ) → bytea

Это нестандартный синтаксис вызова trim().

trim(both from '\x1234567890'::bytea, '\x9012'::bytea) → \x345678


В PostgreSQL есть и другие функции для работы с двоичными строками, перечисленные в Таблице 9.12. Некоторые из них используются в качестве внутренней реализации стандартных функций SQL, приведённых в Таблице 9.11.

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

Функция

Описание

Пример(ы)

bit_count ( bytes bytea ) → bigint

Возвращает число бит в двоичной строке (эту операцию также называют «popcount»).

bit_count('\x1234567890'::bytea) → 15

get_bit ( bytes bytea, n bigint ) → integer

Извлекает из двоичной строки бит с номером n.

get_bit('\x1234567890'::bytea, 30) → 1

get_byte ( bytes bytea, n integer ) → integer

Извлекает из двоичной строки байт с номером n.

get_byte('\x1234567890'::bytea, 4) → 144

length ( bytea ) → integer

Выдаёт число байт в двоичной строке.

length('\x1234567890'::bytea) → 5

length ( bytes bytea, encoding name ) → integer

Выдаёт число символов в двоичной строке, в предположении, что она содержит текст в кодировке encoding.

length('jose'::bytea, 'UTF8') → 4

md5 ( bytea ) → text

Вычисляет MD5-хеш двоичной строки и выдаёт результат в шестнадцатеричном виде.

md5('Th\000omas'::bytea) → 8ab2d3c9689aaf18​b4958c334c82d8b1

set_bit ( bytes bytea, n bigint, newvalue integer ) → bytea

Устанавливает в двоичной строке для бита с номером n значение newvalue.

set_bit('\x1234567890'::bytea, 30, 0) → \x1234563890

set_byte ( bytes bytea, n integer, newvalue integer ) → bytea

Устанавливает в двоичной строке для байта с номером n значение newvalue.

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 ( bytes bytea, start integer [, count integer] ) → bytea

Извлекает из bytes подстроку, начиная с позиции start, длиной до count байт (если это значение указано). (Ей равнозначна функция substring(bytes from start for count).)

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.

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

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

Функция

Описание

Пример(ы)

convert ( bytes bytea, src_encoding name, dest_encoding name ) → bytea

Преобразует двоичную строку, содержащую текст в кодировке src_encoding, в двоичную строку с текстом в кодировке dest_encoding (возможные варианты преобразований описаны в Подразделе 22.3.4).

convert('text_in_utf8'​, 'UTF8', 'LATIN1') → \x746578745f696e5f75746638

convert_from ( bytes bytea, src_encoding name ) → text

Преобразует двоичную строку, содержащую текст в кодировке src_encoding, в строку типа text в кодировке базы данных (возможные варианты преобразований описаны в Подразделе 22.3.4).

convert_from('text_in_utf8'​, 'UTF8') → text_in_utf8

convert_to ( string text, dest_encoding name ) → bytea

Преобразует строку типа text в кодировке базы данных в двоичную строку с текстом в кодировке dest_encoding (возможные варианты преобразований описаны в Подразделе 22.3.4).

convert_to('некоторый_текст', 'UTF8') → \x736f6d655f74657874

encode ( bytes bytea, format text ) → text

Переводит двоичные данные в текстовое представление; поддерживаются следующие значения format: base64, escape, hex.

encode('123\000\001', 'base64') → MTIzAAE=

decode ( string text, format text ) → bytea

Переводит двоичные данные из текстового представления; поддерживает те же значения format, что и функция 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, при этом первой идёт цифра, представляющая старшие биты. Шестнадцатеричные цифры a-f функция encode выводит в нижнем регистре. Так как наименьшая единица данных — байт (8 бит), функция encode всегда возвращает чётное количество символов. Функция decode, с другой стороны, принимает символы a-f в любом регистре. Если на вход функции decode поступают некорректные данные, возникает ошибка — в том числе, если число символов оказывается нечётным.

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

Пред. Наверх След.
9.4. Строковые функции и операторы Начало 9.6. Функции и операторы для работы с битовыми строками
9.5. Binary String Functions and Operators
Prev UpChapter 9. Functions and OperatorsHome Next

9.5. Binary String Functions and Operators #

This section describes functions and operators for examining and manipulating binary strings, that is values of type bytea. Many of these are equivalent, in purpose and syntax, to the text-string functions described in the previous section.

SQL defines some string functions that use key words, rather than commas, to separate arguments. Details are in Table 9.11. Postgres Pro also provides versions of these functions that use the regular function invocation syntax (see Table 9.12).

Table 9.11. SQL Binary String Functions and Operators

Function/Operator

Description

Example(s)

bytea || bytea → bytea

Concatenates the two binary strings.

'\x123456'::bytea || '\x789a00bcde'::bytea → \x123456789a00bcde

bit_length ( bytea ) → integer

Returns number of bits in the binary string (8 times the octet_length).

bit_length('\x123456'::bytea) → 24

btrim ( bytes bytea, bytesremoved bytea ) → bytea

Removes the longest string containing only bytes appearing in bytesremoved from the start and end of bytes.

btrim('\x1234567890'::bytea, '\x9012'::bytea) → \x345678

ltrim ( bytes bytea, bytesremoved bytea ) → bytea

Removes the longest string containing only bytes appearing in bytesremoved from the start of bytes.

ltrim('\x1234567890'::bytea, '\x9012'::bytea) → \x34567890

octet_length ( bytea ) → integer

Returns number of bytes in the binary string.

octet_length('\x123456'::bytea) → 3

overlay ( bytes bytea PLACING newsubstring bytea FROM start integer [ FOR count integer ] ) → bytea

Replaces the substring of bytes that starts at the start'th byte and extends for count bytes with newsubstring. If count is omitted, it defaults to the length of newsubstring.

overlay('\x1234567890'::bytea placing '\002\003'::bytea from 2 for 3) → \x12020390

position ( substring bytea IN bytes bytea ) → integer

Returns first starting index of the specified substring within bytes, or zero if it's not present.

position('\x5678'::bytea in '\x1234567890'::bytea) → 3

rtrim ( bytes bytea, bytesremoved bytea ) → bytea

Removes the longest string containing only bytes appearing in bytesremoved from the end of bytes.

rtrim('\x1234567890'::bytea, '\x9012'::bytea) → \x12345678

substring ( bytes bytea [ FROM start integer ] [ FOR count integer ] ) → bytea

Extracts the substring of bytes starting at the start'th byte if that is specified, and stopping after count bytes if that is specified. Provide at least one of start and count.

substring('\x1234567890'::bytea from 3 for 2) → \x5678

trim ( [ LEADING | TRAILING | BOTH ] bytesremoved bytea FROM bytes bytea ) → bytea

Removes the longest string containing only bytes appearing in bytesremoved from the start, end, or both ends (BOTH is the default) of bytes.

trim('\x9012'::bytea from '\x1234567890'::bytea) → \x345678

trim ( [ LEADING | TRAILING | BOTH ] [ FROM ] bytes bytea, bytesremoved bytea ) → bytea

This is a non-standard syntax for trim().

trim(both from '\x1234567890'::bytea, '\x9012'::bytea) → \x345678


Additional binary string manipulation functions are available and are listed in Table 9.12. Some of them are used internally to implement the SQL-standard string functions listed in Table 9.11.

Table 9.12. Other Binary String Functions

Function

Description

Example(s)

bit_count ( bytes bytea ) → bigint

Returns the number of bits set in the binary string (also known as “popcount”).

bit_count('\x1234567890'::bytea) → 15

get_bit ( bytes bytea, n bigint ) → integer

Extracts n'th bit from binary string.

get_bit('\x1234567890'::bytea, 30) → 1

get_byte ( bytes bytea, n integer ) → integer

Extracts n'th byte from binary string.

get_byte('\x1234567890'::bytea, 4) → 144

length ( bytea ) → integer

Returns the number of bytes in the binary string.

length('\x1234567890'::bytea) → 5

length ( bytes bytea, encoding name ) → integer

Returns the number of characters in the binary string, assuming that it is text in the given encoding.

length('jose'::bytea, 'UTF8') → 4

md5 ( bytea ) → text

Computes the MD5 hash of the binary string, with the result written in hexadecimal.

md5('Th\000omas'::bytea) → 8ab2d3c9689aaf18​b4958c334c82d8b1

set_bit ( bytes bytea, n bigint, newvalue integer ) → bytea

Sets n'th bit in binary string to newvalue.

set_bit('\x1234567890'::bytea, 30, 0) → \x1234563890

set_byte ( bytes bytea, n integer, newvalue integer ) → bytea

Sets n'th byte in binary string to newvalue.

set_byte('\x1234567890'::bytea, 4, 64) → \x1234567840

sha224 ( bytea ) → bytea

Computes the SHA-224 hash of the binary string.

sha224('abc'::bytea) → \x23097d223405d8228642a477bda2​55b32aadbce4bda0b3f7e36c9da7

sha256 ( bytea ) → bytea

Computes the SHA-256 hash of the binary string.

sha256('abc'::bytea) → \xba7816bf8f01cfea414140de5dae2223​b00361a396177a9cb410ff61f20015ad

sha384 ( bytea ) → bytea

Computes the SHA-384 hash of the binary string.

sha384('abc'::bytea) → \xcb00753f45a35e8bb5a03d699ac65007​272c32ab0eded1631a8b605a43ff5bed​8086072ba1e7cc2358baeca134c825a7

sha512 ( bytea ) → bytea

Computes the SHA-512 hash of the binary string.

sha512('abc'::bytea) → \xddaf35a193617abacc417349ae204131​12e6fa4e89a97ea20a9eeee64b55d39a​2192992a274fc1a836ba3c23a3feebbd​454d4423643ce80e2a9ac94fa54ca49f

substr ( bytes bytea, start integer [, count integer ] ) → bytea

Extracts the substring of bytes starting at the start'th byte, and extending for count bytes if that is specified. (Same as substring(bytes from start for count).)

substr('\x1234567890'::bytea, 3, 2) → \x5678


Functions get_byte and set_byte number the first byte of a binary string as byte 0. Functions get_bit and set_bit number bits from the right within each byte; for example bit 0 is the least significant bit of the first byte, and bit 15 is the most significant bit of the second byte.

For historical reasons, the function md5 returns a hex-encoded value of type text whereas the SHA-2 functions return type bytea. Use the functions encode and decode to convert between the two. For example write encode(sha256('abc'), 'hex') to get a hex-encoded text representation, or decode(md5('abc'), 'hex') to get a bytea value.

Functions for converting strings between different character sets (encodings), and for representing arbitrary binary data in textual form, are shown in Table 9.13. For these functions, an argument or result of type text is expressed in the database's default encoding, while arguments or results of type bytea are in an encoding named by another argument.

Table 9.13. Text/Binary String Conversion Functions

Function

Description

Example(s)

convert ( bytes bytea, src_encoding name, dest_encoding name ) → bytea

Converts a binary string representing text in encoding src_encoding to a binary string in encoding dest_encoding (see Section 22.3.4 for available conversions).

convert('text_in_utf8', 'UTF8', 'LATIN1') → \x746578745f696e5f75746638

convert_from ( bytes bytea, src_encoding name ) → text

Converts a binary string representing text in encoding src_encoding to text in the database encoding (see Section 22.3.4 for available conversions).

convert_from('text_in_utf8', 'UTF8') → text_in_utf8

convert_to ( string text, dest_encoding name ) → bytea

Converts a text string (in the database encoding) to a binary string encoded in encoding dest_encoding (see Section 22.3.4 for available conversions).

convert_to('some_text', 'UTF8') → \x736f6d655f74657874

encode ( bytes bytea, format text ) → text

Encodes binary data into a textual representation; supported format values are: base64, escape, hex.

encode('123\000\001', 'base64') → MTIzAAE=

decode ( string text, format text ) → bytea

Decodes binary data from a textual representation; supported format values are the same as for encode.

decode('MTIzAAE=', 'base64') → \x3132330001


The encode and decode functions support the following textual formats:

base64 #

The base64 format is that of RFC 2045 Section 6.8. As per the RFC, encoded lines are broken at 76 characters. However instead of the MIME CRLF end-of-line marker, only a newline is used for end-of-line. The decode function ignores carriage-return, newline, space, and tab characters. Otherwise, an error is raised when decode is supplied invalid base64 data — including when trailing padding is incorrect.

escape #

The escape format converts zero bytes and bytes with the high bit set into octal escape sequences (\nnn), and it doubles backslashes. Other byte values are represented literally. The decode function will raise an error if a backslash is not followed by either a second backslash or three octal digits; it accepts other byte values unchanged.

hex #

The hex format represents each 4 bits of data as one hexadecimal digit, 0 through f, writing the higher-order digit of each byte first. The encode function outputs the a-f hex digits in lower case. Because the smallest unit of data is 8 bits, there are always an even number of characters returned by encode. The decode function accepts the a-f characters in either upper or lower case. An error is raised when decode is given invalid hex data — including when given an odd number of characters.

See also the aggregate function string_agg in Section 9.21 and the large object functions in Section 34.4.

Prev Up Next
9.4. String Functions and Operators Home 9.6. Bit String Functions and Operators
FAQ