Описание
Инструмент PGXLS - SQL схема с набором хранимых процедур для создания файлов(тип bytea) в формате электронной таблицы Excel(.xlsx). Реализована зависимость форматирования от типа данных, преобразование результатов SQL-запроса в лист с автоформатированием и прочее (более подробно в разделе Ключевые особенности)
Принцип использования
На процедурном языке PL/pgSQL создается функция, которая выполняет следующее:
1. Определяет переменную типа pgxls.xls
2. Заполняет документ через вызов хранимых процедур
3. Формирует из переменной файл и возвращает его
Также есть готовая функция, формирующая файл по SQL-запросу
Основные процедуры
| pgxls.create | — | создание документа |
| pgxls.add_row | — | добавление строки |
| pgxls.put_cell | — | заполнение ячейки: установка значения и форматирование |
| pgxls.get_file | — | формирование и получение файла |
| pgxls.get_file_by_query | — | создание файла по SQL-запросу (функция-обертка) |
| pgxls.save_file_by_query | — | сохранение файла по SQL-запросу на сервере (выполняется под суперпользователем) |
Более подробно на странице Документация
Получение и сохранение файлов в SQL-менеджере
-- 1. Создаем и получаем файл(bytea) по SQL-запросу select pgxls.get_file_by_query('select * from pg_tables'); -- 2. Сохраняем Excel-файл на сервере по SQL-запросу call pgxls.save_file_by_query('/tmp/top_relations_by_size.xlsx', 'select oid,relname,pg_relation_size(oid) from pg_class order by 3 desc limit 10'); -- 3. Создаем функцию, возвращающую файл(bytea) create or replace function excel_top_relations_by_size() returns bytea language plpgsql as $$ declare rec record; xls pgxls.xls; begin -- Создаем документ, в параметрах указываем ширины и заголовки столбцов xls := pgxls.create(array[10,80,15], array['oid','Name','Size, bytes']); -- В цикле выбираем данные по запросу for rec in select oid,relname,pg_relation_size(oid) size from pg_class order by 3 desc limit 10 loop -- Добавляем строку call pgxls.add_row(xls); -- В ячейки устанавливаем данные из запроса call pgxls.put_cell(xls, rec.oid); call pgxls.put_cell(xls, rec.relname); call pgxls.put_cell(xls, rec.size); end loop; -- Возвращаем файл(bytea) return pgxls.get_file(xls); end $$; -- Получаем файл select excel_top_relations_by_size();
Сохранение файлов в командной строке
Утилита командной строки psql возвращает bytea в hex-формате, поэтому требуется обратное преобразование.
При использовании языков программирования (например, Java, JavaScript, Python и т.п) преобразование из hex не требуется
Linux
#!/bin/bash
# Примеры сохранения файлов в командной строке Linux
#
# Для преобразования hex в binary используется утилита xxd
#
# При запуске psql не на сервере необходимо установить postgres client и указать параметры подключения через URI:
# psql postgres://[ПОЛЬЗОВАТЕЛЬ]:[ПАРОЛЬ]@[СЕРВЕР]/[БАЗА_ДАННЫХ] -Aqt -c "...
# 1. Создание файла по SQL-запросу
psql -Aqt -c "select pgxls.get_file_by_query('select * from pg_tables')" | xxd -r -ps > pg_tables.xlsx
# 2. Сохраняем Excel-файл на сервере по SQL-запросу
psql -c "call pgxls.save_file_by_query('/tmp/top_relations_by_size.xlsx', 'select oid,relname,pg_relation_size(oid) from pg_class order by 3 desc limit 10')"
# 3. Сохранение файла из SQL-функции
psql -Aqt -c "select excel_top_relations_by_size()" | xxd -r -ps > top_relations_by_size.xlsx
Windows
rem Примеры сохранения файлов в командной строке Windows
rem Для преобразования hex в binary используется утилита certutil и промежуточный временный файл
rem При запуске psql не на сервере необходимо установить postgres client и указать параметры подключения через URI:
rem psql postgres://[ПОЛЬЗОВАТЕЛЬ]:[ПАРОЛЬ]@[СЕРВЕР]/[БАЗА_ДАННЫХ] -Aqt -c "...
rem 1. Создание файла по SQL-запросу
psql -Aqt -c "select pgxls.get_file_by_query('select * from pg_tables')" -o hex.tmp
certutil -decodehex -f hex.tmp pg_tables.xlsx
rem 2. Сохраняем Excel-файл на сервере по SQL-запросу
psql -c "call pgxls.save_file_by_query('/tmp/top_relations_by_size.xlsx', 'select oid,relname,pg_relation_size(oid) from pg_class order by 3 desc limit 10')"
rem 3. Сохранение файла из SQL-функции
psql -Aqt -c "select excel_top_relations_by_size()" -o hex.tmp
certutil -decodehex -f hex.tmp top_relations_by_size.xlsx
Большие файлы
- данные построчно записываются во временную таблицу, что снимает требование к памяти. Для получения файла частями реализована отдельная функцияАвтоформатирование
- для каждого столбца форматирование настраивается в зависимости от типа данныхSQL-запросы
- возможно добавлять лист с результатами SQL-запросаСтили
- для столбцов и ячеек поддерживается установка формата, шрифта, границы, заливки и выравниванияНастройка печати
- для каждого листа можно установить формат и ориентацию бумаги, заголовок, повторяющиеся строки ("шапку" таблицы) и отступыПараллельность
- возможно создавать в одной сессии несколько файлов параллельно
PGXLS поддерживает генерацию отчетов искусственным интеллектом.
Из общедоступных нейросетей (без регистрации и VPN) сложный и качественный код создает Qwen, для простых отчетов можно использовать Google AI
Правила при написании промптов для ИИ
- явно указать ссылку на документацию https://pgxls.org/ru/documentation/
- описать структуру таблиц (например, в виде DDL-команд) или явно перечислить поля в SQL-запросе, исключение составляют системные представления (ИИ знает их структуру)
Рекомендуется использовать интерактивные примеры как шаблоны для написания промптов - можно отредактировать и спросить ИИ
Пример промпта создания отчета по системным представлениям
Пример промпта для SQL-запроса с форматированием
Пример промпта выгрузки таблиц с параметром
Большие проекты
- применение в больших проектах не рекомендуется, т.к. обработка бинарных данных в SQL очень неэффективна, отчеты формируются долго и возникает значительное потребление CPU. В больших проектах отчеты должна формировать backend-часть приложенияСжатие не поддерживается
- данные копируются, одинаковые строки дублируютсяНеоптимальная высота строк
- высота строк рассчитывается с допущениями и может быть неоптимальной
Обзорное видео размещено на YouTube