Thread: BUG #6326: SELECT ARRAY(SELECT ...) syntax and array[] results doesn't work

The following bug has been logged on the website:

Bug reference:      6326
Logged by:          Maksym Boguk
Email address:      maxim.boguk@gmail.com
PostgreSQL version: 9.1.1
Operating system:   Linux
Description:=20=20=20=20=20=20=20=20

SELECT ARRAY(SELECT ...)=20
doesn't work when subselect return any array.

Test case:

db=3D# SELECT ARRAY(SELECT 1 UNION ALL SELECT 2);
 ?column?
----------
 {1,2}

All good... now:

db=3D# SELECT ARRAY(SELECT array[1,2]::float[] UNION ALL SELECT
array[3,4]::float[]);
ERROR:  could not find array type for data type double precision[]
db=3D# SELECT ARRAY(SELECT array[1,2]::integer[] UNION ALL SELECT
array[3,4]::integer[]);
ERROR:  could not find array type for data type integer[]

Is that syntax supposed to work with anyarray types?

maxim.boguk@gmail.com writes:
> SELECT ARRAY(SELECT ...)
> doesn't work when subselect return any array.
> Is that syntax supposed to work with anyarray types?

No.

            regards, tom lane

On Mon, Dec 5, 2011 at 2:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> maxim.boguk@gmail.com writes:
> > SELECT ARRAY(SELECT ...)
> > doesn't work when subselect return any array.
> > Is that syntax supposed to work with anyarray types?
>
> No.
>
>                        regards, tom lane
>

Hi.

Thank you very much for answer.

Ok the second problem (possible related to first becuse error is the same):

array_agg doesn't work with anyarray as well:

sports=# select array_agg(val) FROM (SELECT array[1,2]::integer[] as val
UNION ALL SELECT array[3,4]::integer[]) as t;
ERROR:  could not find array type for data type integer[]


PS: I try create two-dimentional integer array from  query results:

select val1::integer, val2::integer from somequery

to get integer[][] with content like:
[[val1-1,val1-2], [val2-1, val2-2], val[3-1, val3-2]... ]

Is there any way to perform that task it with reasonable efficiency?


--
Maxim Boguk
Senior Postgresql DBA.

Maxim Boguk <maxim.boguk@gmail.com> writes:
> PS: I try create two-dimentional integer array from  query results:

Well, you could do that with a suitably defined aggregate having the
signature "agg(anyarray) returns anyarray".  But array_agg has the
signature "array_agg(anyelement) returns anyarray" so you can't use
it on an array input.  2-D arrays are not distinct from 1-D arrays
so far as the type system is concerned.

            regards, tom lane