Add Postgres range_merge aggregate function to postgres.contrib

[Jack Linke]

Most of the Postgres range functions have been exposed for querying in django, but not the range_merge Aggregate function.

As background, range_merge "Computes the smallest range that includes ... the given ranges".

The existing Postgres functions exposed for queries in django: ​https://docs.djangoproject.com/en/4.1/ref/contrib/postgres/fields/#querying-using-the-bounds

The full list of Posgres range functions (note that range_merge is the only one not represented in django): ​https://www.postgresql.org/docs/14/functions-range.html#RANGE-FUNCTIONS-TABLE

---

To make use of this aggregation in my own project, I created a django package that runs a migration to CREATE (or DROP) the aggregate function in the database (​https://pypi.org/project/django-range-merge/).

operations = [
    migrations.RunSQL(
        sql=[("CREATE OR REPLACE AGGREGATE range_merge(anyrange)(sfunc=range_merge, stype=anyrange);")],
        reverse_sql=[("DROP AGGREGATE IF EXISTS range_merge(anyrange);")],
    )
]

Then, given the model:

class Event(models.Model):
    name = models.CharField(max_length=30)
    potential_visitors = models.IntegerRangeField()

And a few model instances:

{"id" : 1, "name" : "Birthday", "potential_visitors" : "[2, 3)"}
{"id" : 2, "name" : "Bake Sale", "potential_visitors" : "[30, 50)"}
{"id" : 3, "name" : "Band Camp", "potential_visitors" : "[22, 28)"}
{"id" : 4, "name" : "Cooking Show", "potential_visitors" : "[7, 20)"}
{"id" : 5, "name" : "Pajama Day", "potential_visitors" : "[15, 30)"}

Executing the query:

Event.objects.all().aggregate(
    output=Aggregate(F("potential_visitors"), function="range_merge")
)

Returns:

{'output': NumericRange(2, 50, '[)')}

---

It looks like RunSQL is not used anywhere in django's codebase, except in tests, so I am not sure if adding this to the contrib.postgres codebase would be welcomed.

Should this be added to django, or should it remain as a package separate from django itself?

[Claude Paroz]

I think adding the range_merge function could be accepted in contrib.postgres. However, what you suggest is to add a custom aggregate (which is a different variant of the range_agg/range_intersect_agg aggregations added in PostgreSQL 14). Therefore, my opinion is that your custom aggregation is quite niche and could stay in a custom package.

[Mariusz Felisiak]

I agree with Claude. Marking of adding a custom aggregate function as wontfix.