Community mailing list archives

Creating read-only models from SQL functions

Savoir-Faire Linux, Vince Vinet
- 10/29/2014 10:21:42

tl;dr : I want to know if it would be possible to have an orm.Model
query its data from a SQL function instead of a view/table. Can you
see any problems that it would create?

Full verison: while modifying the production analysis report,
I've stumbled across the problem that dates extracted from stock
moves do not consider the user time zone. A simple example would
be a production made at 10 PM on Dec 31st, which is stored in UTC
as Jan 1st at 3 AM. The date is then in the wrong year, and no amount
of playing with the domain/query will give me back the missing info.

One of the ways to solve this would be to replace the report view by
a function that returns a table.
CREATE FUNCTION production_analysis_report(tzname text)
RETURNS TABLE (id int, ...) AS $$
to_char(timezone(tzname, timezone('UTC'::text,,
            'YYYY-MM-DD'::text) as creation_day,
FROM stock_move s ...

Passing in the timezone allows converting any dates in the view to
the local time before any extraction. In this case timezone has to be
used twice: once to say the UTC timestamp is in UTC, and once to
convert it to the user timezone.

To do this, I need to be able to have an orm.Model perform queries
like "SELECT ... FROM function_name(params)" .I would like to
know if it seems like a reasonable thing to do, or if there is a better
way I am overlooking.

Vincent Vinet