A system that allows business users to easily view and query various management information.
What is your problem ? A description of the problem, not the solution you are proposing.
I find myself usefully populating tables using simple SELECT * FROM table but then invariably come back to improve the formatting of the columns - this leads to me having to list all the columns in the expression (and revisit them when I update the underlying database tables) in order to format them. There are a number of common patterns - the most frequent being the formatting of timestamps for created/updated/deleted e.g TO_CHAR(updated, 'DD/MM/YYYY HH:MI:SS') AS updated. There are also other patterns that appear often like building markdown links.
What are you currently doing ? Since your solution is not implemented in SQLPage currently, what are you doing instead ?
Explicitly listing and maintaining all columns in the table query so that I can format them using TO_CHAR or similar.
Describe the solution you'd like
A method for defining a column formatter and then applying it automatically to a query. It might look something like:
SELECT
'table' AS component
,'name' AS markdown
,'pretty-timestamps' AS formatter
Describe alternatives you've considered
A few alternatives I've considered:
suck it up and keep going :)
add a pre-compiler that processes the .sql files to expand the SELECT * queries and add the appropriate formatting options
try to minimise the noise by creating sql functions to perform the formatting logic in a single place
Additional context
There seem to be two prevalent patterns - one is simply by type, e.g. pretty print all timestampz, the other being by name and type, e.g. make markdown links from all bigint columns entitled customer_id. From a scope point of view I could see this being useful to apply to the system as a whole or in specific contexts (e.g. specific query or by path).
A system that allows business users to easily view and query various management information.
I find myself usefully populating tables using simple
SELECT * FROM tablebut then invariably come back to improve the formatting of the columns - this leads to me having to list all the columns in the expression (and revisit them when I update the underlying database tables) in order to format them. There are a number of common patterns - the most frequent being the formatting of timestamps for created/updated/deleted e.gTO_CHAR(updated, 'DD/MM/YYYY HH:MI:SS') AS updated. There are also other patterns that appear often like building markdown links.Explicitly listing and maintaining all columns in the table query so that I can format them using
TO_CHARor similar.Describe the solution you'd like
A method for defining a column formatter and then applying it automatically to a query. It might look something like:
Describe alternatives you've considered
A few alternatives I've considered:
.sqlfiles to expand theSELECT *queries and add the appropriate formatting optionsAdditional context
There seem to be two prevalent patterns - one is simply by type, e.g. pretty print all
timestampz, the other being by name and type, e.g. make markdown links from allbigintcolumns entitledcustomer_id. From a scope point of view I could see this being useful to apply to the system as a whole or in specific contexts (e.g. specific query or by path).