Re: BUG #14626: array_agg( anyarray ) unexpected error with multi-valued single-dimension array

On Wed, Apr 19, 2017 at 2:32 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Wed, Apr 19, 2017 at 1:03 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> david(dot)g(dot)johnston(at)gmail(dot)com writes:
>> > This doesn't, and should since the number of elements in the non-empty
>> array
>> > shouldn't change the dimensionality logic.
>>
>> > SELECT array_agg(CASE WHEN a = ARRAY[]::text[] THEN ARRAY['N/A']::text[]
>> > ELSE a END)
>> > FROM ( VALUES (1, ARRAY[]::text[]), (1, ARRAY['1','2']::text[]) ) vals
>> (v,
>> > a)
>>
>> Why do you think that should work? You're asking array_agg to accumulate
>> a 1-D length-1 array and then a 1-D length-2 array. There's no way to
>> make a rectangular 2-D array out of that, except perhaps by inventing
>> entries which isn't in array_agg's charter.
>>
> ​
> All this and all I really want is a friggin' "array_append / array_concat"
> aggregate function that accepts either scalars or matching "primary
> dimension" arrays - and treats empty arrays as no-ops.
>
>
​In the end I realized that serializing the arrays to text would work just
fine. The extra I/O for converting from and to an actual array type is
immaterial in my situation. It still doesn't remove my actual desire for
an aggregate array_concat type function, and the for a rectangular array is
a bit annoying (i.e., allow non-rectangular and just report an
out-of-bounds error on attempts to explicitly access non-existent elements)
but that's easy enough to toss in with the other peculiarities in this
area.

SELECT unnest(array_agg)::text[]
FROM (
SELECT array_agg(a)
FROM ( VALUES (1, ARRAY[]::text[]::text), (1, ARRAY['1','2']::text[]::text)
) vals (v, a)
) txt_arrays;

David J.