Array reverse

From PostgreSQL wiki

Revision as of 19:27, 21 June 2010 by Johto (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
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;
Personal tools