Sort a text aggregate created with array_agg in postgresql

Question

I have a table in postgresql. The following table "animals" will do to explain my problem:

name ------ tiger cat dog 

Now I am using the following query:

SELECT     array_to_string(array_agg("name"), ', ') FROM     animals; 

The result is: "tiger, cat, dog". But I would like to sort the aggregate, before it is converted into a string. So this is the result I am hoping for:

"cat, dog, tiger". 

So how can I sort an string array in postgresql 8.4 before converting it to a string. ORDER BY on the row "name" does not work and the built-in sort function processes only integer values.

Anyone a good idea, how to solve this in pure SQL?

Thanx a lot Richard

Answer 1

For modern PostgreSQL (since version 9.0), you can use an ORDER BY clause in an aggregate expression:

SELECT     array_to_string(array_agg(name ORDER BY name), ', ') FROM     animals; 

Also, for your specific purpose, you can use string_agg to simplify your query:

SELECT     string_agg(name, ', ' ORDER BY name) FROM     animals; 

Answer 2

This will be available in PostgreSQL 9.0:

http://www.postgresql.org/docs/9.0/static/release-9-0.html, Section E.1.3.6.1. Aggregates

In the meantime, you could do something like this which may solve the problem (albeit clunky):

SELECT array_agg(animal_name) FROM (     SELECT "name" AS animal_name     FROM animals     ORDER BY "name" ) AS sorted_animals;