PostgreSQL: sort an array of elements using some sorting condition

Question

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;
$$;

Gabriel's Messanger · Accepted Answer · 2015-11-27 10:07:51Z

5

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;
$$;

answered Nov 27, 2015 at 10:07

Gabriel's Messanger

3,36822 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: sort an array of elements using some sorting condition

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related