PGXLS — Export to Excel from PostgreSQL
Tool is described in more detail on a separate website pgxls.org
Tool PGXLS is SQL 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
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