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

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

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?

Solution

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 ...