unwrap postgresql array into rows

Question

What is the fastest way to unwrap array into rows in PostgreSQL? For instance,

We have:

a - {1,2} {2,3,4} 

And we need:

b -  1 2 2 3 4 

I'm using:

select explode_array(a) as a from a_table; 

where explode_array is:

create or replace function explode_array(in_array anyarray) returns setof anyelement as $$     select ($1)[s] from generate_series(1,array_upper($1, 1)) as s; $$ 

Is there any better way?

Answer 1

Use unnest. For example:

CREATE OR REPLACE FUNCTION test( p_test text[] )   RETURNS void AS $BODY$ BEGIN   SELECT id FROM unnest( p_test ) AS id; END; $BODY$   LANGUAGE plpgsql IMMUTABLE   COST 1; 

Answer 2

unnest --> expand an array to a set of rows

unnest(ARRAY[1,2]) 1 2 

http://www.sqlfiddle.com/#!1/c774a/24