Re: Ordering of data on calls to user defined aggregate.

On Saturday, May 18, 2002, at 03:56 PM, Tom Lane wrote:

> Tim Hart <timjhart(at)shaw(dot)ca> writes:
>> Short version ( for the busy folk).
>> Is there any guarantee of the ordering of data on calls to a user
>> defined aggregate. (postgresql 7.2.1)
>
> None whatever.

Reasonable.

> The outer query has no idea that the inner query's output is already
> sorted, so it re-sorts ... using only the specified GROUP BY key.

Kind of what I figured. I've been in a discussion with Joel Burton where
I stated the same assumption. Makes sense to me.

<snippage>

> grouping I'm not sure there's a clean way to do it. I can think of
> a hack for the case where fk/name pairs are unique:
>
> select fk, my_agg(DISTINCT name) from foo group by fk;
>
> This relies on the assumption that the aggregate code will implement
> DISTINCT by means of sort/unique processing, which seems unlikely to
> break anytime soon. But it won't help if you want the aggregate to see
> multiple values of the same name for the same fk.

That actually works for me. But, as you say, it makes some assumptions.
It's also not very clear from the the SQL what the intent is. If I
really had a need to do this, I'd probably implement this as a function:

select fk, get_sorted_delimited_list(fk, ' and ' ) as names from foo
order by names;

>
> There is some talk of reimplementing grouped aggregation using hash
> tables, which'd eliminate the upper SORT step and thereby give the
> behavior you want. I dunno how soon anyone will get around to it
> though.

No worries. I can't really say I *want* that behavior. Can't say I
*don't* want it either. I was just after a deeper understanding of the
current behavior. You've provided it, and am grateful. :)