PGXLS — Экспорт в Excel из PostgreSQL
Более подробно инcтрумент описан на отдельном сайте pgxls.org


Описание

Инструмент PGXLS - схема с набором хранимых процедур для создания файлов(тип 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