SHARE
TWEET

Untitled

a guest May 8th, 2012 32 Never
  1. ERROR: function unnest(integer[]) does not exist in postgresql
  2. SELECT UNNEST(ARRAY[1,2,3,4])
  3.        
  4. ERROR: function unnest(integer[]) does not exist in postgresql.
  5.        
  6. CREATE OR REPLACE FUNCTION unnest(anyarray)
  7.   RETURNS SETOF anyelement AS
  8. $BODY$
  9.    SELECT $1[i] FROM generate_series(array_lower($1,1), array_upper($1,1)) i;
  10. $BODY$ LANGUAGE sql IMMUTABLE;
  11.        
  12. SELECT unnest('{1,2,3,4}'::int[])  -- works
  13. SELECT unnest('{{1,2},{3,4},{5,6}}'::int[])  -- fails! (returns all NULLs)
  14.        
  15. CREATE OR REPLACE FUNCTION unnest2(anyarray) -- for 2-dimensional arrays
  16.   RETURNS SETOF anyelement AS
  17. $BODY$
  18. SELECT $1[i][j]
  19. FROM  (
  20.     SELECT i, generate_series(array_lower($1,2), array_upper($1,2)) j
  21.     FROM  (
  22.         SELECT generate_series(array_lower($1,1), array_upper($1,1)) i
  23.         ) x
  24.     ) y;
  25. $BODY$ LANGUAGE sql IMMUTABLE;
  26.        
  27. SELECT unnest2('{{1,2},{3,4},{5,6}}'::int[])  -- works!
RAW Paste Data
Top