Получаем структурированные данные из PostgreSQL

Приходилось ли Вам когда-нибудь ломать голову над тем как вернуть из хранимой процедуры PostgreSQL сложную конструкцию с хитрой иерархией, и при этом не писать в приложении огромный костыль для парсинга древовидной структуры, утолканной силами разработчика в плоскую реляционную таблицу? Если ответ положительный, то прошу под кат…


Доброго времени суток!
Всем известно что результатом запроса к реляционной СУБД является таблица. Табличный контейнер, в виду своей жесткой структуры, налагает ряд ограничений на представляемые данные. Для примера, результатом выборки, имеющей в своем составе объединения (join), является денормализованная структура, скрывающая изначальную топологию данных, что создает сложности в обработке такого результата приложением. С увеличением количества join'ов ситуация только ухудшается.
Но не все так плохо, как может показаться, потому что разработчик PostgreSQL имеет в своем распоряжении гибкие структуры данных, способные инкапсулировать dataset любой сложности. Речь о массивах (arrays) и структурах (record, composite type).
Результат вышеописанной выборки с объединениями, превращается в элегантную, строго иерархичную конструкцию вида:

(
table1_column1 int,
table1_column2 varchar,
table1_column3 numeric,
table2_columns t2_columns[]
)


где t2_columns это структура вида
(
table2_column1 double precision,
table2_column2 timestamp
)


Таким образом путем увеличения уровня вложенности можно передавать сколь угодно сложные иерархические конструкции.
В моем случае приложение было написано на PL/pgSQL и в качестве интерфейса предоставляло хранимые процедуры, возвращающие те самые сложно организованные данные. Собственно эта статья о подходе к разбору сериализованных PostgreSQL dataset'ов.

Непосредственно от разбора сериализованных в строку PostgreSQL данных (та самая строка, что получается путем приведения записи к varchar) было решено отказаться сразу, потому что средств к такому парсингу не имеется нигде кроме ядра postgres'а.
Не заставившая себя долго ждать идея изменить представление данных, чтобы использовать более стандартизованные средства разбора, получила развитие.
Потратив некоторое время на поиск готового решения, и отбросив таких кандидатов как встроенные типы xml и hstore, я пришел к выводу (возможно, ошибочному) что подходящего по всем параметрам способа передачи данных в приложение не имеется.

В качестве представления данных, для будущего велосипеда, был избран JSON (из соображений компактности и текстовости), а способом реализации стала native библиотека для PostgreSQL (на pure C). Не буду вдаваться во внутреннее устройство получившегося инструмента, тем более что оно довольно простое и любой желающий без проблем сможет разобраться. Рассмотрим бибилиотеку с утилитарной точки зрения. В качестве интерфейса предоставляется набор функций.

Примеры использования:

Сериализуем структуру:

select to_json( row( 10, 'Some text', 12.5, row( 'text in nested record', array[ 1, 2, 3 ] )::text_and_array, array[ 'array', 'of', 'text' ] ) )

Результат запроса:

{"f1":10,"f2":"Some text","f3":12.5,"f4":{"str":"text in nested record","arr":[1,2,3]},"f5":["array","of","text"]}

Сериализуем dataset'ы:

select json_agg( q.*, 'json_field_name1' ), json_agg_plain( q.*, 'json_field_name2' ) from ... as q;

Результат запроса:

{"json_field_name1":[{ ... },{ ... }, ...]}		"json_field_name2":[{ ... },{ ... }, ...]

Позже библиотека была расширена функционалом, позволяющим выполнять обратную операцию разбора JSON и заполнения полей структур.

Десериализуем структуры и массивы:

 select from_json( 'some_record_type', '{"field1":"some text","field2":123,"field3":["this","is","array","of","text"]}' );
 select arr_from_json( 'some_type[]', '[{"this is array of"},{"records with one field"}]' );

Результаты запросов:
("some text",123,"{\"this\",\"is\",\"array\",\"of\",\"text\"}")

{("this is array of"),("records with one field")}


Описанный сериализатор можно использовать совместно с любой средой исполнения, содержащей средства разбора JSON (у нас это C++ и PHP). Benchmark'и показывают производительность, сравнимую со встроенным в PostgreSQL сериализатором.

Спасибо за внимание, замечания и конструктивная критика приветствуются.

Ссылка на библиотеку