schireson / pytest-mock-resources / 24798864780

89%

push

github

feat: handle MEDIAN and LISTAGG functions for Redshift (#230)

Adds two Redshift aggregate UDFs that previously raised 'function does
not exist' errors against the mock fixture:

- MEDIAN(col): implemented as a CREATE AGGREGATE using array_append as
  the state function and a final function that computes
  percentile_cont(0.5) WITHIN GROUP over the unnested array. This
  matches Redshift MEDIAN semantics (average of the two middle values
  on even-count input, NULLs ignored). Overloads are registered for
  NUMERIC and DOUBLE PRECISION.

- LISTAGG(col[, delimiter]): implemented as a CREATE AGGREGATE whose
  state function concatenates non-NULL values with an optional
  delimiter. Overloads cover both LISTAGG(col) and LISTAGG(col, delim).

These work around the constraint (noted in the #213 thread) that
Redshift does not allow SQL UDFs for aggregate operations by leveraging
Postgres native CREATE AGGREGATE, which is available because the
Redshift fixture runs on top of a Postgres container.

Closes the remaining MEDIAN and LISTAGG checkboxes on #213.

Co-authored-by: tl212 <tl212@users.noreply.github.com>

343 of 423 branches covered (81.09%)

Branch coverage included in aggregate %.

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

1464 of 1612 relevant lines covered (90.82%)

16.52 hits per line