Здравствуйте! Меня зовут Алексей Калинин. Это вторая моя статья, посвященная разработанному мною Интерпретатору. На просторах интернета мне встретилась вот эта интересная статья, в которой автор описал возможности расширения функциональности БД PostgreSQL. После ее прочтения я решил попробовать встроить разработанный мною интерпретатор в БД PostgreSQL (тем более сам автор статьи предлагал это попробовать). За одно и проверить две вещи: легко ли добавить новый язык (новое расширение) в PostgreSQL и хватит ли возможностей моего интерпретатора для работы внутри БД. И если эксперимент по встраиванию нового языка в PostgreSQL пройдет успешно, тогда код, написанные на нем, можно использовать в реальных проектах.
Вот краткое описание возможностей языка, для которого разработан интерпретатор:
-
По синтаксису он похож на Oracle PL/SQL.
-
Поддерживает следующие типы данных: varchar2, integer, number, date, json, xml, html, record, boolean.
-
Поддерживает работу с курсорами, в нем реализованы операторы: for, while, open, if.
-
Имеются следующие встроенные пакеты:
a) TEXT_IO – для работы с локальными файлами.
b) HTTP_IO – для работы с http/https- запросами из БД.
c) JSON_IO – для работы с JSON.
d) RECORD_IO – для работы с переменными типа record.
e) XML_IO – для парсинга (разбора) XML-документов.
f) HTML_IO – для парсинга (разбора) HTML-документов.
-
Реализован оператор execute для выполнения произвольного текста.
-
Также реализована поддержка различных встроенных функций – instr, substr, trim, sysdate и т. д.
Более полное описание языка можно найти на github. (https://github.com/ank-75/pl_cpl_sql)
Встраивание интерпретатора в PostgreSQL.
Согласно указанной статьи, для реализации языка в PostgreSQL надо написать на C три функции:
-
CALL HANDLER — обработчик вызова, который будет исполнять процедуру/функцию на языке;
-
INLINE HANDLER — обработчик анонимных блоков;
-
VALIDATOR — функцию проверки кода при создании процедуры/функции.
Для того, чтобы внутри C-функций можно было бы обращаться к моему интерпретатору, я перенес код из Delphi в Lazarus и собрал там в виде динамической библиотеки (pl_cpl_sql_lib.dll для Windows, libpl_cpl_sql_lib.so для Linux). Бесплатный Lazarus оказался тем хорош, что позволил один и тот же код собрать для разных ОС.
Также мне пришлось доработать интерпретатор. Для описания процедур/функции пришлось добавить обработку введенных параметров и возврат значения.
Все указанные в этой статье действия проводились компьютере с ОС Alt Linux.
На github также есть версии библиотек для Windows, инструкция, как установить расширение в БД и каталог с примерами кода.
В качестве клиентского средства для работы с БД использовался DBeaver.
Далее я создал C-файл (pl_cpl_sql_ext.c) и написал в нем три функции. Возможно, код и не оптимален, так как опыта программирования на С у меня не было. Последний раз я программировал на С лет 20 назад. Так как в сети довольно мало информации на данную тему, мне пришлось изучать исходные коды расширений plpython и plpgsql. Также позже мне встретилась вот такая статья , в которой есть раздел подробного описания механизма создания расширений для PostgreSQL.
Новый язык в PostgreSQL будет называться — pl_cpl_sql. Полный код C-файла можно посмотреть на github. Здесь рассмотрим отдельные его элементы.
-
Функция для проверки кода перед созданием процедуры/функции — pl_cpl_sql_validator.
//----------------------------------------------------------- //----------------------------------------------------------- //----------------------------------------------------------- // Реализация функции для выполнения проверки корректности кода Datum pl_cpl_sql_validator(PG_FUNCTION_ARGS) { Oid func_oid = PG_GETARG_OID(0); char* prosrc; int numargs; Oid* argtypes; char** argnames; char* argmodes; Form_pg_proc pl_struct; char* proname; bool isnull; Datum ret; FmgrInfo* arg_out_func; Form_pg_type type_struct; HeapTuple type_tuple; int i; int pos; Oid* types; Oid rettype; PG_TRY(); { //----------------------------------------- //---Проинициализируем структуру, которую будем передавать в качестве входных параметров THandlerInputInfoRec* input_info_struct = (THandlerInputInfoRec*)palloc(sizeof(THandlerInputInfoRec)); input_info_struct->Args = (char*)palloc(200 * sizeof(char)); // Выделяем память для строки input_info_struct->RetType = (char*)palloc(100 * sizeof(char)); // Выделяем память для строки //---Присвоим значения по умолчанию strcpy(input_info_struct->Args, ""); strcpy(input_info_struct->RetType, ""); //----------------------------------------- // Получаем кортеж функции по OID HeapTuple tuple = SearchSysCache1(PROCOID, ObjectIdGetDatum(func_oid)); if (!HeapTupleIsValid(tuple)) { ereport(ERROR, (errmsg("Function with OID %u does not exist", func_oid))); } //-------------------------------------- //---Получим текст функции для проверки и выполнения pl_struct = (Form_pg_proc)GETSTRUCT(tuple); proname = pstrdup(NameStr(pl_struct->proname)); ret = SysCacheGetAttr(PROCOID, tuple, Anum_pg_proc_prosrc, &isnull); if (isnull) elog(ERROR, "could not find source text of function \"%s\"", proname); //-------------------------------------- //---Текст исполняемой процедуры/функции prosrc = DatumGetCString(DirectFunctionCall1(textout, ret)); size_t length = strlen(prosrc); input_info_struct->ProcSrc = (char*)palloc((length + 1) * sizeof(char)); // Выделяем память для строки //---Сохраним в структуру текст проверяемой функции strcpy(input_info_struct->ProcSrc, prosrc); //-------------------------------------- //---Получим кол-во аргументов функции numargs = get_func_arg_info(tuple, &types, &argnames, &argmodes); //-------------------------------------- //---Переберем все аргументы процедуры/функции for (i = pos = 0; i < numargs; i++) { HeapTuple argTypeTup; Form_pg_type argTypeStruct; char* value; Assert(types[i] == pl_struct->proargtypes.values[pos]); argTypeTup = SearchSysCache1(TYPEOID, ObjectIdGetDatum(types[i])); if (!HeapTupleIsValid(argTypeTup)) elog(ERROR, "cache lookup failed for type %u", types[i]); argTypeStruct = (Form_pg_type)GETSTRUCT(argTypeTup); ReleaseSysCache(argTypeTup); //---Сохраним данные в структуру strcat(input_info_struct->Args, argnames[i]); strcat(input_info_struct->Args, ","); } //---Определим тип возвращаемого значения rettype = pl_struct->prorettype; //---Сохраним данные в структуру strcat(input_info_struct->RetType, format_type_be(rettype)); //---------------------------------------------------- //---------------------------------------------------- //--Присоединяем библиотеку void* hlib = dlopen("libpl_cpl_sql_lib.so", RTLD_NOW | RTLD_GLOBAL); if (hlib) { Pl_Cpl_Sql_Validate_Func ProcAdd = (Pl_Cpl_Sql_Validate_Func)dlsym(hlib, "pl_cpl_sql_validator"); if (ProcAdd) { THandlerResultRec* result = ProcAdd(input_info_struct); // Вызов функции из DLL //---Освободим память dlclose(hlib); //---если обранужилась ошибка if (result->OutType == 1) { ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("%s", result->Errors))); } } else { //---Освободим память dlclose(hlib); ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("ERR-01: Ошибка при выполнении валидации!"))); } } else { ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("ERR-02: Библиотека не найдена!"))); } ReleaseSysCache(tuple); // Освобождаем кэш } PG_CATCH(); { PG_RE_THROW(); } PG_END_TRY(); // Если все проверки пройдены, просто возвращаем PG_RETURN_VOID(); } Из переданных аргументов (PG_FUNCTION_ARGS) получаем код создаваемой процедуры/функции. Подключаем библиотеку интерпретатора (libpl_cpl_sql_lib.so) и передаем ей текст на проверку. Если текст не корректный – возвращаем ошибку. Если ошибок не обнаружено — процедура/функция создается в БД.
-
Функция для выполнения кода процедуры/функции — pl_cpl_sql_call_handler.
//----------------------------------------------------------- //----------------------------------------------------------- //----------------------------------------------------------- // Реализация функции для выполнения кода Datum pl_cpl_sql_call_handler(PG_FUNCTION_ARGS) { HeapTuple pl_tuple; Datum ret; char* prosrc; bool isnull; FmgrInfo* arg_out_func; Form_pg_type type_struct; HeapTuple type_tuple; Form_pg_proc pl_struct; volatile MemoryContext proc_cxt = NULL; Oid* argtypes; char** argnames; char* argmodes; char* proname; Form_pg_type pg_type_entry; Oid result_typioparam; Oid prorettype; FmgrInfo result_in_func; int numargs; size_t length; PG_TRY(); { //----------------------------------------- //---Проинициализируем структуру, которую будем передавать в качестве входных параметров THandlerInputInfoCallRec* input_info_struct = (THandlerInputInfoCallRec*)palloc(sizeof(THandlerInputInfoCallRec)); //----------------------------------------- pl_tuple = SearchSysCache1(PROCOID, ObjectIdGetDatum(fcinfo->flinfo->fn_oid)); //----------------------------------------- if (!HeapTupleIsValid(pl_tuple)) elog(ERROR, "cache lookup failed for function %u", fcinfo->flinfo->fn_oid); //----------------------------------------- pl_struct = (Form_pg_proc)GETSTRUCT(pl_tuple); proname = pstrdup(NameStr(pl_struct->proname)); ret = SysCacheGetAttr(PROCOID, pl_tuple, Anum_pg_proc_prosrc, &isnull); if (isnull) elog(ERROR, "could not find source text of function \"%s\"", proname); //---Текст исполняемой процедуры/функции prosrc = DatumGetCString(DirectFunctionCall1(textout, ret)); //------------------------------------------------- //---Запишем текст процедуры функции в структуру--- //------------------------------------------------- length = strlen(prosrc); input_info_struct->ProcSrc = (char*)palloc((length + 1) * sizeof(char)); // Выделяем память для строки strcpy(input_info_struct->ProcSrc, prosrc); //------------------------------------------------- proc_cxt = AllocSetContextCreate(TopMemoryContext, "PL_CPL_SQL function", ALLOCSET_SMALL_SIZES); //------------------------------------------------- arg_out_func = (FmgrInfo*)palloc0(fcinfo->nargs * sizeof(FmgrInfo)); //---Получим аргументы функции numargs = get_func_arg_info(pl_tuple, &argtypes, &argnames, &argmodes); //------------------------------------------------- //---Массив с параметрами-------------------------- TArgsArray args_arr; args_arr.count = numargs; args_arr.ArgName = palloc(numargs * sizeof(char*)); args_arr.ArgValue = palloc(numargs * sizeof(char*)); args_arr.ArgType = palloc(numargs * sizeof(char*)); //------------------------------------------------- //---Обойдем все аргументы------------------------- int param_count = 0; int pos = 0; for (int i = 0; i < numargs; i++) { Oid argtype = pl_struct->proargtypes.values[i]; char* value; Assert(argtypes[i] == pl_struct->proargtypes.values[pos]); type_tuple = SearchSysCache1(TYPEOID, ObjectIdGetDatum(argtype)); if (!HeapTupleIsValid(type_tuple)) elog(ERROR, "cache lookup failed for type %u", argtype); type_struct = (Form_pg_type)GETSTRUCT(type_tuple); fmgr_info_cxt(type_struct->typoutput, &(arg_out_func[i]), proc_cxt); ReleaseSysCache(type_tuple); value = OutputFunctionCall(&arg_out_func[i], fcinfo->args[i].value); //---------------------------------- //---Сохраним значения параметров args_arr.ArgName[i] = argnames[i]; args_arr.ArgValue[i] = value; args_arr.ArgType[i] = format_type_be(argtypes[i]); param_count++; } /* Тип возвращаемого значения */ prorettype = pl_struct->prorettype; ReleaseSysCache(pl_tuple); //---Запишем в структуру------------ length = strlen(format_type_be(prorettype)); input_info_struct->RetType = (char*)palloc((length + 1) * sizeof(char)); // Выделяем память для строки strcpy(input_info_struct->RetType, format_type_be(prorettype)); char* exec_result; //----------------------------------- //--Присоединяем библиотеку void* hlib = dlopen("libpl_cpl_sql_lib.so", RTLD_NOW | RTLD_GLOBAL); if (hlib) { Pl_Cpl_Sql_Call_Func ProcAdd = (Pl_Cpl_Sql_Call_Func)dlsym(hlib, "pl_cpl_sql_call_handler"); if (ProcAdd) { THandlerResultCallRec* result = ProcAdd(input_info_struct, &args_arr); // Вызов функции из DLL //---Освободим память dlclose(hlib); //---Определим тип сообщения if (result->OutType == 1) { //---Если была только ошибка ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("%s", result->Errors))); } if (result->OutType == 2) { //---Если было только сообщение (вывод сообщения уровня NOTICE) elog(NOTICE, "%s", result->Messages); } if (result->OutType == 3) { //---Если была ошибка и сообщение elog(NOTICE, "%s", result->Messages); ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("%s", result->Errors))); } //---Только если тип возвращаемого значения не void if (prorettype != VOIDOID) { //---Обработаем полученный результат length = strlen(result->Result); exec_result = (char*)palloc((length + 1) * sizeof(char)); // Выделяем память для строки strcpy(exec_result, result->Result); } } else { //---Освободим память dlclose(hlib); ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("ERR-01: Ошибка выполнения!"))); } } else { ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("ERR-02: Библиотека не найдена!"))); } //---Если нет возвращаемого значения if (prorettype == VOIDOID) { //---Выходим PG_RETURN_NULL(); } else { type_tuple = SearchSysCache1(TYPEOID, ObjectIdGetDatum(prorettype)); if (!HeapTupleIsValid(type_tuple)) elog(ERROR, "cache lookup failed for type %u", prorettype); pg_type_entry = (Form_pg_type)GETSTRUCT(type_tuple); result_typioparam = getTypeIOParam(type_tuple); fmgr_info_cxt(pg_type_entry->typinput, &result_in_func, proc_cxt); ReleaseSysCache(type_tuple); ret = InputFunctionCall(&result_in_func, exec_result, result_typioparam, -1); } } PG_CATCH(); { PG_RE_THROW(); } PG_END_TRY(); //---Возвращаем значение PG_RETURN_DATUM(ret); }Из переданных аргументов (PG_FUNCTION_ARGS) получаем код выполняемой процедуры/функции, значения параметров и тип возвращаемого значения. Подключаем библиотеку интерпретатора (libpl_cpl_sql_lib.so) и передаем ей эти данные на выполнение. Если текст не корректный – возвращаем ошибку, если все нормально – возвращаем результат выполнения и текст сообщения (если оно было). Передавать в процедуру/функцию можно только параметры следующих типов (postgreSQL): numeric, bigint, varchar, text, json, jsonb. При выполнении кода данные типы транслируются во внутренние типы интерпретатора.
-
Функция для выполнения кода анонимного блока — pl_cpl_sql_inline_handler.
//----------------------------------------------------------- //----------------------------------------------------------- //----------------------------------------------------------- // Реализация функции для выполнения анонимного блока do $$ begin end; $$ Datum pl_cpl_sql_inline_handler(PG_FUNCTION_ARGS) { LOCAL_FCINFO(fake_fcinfo, 0); InlineCodeBlock* codeblock = (InlineCodeBlock*)DatumGetPointer(PG_GETARG_DATUM(0)); PG_TRY(); { //--Присоединяем библиотеку void* hlib = dlopen("libpl_cpl_sql_lib.so", RTLD_NOW | RTLD_GLOBAL); if (hlib) { Pl_Cpl_Sql_inline_Func ProcAdd = (Pl_Cpl_Sql_inline_Func)dlsym(hlib, "pl_cpl_sql_inline_handler"); if (ProcAdd) { THandlerResultRec* result = ProcAdd(codeblock->source_text); // Вызов функции из DLL //---Освободим память dlclose(hlib); //---Определим тип сообщения if (result->OutType == 1) { //---Если была только ошибка ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("%s", result->Errors))); } if (result->OutType == 2) { //---Если было только сообщение (вывод сообщения уровня NOTICE) elog(NOTICE, "%s", result->Messages); } if (result->OutType == 3) { //---Если была ошибка и сообщение elog(NOTICE, "%s", result->Messages); ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("%s", result->Errors))); } } else { //---Освободим память dlclose(hlib); ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("ERR-01: Ошибка выполнения!"))); } } else { ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("ERR-02: Библиотека не найдена!"))); } } PG_CATCH(); { PG_RE_THROW(); } PG_END_TRY(); PG_RETURN_VOID(); }Из аргументов функции получаем выполняемый код. Подключаем библиотеку интерпретатора (libpl_cpl_sql_lib.so) и передаем ей текст на выполнение. Если при выполнении кода возникли сообщения или ошибки — то выводим их.
Для работы с БД из C-кода имеется SPI (Server Programming Interface). Это набор C-функций для работы с данными. Так как мой интерпретатор реализован в виде внешней библиотеки, то такой способ выполнения операций с данным не подходит. Был выбран способ, когда в момент выполнения кода интерпретатором происходит создание отдельного соединение к БД (как будто работа происходит внутри клиентского приложения) (рис.1). Это способ чем-то похож на работу с БД из PL/Java.
В общем смысле, хотя интерпретатор и вызывается из локальной БД, он к ней не привязан. В каталог, где находиться библиотека интерпретатора, помещается файл с параметрами соединения к БД (conn_params.json) примерно следующего содержания:
[ {"ConnType": "PSQL", "ConnName": "db_PG", "UserName": "username", "PassWord": "password", "HostName": "hostname", "DataBase": "database", "Port": "port" }, {"ConnType": "Oracle", "ConnName": "db_Oracle", "UserName": "username", "PassWord": "password", "HostName": "hostname", "DataBase": "database", "Port": "port" } ]Используя данные параметры можно подсоединиться к текущей БД.
На следующем этапе создаем три файла следующего содержания:
-
pl_cpl_sql_ext.control – (управляющий файл, определяющий основные свойства нового языка).
comment = 'PL/сPLSQL procedural language' default_version = '0.1' module_pathname = '$libdir/pl_cpl_sql_ext' relocatable = false schema = pg_catalog superuser = false -
pl_cpl_sql_ext—1.0.sql – (файл SQL, который создает объекты нового языка)
-- handler CREATE FUNCTION pl_cpl_sql_call_handler() RETURNS language_handler AS '$libdir/pl_cpl_sql_ext' LANGUAGE C; -- inline CREATE FUNCTION pl_cpl_sql_inline_handler(oid internal) RETURNS void AS '$libdir/pl_cpl_sql_ext', 'pl_cpl_sql_inline_handler' LANGUAGE C; -- validator CREATE FUNCTION pl_cpl_sql_validator(oid_ oid) RETURNS void AS '$libdir/pl_cpl_sql_ext', 'pl_cpl_sql_validator' LANGUAGE C; CREATE TRUSTED LANGUAGE pl_cpl_sql HANDLER pl_cpl_sql_call_handler INLINE pl_cpl_sql_inline_handler validator pl_cpl_sql_validator; COMMENT ON LANGUAGE pl_cpl_sql IS 'PL/cPLSQL procedural language'; -
makefile – (файл для сборки расширения)
MODULES = pl_cpl_sql_ext EXTENSION = pl_cpl_sql_ext DATA = pl_cpl_sql_ext--1.0.sql PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS)Далее выполняем команды
Make
Make install.
Для Alt Linux происходит компиляция созданного C-файла и его регистрация в БД PostgreSQL.
Собранную библиотеку интерпретатора (pl_cpl_sql_lib.dll для Windows, libpl_cpl_sql_lib.so для Linux) выкладываем в каталог, где он будет виден БД PostgreSQL.
Далее в DBeaver подключаемся к БД выполняем файл pl_cpl_sql_ext—1.0.sql.
Выполняем запрос – select * from pg_language.
Видим, что в перечне языков появился новый язык — pl_cpl_sql. Так как язык создан как TRUSTED, то создавать процедуры/функции на нем могут любые пользователи.
Проверка работоспособности интерпретатора.
Будем выполнять код, написанный на языке – pl_cpl_sql.
В базе данных создаем схему – test. В ней будем проводить тестирование.
-
Пример 1. (выполним анонимный блок)
Выполним анонимный блок, написанный на новом языке — pl_cpl_sql. Отправим post-запрос с помощью встроенного пакета http_io. В качестве языка указываем — LANGUAGE pl_cpl_sql.
do LANGUAGE pl_cpl_sql $$ declare http_req http_io.req; http_resp http_io.resp; begin http_req := http_io.begin_request('https://httpbin.org/post', 'POST'); http_io.set_header(http_req, 'Content-Type', 'application/json'); http_io.set_resp_encoding(http_req, 'UTF8'); http_io.write_text(http_req, '{"command":"FIND_INCOME_DOC", "body":{"filter":{"start_date":"2024-06-26T00:00:00","end_date":"2024-06-26T23:59:59"},"start_from":0,"count":100} }'); http_resp := http_io.get_response(http_req); dbms_output.put_line('HTTP response status code: ' || http_resp.status_code); dbms_output.put_line('HTTP response text: ' || http_resp.response_text); end; $$;Выполняем код в DBeaver.
В окне «Вывод» видим полученный результат. Процедура dbms_output.put_line выводит результат в тот же буфер, в который выводит и стандартный оператор raise notice.
Если из кода уберем строку — LANGUAGE pl_cpl_sql – то получим сообщение об ошибке
-
Пример 2. Создание функции на языке pl_spl_sql.
CREATE OR REPLACE FUNCTION test.test_json(p_int bigint, p_name text, p_json json) RETURNS text LANGUAGE pl_cpl_sql AS $function$ declare obj json := '{"main_obj": {"1": 123, "2": 456}}'; new_obj json; arr json := '[]'; begin dbms_output.put_line('1. obj = '||obj); ------------ -- Добавим две пары ключ-значение obj := json_io.add(obj, 'id', '1000', 'name', p_name); dbms_output.put_line('2. obj = '||obj); ------------ --Добавим новый подобъект new_obj := '{"dddd": 890}'; obj := json_io.add(obj, 'new_obj', new_obj); dbms_output.put_line('3. obj = '||obj); ------------ --В массив arr добавим новый элемент arr := json_io.add(arr, '', new_obj); dbms_output.put_line('4. arr = '||arr); ------------ --В массив arr добавим новый элемент arr := json_io.add(arr, '', '{"aaaa": 111}'); dbms_output.put_line('5. arr = '||arr); ------------ --Добавим массив в объект - obj obj := json_io.add(obj, 'rows', arr); obj := json_io.add(obj, 'input_obj', p_json); ------------ --Выведем результат на экран dbms_output.put_line('6. Результат: obj = '||json_io.format(obj)); ------------ --Вернем результат как значение функции return obj; END; $function$ Выполняем код в DBeaver.
Видим, что функция успешно создана.
Далее выполним вызов функции с параметрами:
SELECT test.test_json(12345,’Привет!’,'{«ID»: 123456789}’);
Видим, что PostgreSQL успешно выполнил наше SQL-выражение. Был возвращен результат. Все выданные функцией сообщения отобразились в окне «Вывод».
-
Пример 3. Работа с данными БД PostgreSQL.
Как уже было сказано ранее, интерфейс SPI (Server Programming Interface) здесь не поддерживается. Для работы с данными БД в любой SQL-инструкции (курсора, select/insert/update/delete-выражения) в обязательном порядке должна быть указана метка /*##db=dbname##*/, в которой указано название БД, для которой будет выполнятся данная конструкция. Параметры соединения указаны в файле — conn_params.json.
В общем смысле из одного кода может быть сделано несколько соединения к различным БД (Oracle, PostgreSQL), а также несколько соединений к одной и той же БД.
В этом примере создадим таблицу в БД, заполним ее данными и выведем эти данные на экран.
do LANGUAGE pl_cpl_sql $$ declare sql_stmt VARCHAR2; cursor cur_main_pg is select * from /*##db=db_PG##*/ test.categories ct order by ct.description; begin dbms_output.put_line('--Create table'); --Создадим тестовую таблицу EXECUTE /*##db=db_PG##*/ 'CREATE TABLE if not exists test.categories ( category_id smallint NOT NULL, category_name character varying(15) NOT NULL, description text );'; dbms_output.put_line('--Clear table'); -- Очистим таблицу с категориями delete /*##db=db_PG##*/ from test.categories; dbms_output.put_line('--Insert'); --Заполним таблицу данными sql_stmt := ' do $block$ begin INSERT INTO test.categories VALUES (1, ''Beverages'', ''Soft drinks, coffees, teas, beers, and ales''); INSERT INTO test.categories VALUES (2, ''Condiments'', ''Sweet and savory sauces, relishes, spreads, and seasonings''); INSERT INTO test.categories VALUES (3, ''Confections'', ''Desserts, candies, and sweet breads''); INSERT INTO test.categories VALUES (4, ''Dairy Products'', ''Cheeses''); INSERT INTO test.categories VALUES (5, ''Grains/Cereals'', ''Breads, crackers, pasta, and cereal''); INSERT INTO test.categories VALUES (6, ''Meat/Poultry'', ''Prepared meats''); INSERT INTO test.categories VALUES (7, ''Produce'', ''Dried fruit and bean curd''); INSERT INTO test.categories VALUES (8, ''Seafood'', ''Seaweed and fish''); end; $block$'; --выполним анонимный блок EXECUTE /*##db=db_PG##*/ sql_stmt; dbms_output.put_line('--Select data'); --Выведем на экран содержимое созданной таблицы for rec in cur_main_pg loop dbms_output.put_line(json_io.record_to_json(rec)); end loop; end; $$;Выполним указанный код.
Видим, что код выполнен успешно. Таблица в БД создана и заполнена данными. Курсор по данным из таблицы вывел данные на экран в виде json.
-
Пример 4. Парсинг новостного сайта.
В данном примере мы, с помощью встроенного пакета http_io, загрузим содержимое сайта. Далее, с помощью другого пакета — html_io, распарсим его содержимое и выведем на экран перечень новостей.
do LANGUAGE pl_cpl_sql $$ declare http_req http_io.req; http_resp http_io.resp; html_doc html; begin dbms_output.put_line('Парсинг новостного сайта - https://www.yarnews.net/news/bymonth/2024/12/0/'||chr(10)); http_req := http_io.begin_request('https://www.yarnews.net/news/bymonth/2024/12/0/', 'GET'); http_io.set_header(http_req, 'Content-Type', 'html/text'); http_resp := http_io.get_response(http_req); html_doc := http_resp.response_text; for i in 1..html_io.get_node_count(html_doc) loop /* dbms_output.put_line('level='||html_io.get_node_prop(html_doc, i, 'level')|| ', type='||html_io.get_node_prop(html_doc, i, 'type')|| ', path='||html_io.get_node_prop(html_doc, i, 'path')|| ', name='||html_io.get_node_prop(html_doc, i, 'name')|| ', val='||html_io.get_node_prop(html_doc, i, 'value')|| ', attrs='||html_io.get_node_all_attr(html_doc,i) ); */ if (html_io.get_node_prop(html_doc, i, 'level') = 12) and ((instr(html_io.get_node_prop(html_doc, i, 'path'),'/a') > 0) or (instr(html_io.get_node_prop(html_doc, i, 'path'),'/h3') > 0) or (instr(html_io.get_node_prop(html_doc, i, 'path'),'/span') > 0) ) then if html_io.get_node_prop(html_doc, i, 'value') != '' then dbms_output.put_line(html_io.get_node_prop(html_doc, i, 'value')); end if; end if; end loop; end; $$;Выполним указанный код.
Итого:
-
Удалось успешно встроить интерпретатор в PostgreSQL. Это было нелегко для человека, не программирующего на Си. К тому же C-файл расширения для PostgreSQL должен быть написан по определенным канонам и поддерживать нужную структуру.
-
Интерпретатор показал себя хорошо. Его возможностей хватило для встраивания в PostgreSQL. Был получен новый язык программирования (pl_cpl_sql), на котором можно писать процедуры/функции внутри БД, в дальнейшем используя их в коде языков SQL и PLpgSQL. Язык содержит в себе функциональность для выполнения http/https-запросов (как в расширении http), доступ на уровне языка к другим базам данных (PostgreSQL и Oracle) (как в расширениях dblink, oracleFDW), позволяет производить разбор XML и HTML-документов.
ссылка на оригинал статьи https://habr.com/ru/articles/882596/
Добавить комментарий