Thread: is 8.4 array_agg() supposed to work with array values?

Hi,

The docs at http://developer.postgresql.org/pgdocs/postgres/functions-aggregate.html
don't prohibit using array values with array_arg(), so I assumed that it would work.
However, with CVS HEAD from Friday afternoon, I get

test=> select version() ;                                                       version
----------------------------------------------------------------------------------------------------------------------
PostgreSQL8.4devel on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070502 (Red Hat 4.1.2-12), 64-bit
 
(1 row)

test=> select v.a, pg_typeof(v.a) from (values (array[1,2]), (array[3,4])) as v(a) ;   a   | pg_typeof
-------+----------- {1,2} | integer[] {3,4} | integer[]
(2 rows)

test=> select array_agg(v.a) from (values (array[1,2]), (array[3,4])) as v(a) ;
ERROR:  could not find array type for data type integer[]
test=>

If this is expected behavior, the docs should mention the restriction.

-- todd

On Monday 19 January 2009 23:22:21 Todd A. Cook wrote:
> The docs at
> http://developer.postgresql.org/pgdocs/postgres/functions-aggregate.html
> don't prohibit using array values with array_arg(), so I assumed that it
> would work.

> test=> select array_agg(v.a) from (values (array[1,2]), (array[3,4])) as
> v(a) ; ERROR:  could not find array type for data type integer[]

Yeah ... This is one of the weirdnesses of the PostgreSQL array 
implementation.  integer[] and integer[][] etc. are actually the same type, 
just using a different number of dimensions internally.  This would work much 
better if integer[][] where "array of integer[]", in the same way as 
integer[] is "array of integer", in the way C deals with arrays.  This is 
also a main reason why composite types and arrays don't mix orthogonally; 
there is no way to represent that in the system catalogs.  To get back to 
your question, as far as array_agg() itself is concerned, it would probably 
work, but the rest of the sytem doesn't deal with it very well.  You will 
probably be able to find a number of other places that break when trying to 
derive the array type from something that is already an array.

On Tue, Jan 20, 2009 at 5:09 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
> This would work much
> better if integer[][] where "array of integer[]", in the same way as
> integer[] is "array of integer", in the way C deals with arrays.

Well the situation in C is substantially more complicated than you
make out. I think the analogous situation in C is actually having an
array of integer* pointing to separately allocated arrays.

There are a number of places where Postgres has to special-case arrays
and array references (think of "update foo set bar[n] = baz" for
example) so I don't think it would work to just create the array type
for integer[] either.

-- 
greg

On 1/20/09, Peter Eisentraut <peter_e@gmx.net> wrote:
>  integer[] is "array of integer", in the way C deals with arrays.  This is
>  also a main reason why composite types and arrays don't mix orthogonally;
>  there is no way to represent that in the system catalogs.

What do you mean by this exactly?  We've had arrays of composite types
since 8.2.

merlin

On Tuesday 20 January 2009 18:09:51 Merlin Moncure wrote:
> On 1/20/09, Peter Eisentraut <peter_e@gmx.net> wrote:
> >  integer[] is "array of integer", in the way C deals with arrays.  This
> > is also a main reason why composite types and arrays don't mix
> > orthogonally; there is no way to represent that in the system catalogs.
>
> What do you mean by this exactly?  We've had arrays of composite types
> since 8.2.

Yeah, the simple cases work.  I meant it more in a general sense of structured 
and arbitrarily composed types.  I'm also interested in multiset support, for 
example.  Under the current arrangement, you couldn't really have array of 
multiset of array of something.

Peter Eisentraut wrote:
> On Monday 19 January 2009 23:22:21 Todd A. Cook wrote:
>> The docs at
>> http://developer.postgresql.org/pgdocs/postgres/functions-aggregate.html
>> don't prohibit using array values with array_arg(), so I assumed that it
>> would work.
> 
>> test=> select array_agg(v.a) from (values (array[1,2]), (array[3,4])) as
>> v(a) ; ERROR:  could not find array type for data type integer[]
> 
> Yeah ... This is one of the weirdnesses of the PostgreSQL array 
> implementation.  integer[] and integer[][] etc. are actually the same type, 
> just using a different number of dimensions internally.  This would work much 
> better if integer[][] where "array of integer[]", in the same way as 
> integer[] is "array of integer", in the way C deals with arrays.  This is 
> also a main reason why composite types and arrays don't mix orthogonally; 
> there is no way to represent that in the system catalogs.  To get back to 
> your question, as far as array_agg() itself is concerned, it would probably 
> work, but the rest of the sytem doesn't deal with it very well.  You will 
> probably be able to find a number of other places that break when trying to 
> derive the array type from something that is already an array.

Thanks for the clarification.

-- todd