With Django's ORM, how do you expose a database function it lacks by subclassing Func, and what pitfalls come with doing it?

seniorshould knowfreq 32%

answer

function and template attributes
arity and output_field
as_<vendor>() overrides
strings are column references
keyword extras are interpolated

basics

~10 s

Subclass django.db.models.Func, set function (and optionally template, arity, output_field), and use it in annotate(), filter() or order_by(). Pitfalls: string arguments mean columns, keyword extras are pasted into SQL unescaped, and mixed types need output_field.

solid answer

~40 s

A Func subclass sets function = "AGE", keeps the default template = "%(function)s(%(expressions)s)" or overrides it, and optionally arity (Django raises TypeError on the wrong argument count) and output_field so results convert to the right Python type. Per-backend SQL goes in as_postgresql(), as_sqlite() and so on, which call super().as_sql(..., function=..., template=...). The pitfalls: positional strings are treated as column names and wrapped in F(), so literals need Value(); keyword arguments (**extra) are interpolated into the SQL string, not bound, so user input must go in as a positional expression; a literal % in template must be written %%%% because the string is formatted twice; and arguments of different field types raise FieldError: Expression contains mixed types unless you set output_field.

A `Func` subclass sets `function = "AGE"`, keeps the default `template = "%(function)s(%(expressions)s)"` or overrides it, and optionally `arity` (Django raises `TypeError` on the wrong argument count) and `output_field` so results convert to the right Python type. Per-backend SQL goes in `as_postgresql()`, `as_sqlite()` and so on, which call `super().as_sql(..., function=..., template=...)`. The pitfalls: positional **strings are treated as column names** and wrapped in `F()`, so literals need `Value()`; **keyword arguments (`**extra`) are interpolated into the SQL string**, not bound, so user input must go in as a positional expression; a literal `%` in `template` must be written `%%%%` because the string is formatted twice; and arguments of different field types raise `FieldError: Expression contains mixed types` unless you set `output_field`.

code

python · 15 lines

python

from django.db.models import Func, Value


class Position(Func):
    """POSITION(substring IN column); substring is bound, not interpolated."""

    function = "POSITION"
    arg_joiner = " IN "
    arity = 2

    def __init__(self, expression, substring):
        super().__init__(Value(substring), expression)


# Employee.objects.annotate(pos=Position("title", user_text)).filter(pos__gt=0)

go deeper

for a junior

Recall that you subclass Func and set function; the result is used in annotate() like built-in functions.

for a middle

Explain template, arity, output_field and as_vendor overrides, and why strings passed positionally become column references.

for a senior

Show the injection trap in keyword extras and the mixed-types error, and decide when a Func should replace scattered RawSQL.

for a principal

Judge how much vendor-specific SQL a codebase should wrap in shared expressions versus keeping portable, and who owns that library.

Why `Func` exists

Django ships a large library of database functions (django.db.models.functions: Lower, Coalesce, Extract, Cast, Concat, Greatest, UUID7 in 6.1, and many more). When the SQL function you need is missing, you do not have to drop to raw SQL: subclass Func and you get a reusable, composable expression that works in annotate(), filter(), order_by(), update() and inside other expressions.

The moving parts

Attribute / method	Default	Purpose
`function`	`None`	SQL function name, fills `%(function)s`
`template`	`"%(function)s(%(expressions)s)"`	the SQL shape
`arg_joiner`	`", "`	joins the compiled arguments
`arity`	`None`	if set, a different argument count raises `TypeError`
`output_field`	inferred	Django field type of the result
`as_<vendor>()`	—	per-backend override, e.g. `as_postgresql`, `as_mysql`

A PostgreSQL age() wrapper:

python

from django.db.models import DurationField, Func


class Age(Func):
    function = "AGE"
    arity = 2
    output_field = DurationField()

Employee.objects.annotate(tenure=Age(Now(), "hired_at")) compiles on PostgreSQL to AGE(STATEMENT_TIMESTAMP(), "hr_employee"."hired_at") (that is how Now() renders there), and tenure comes back as a timedelta because output_field is a DurationField.

Backend-specific SQL

When engines disagree on the name or syntax, implement as_<vendor>() and call the base with overrides:

Define as_sql() behaviour through the class attributes for the common case.
Add def as_mysql(self, compiler, connection, **extra_context): returning super().as_sql(compiler, connection, function="...", template="...", **extra_context).
Django picks as_<connection.vendor>() automatically when compiling for that backend.

Django's own ConcatPair does exactly this to use CONCAT_WS on MySQL.

Pitfalls interviewers probe

Strings are column references. Positional arguments that are strings are wrapped in F(); other Python values in Value(). Age(Now(), "2020-01-01") looks up a field called 2020-01-01 and fails. Wrap literals yourself: Value("2020-01-01").
Keyword extras are not parameters. Any **extra passed to __init__ (and **extra_context in as_sql) is string-interpolated into the template. The docs' own example is a Position function with template = "%(function)s('%(substring)s' in %(expressions)s)": passing a user-supplied substring as a keyword is SQL injection. The fix passes it as a positional expression — super().__init__(Value(substring), expression) with arg_joiner = " IN " — so it compiles to a bound parameter. Wrap it in Value(): a bare positional string would be read as a column name.
output_field for mixed inputs. If the arguments resolve to different field classes, Django raises FieldError: Expression contains mixed types: ... You must set output_field. If nothing can be inferred you get "Cannot resolve expression type, unknown output_field". Setting output_field on the class or per call removes the guesswork.
Percent signs. The template is formatted in as_sql() and then again by the driver with query params, so a literal % (for example in strftime('%W', ...)) must be quadrupled to %%%%.
Aggregates are different. A function that collapses rows should subclass Aggregate so Django adds a GROUP BY; a plain Func is evaluated per row.
Lookups and transforms are a separate API. Registering a function so it can be used as field__myfunc in a filter keyword is done with Transform/Lookup registration, not Func alone.

Using the function once it exists

A Func subclass is a first-class expression, so it composes with the rest of the QuerySet API:

Annotate and filter: Employee.objects.annotate(tenure=Age(Now(), "hired_at")).filter(tenure__gt=timedelta(days=365)).
Order: order_by(Age(Now(), "hired_at").desc()).
Update: Employee.objects.update(title_pos=Position("title", "Lead")) computes in the database row by row.
Nest: pass the function to another expression, or pass F() and other functions into it.
Pass arguments per call: output_field=... or even function= and template= can be given at instantiation to adapt one class without subclassing again.

Keep such classes in one module (for example myapp/db_functions.py) so they are reused rather than redefined, and cover them with a test that runs on the production engine, since the SQL is vendor-specific.

When `Func` beats `RawSQL`

RawSQL is fine for a one-off fragment, but a Func subclass takes expressions as arguments (so F("a"), other functions and OuterRef compose), declares its return type once, supports per-vendor SQL, and binds its values as parameters by default. Anything used in more than one query, or taking column arguments, is worth a small Func.

## Why `Func` exists Django ships a large library of **database functions** (`django.db.models.functions`: `Lower`, `Coalesce`, `Extract`, `Cast`, `Concat`, `Greatest`, `UUID7` in 6.1, and many more). When the SQL function you need is missing, you do not have to drop to raw SQL: subclass **`Func`** and you get a reusable, composable expression that works in `annotate()`, `filter()`, `order_by()`, `update()` and inside other expressions. ## The moving parts | Attribute / method | Default | Purpose | |---|---|---| | `function` | `None` | SQL function name, fills `%(function)s` | | `template` | `"%(function)s(%(expressions)s)"` | the SQL shape | | `arg_joiner` | `", "` | joins the compiled arguments | | `arity` | `None` | if set, a different argument count raises `TypeError` | | `output_field` | inferred | Django field type of the result | | `as_<vendor>()` | — | per-backend override, e.g. `as_postgresql`, `as_mysql` | A PostgreSQL `age()` wrapper: ```python from django.db.models import DurationField, Func class Age(Func): function = "AGE" arity = 2 output_field = DurationField() ``` `Employee.objects.annotate(tenure=Age(Now(), "hired_at"))` compiles on PostgreSQL to `AGE(STATEMENT_TIMESTAMP(), "hr_employee"."hired_at")` (that is how `Now()` renders there), and `tenure` comes back as a `timedelta` because `output_field` is a `DurationField`. ## Backend-specific SQL When engines disagree on the name or syntax, implement `as_<vendor>()` and call the base with overrides: 1. Define `as_sql()` behaviour through the class attributes for the common case. 2. Add `def as_mysql(self, compiler, connection, **extra_context):` returning `super().as_sql(compiler, connection, function="...", template="...", **extra_context)`. 3. Django picks `as_<connection.vendor>()` automatically when compiling for that backend. Django's own `ConcatPair` does exactly this to use `CONCAT_WS` on MySQL. ## Pitfalls interviewers probe - **Strings are column references.** Positional arguments that are strings are wrapped in `F()`; other Python values in `Value()`. `Age(Now(), "2020-01-01")` looks up a field called `2020-01-01` and fails. Wrap literals yourself: `Value("2020-01-01")`. - **Keyword extras are not parameters.** Any `**extra` passed to `__init__` (and `**extra_context` in `as_sql`) is **string-interpolated into the template**. The docs' own example is a `Position` function with `template = "%(function)s('%(substring)s' in %(expressions)s)"`: passing a user-supplied `substring` as a keyword is SQL injection. The fix passes it as a positional expression — `super().__init__(Value(substring), expression)` with `arg_joiner = " IN "` — so it compiles to a bound parameter. Wrap it in `Value()`: a bare positional string would be read as a column name. - **`output_field` for mixed inputs.** If the arguments resolve to different field classes, Django raises `FieldError: Expression contains mixed types: ... You must set output_field.` If nothing can be inferred you get "Cannot resolve expression type, unknown output_field". Setting `output_field` on the class or per call removes the guesswork. - **Percent signs.** The template is formatted in `as_sql()` and then again by the driver with query params, so a literal `%` (for example in `strftime('%W', ...)`) must be quadrupled to `%%%%`. - **Aggregates are different.** A function that collapses rows should subclass `Aggregate` so Django adds a `GROUP BY`; a plain `Func` is evaluated per row. - **Lookups and transforms are a separate API.** Registering a function so it can be used as `field__myfunc` in a filter keyword is done with `Transform`/`Lookup` registration, not `Func` alone. ## Using the function once it exists A `Func` subclass is a first-class expression, so it composes with the rest of the QuerySet API: - **Annotate and filter:** `Employee.objects.annotate(tenure=Age(Now(), "hired_at")).filter(tenure__gt=timedelta(days=365))`. - **Order:** `order_by(Age(Now(), "hired_at").desc())`. - **Update:** `Employee.objects.update(title_pos=Position("title", "Lead"))` computes in the database row by row. - **Nest:** pass the function to another expression, or pass `F()` and other functions into it. - **Pass arguments per call:** `output_field=...` or even `function=` and `template=` can be given at instantiation to adapt one class without subclassing again. Keep such classes in one module (for example `myapp/db_functions.py`) so they are reused rather than redefined, and cover them with a test that runs on the production engine, since the SQL is vendor-specific. ## When `Func` beats `RawSQL` `RawSQL` is fine for a one-off fragment, but a `Func` subclass takes **expressions as arguments** (so `F("a")`, other functions and `OuterRef` compose), declares its **return type once**, supports **per-vendor SQL**, and binds its values as parameters by default. Anything used in more than one query, or taking column arguments, is worth a small `Func`.

How would you make a custom Func emit different SQL on MySQL?
Define `as_mysql(self, compiler, connection, **extra_context)` on the subclass and return `super().as_sql(compiler, connection, function=..., template=..., **extra_context)` with the MySQL spelling. Django calls `as_<vendor>()` automatically when the connection's vendor matches, and falls back to `as_sql()` elsewhere.
When should a custom function subclass Aggregate instead of Func?
When it combines many rows into one value, such as a statistical or string-concatenating aggregate. `Aggregate` tells the query compiler a `GROUP BY` is needed and supports `distinct`, `filter` and `default`; a plain `Func` is applied to each row independently.

saying these in an interview costs you the question

Believes a positional string argument to Func is sent as a string literal
Passes user input to a Func subclass as a keyword argument
Thinks arity is only documentation and never enforced
Expects Django to infer the result type of any function automatically
Uses a plain Func for a function that should trigger GROUP BY

With Django's ORM, how do you expose a database function it lacks by subclassing Func, and what pitfalls come with doing it?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

Why Func exists

The moving parts

Backend-specific SQL

Pitfalls interviewers probe

Using the function once it exists

When Func beats RawSQL

▾common follow-ups2 questions

▾red flags5 flags

Why Func exists

The moving parts

Backend-specific SQL

Pitfalls interviewers probe

Using the function once it exists

When Func beats RawSQL

answer

go deeper

Why `Func` exists

When `Func` beats `RawSQL`

Why `Func` exists

When `Func` beats `RawSQL`