supabase / supabase-swift / 33425268985

88%

push

github

feat(postgrest): add aggregate functions as select-only expressions (#1294)

* feat(postgrest): add aggregate functions as select-only expressions

`sum()`, `avg()`, `min()`, `max()`, `count()` and `PostgrestAggregate.countAll`
render `amount.sum()` and `count()` in a `select` list.

`PostgrestAggregate` conforms to `PostgrestColumnExpression` and to neither
refinement, because PostgREST has no `HAVING`: filtering on an aggregate is not
a request it can serve, and `order=children(amount.sum()).desc` is a PGRST100
"failed to parse order" — every other spelling of it fails too. So
`$0.amount.sum().gt(100)` does not compile.

`min()`/`max()` keep the column's type while `sum()`/`avg()` do not: a min of an
`Int` column is an `Int`, but a sum can overflow it and an average is rarely
integral.

Aggregates need `db-aggregates-enabled`, which is on for hosted Supabase and
off by default in self-hosted PostgREST.

* docs(postgrest): document the empty-result shape of an aggregate

An aggregate over zero matching rows comes back `null` for `sum`/`avg`/`min`/
`max` and `0` for `count`, which a caller decoding the response by hand has to
know. Measured against Postgres 15 + PostgREST v12.2.3 with
`db-aggregates-enabled`:

    ?select=amount.sum(),amount.count()&id=eq.99999   -> [{"sum":null,"count":0}]
    ?select=count()&id=eq.99999                       -> [{"count":0}]
    ?select=category,amount.sum()&id=eq.99999         -> []

The grouping column is what decides it: with one, PostgREST returns no row at
all, so only the bare shape can produce the `null`.

`Value` stays non-optional. It types the expression rather than the response,
and nothing in the SDK decodes through it, so making it optional would change
no behavior while reintroducing the optional `Value` that strips operators from
anything chained off it.

19 of 19 new or added lines in 1 file covered. (100.0%)

9522 of 10908 relevant lines covered (87.29%)

89.52 hits per line