F.16. dbms_lob — работа с большими объектами
Пред. НаверхПриложение F. Дополнительно поставляемые модули и расширения, поставляемые в postgrespro-ent-16-contribНачало След.

F.16. dbms_lob — работа с большими объектами #

F.16.1. Установка
F.16.2. Типы данных
F.16.3. Вспомогательные функции
F.16.4. Функции и процедуры dbms_lob
F.16.5. Пример

dbms_lob — это расширение Postgres Pro, позволяющее работать с большими объектами (LOB): BLOB, CLOB, BFILE и временными LOB. Расширение можно использовать для обращения к определённым частям больших объектов или большим объектам целиком и управления ими. Функциональность, предоставляемая этим модулем, во многом пересекается с функциональностью пакета DBMS_LOB в Oracle.

Примечание

Обратите внимание, что расширение dbms_lob зависит от расширения pgpro_sfile, поэтому pgpro_sfile устанавливается первым. Более подробная информация описана в документации самого расширения pgpro_sfile.

F.16.1. Установка #

Расширение dbms_lob включено в состав Postgres Pro Enterprise как стандартное расширение. Чтобы задействовать dbms_lob, создайте расширение с помощью следующего запроса:

CREATE EXTENSION dbms_lob;

F.16.2. Типы данных #

Расширение dbms_lob работает с несколькими типами данных:

  • Тип bfile предоставляется расширением pgpro_bfile.

    CREATE TYPE BFILE AS (
    dir_id int,
    file_name text
);

    Таблица F.14. Параметры bfile

    ПараметрОписание
    dir_idИдентификатор каталога, в котором хранится bfile.
    file_nameИмя файла, из которого нужно прочитать bfile.

  • Тип blob хранит двоичные данные и имеет тот же интерфейс, что и BLOB в Oracle. Предоставляется расширением pgpro_sfile.

    CREATE TYPE dbms_lob.blob AS (
    temp_data   bytea,
    mime        text,
    sf          @extschema:pgpro_sfile@.sfile
);

    Таблица F.15. Параметры blob

    ПараметрОписание
    sfОбъект типа sfile на диске, в котором хранятся объекты BLOB.
    temp_dataВременные данные BLOB, хранящиеся в памяти.
    mimeВспомогательные данные, которые определяют тип данных, хранящихся в BLOB.

  • Тип clob является эквивалентом CLOB и NCLOB в Oracle. Поддерживается только кодировка UTF-8.

    CREATE TYPE CLOB AS (
    t       text,
    istemp  bool,
    mime    text
);

    Таблица F.16. Параметры clob

    ПараметрОписание
    tОбъект типа text на диске, в котором хранятся данные.
    istempОпределяет, является ли объект временным.
    mimeВспомогательные данные, которые определяют тип данных, хранящихся в CLOB.

F.16.3. Вспомогательные функции #

bfilename(dirname text, filename text) returns bfile #

Создаёт объект bfile, связанный с физическим файлом в файловой системе. Здесь dirname — это имя объекта каталога, созданного функцией bfile_directory_create(), где находится файл filename.

empty_blob() returns blob #

Создаёт пустой объект blob, содержащий объект типа sfile без данных. Его можно заполнить данными с помощью функций записи.

empty_clob() returns clob #

Создаёт пустой объект clob, содержащий пустую строку. Его можно заполнить данными с помощью функций записи.

to_blob(b bytea) returns blob
to_blob(f bfile, mime_type text) returns blob #

Преобразует объекты типа bytea в объекты типа blob. Если исходным файлом является bfile, можно указать тип данных mime.

to_clob(t text) returns clob
to_clob(t varchar) returns clob
 to_clob(b bfile, int csid, mime text) returns clob #

Преобразует текстовые объекты в объекты типа clob. Если исходным файлом является bfile, его данные считываются и преобразуются в тип clob. Поддерживается только кодировка UTF-8.

to_raw(b blob) returns clob
to_raw(b bfile) returns clob #

Копирует данные из файла типа blob или bfile в файл типа bytea. Обрабатывается только первый ГБ данных.

F.16.4. Функции и процедуры dbms_lob #

F.16.4.1. Открытие и закрытие больших объектов #

open(file_loc IN OUT bfile, open_mode IN int)
open(lob_loc IN OUT blob, open_mode IN int)
 open(lob_loc IN OUT clob, open_mode IN int) #

Открывает объект типа bfile. Параметр open_mode указывает, в каком режиме будет открыт файл: чтения/записи или только для чтения. Для типа bfile поддерживается исключительно режим только для чтения (0). Функции open(blob) и open(clob) не выполняют никаких действий и необходимы только для обеспечения совместимости синтаксиса.

isopen(file_loc IN bfile)
isopen(lob_loc IN blob)
 isopen(lob_loc IN clob) #

Проверяет, открыт ли объект типа bfile. Возвращает 1, если LOB открыт, и 0 в противном случае. Функции isopen(blob) и isopen(clob) всегда возвращают 1 и необходимы только для обеспечения совместимости синтаксиса.

close(file_loc IN OUT bfile)
close(lob_loc IN OUT blob)
 close(lob_loc IN OUT clob) #

Проверяет, открыт ли объект bfile, и если да, то закрывает его. Функции close(blob) и close(clob) не выполняют никаких действий и необходимы только для совместимости синтаксиса.

createtemporary(lob_loc IN OUT blob, cache IN bool, dur IN int default 10)
createtemporary(lob_loc IN OUT clob, cache IN bool, dur IN int default 10) #

Создаёт временный объект LOB, в котором данные типа blob хранятся как данные типа bytea, а данные типа clob — как данные типа text.

freetemporary(lob_loc IN OUT blob)
freetemporary(lob_loc IN OUT clob) #

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

F.16.4.2. Чтение LOB #

getlength(file_loc IN bfile)
getlength(lob_loc IN blob)
 getlength(lob_loc IN clob) #

Возвращает длину blob или bfile в байтах или clob в символах.

read(file_loc IN bfile, amount IN OUT int, offset IN int, buffer OUT bytea)
read(lob_loc IN blob, amount IN OUT int, offset IN int, buffer OUT bytea)
 read(lob_loc IN clob, amount IN OUT int, offset IN int, buffer OUT text) #

Считывает часть LOB и записывает указанное количество байтов (для blob/bfile) или символов (для clob) в буфер (buffer), начиная с абсолютного смещения (offset) от начала LOB. Обратите внимание, что для чтения с начала файла необходимо указать для параметра offset значение 1.

get_storage_limit(lob_loc IN blob)
get_storage_limit(lob_loc IN clob) #

Возвращает размер хранилища LOB для указанного LOB.

substr(file_loc IN bfile, amount IN int, offset IN int)
substr(lob_loc IN blob, amount IN int, offset IN int)
 substr(lob_loc IN clob, amount IN int, offset IN int) #

Возвращает количество (amount) байтов (для blob/bfile) или символов (для clob) LOB, начиная с абсолютного смещения (offset) от начала LOB.

instr(file_loc IN bfile, pattern IN int, offset IN bigint default 1, nth IN bigint default 1)
instr(lob_loc IN blob, pattern IN int, offset IN bigint default 1, nth IN bigint default 1)
 instr(lob_loc IN clob, pattern IN int, offset IN bigint default 1, nth IN bigint default 1) #

Возвращает соответствующую позицию n-ого (nth) вхождения шаблона (pattern) в LOB, начиная с указанного смещения (offset). Возвращает 0, если шаблон pattern не найден. Поиск осуществляется только в первом ГБ данных.

F.16.4.3. Изменение LOB #

write(lob_loc IN OUT blob, amount IN int, offset IN bigint, buffer IN bytea)
write(lob_loc IN OUT clob, amount IN int, offset IN int, buffer IN text) #

Записывает указанный объём (amount) данных во внутренний большой объект, начиная с абсолютного смещения (offset) от начала большого объекта. Данные записываются из буфера, указанного в параметре buffer. Если указанное смещение (offset) выходит за пределы данных, находящихся в данный момент в LOB, то вставляются нулевые заполнители (для blob) или пробелы (для clob).

writeappend(lob_loc IN OUT blob, amount IN int, buffer IN bytea)
writeappend(lob_loc IN OUT clob, amount IN int, buffer IN text) #

Записывает указанный объём (amount) данных в конец внутреннего LOB. Данные записываются из буфера, указанного в параметре buffer.

erase(lob_loc IN OUT blob, amount IN OUT int, offset IN bigint default 1)
erase(lob_loc IN OUT clob, amount IN OUT int, offset IN int default 1) #

Удаляет весь внутренний LOB или часть внутреннего LOB. Когда данные стираются из середины LOB, записываются нулевые байтовые заполнители (для временных blob) или пробелы (для clob). Постоянные объекты типа blob могут быть удалены только целиком.

trim(lob_loc IN OUT blob, newlen IN bigint)
trim(lob_loc IN OUT clob, newlen IN int) #

Обрезает значение внутреннего LOB до длины, указанной в параметре newlen. Необходимо указать длину в байтах для временного blob и длину в символах для clob. Применимо для постоянного blob, только если новая длина равна нулю, что означает удаление объекта.

F.16.4.4. Операции с несколькими LOB #

compare(lob_1 IN bfile, lob_2 IN bfile, amount IN bigint, offset_1 IN bigint default 1, offset_2 IN bigint default 1) returns int
compare(lob_1 IN blob, lob_2 IN blob, amount IN int default 1024*1024*1024-8, offset_1 IN bigint default 1, offset_2 IN bigint default 1) returns int
 compare(lob_1 IN clob, lob_2 IN clob, amount IN int default (1024*1024*1024-8)/2, offset_1 IN int default 1, offset_2 IN int default 1) returns int #

Сравнивает два полных LOB или части двух LOB. Можно сравнивать только LOB, имеющие одинаковые типы данных. Для bfile и blob выполняется двоичное сравнение. Для clob файлы сравниваются в соответствии с текущим правилом сортировки базы данных.

append(lob_1 IN OUT blob, lob_2 IN blob)
append(lob_1 IN OUT clob, lob_2 IN clob) #

Добавляет содержимое исходного внутреннего LOB в целевой LOB. Исходный LOB добавляется полностью.

copy(dest_lob IN OUT blob, src_lob IN blob, amount IN bigint, dest_offset IN bigint default 1, src_offset IN bigint default 1) returns int
copy(dest_lob IN OUT clob, src_lob IN clob, amount IN int, dest_offset IN int default 1, src_offset IN int default 1) returns int #

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

converttoblob(dest_lob IN OUT blob, src_clob IN clob, amount IN int, dest_offset IN OUT bigint, src_offset IN OUT int, blob_csid IN int, lang_context IN OUT int, warning OUT int) #

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

converttoclob(dest_lob IN OUT clob, src_blob IN blob, amount IN int, dest_offset IN OUT int, src_offset IN OUT bigint, blob_csid IN int, lang_context IN OUT int, warning OUT int) #

Считывает двоичные данные из исходного blob, преобразует их в кодировку UTF-8 и записывает преобразованные символьные данные в целевой clob.

F.16.4.5. Устаревшие API #

fileexists(file_loc IN bfile) returns int #

Проверяет, действительно ли существует в файловой системе файл, на который указывает заданный указатель bfile. Реализовано как bfile_fileexists.

fileopen(file_loc IN OUT bfile, open_mode IN int) returns int #

Открывает указанный bfile в режиме только для чтения. Реализовано как функция bfile_open.

fileisopen(file_loc IN bfile) returns int #

Проверяет, открыт ли указанный bfile.

loadfromfile(dest_lob IN OUT blob, src_bfile IN bfile, amount IN int default 1024*1024*1024-8, dest_offset IN bigint default 1, src_offset IN bigint default 1) #

Преобразует данные из указанного bfile в blob.

fileclose(file_loc IN OUT bfile) #

Закрывает ранее открытый bfile. Реализовано как функция bfile_close.

filecloseall() #

Закрывает все файлы bfile, открытые в сеансе. Реализовано как bfile_close_all.

filegetname(file_loc IN bfile, dir_alias OUT text, filename OUT text) #

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

F.16.4.6. Прочие параметры #

loadblobfromfile(dest_lob IN OUT blob, src_bfile IN bfile, amount IN int default 1024*1024*1024-8, dest_offset IN bigint default 1, src_offset IN bigint default 1) returns int #

Синоним для loadfromfile().

loadclobfromfile(dest_lob IN OUT clob, src_bfile IN bfile, amount IN int, dest_offset IN OUT int, src_offset IN OUT bigint, bfile_csid IN int, lang_context IN OUTint, warning OUTint) #

Загружает данные из bfile во внутренний clob.

setcontenttype(lob_loc IN OUT blob, contenttype IN text)
setcontenttype(lob_loc IN OUT clob, contenttype IN text) #

Устанавливает строку типа содержимого, связанную с LOB.

getcontenttype(lob_loc IN blob) returns text
getcontenttype(lob_loc IN clob) returns text #

Возвращает строку типа содержимого, связанную с LOB.

getchunksize(lob_loc IN blob) returns text
getchunksize(lob_loc IN clob) returns text #

Возвращает объём пространства, используемого в порции LOB для хранения значения LOB.

F.16.5. Пример #

Ниже приведён пример работы расширения dbms_lob.

DO
$$
DECLARE
  cur_clob  dbms_lob.clob;
  buffer    text;
  amount    int := 3000;
BEGIN
  cur_clob := dbms_lob.empty_clob();
  cur_clob.t := 'just some sample text';
  raise notice 'clob length: %', dbms_lob.getlength(cur_clob);
  call dbms_lob.read(cur_clob, amount, 1, buffer);
  raise notice 'all clob read: %', buffer;
  amount := 6;
  call dbms_lob.read(cur_clob, amount, 4, buffer);
  raise notice 'clob read from 4 position for 6 symbols: %', buffer;
  raise notice 'storage limit: %', dbms_lob.get_storage_limit(cur_clob);
  raise notice 'clob substr from 6 position for 8 symbols: %', dbms_lob.substr(cur_clob, 8, 6);
  raise notice 'third postion of letter s in clob: %', dbms_lob.instr(cur_clob, 's', 1, 3);

  call dbms_lob.write(cur_clob, 6, 4, 'foobar');
  raise notice 'new clob contents: %', cur_clob.t;
  call dbms_lob.write(cur_clob, 3, 25, 'baz');
  raise notice 'new clob contents: %', cur_clob.t;

  call dbms_lob.writeappend(cur_clob, 4, 'test');
  raise notice 'new clob contents: %', cur_clob.t;

  amount := 3;
  call dbms_lob.erase(cur_clob, amount, 2);
  raise notice 'amount of symbols deleted: %', amount;
  raise notice 'new clob contents: %', cur_clob.t;
  call dbms_lob.erase(cur_clob, amount, 30);
  raise notice 'amount of symbols deleted: %', amount;
  raise notice 'new clob contents: %', cur_clob.t;

  call dbms_lob.trim_(cur_clob, 22);
  raise notice 'new clob contents: %', cur_clob.t;
END;
$$;
--output
NOTICE:  clob length: 21
NOTICE:  all clob read: just some sample text
NOTICE:  clob read from 4 position for 6 symbols: t some
NOTICE:  storage limit: 536870908
NOTICE:  clob substr from 6 position for 8 symbols: some sam
NOTICE:  third postion of letter s in clob: 11
NOTICE:  new clob contents: jusfoobar sample text
NOTICE:  new clob contents: jusfoobar sample text   baz
NOTICE:  new clob contents: jusfoobar sample text   baztest
NOTICE:  amount of symbols deleted: 3
NOTICE:  new clob contents: j   oobar sample text   baztest
NOTICE:  amount of symbols deleted: 2
NOTICE:  new clob contents: j   oobar sample text   bazte
NOTICE:  new clob contents: j   oobar sample text

А вот так может выглядеть работа с LOB, находящимися в базе данных:

-- Создание таблицы и добавление данных
CREATE TABLE dbms_lob_test (id INTEGER GENERATED ALWAYS AS IDENTITY, blob_col DBMS_LOB.BLOB);
INSERT INTO dbms_lob_test (blob_col) VALUES (dbms_lob.to_blob(decode('d6b7a686ab4d4e9c5d2cbf49db6bc0f1', 'hex')));

DO $$
DECLARE
  v_lob_loc DBMS_LOB.BLOB;
  v_buffer BYTEA;
  v_amount INTEGER := 32700;
  v_offset BIGINT := 1;
  v_length BIGINT;
BEGIN
  SELECT (blob_col).* INTO v_lob_loc FROM dbms_lob_test WHERE id=1;
  CALL dbms_lob.open(v_lob_loc, 0); -- Для DBMS_LOB.BLOB не обязательно
  SELECT DBMS_LOB.getlength(v_lob_loc) into v_length;
  RAISE NOTICE 'BLOB len=%', v_length;
  CALL dbms_lob.read(v_lob_loc, v_amount, v_offset, v_buffer);
  RAISE NOTICE 'Read % bytes', v_amount;
  RAISE NOTICE 'Buffer: %', encode(v_buffer, 'hex');
  CALL dbms_lob.close(v_lob_loc); -- Для DBMS_LOB.BLOB не обязательно
END $$;

Вывод будет выглядеть вот так:

BLOB len=16
Read 16 bytes
Buffer: d6b7a686ab4d4e9c5d2cbf49db6bc0f1
Пред. Наверх След.
dblink_build_sql_update Начало F.17. dict_int — пример словаря текстового поиска для целых чисел