This week only. Pastebin PRO Accounts Christmas Special! Don't miss out!Want more features on Pastebin? Sign Up, it's FREE!
Guest

Untitled

By: a guest on May 8th, 2012  |  syntax: None  |  size: 0.87 KB  |  views: 29  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  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!
clone this paste RAW Paste Data