Array reverse

From PostgreSQL wiki
Jump to navigationJump to 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;