id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
34078	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?"	New feature	closed	contrib.postgres	4.1	Normal	wontfix	postgres range range_merge aggregation aggregate		Unreviewed	0	0	0	0	0	0