sql - Convert multi-dimensional array to records -

given: {{1,"a"},{2,"b"},{3,"c"}}
desired:

 foo | bar -----+------   1  |    2  |  b   3  |  c 

you can intended result following query; however, it'd better have scales size of array.

select arr[subscript][1] foo, arr[subscript][2] bar  ( select generate_subscripts(arr,1) subscript, arr         (select '{{1,"a"},{2,"b"},{3,"c"}}'::text[][] arr) input        ) sub; 

not sure mean saying "it'd better have scales size of array". of course can not have columns added resultset inner array size grows, because postgresql must know exact colunms of query before execution (so before begins read string).

but propose converting string normal relational representation of matrix:

select i, j, arr[i][j] a_i_j (  select i, generate_subscripts(arr,2) j, arr (   select generate_subscripts(arr,1) i, arr   (select ('{{1,"a",11},{2,"b",22},{3,"c",33},{4,"d",44}}'::text[][]) arr) input  ) sub_i ) sub_j 

which gives:

i | j | a_i_j --+---+------ 1 | 1 | 1 1 | 2 | 1 | 3 | 11 2 | 1 | 2 2 | 2 | b 2 | 3 | 22 3 | 1 | 3 3 | 2 | c 3 | 3 | 33 4 | 1 | 4 4 | 2 | d 4 | 3 | 44 

such result may rather usable in further data processing, think.

of course, such query can handle array predefined number of dimensions, array sizes of dimensions can changed without rewriting query, bit more flexible approach.

addition: yes, using with recursive 1 can build resembling query, capable of handling array arbitrary dimensions. none less, there no way overcome limitation coming relational data model - exact set of columns must defined @ query parse time, , no way delay until execution time. so, forced store indices in 1 column, using array.

here query extracts elements arbitrary multi-dimensional array along zero-based indices (stored in one-dimensional array):

with recursive extract_index(k,idx,elem,arr,n) (  select (row_number() over())-1 k, idx, elem, arr, n (   select array[]::bigint[] idx, unnest(arr) elem, arr, array_ndims(arr) n    ( select '{{{1,"a"},{11,111}},{{2,"b"},{22,222}},{{3,"c"},{33,333}},{{4,"d"},{44,444}}}'::text[] arr ) input  ) plain_indexed union  select k/array_length(arr,n)::bigint k, array_prepend(k%array_length(arr,2),idx) idx, elem, arr, n-1 n   extract_index  n!=1 ) select array_prepend(k,idx) idx, elem extract_index n=1 

which gives:

idx     | elem --------+----- {0,0,0} | 1 {0,0,1} | {0,1,0} | 11 {0,1,1} | 111 {1,0,0} | 2 {1,0,1} | b {1,1,0} | 22 {1,1,1} | 222 {2,0,0} | 3 {2,0,1} | c {2,1,0} | 33 {2,1,1} | 333 {3,0,0} | 4 {3,0,1} | d {3,1,0} | 44 {3,1,1} | 444 

formally, seems prove concept, wonder real practical use 1 make out of :)