Re: order function in aggregate

Richard Huxton <dev(at)archonet(dot)com> writes:
> Michael Toews wrote:
>> This is where I get confused, since in the aggregate function, I have
>> specified `SORTOP="<"`, which according to the documentation
>> (http://www.postgresql.org/docs/8.3/interactive/sql-createaggregate.html)
>> "must be equivalent to":
>>
>> SELECT sub FROM aggr_test ORDER BY sub USING <; -- LIMIT 1;

> That's for things like min()/max() where you only want the first/last
> value of some set. You want all of them.

Right: sortop is an assertion that the aggregate behaves like min() or
max(), not a request for the system to do something strange to the
aggregate's input.

> You could accumulate the values in an array and then sort that with the
> final-func that create aggregate supports.

The traditional way to get presorted input to an aggregate is

select myagg(x) from (select whatever as x from ... order by ...) ss;

You can avoid that with an internal sort in the aggregate, as Richard
suggests, but it might not be worth the trouble --- and in any case
it'd be hard to sort internally on anything except the exact values
being aggregated. With the subselect approach you can order on anything
at all.

regards, tom lane