Array Unnest

Compatibility Snippets

Array Unnest

Works with PostgreSQL

<=8.3

Written in

SQL

Depends on

Nothing

PostgreSQL 8.4 includes a function for expanding any array of any dimension into a set of elements. It is extremely helpful when working with arrays.
Here is how to add it to 8.3 for 1-dimenstional arrays (by Scott Bailey 'Artacus'):

CREATE OR REPLACE FUNCTION unnest(anyarray)
  RETURNS SETOF anyelement AS
$BODY$
SELECT $1[i]
FROM   generate_series(array_lower($1,1), array_upper($1,1)) i
$BODY$
  LANGUAGE sql IMMUTABLE;

For 2-dimensional arrays (by Erwin Brandstetter):

CREATE OR REPLACE FUNCTION unnest_2d(anyarray)
  RETURNS SETOF anyelement AS
$func$
SELECT $1[d1][d2]
FROM   generate_series(array_lower($1,1), array_upper($1,1)) d1
     , generate_series(array_lower($1,2), array_upper($1,2)) d2
$func$
LANGUAGE sql IMMUTABLE;

Get 1d arrays out of a 2d array (by Erwin Brandstetter):

CREATE OR REPLACE FUNCTION unnest_2d_1d(anyarray)
  RETURNS SETOF anyarray AS
$func$
SELECT array_agg($1[d1][d2])
FROM   generate_series(array_lower($1,1), array_upper($1,1)) d1
     , generate_series(array_lower($1,2), array_upper($1,2)) d2
GROUP  BY d1
ORDER  BY d1
$func$
LANGUAGE sql IMMUTABLE;

SQL Fiddle demo.