Ordering of data on calls to user defined aggregate.
От | Tim Hart |
---|---|
Тема | Ordering of data on calls to user defined aggregate. |
Дата | |
Msg-id | 34E125CA-6A2D-11D6-8AD8-000393460410@shaw.ca обсуждение исходный текст |
Ответы |
Re: Ordering of data on calls to user defined aggregate.
Re: Ordering of data on calls to user defined aggregate. |
Список | 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?
В списке pgsql-general по дате отправления: