Database Reference
In-Depth Information
hannu=# insert into mydata values(1, 'one'),
(2,'two');
INSERT 0 2
hannu=# select array_to_rows(array(select m
from mydata m));
-[ RECORD 1 ]-+--------
array_to_rows | (1,one)
-[ RECORD 2 ]-+--------
array_to_rows | (2,two)
hannu=# select * from
array_to_rows(array(select m from mydata m));
-[ RECORD 1 ]
id | 1
data | one
-[ RECORD 2 ]
id | 2
data | two
The two last
select
statements return a one-column table of type
mydata
, and
a two-column table of the same expanded into its component columns. This single
function was flexible enough to handle several different types of data without any
changes.
Note
There is a more potent version of
array_to_rows
built into PostgreSQL called
unnest()
. The built-in function is faster than our sample one and can also deal
with arrays with more than one dimension:
hannu=# select unnest('{{1,2,3},
{4,5,6}}'::int[]);
-[ RECORD 1 ]
unnest | 1
-[ RECORD 2 ]
