PGXLS
Report Building and Export to Excel from PostgreSQL

Description

Usage method

Basic procedures

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

#!/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

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

• Large files

  - data is inserted row by row into a temporary table, which does not require memory Separate function is implemented to get large file

• Auto-format

  - for each column, a format is configured depending on data type

• SQL queries

  - it is possible to add sheet with the results of SQL query

• Styles

  - for columns and cells, support format, font, border, fill and alignment

• Print setup

  - for each sheet, the paper format and orientation, title, repeating rows (table header) and margins are specified

• Parallelism

  - it is possible to create several files in parallel in one session

Rules for writing AI prompts

• Explicitly provide a link to the documentation via https://pgxls.org/documentation/ or download the file pgxls-documentation.html
• Describe the table structure (e.g., using DDL commands) or explicitly list the fields in the SQL query, with the exception of system views (the AI already knows their structure)

Example prompt for creating a report on system views

Example prompt with formatting and complex logic

Example prompt for exporting large tables with a parameter

• Large projects - processing binary data in SQL is inefficient: reports take a long time to generate, and CPU consumption spikes significantly. As workloads grow, it is recommended to install the commercial PGXLS Highload Plugin, which shifts computations to a lower level (C language) and allows report generation on a replica

• Compression not supported

  - data copied, identical lines duplicated

• Row height not optimal

  - row height calculated with assumptions and may be not optimal

Advantages

• Execution on replicas — eliminating temporary tables allows you to generate reports on a standby replica
• Performance boost — actual report generation time (excluding data query) is reduced by 3–5 times

Architecture and Implementation

Engineering Manifesto of the Commercial Pack

• Full functionality in open source — the paid pack is created solely for performance optimization under extreme loads: it improves CPU utilization, shifts heavy tasks to a replica or a separate process, and reduces disk subsystem load (IOPS)
• Forward and backward compatibility — highload plugins do not alter function signatures and preserve the original application API. You can opt out of the plugins at any moment, seamlessly rolling back to the basic lightweight version
• No artificial limits — the basic version is free and has no limits on data volume, number of sessions, or similar constraints
• Task-focused purchasing — each plugin can be acquired separately and strictly within the scope of technical support, following workload and general task analysis
• Air-gapped security — complete autonomy: no cloud computing, no network license checks, and no external neural network models
• Giving up lightweight nature — the binary plugin requires high engineering qualifications, which is fully justified by the scale of the tasks being solved
• Source code delivery — the plugin's source code is provided for an additional fee for customization under a limited license
• Independent implementation — a detailed description of the architecture allows you to develop the plugin yourself