Сортировка текстового агрегата, созданного с помощью array_agg в postgresql

У меня есть таблица в postgresql. Следующая таблица "животные" сделает, чтобы объяснить мою проблему:

name
------
tiger
cat
dog

Теперь я использую следующий запрос:

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

Результат: "тигр, кошка, собака". Но я хотел бы отсортировать агрегат, прежде чем он будет преобразован в строку. Поэтому я надеюсь, что я надеюсь:

"cat, dog, tiger".

Итак, как я могу отсортировать массив строк в postgresql 8.4, прежде чем преобразовать его в строку. ORDER BY в строке "name" не работает, и встроенная функция сортировки обрабатывает только целые значения.

Любая хорошая идея, как ее решить в чистом SQL?

Тонк много Ричард

Ответ 1

Это будет доступно в PostgreSQL 9.0:

http://www.postgresql.org/docs/9.0/static/release-9-0.html, раздел E.1.3.6.1. Заполнители

В то же время вы можете сделать что-то подобное, что может решить проблему (хотя и неуклюжий):

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

Ответ 2

Для PostgreSQL 9.0 и более поздних версий вы можете использовать предложение ORDER BY в агрегированном выражении:

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

Кроме того, для вашей конкретной цели вы можете использовать string_agg, чтобы упростить свой запрос:

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

Ответ 3

Хотя ответ Мэтью Вуда лучше для вашего случая, вот способ сортировки массивов в PostgreSQL 8.4 и выше:

SELECT array(
    SELECT unnest(array[3,2,1]) AS x ORDER BY x
);

Знание функций array и unnest может быть удобно, так как оно также позволяет вам делать такие вещи, как "map" над массив:

SELECT array(
    SELECT x*x FROM (SELECT unnest(array[1,2,3]) AS x) as subquery
);

Опять же, это может быть вашим за цену PostgreSQL 8.4.

Ответ 4

Вы пытались использовать generate_series() в массиве, а затем сделать SELECT...ORDER BY по этому результату (или просто вставить его внутри SELECT), прежде чем преобразовать его в строку?

Ответ 5

Тем не менее, для версии 8.4, используя решение, предложенное Мэтью Вудом, если вам нужно сделать группировку во внешнем запросе, внутренний запрос также должен быть отсортирован для согласования сортировки.

SELECT family, array_agg(animal_name)
FROM (
    SELECT family, "name" AS animal_name
    FROM animals
    ORDER BY family, "name"
) AS sorted_animals
group by family;