Re: creating array of integer[] out of query - how?

On Wed, Sep 02, 2009 at 11:50:38AM +0200, Massa, Harald Armin wrote:
> select array(
> > select x from (
> > select array[2,3] as a
> > union
> > select array[3,4] as a ) x);
> >
> > ERROR: could not find array type for datatype record
>
> ... I remember being there before :( arrays of rows are also not available.

Doh, sorry I forgot that that's an 8.4 only. Before that you must
create your own composite type.

> To all: is there a deeper reason why there is no array type for datatype
> record available?

Not enough demand :)

> [1] http://www.postgresql.org/docs/current/static/sql-createtype.html
> >
> > Thanks for the hint with CREATE TYPE, especially the lines
>
> """
> Whenever a user-defined type is created, PostgreSQL automatically creates an
> associated array type,
> """
> fills me with joy. ;)

Try:

CREATE TYPE intarr AS (arr int[]);
SELECT array(
SELECT x::intarr FROM (
SELECT array[2,3]
UNION ALL
SELECT array[3,4]) x(a));

and it should do the right thing in 8.3.

--
Sam http://samason.me.uk/