Ordering of data on calls to user defined aggregate.

From: Tim Hart <timjhart(at)shaw(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: Ordering of data on calls to user defined aggregate.
Date: 2002-05-18 07:02:24
Message-ID: 34E125CA-6A2D-11D6-8AD8-000393460410@shaw.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Longer version ( for those that are curious or need more info ).

I was goofing around tonight and decided to create a concat_with_and
aggregate for varchar. My desired effect: if I have a table like this:

create table foo( id bigint, fk bigint, name varchar);

with data
id fk name
1 1 A
2 1 B
3 2 E
4 3 D
5 3 C

and a query like this:

select fk, concat_with_and(name) names from foo group by fk;

then the results would look like:
fk names
1 A and B
2 E
3 D and C

I had no trouble creating the aggregate function or the supporting
(state transition) function:

create function concat_with_and(varchar, varchar) returns varchar as
'select case when $1 is null then $2 else $1 || \' and \' || $2 end;'
language sql;

I did not use a final function.

Everything worked like a charm. Then I decided to take it one step
further. What if I wanted all the names sorted in alphabetical order
( not across records, but within the record)?

fk names
1 A and B
2 E
3 C and D

So I tried a query like this:

select fk, concat_with_and(name) from ( select fk, name from foo order
by fk, name) sub_select group by fk;

From just eyeballing the first 10 to 12 pages of the results, all but 2
records had the names in alphabetical order. So I removed the subselect
and ran the query again - this time paying attention to the ordering
within names. Very few entries in the 'names' column were in
alphabetical order at all.

I tried to develop an example dataset to include here that would
illustrate cases where it doesn't order them alphabetically. I was
unable to do so. I'm experimenting with a dataset of about 15000 records
for my queries.

Where I'm coming from:
I know there are several other ways to achieve the results I was looking
for above. I have no real need for this. I was just looking for an
excuse to write my own aggregate function. What I found intriguing were
the possibilities - and the opportunity to increase my understanding of
what goes on under the hood.

questions:
From a veteran's point of view, is the approach above elegant or a gross
hack? Somewhere in between?

Am I correct in assuming that the 'group by' clause in my second query
affects the ordering of the subquery? Any idea why it only affected a
small percentage of them?

Could I introduce a final function to solve my problem? The state
transition function simply collects all the entries. The final function
would order them and delimit them with 'and'. In practice, if this were
required I'd hang it up & write a pl/pgsql function and skip the
aggregation all together. The extra work begins to clutter the idea. I'd
prefer not to force every 'concat_with_and' aggregation to sort the data
as well.

Any other comments?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Marin Dimitrov 2002-05-18 08:29:04 Re: Download postgresql
Previous Message Alan Wayne 2002-05-18 02:02:20 how do I unsubscribe? Install problems?