Fixed #26167 -- Added support for functional indexes.

Thanks Simon Charette, Mads Jensen, and Mariusz Felisiak for reviews.

Co-authored-by: Markus Holtermann <info@markusholtermann.eu>

4 files changed, 152 insertions, 10 deletions

diff --git a/docs/ref/checks.txt b/docs/ref/checks.txt
index a3d1b0664d..b07a81f413 100644
--- a/docs/ref/checks.txt
+++ b/docs/ref/checks.txt
@@ -381,6 +381,7 @@ Models
 * **models.E041**: ``constraints`` refers to the joined field ``<field name>``.
 * **models.W042**: Auto-created primary key used when not defining a primary
   key type, by default ``django.db.models.AutoField``.
+* **models.W043**: ``<database>`` does not support indexes on expressions.
 
 Security
 --------
diff --git a/docs/ref/contrib/postgres/indexes.txt b/docs/ref/contrib/postgres/indexes.txt
index f9c0fb2f97..4a9b2ad22e 100644
--- a/docs/ref/contrib/postgres/indexes.txt
+++ b/docs/ref/contrib/postgres/indexes.txt
@@ -10,7 +10,7 @@ available from the ``django.contrib.postgres.indexes`` module.
 ``BloomIndex``
 ==============
 
-.. class:: BloomIndex(length=None, columns=(), **options)
+.. class:: BloomIndex(*expressions, length=None, columns=(), **options)
 
     .. versionadded:: 3.1
 
@@ -30,10 +30,15 @@ available from the ``django.contrib.postgres.indexes`` module.
 
     .. _bloom: https://www.postgresql.org/docs/current/bloom.html
 
+    .. versionchanged:: 3.2
+
+        Positional argument ``*expressions`` was added in order to support
+        functional indexes.
+
 ``BrinIndex``
 =============
 
-.. class:: BrinIndex(autosummarize=None, pages_per_range=None, **options)
+.. class:: BrinIndex(*expressions, autosummarize=None, pages_per_range=None, **options)
 
     Creates a `BRIN index
     <https://www.postgresql.org/docs/current/brin-intro.html>`_.
@@ -43,24 +48,34 @@ available from the ``django.contrib.postgres.indexes`` module.
 
     The ``pages_per_range`` argument takes a positive integer.
 
+    .. versionchanged:: 3.2
+
+        Positional argument ``*expressions`` was added in order to support
+        functional indexes.
+
     .. _automatic summarization: https://www.postgresql.org/docs/current/brin-intro.html#BRIN-OPERATION
 
 ``BTreeIndex``
 ==============
 
-.. class:: BTreeIndex(fillfactor=None, **options)
+.. class:: BTreeIndex(*expressions, fillfactor=None, **options)
 
     Creates a B-Tree index.
 
     Provide an integer value from 10 to 100 to the fillfactor_ parameter to
     tune how packed the index pages will be. PostgreSQL's default is 90.
 
+    .. versionchanged:: 3.2
+
+        Positional argument ``*expressions`` was added in order to support
+        functional indexes.
+
     .. _fillfactor: https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-STORAGE-PARAMETERS
 
 ``GinIndex``
 ============
 
-.. class:: GinIndex(fastupdate=None, gin_pending_list_limit=None, **options)
+.. class:: GinIndex(*expressions, fastupdate=None, gin_pending_list_limit=None, **options)
 
     Creates a `gin index <https://www.postgresql.org/docs/current/gin.html>`_.
 
@@ -79,13 +94,18 @@ available from the ``django.contrib.postgres.indexes`` module.
     to tune the maximum size of the GIN pending list which is used when
     ``fastupdate`` is enabled.
 
+    .. versionchanged:: 3.2
+
+        Positional argument ``*expressions`` was added in order to support
+        functional indexes.
+
     .. _GIN Fast Update Technique: https://www.postgresql.org/docs/current/gin-implementation.html#GIN-FAST-UPDATE
     .. _gin_pending_list_limit: https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-GIN-PENDING-LIST-LIMIT
 
 ``GistIndex``
 =============
 
-.. class:: GistIndex(buffering=None, fillfactor=None, **options)
+.. class:: GistIndex(*expressions, buffering=None, fillfactor=None, **options)
 
     Creates a `GiST index
     <https://www.postgresql.org/docs/current/gist.html>`_. These indexes are
@@ -109,13 +129,18 @@ available from the ``django.contrib.postgres.indexes`` module.
     Provide an integer value from 10 to 100 to the fillfactor_ parameter to
     tune how packed the index pages will be. PostgreSQL's default is 90.
 
+    .. versionchanged:: 3.2
+
+        Positional argument ``*expressions`` was added in order to support
+        functional indexes.
+
     .. _buffering build: https://www.postgresql.org/docs/current/gist-implementation.html#GIST-BUFFERING-BUILD
     .. _fillfactor: https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-STORAGE-PARAMETERS
 
 ``HashIndex``
 =============
 
-.. class:: HashIndex(fillfactor=None, **options)
+.. class:: HashIndex(*expressions, fillfactor=None, **options)
 
     Creates a hash index.
 
@@ -127,12 +152,17 @@ available from the ``django.contrib.postgres.indexes`` module.
         Hash indexes have been available in PostgreSQL for a long time, but
         they suffer from a number of data integrity issues in older versions.
 
+    .. versionchanged:: 3.2
+
+        Positional argument ``*expressions`` was added in order to support
+        functional indexes.
+
     .. _fillfactor: https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-STORAGE-PARAMETERS
 
 ``SpGistIndex``
 ===============
 
-.. class:: SpGistIndex(fillfactor=None, **options)
+.. class:: SpGistIndex(*expressions, fillfactor=None, **options)
 
     Creates an `SP-GiST index
     <https://www.postgresql.org/docs/current/spgist.html>`_.
@@ -140,4 +170,30 @@ available from the ``django.contrib.postgres.indexes`` module.
     Provide an integer value from 10 to 100 to the fillfactor_ parameter to
     tune how packed the index pages will be. PostgreSQL's default is 90.
 
+    .. versionchanged:: 3.2
+
+        Positional argument ``*expressions`` was added in order to support
+        functional indexes.
+
     .. _fillfactor: https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-STORAGE-PARAMETERS
+
+``OpClass()`` expressions
+=========================
+
+.. versionadded:: 3.2
+
+.. class:: OpClass(expression, name)
+
+    An ``OpClass()`` expression represents the ``expression`` with a custom
+    `operator class`_ that can be used to define functional indexes. To use it,
+    you need to add ``'django.contrib.postgres'`` in your
+    :setting:`INSTALLED_APPS`. Set the ``name`` parameter to the name of the
+    `operator class`_.
+
+    For example::
+
+        Index(OpClass(Lower('username'), name='varchar_pattern_ops'))
+
+    creates an index on ``Lower('username')`` using ``varchar_pattern_ops``.
+
+    .. _operator class: https://www.postgresql.org/docs/current/indexes-opclass.html
diff --git a/docs/ref/models/indexes.txt b/docs/ref/models/indexes.txt
index 9dda45ad44..d2cf98e9e1 100644
--- a/docs/ref/models/indexes.txt
+++ b/docs/ref/models/indexes.txt
@@ -21,10 +21,55 @@ options`_.
 ``Index`` options
 =================
 
-.. class:: Index(fields=(), name=None, db_tablespace=None, opclasses=(), condition=None, include=None)
+.. class:: Index(*expressions, fields=(), name=None, db_tablespace=None, opclasses=(), condition=None, include=None)
 
     Creates an index (B-Tree) in the database.
 
+``expressions``
+---------------
+
+.. attribute:: Index.expressions
+
+.. versionadded:: 3.2
+
+Positional argument ``*expressions`` allows creating functional indexes on
+expressions and database functions.
+
+For example::
+
+    Index(Lower('title').desc(), 'pub_date', name='lower_title_date_idx')
+
+creates an index on the lowercased value of the ``title`` field in descending
+order and the ``pub_date`` field in the default ascending order.
+
+Another example::
+
+    Index(F('height') * F('weight'), Round('weight'), name='calc_idx')
+
+creates an index on the result of multiplying fields ``height`` and ``weight``
+and the ``weight`` rounded to the nearest integer.
+
+:attr:`Index.name` is required when using ``*expressions``.
+
+.. admonition:: Restrictions on Oracle
+
+    Oracle requires functions referenced in an index to be marked as
+    ``DETERMINISTIC``. Django doesn't validate this but Oracle will error. This
+    means that functions such as
+    :class:`Random() <django.db.models.functions.Random>` aren't accepted.
+
+.. admonition:: Restrictions on PostgreSQL
+
+    PostgreSQL requires functions and operators referenced in an index to be
+    marked as ``IMMUTABLE``. Django doesn't validate this but PostgreSQL will
+    error. This means that functions such as
+    :class:`Concat() <django.db.models.functions.Concat>` aren't accepted.
+
+.. admonition:: MySQL and MariaDB
+
+    Functional indexes are ignored with MySQL < 8.0.13 and MariaDB as neither
+    supports them.
+
 ``fields``
 ----------
 
@@ -130,8 +175,8 @@ indexes records with more than 400 pages.
 .. admonition:: Oracle
 
     Oracle does not support partial indexes. Instead, partial indexes can be
-    emulated using functional indexes. Use a :doc:`migration
-    </topics/migrations>` to add the index using :class:`.RunSQL`.
+    emulated by using functional indexes together with
+    :class:`~django.db.models.expressions.Case` expressions.
 
 .. admonition:: MySQL and MariaDB
 
diff --git a/docs/releases/3.2.txt b/docs/releases/3.2.txt
index 8796419c62..57ab1baf34 100644
--- a/docs/releases/3.2.txt
+++ b/docs/releases/3.2.txt
@@ -95,6 +95,42 @@ or on a per-model basis::
 In anticipation of the changing default, a system check will provide a warning
 if you do not have an explicit setting for :setting:`DEFAULT_AUTO_FIELD`.
 
+.. _new_functional_indexes:
+
+Functional indexes
+------------------
+
+The new :attr:`*expressions <django.db.models.Index.expressions>` positional
+argument of :class:`Index() <django.db.models.Index>` enables creating
+functional indexes on expressions and database functions. For example::
+
+    from django.db import models
+    from django.db.models import F, Index, Value
+    from django.db.models.functions import Lower, Upper
+
+
+    class MyModel(models.Model):
+        first_name = models.CharField(max_length=255)
+        last_name = models.CharField(max_length=255)
+        height = models.IntegerField()
+        weight = models.IntegerField()
+
+        class Meta:
+            indexes = [
+                Index(
+                    Lower('first_name'),
+                    Upper('last_name').desc(),
+                    name='first_last_name_idx',
+                ),
+                Index(
+                    F('height') / (F('weight') + Value(5)),
+                    name='calc_idx',
+                ),
+            ]
+
+Functional indexes are added to models using the
+:attr:`Meta.indexes <django.db.models.Options.indexes>` option.
+
 ``pymemcache`` support
 ----------------------
 
@@ -210,6 +246,10 @@ Minor features
 * Lookups for :class:`~django.contrib.postgres.fields.ArrayField` now allow
   (non-nested) arrays containing expressions as right-hand sides.
 
+* The new :class:`OpClass() <django.contrib.postgres.indexes.OpClass>`
+  expression allows creating functional indexes on expressions with a custom
+  operator class. See :ref:`new_functional_indexes` for more details.
+
 :mod:`django.contrib.redirects`
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~