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


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, column) FROM table;

Your mileage may vary. Google is your friend.

Hi patcoll, thanks for your answer. That actually looks really promising. I'll give it a try. What remains is the problem of storing a different number of columns with different formats for each user.

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.

Chris Adams
Hi Chris, thanks for your answer. I'm pretty sure PostgreSQL is actually capable of storing timestamps with time zone, which would as well create a universal format for storing them. Besides, 'today', 'yesterday', 'tomorrow' etc. are all supported date input types in PostgreSQL. My main problem is still to store and retrieve the format definition within a table.
Okay - I usually recommend leaving formatting out of the database but otherwise it sounds like patcoll's suggestion is closer to what you want.
Chris Adams