PGXLS — Экспорт в Excel из PostgreSQL
Более подробно инcтрумент описан на отдельном сайте pgxls.org
Инструмент 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_cellvalue(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