Re: Re: Proposal/design feedback needed: WITHIN GROUP (sql standard ordered set aggregate functions)

Hi all,

This is our current work-in-progress patch for WITHIN GROUP.

What mostly works:
- percentile_cont(float8) within group (order by float8)
- percentile_cont(float8) within group (order by interval)
- percentile_disc(float8) within group (order by float8)

What doesn't work:
- supporting other types in percentile_disc (want polymorphism to
work first)
- no commands yet to add new ordered set functions (want to nail
down the catalog representation first)
- no hypothetical set functions yet (need to resolve the above two
points first)
- some rough edges
- probably some bugs
- docs

Implementation details:

For execution, we repurpose the existing aggregate-orderby mechanics.
Given func(directargs) WITHIN GROUP (ORDER BY args), we process the
(ORDER BY args) into a tuplesort in the same way currently done for
agg(args ORDER BY args). Rather than using a transfn, we then call the
finalfn as finalfn(directargs), providing an API by which the finalfn
can access the tuplesort. (This is somewhat inspired by the window
function API, but unfortunately has nothing in common with it in terms
of requirements, so we couldn't just reuse it.)

func(p1,p2,...) WITHIN GROUP (ORDER BY q1,q2,...) is represented in the
catalog with two pg_proc rows:

func(p1,p2,...,q1,q2,...) (proisagg=true)
func_final(p1,p2,...)

with the usual pg_aggregate row linking them, though aggtransfn is set
to InvalidOid (as is aggtranstype) and an additional flag indicates
that this is an ordered set function.

(This representation is inadequate for a number of reasons; it does not
handle polymorphism well and would require special-case coding for
hypothetical set functions, which we have not yet tackled. See our other
post.)

Regards,

Atri
--
Regards,

Atri
l'apprenant