CODEWITHSUNDEEP
multidimensional-arrayplpgsqlpostgresql-9.0
Eugene
Eugene

Reputation: 11270

pl/pgsql merging or combining arrays

How to merge/combine arrays in pl/pgsql?

For example I have an 3 arrays: {1,2,3}, {"a","b","c"}, and {32,43,23}

After merging I need to get:

{{1,"a",32}, {2,"b",43}, {3,"c",23}}

My version of PostgreSQL is 9.0

Upvotes: 2

Views: 814

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324265

It sounds like you want an n-argument zip function, as found in some functional languages and languages with functional extensions.

In this case you can't do exactly what yu want, because those arrays are of hetrogeneous types. PostgreSQL arrays must be of homogenous type, so this won't work. The desired result you show is an invalid array.

You could create an array of ROWs (anonymous records), or cast all the values to text.

For example:

SELECT array_agg(ROW(a,b,c)) 
FROM (
  SELECT 
    unnest('{1,2,3}'::integer[]),
    unnest('{"a","b","c"}'::text[]),
    unnest('{32,43,23}'::integer[])
)
x(a,b,c);

will produce:

{"(1,a,32)","(2,b,43)","(3,c,23)"}

which is an array of three rowtypes cast to text. It will be awkward to work with because Pg has only very limited support for anonymous records; most importantly in this case you cannot cast a text value to RECORD(integer,text,integer), you must actually CREATE TYPE and cast to the defined type.

Because of that limitation, you may instead want to cast all the values to text and use a two-dimensional array of text. You'd expect to be able to do that with a simple array_agg, but frustratingly this fails:

SELECT array_agg(ARRAY[a::text,b,c::text]) 
FROM (
  SELECT 
    unnest('{1,2,3}'::integer[]),
    unnest('{"a","b","c"}'::text[]),
    unnest('{32,43,23}'::integer[])
)
x(a,b,c);

producing:

ERROR:  could not find array type for data type text[]

because array_agg doesn't support arrays as input. You need to define another variant of array_agg that takes a text[] input. I wrote one a while ago but can't find it now; I'll try to locate it and update if I find it. In the mean time you can work around it by casting the inner array to text:

SELECT array_agg(ARRAY[a::text,b,c::text]::text) 
    FROM (
      SELECT 
        unnest('{1,2,3}'::integer[]),
        unnest('{"a","b","c"}'::text[]),
        unnest('{32,43,23}'::integer[])
    )
    x(a,b,c);

producing output like:

 {"{1,a,32}","{2,b,43}","{3,c,23}"}

... OK, I haven't found the one I wrote, but here's an example from Erwin that does the job fine. Try this:

CREATE AGGREGATE array_agg_mult (anyarray)  (
    SFUNC     = array_cat
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
);

SELECT array_agg_mult(ARRAY[ARRAY[a::text,b,c::text]]) 
FROM (
  SELECT 
    unnest('{1,2,3}'::integer[]),
    unnest('{"a","b","c"}'::text[]),
    unnest('{32,43,23}'::integer[])
)
x(a,b,c);

Output:

 {{1,a,32},{2,b,43},{3,c,23}}

Upvotes: 4

Related Questions