arrays - PostgreSQL:使用某种排序条件对元素数组进行排序

假设您需要将numranges数组按左边界降序排序。以下是最简单的方法：unnest将数组放入表中，对表进行排序，array_agg将其重新放入数组中。代码里会是什么样子？以下是我的非工作尝试：

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移到聚合函数中，以确定aggragate顺序，请参见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;
$$;