Re: converting 1 dimensional array to 2 dimensional array

On Mon, Mar 12, 2012 at 3:31 PM, Nicholas Wilson <nwilson5(at)gmail(dot)com> wrote:
> Hi, need help with some syntax.
>
> Currently we have a column of type text[]. It is a 1 dimensional array of
> text values. Each of them we have formatted in such a way that it can be
> split into two values. We are trying to convert it into a two dimensional
> array but are unsure how to do that.
>
> i.e. converting:
> {a^^val1, b^^val2, c^^val3}
> to
> {{a,val1}, {b,val2}, {c,val3}}
>
> I can do
> Select array(select '{"' || replace(unnest(col1), '^^', '","') || '"}') from
> table;
>
> But that still gives me one dimensional text array. I can't cast it to
> text[] because there are no array of arrays. How do I get this into a 2
> dimensional array?

It turns out to be a real bugaboo to great >1d arrays except for the
following methods:
*) textual definition
*) nested array[] (this method only works well when your input list is constant)
*) select array[1d array] (works fine but you have no control over
array structure)
*) iterative approaches in plpgsql which are slow and suck

This is the best 1-liner I could come up with. It's going to be
really sensitive to input data and will break for example if there are
any double quotes in your field. If the number of array elements is
fixed there's a much better way.

postgres=# select replace(array(select
string_to_array(unnest('{a^^val1, b^^val2, c^^val3}'::text[]),
'^^')::text)::text, '"', '')::text[];
replace
------------------------------
{{a,val1},{b,val2},{c,val3}}

postgres=# select array_dims(replace(array(select
string_to_array(unnest('{a^^val1, b^^val2, c^^val3}'::text[]),
'^^')::text)::text, '"', '')::text[]);
array_dims
------------
[1:3][1:2]

merlin