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