Array reverse

From PostgreSQL wiki

Jump to: navigation, search

Library Snippets

SQL function to reverse an array

Works with PostgreSQL

Any version

Written in

SQL

Depends on

Nothing

PostgreSQL provides no built-in array_reverse() function, so here's one for your use. It's not fast, but it'll do the job. 

CREATE OR REPLACE FUNCTION array_reverse(anyarray) RETURNS anyarray AS $$
SELECT ARRAY(
    SELECT $1[i]
    FROM generate_series(
        array_lower($1,1),
        array_upper($1,1)
    ) AS s(i)
    ORDER BY i DESC
);
$$ LANGUAGE 'sql' STRICT IMMUTABLE;

For 8.4 or later, the more-correct code below works, as it accounts for any skips in the subscripts. 

CREATE OR REPLACE FUNCTION array_reverse(anyarray) RETURNS anyarray AS $$
SELECT ARRAY(
    SELECT $1[i]
    FROM generate_subscripts($1,1) AS s(i)
    ORDER BY i DESC
);
$$ LANGUAGE 'sql' STRICT IMMUTABLE;
Retrieved from "http://wiki.postgresql.org/wiki/Array_reverse"
Personal tools