Здравствуйте! Меня зовут Алексей Калинин. Это вторая моя статья, посвященная разработанному мною Интерпретатору. На просторах интернета мне встретилась вот эта интересная статья, в которой автор описал возможности расширения функциональности БД 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-документов.
Автор: ank_75
