Re: Can ARRAY( ... ) generate text[][]?

On Thu, Jun 10, 2010 at 6:59 PM, J. Greg Davidson <greg@ngender.net> wrote:
> Hi fellow PostgreSQL hackers!
>
> I tried to write an SQL glue function to turn an array
> of alternating key/value pairs into an array of arrays
> and got the message
>
> ERROR:  42704: could not find array type for data type text[]

I do it like this:

create type pair_t as (key text, value text);

> -- BEGIN CODE
>
> -- Here's a simplified example:
>
> CREATE OR REPLACE
> FUNCTION text__(variadic text[]) RETURNS text[][] AS $$
> SELECT ARRAY(
>      SELECT ARRAY[ $1[i], $1[i+1] ]
>      FROM generate_series(1, array_upper($1,1), 2) i
> )
> $$ LANGUAGE sql;

create or replace function pairs(variadic text[]) returns pair_t[] as
$$
  select array(select ($1[i], $1[i+1])::pair_t
    FROM generate_series(1, array_upper($1,1), 2) i)
$$ language sql immutable;

merlin