PostgreSQL：使用某种排序条件对元素数组进行排序 [英] PostgreSQL: sort an array of elements using some sorting condition

问题描述

假设您需要按例如左边界降序对数字范围的数组进行排序。以下方法是最简单的吗： unnest 将数组放入表中，对表进行排序， array_agg 将其放回数组中。代码看起来如何？这是我的无效尝试：

Suppose you need to sort an array of numranges by, say, descending left boundary. Is the following approach the simplest: unnest the array into a table, sort the table, array_agg it back into an array. How would that look in code? Here is my non-working attempt:

DO $$
DECLARE
    x numrange[] := '{"[0, 3]", "[0, 1]", "[3, 5]", "[3, 8]"}';
BEGIN

    x := (
          WITH x AS (
              SELECT xrow FROM unnest(x) AS xrow
          )
          SELECT array_agg(xrow) FROM x ORDER BY lower(xrow) DESC
    );
    RAISE NOTICE '%', x;
END;
$$;

推荐答案

您必须移动 ORDER BY 转换为集合函数，以影响集合顺序，请参见手册：

You must move ORDER BY into aggregate function, to afect aggragate order see manual:

DO $$
DECLARE
    x numrange[] := '{"[0, 3]", "[0, 1]", "[3, 5]", "[3, 8]"}';
BEGIN

    x := (WITH x AS (
            SELECT xrow FROM unnest(x) AS xrow
        )
        SELECT array_agg(xrow  ORDER BY lower(xrow) DESC) FROM x

    );
    RAISE NOTICE '%', x;
END;
$$;

这篇关于PostgreSQL：使用某种排序条件对元素数组进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！