array_unnest()

CREATE OR REPLACE FUNCTION array_unnest(a anyarray)
  RETURNS SETOF anyelement AS
$BODY$
select ($1)[s.i] 
  from generate_series( array_lower($1, 1), array_upper($1, 1 ) ) as s(i);
$BODY$
  LANGUAGE 'sql' IMMUTABLE STRICT;

 

 

SELECT id, value FROM t1;
   id   | value             
----- +----------------------------------- 
 1     | {1, 2, 3} 
 2     | {1, 4} 


SELECT id, array_unnest(value) AS v2 FROM t1;
   id   |   v2
----- +----------------------------------- 
 1     | 1
 1     | 2
 1     | 3
 2     | 1
 1     | 4


SELECT id, ARRAY(array_unnest(value)) AS v3 FROM t1;
   id   | v3             
----- +----------------------------------- 
 1     | {1, 2, 3} 
 2     | {1, 4} 

转载于:https://www.cnblogs.com/shuaixf/archive/2012/05/22/2513434.html