After brief info about F() I would like to show you Func(), which I find really sexy way to execute database specific functions wihout need of using extra or even stranger raw.
The classes documented below provide a way for users to use functions provided by the underlying database as annotations, aggregations, or filters in Django. Functions are also expressions, so they can be used and combined with other expressions like aggregate functions.
An F() object represents the value of a model field or annotated column. It makes it possible to refer to model field values and perform database operations using them without actually having to pull them out of the database into Python memory.
When Django encounters an instance of F(), it overrides the standard Python operators to create an encapsulated SQL expression;
F() can be used on QuerySets of object instances, with update(). This reduces the two queries we were using before - the get() and the save() - to just one.
F() therefore can offer performance advantages by: getting the database, rather than Python, to do work reducing the number of queries some operations require
Another useful benefit of F() is that having the database - rather than Python - update a field’s value avoids a race condition.
Django supports the use of addition, subtraction, multiplication, division, modulo, and power arithmetic with F() objects, both with constants and with other F() objects. Instead, Django uses the F() object to generate a SQL expression that describes the required operation at the database level.
F() can be used to create dynamic fields on your models by combining different fields with arithmetic
Func() expressions are the base type of all expressions that involve database functions
A Value() object represents the smallest possible component of an expression: a simple value. When you need to represent the value of an integer, boolean, or string within an expression, you can wrap that value within a Value().
Using postgres regexp_replace for creating annotated version of field with escaping certain chars
(in this case chars not allowed as windows file names:
*"/:;|=><,|) and having annotated save name to our model. From postgres documentation we can find out, that this db function usage should looks like this:
regexp_replace(string text, pattern text, replacement text [, flags text])
Example of using
regexp_replace from inside of Django by cool
We pass to func:
F() with name of certain database field
Value() with needed args, that is: pattern text, replacment text, g flag saying that we want replace each occurence (not only the first one)
kwarg function specifying database function we want execute on F() with args
Using postgres array_length for annotatting model with length of its array field to sort by this length. ArrayField can be in fact grid - array of arrays, so when you use array_length you have to provide which row. I am using 1-dim array, so I provide 1.
Build own library on top of database functions
You can override djangos Func and provide own logic over some db function, which can be then easily used in your application.
Access to powerfull functions offered by Postgres. http://www.postgresql.org/docs/9.1/static/functions.html