pg_spreadsheetml

Exports an SQL query result into Microsoft Excel format.
pl/pgsql function returns SpreadsheetML (XML format for storing Microsoft Excel spreadsheets) as setof text.

The prototype of the function is as follows:

FUNCTION pg_spreadsheetml(arg_query text, arg_parameters json DEFAULT '{}'::json)
 RETURNS SETOF text
 LANGUAGE plpgsql SECURITY DEFINER

arg_query is parameterised by plain text susbtitution (macro expansion).
Macro parameter placeholders are defined as valid uppercase identifiers with two underscores as prefix and suffix, i.e. __NUMBER_OF_DAYS__, __COST__, etc. See the example SQL-only and PHP CLI scripts.

Optional arg_parameters is JSON with parameters' names/values, e.g., {"number_of_days":"7 days", "cost":15.00}. Parameter names are K&R case-insensitive identifiers.

Hyperlinks: Cell values that match this regex pattern ^#(.+)##(.+)$, i.e. #value##URL will be presented as hyperlinks.

Note: pg_spreadsheetml is injection prone and therefore it must be declared as a security definer owned by a limited user.

Note: The example runs against the popular DVD rental sample database.