PGXLS — Export to Excel from PostgreSQL
Tool is described in more detail on a separate website pgxls.org


Description

Tool PGXLS is schema with stored procedures for creating files (bytea type) in Excel format (.xlsx). Implemented dependence format on data type, conversion SQL query into sheet with autoformat and more (for more details, see Important qualities)

Usage method


PL/pgSQL function is created that does:
1. Defines variable of type pgxls.xls
2. Sets document cells via stored procedure calls
3. Builds file from varible and returns it

There is also function that builds file by SQL query

Basic procedures


pgxls.create -create document
pgxls.add_row -add row
pgxls.put_cell -fill cell: set value and format
pgxls.get_file -build and get file
pgxls.get_file_by_query -create file by SQL query (wrapper)
pgxls.save_file_by_query -save file by SQL query on server (call as superuser)

More details on page Documentation


Simple examples

Get and save files in SQL manager
-- 1. Create and get file (bytea) by SQL query
select pgxls.get_file_by_query('select * from pg_tables');

-- 2. Save Excel file on server by SQL query
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. Create function that returns file (bytea)
create or replace function excel_top_relations_by_size() returns bytea language plpgsql as $$
declare 
  rec record;
  xls pgxls.xls; 
begin
  -- Create document, specify widths and captions of columns in parameters
  xls := pgxls.create(array[10,80,15], array['oid','Name','Size, bytes']);
  -- Select data in loop
  for rec in
    select oid,relname,pg_relation_size(oid) size from pg_class order by 3 desc limit 10    
  loop
    -- Add row
    call pgxls.add_row(xls);
    -- Set data from query into cells
    call pgxls.put_cell(xls, rec.oid);      
    call pgxls.put_cell(xls, rec.relname);   
    call pgxls.put_cell(xls, rec.size);
  end loop;  
  -- Returns file(bytea)
  return pgxls.get_file(xls);      
end
$$;
-- Get file
select excel_top_relations_by_size();


Save files on command line

The psql command line utility returns bytea in hex format, so the reverse conversion is required.
When using programming languages​(such as Java, JavaScript, Python, etc.), conversion from hex is not required

Linux
#!/bin/bash
# Examples of saving files in Linux command line

# To convert hex to binary, use xxd utility

# When running psql on non-server, install postgres client and specify connection parameters via URI:
# psql postgres://[USERNAME]:[PASSWORD]@[SERVER]/[DATABASE] -Aqt -c "...

# 1. Create file by SQL query
psql -Aqt -c "select pgxls.get_file_by_query('select * from pg_tables')" | xxd -r -ps > pg_tables.xlsx

# 2. Save Excel file on server by SQL query
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. Save file from SQL function
psql -Aqt -c "select excel_top_relations_by_size()" | xxd -r -ps > top_relations_by_size.xlsx

Windows
rem Examples of saving files in Windows command line

rem To convert hex to binary, use certutil utility and temporary file

rem When running psql on non-server, install postgres client and specify connection parameters via URI:
rem psql postgres://[USERNAME]:[PASSWORD]@[SERVER]/[DATABASE] -Aqt -c "...

rem 1. Create file by SQL query
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. Save Excel file on server by SQL query
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. Save file from SQL function
psql -Aqt -c "select excel_top_relations_by_size()" -o hex.tmp 
certutil -decodehex -f hex.tmp top_relations_by_size.xlsx