dynamic column formatting in sql - and a backend to store the formatting

Question

Hi all,

I'm trying to create a system in Python in which one can select a number of rows from a set of tables, which are to be formatted in a user-defined way. Let's say the table a has a set of columns, some of which include a date or timestamp value. The user-defined format for each column should be stored in another table, and queried and applied on the main query at runtime.

Let me give you an example: There are different ways of formatting a date column, e.g. using

SELECT to_char(column, 'YYYY-MM-DD') FROM table;

in PostgreSQL. For example, I'd like the second parameter of the to_char() builtin to be queried dynamically from another table at runtime, and then applied if it has a value.

Reading the definition from a table as such is not that much of a problem, rather than creating a database scheme which would receive data from a user interface from which a user can select which formatting instructions to apply to the different columns. The user should be able to pick his set of columns to be included in his query, as well as his user defined formatting for each column.

I've been thinking about doing this in an elegant and efficient way for some days now, but to no avail. Having the user put in his desired definition in a text field and including it in a query would pretty much generate an invitation for sql injection attacks (although I could use escape() functions), and storing every possible combination doesn't seem feasible to me either.

I'd be very glad if you guys could help me out on this. Since this is my first question on SO, please advise whether I should rephrase it or you need more information, or I did something else wrong.

Thank you very much. Thomas

Answer 1

Seems to me a stored procedure or a sub-select would work well here, though I haven't tested it. Let's say you store a date_format for each user in the users table.

SELECT to_char((SELECT date_format FROM users WHERE users.id=123), column) FROM table;

Your mileage may vary. Google is your friend.

Answer 2

Pull the dates out as Unix timestamps and format them in Python:

SELECT DATE_PART('epoch', TIMESTAMP(my_col)) FROM my_table;

my_date = datetime.datetime.fromtimestamp(row[0]) # Or equivalent for your toolkit

I've found a couple of advantages to this approach: unix timestamps are the most space-efficient common format (this approach is effectively language neutral) and because the language you're querying the database in is richer than the underlying database, giving you plenty of options if you start wanting to do friendlier formatting like "today", "yesterday", "last week", "June 23rd".

I don't know what sort of application you're developing but if it's something like a web app which will be used by multiple people I'd also consider storing your database values in UTC so you can apply user-specific timezone settings when formatting without having to consider them for all of your database operations.