ERROR: function unnest(integer[]) does not exist in postgresql

Question

SELECT UNNEST(ARRAY[1,2,3,4])

While executing the above query I got the error like this:

ERROR: function unnest(integer[]) does not exist in postgresql.

I am using PostgreSQL 8.3 and I have installed the _int.sql package in my db for integer array operation.

How to resolve this error?

Answer 1

unnest() is not part of the module intarray, but of standard PostgreSQL. However, you need Postgres 8.4 or later for that.

So you can resolve this by upgrading to a more recent version. See the versioning policy of the PostgreSQL project.

Here's a poor man's unnest() for Postgres 8.4:

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

Only works for one-dimensional arrays - as opposed to modern unnest() which also takes accepts multiple dimensions:

SELECT unnest('{1,2,3,4}'::int[])  -- works
SELECT unnest('{{1,2},{3,4},{5,6}}'::int[])  -- fails (returns all NULLs)

You could implement more functions for n-dimensional arrays:

CREATE OR REPLACE FUNCTION unnest2(anyarray) -- for 2-dimensional arrays
  RETURNS SETOF anyelement
  LANGUAGE sql IMMUTABLE AS
$func$
SELECT $1[i][j]
FROM  (
   SELECT i, generate_series(array_lower($1,2), array_upper($1,2)) AS j
   FROM   generate_series(array_lower($1,1), array_upper($1,1)) i
   ) sub;
$func$;

Call:

SELECT unnest2('{{1,2},{3,4},{5,6}}'::int[])  -- works

You could also write a PL/pgSQL function that deals with multiple dimensions ...